#!/bin/env python#-*- coding:utf-8 -*-#@Time : 2020/11/19 10:00#@Author: wangbo#@File : mysql_DBUtils.py
importjsonimportdatetimefrom dbutils.pooled_db importPooledDBimportpymysqlclassMysqlClient(object):__pool =None;def __init__(self, mincached=3, maxcached=5, maxshared=5, maxconnections=5, blocking=True,
maxusage=100, setsession=None, reset=True,
host='192.168.1.7', port=33064, db='deploy',
user='depUsr', passwd='deploY123', 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();defclose(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, resultdef execute(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 dbauto_server_token WHERE id = 50'result1=mc.select_one(sql1)print json.dumps(result1[1], ensure_ascii=False)
sql2= 'SELECT * FROM dbauto_server_token WHERE id IN (%s,%s,%s)'param= (49, 50, 51)print json.dumps(mc.select_many(sql2, param)[1], ensure_ascii=False)