python sql语句封装连接mysql
进行了代码优化，欢迎评审
#!/usr/bin/python
# -*- coding:utf-8 -*-
import logging
logging.basicConfig(level=logging.INFO)
class baselib(json.JSONEncoder):
def __init__(self):
self.conn = mysql.connector.connect(host='192.1.1.1',port=3306,user='root',passwd='123456',db='user',charset="utf8")
def query(self,sql):
try:
self.cursor = self.conn.cursor(dictionary=True) #返回数据为dict
data = self.cursor.execute(sql)
data = self.cursor.fetchone()
data = json.dumps(data,cls=CJsonEncoder).decode("unicode-escape")
except mysql.connector.Error as e:
print ('Error : {}'.format(e))
finally:
print 'Connect wemedia closed in finally'
return data
def GetBitradeSqlAll(self,sql):
try:
self.cursor = self.conn.cursor()
data = self.cursor.execute(sql)
data = self.cursor.fetchall()
data = json.dumps(data,cls=CJsonEncoder).decode("unicode-escape")
except mysql.connector.Error as e:
print ('Error : {}'.format(e))
finally:
print 'Connect wemedia closed in finally'
return data
import mysql.connector
class mysqltest(object):
def __init__(self):
pass
def selectmysql(self,sql):
global conn,cursor
conn = mysql.connector.connect(host='127.0.0.1',port=3306,user='test',passwd='root@test',db='testuser', use_unicode=True)
try:
cursor = conn.cursor()
# 插入一行记录，注意MySQL的占位符是%s:
data = cursor.execute(sql)
# 查询单数据时用
#"%s" '% index
wmid = cursor.fetchone()
# wmid = cursor.fetchall() 查询多数据时使用
except mysql.connector.Error as e:
print ('Error : {}'.format(e))
finally:
print 'Connect wemedia closed in finally'
return wmid
if __name__ == '__main__':
w= mysqltest()
sql = '''select wm_id from wm_owner_info where wm_owner_info.id =59129'''
data = w.selectmysql(sql)
print data
conn.commit()
cursor.close
conn.close
以下为历史版本
#!/usr/bin/python
# -*- coding: UTF-8 -*-
import mysql.connector
class connectMysql(object):
def ExecNonQuery(sql):
conn = MySQLdb.connect(host='xxxx',user='xxxx',passwd='xxxx',db='xxxx')
cursor = conn.cursor()
cursor.execute(sql)
res = cursor.fetchone()
print res
cursor.close
conn.close
#!/usr/bin/python
# -*- coding: UTF-8 -*-
from connectMysql import connectSQl
class diaoyong(object):
def mysql_query(self,i):
sad = connectSQl()
sad.ExecNonQuery('select * from user where id = "%s" ' % i )
if __name__ == '__main__':
w = diaoyong()
w.mysql_query('5')
