Python DBUtils 连接池对象 (PooledDB)

# coding: utf-8
import pymysql
import functools
from seemmo.common.time_func import now_time, today
from conf.config import DB_HOST, DB_PORT, DB_USER, DB_NAME, DB_CHARSET
from pymysql.cursors import DictCursor
from DBUtils.PooledDB import PooledDB
​
# 获取加密的mysql密码
from seemmo.common.utils import get_pwd
DB_PASSWD = get_pwd("mysql_pwd")
​
​
class MysqlPool(PooledDB):
__instance = None
__pool = None
​
def __new__(cls, *args, **kwargs):
if not cls.__instance:
# cls.__instance = object.__new__(cls)
cls.__instance = super().__new__(cls)
return cls.__instance
​
def __init__(self):
"""创建链接池"""
PooledDB.__init__(self, pymysql, maxconnections=50, mincached=0, maxcached=0, maxshared=0, blocking=True, host=DB_HOST, port=DB_PORT,user=DB_USER, password=DB_PASSWD, database=DB_NAME, charset=DB_CHARSET, cursorclass=DictCursor)
​
def conn(self):
_conn = self.connection()
return _conn

# 创建连接池对象
mysql_pool = MysqlPool()
​
​
# mysql 闭包环境
def db_wrap(method):
@functools.wraps(method)
def _wrapper(*args, **kwargs):
conn = mysql_pool.conn()
cursor = conn.cursor()
try:
conn.begin()
retval = method(cursor, *args, **kwargs)
conn.commit()
except Exception as e:
conn.rollback()
raise e
finally:
cursor.close()
conn.close()
return retval
return _wrapper
​
​
@db_wrap
def create_table(cursor, sql):
ret = cursor.execute(sql)
return ret
​
​
@db_wrap
def query_by_index(cursor, table_name, start_index, next_index):
"""
根据 index 范围查询违法记录
:param cursor: 数据库操作游标
:param table_name: 需要操作的表名称
:param start_index: 开始 index
:param next_index: 截止 index
:return: 查询结果
"""
sql = """select * from {} where id > {} and id <= {}""".format(table_name, start_index, next_index)
cursor.execute(sql)
res = cursor.fetchall()
return res