pooleddb mysql_使用dbutils的PooledDB连接池，操作数据库

#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)