|
|
"""
|
|
|
使用DBUtils数据库连接池中的连接，操作数据库
|
|
|
OperationalError: (2006, ‘MySQL server has gone away’)
|
|
|
"""
|
|
|
import datetime
|
|
|
import json
|
|
|
|
|
|
import pymysql
|
|
|
from dbutils.pooled_db import PooledDB
|
|
|
|
|
|
|
|
|
class MysqlClient(object):
|
|
|
__pool = None
|
|
|
|
|
|
def __init__(self, mincached=10, maxcached=20, maxshared=10, maxconnections=200, blocking=True,
|
|
|
maxusage=100, setsession=None, reset=True,
|
|
|
host="10.10.14.199", port=22066, db="dsideal_db",
|
|
|
user="root", passwd="DsideaL147258369", charset='utf8mb4'):
|
|
|
"""
|
|
|
:param mincached:连接池中空闲连接的初始数量
|
|
|
:param maxcached:连接池中空闲连接的最大数量
|
|
|
:param maxshared:共享连接的最大数量
|
|
|
:param maxconnections:创建连接池的最大数量
|
|
|
:param blocking:超过最大连接数量时候的表现，为True等待连接数量下降，为false直接报错处理
|
|
|
:param maxusage:单个连接的最大重复使用次数
|
|
|
:param setsession:optional list of SQL commands that may serve to prepare
|
|
|
the session, e.g. ["set datestyle to ...", "set time zone ..."]
|
|
|
:param reset:how connections should be reset when returned to the pool
|
|
|
(False or None to rollback transcations started with begin(),
|
|
|
True to always issue a rollback for safety's sake)
|
|
|
:param host:数据库ip地址
|
|
|
:param port:数据库端口
|
|
|
:param db:库名
|
|
|
:param user:用户名
|
|
|
:param passwd:密码
|
|
|
:param charset:字符编码
|
|
|
"""
|
|
|
|
|
|
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()
|
|
|
except Exception as e:
|
|
|
print(e)
|
|
|
|
|
|
def __execute(self, sql, param=()):
|
|
|
count = self._cursor.execute(sql, param)
|
|
|
# print(count)
|
|
|
self._conn.commit()
|
|
|
return count
|
|
|
|
|
|
@staticmethod
|
|
|
def __dict_datetime_obj_to_str(result_dict):
|
|
|
"""把字典里面的datatime对象转成字符串，使json转换不出错"""
|
|
|
if result_dict:
|
|
|
result_replace = {k: v.__str__() for k, v in result_dict.items() if isinstance(v, datetime.datetime)}
|
|
|
result_dict.update(result_replace)
|
|
|
return result_dict
|
|
|
|
|
|
def find(self, sql, param=()):
|
|
|
"""查询单个结果"""
|
|
|
count = self.__execute(sql, param)
|
|
|
result = self._cursor.fetchone()
|
|
|
""":type result:dict"""
|
|
|
result = self.__dict_datetime_obj_to_str(result)
|
|
|
return count, result
|
|
|
|
|
|
def findList(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 in result]
|
|
|
return count, result
|
|
|
|
|
|
def execute(self, sql, param=()):
|
|
|
count = self.__execute(sql, param)
|
|
|
return count
|
|
|
|
|
|
def begin(self):
|
|
|
"""开启事务"""
|
|
|
self._conn.autocommit(0)
|
|
|
|
|
|
def end(self, option='commit'):
|
|
|
"""结束事务"""
|
|
|
if option == 'commit':
|
|
|
self._conn.autocommit()
|
|
|
else:
|
|
|
self._conn.rollback()
|
|
|
|
|
|
|
|
|
if __name__ == "__main__":
|
|
|
|
|
|
# 调用示例
|
|
|
db = MysqlClient()
|
|
|
sql1 = 'SELECT * FROM t_sys_loginperson limit 1'
|
|
|
result1 = db.find(sql1)
|
|
|
print(json.dumps(result1[1], ensure_ascii=False))
|
|
|
|
|
|
sql2 = 'SELECT * FROM t_cdwl_subject WHERE subject_id IN (%s,%s,%s)'
|
|
|
param = (6, 8, 9)
|
|
|
|
|
|
sql = 'select resource_type_name,c from t_huanghai_pie'
|
|
|
l1 = db.findList(sql)
|
|
|
keys = []
|
|
|
values = []
|
|
|
for i in range(0, l1[0]):
|
|
|
keys.append(l1[1][i]['resource_type_name'])
|
|
|
values.append(l1[1][i]['c'])
|
|
|
|
|
|
# print(json.dumps(db.findList(sql2, param)[1], ensure_ascii=False))
|
