#coding=utf-8
"""使用DBUtils数据库连接池中的连接,操作数据库
OperationalError: (2006, ‘MySQL server has gone away’)"""
importjsonimportpymysqlimportdatetimefrom DBUtils.PooledDB importPooledDBimportpymysqlclassMysqlClient(object):__pool =None;def __init__(self, mincached=10, maxcached=20, maxshared=10, maxconnections=200, blocking=True,
maxusage=100, setsession=None, reset=True,
host=‘127.0.0.1‘, port=3306, db=‘test‘,
user=‘root‘, passwd=‘123456‘, charset=‘utf8mb4‘):if not self.__pool:
self.__class__.__pool =PooledDB(pymysql,
mincached, maxcached,
maxshared, maxconnections, blocking,
maxusage, setsession, reset,
host=host, port=port, db=db,
user=user, passwd=passwd,
charset=charset,
cursorclass=pymysql.cursors.DictCursor
)
self._conn=None
self._cursor=None
self.__get_conn()def __get_conn(self):
self._conn= self.__pool.connection();
self._cursor=self._conn.cursor();def __close(self):try:
self.cursor.close()
self.conn.close()exceptException as e:printedef __execute(self, sql, param=()):
count=self._cursor.execute(sql, param)printcountreturncount
@staticmethoddef __dict_datetime_obj_to_str(result_dict):"""把字典里面的datatime对象转成字符串,使json转换不出错"""
ifresult_dict:
result_replace= {k: v.__str__() for k, v in result_dict.items() ifisinstance(v, datetime.datetime)}
result_dict.update(result_replace)returnresult_dictdef select_one(self, sql, param=()):"""查询单个结果"""count= self.__execute(sql, param)
result=self._cursor.fetchone()""":type result:dict"""result= self.__dict_datetime_obj_to_str(result)returncount, resultdef select_many(self, sql, param=()):"""查询多个结果
:param sql: qsl语句
:param param: sql参数
:return: 结果数量和查询结果集"""count= self.__execute(sql, param)
result=self._cursor.fetchall()""":type result:list"""[self.__dict_datetime_obj_to_str(row_dict) for row_dict inresult]returncount, resultdefexecute(self, sql, param):
count= self.__execute(sql, param)returncountdefbegin(self):"""开启事务"""self._conn.autocommit(0)def end(self, option=‘commit‘):"""结束事务"""
if option == ‘commit‘:
self._conn.autocommit()else:
self._conn.rollback()if __name__ == "__main__":
mc=MysqlClient()
sql1= ‘SELECT * FROM shijiji WHERE id = 1‘result1=mc.select_one(sql1)print json.dumps(result1[1], ensure_ascii=False)
sql2= ‘SELECT * FROM shijiji WHERE id IN (%s,%s,%s)‘param= (2, 3, 4)print json.dumps(mc.select_many(sql2, param)[1], ensure_ascii=False)