编辑一个极简单的python数据库连接池

基于Pymssql

为什么要写这个

Emmm主要还是网上说的DBPool库看的我觉得复杂
其次为sqlserver2008基本上orm库都不支持所以只能用pymssql
使用pymssql过程中,如果链接超过8个小时会被sqlserver强行掐断

怎么做

将pymssql链接缓存, 并记录链接时间,超过6小时则舍弃
使用简单的散列表来进行链接的管理

难点

对于大量并发请求和我多线程循环进行大量数据处理的时候 还在测试是否有问题

代码

数据库链接对象

'''
EML_config = {
    'host': '172.18.12.200',
    'user': 'client',
    'password': 'a1122330',
    'database': 'BI_DATA',
    'timeout': 3,
    'charset': 'CP936',
    'login_timeout': 2,
}
'''
class DB_OBJECT():
    conn = None
    is_lock = False
    as_dict = True
    init_time = None
    database = ''

    def __init__(self, db_config: dict):
        self.conn = pymssql.connect(**db_config)
        self.init_time = dt.datetime.now()
        self.database = db_config.get('database')

    def lock(self):
        with lock:
            self.is_lock = True

    def __unlock(self):
        with lock:
            self.is_lock = False

    def isLock(self):
        return self.is_lock

    def setGetDict(self, as_dict: bool):
        self.as_dict = as_dict

    def isExpired(self) -> bool:
        """
        一般8个小时,SqlServer就会断开链接
        :return: if True 则丢弃
        """
        if dt.datetime.now() - self.init_time > dt.timedelta(hours=4):
            return True
        return False

    def close(self):
        if self.conn and not self.isLock():
            self.lock()
            self.conn.close()
            self.conn = None

    def __enter__(self):
        """
        pymssql 在获取游标的时候会自动在加上BEGIN TRAN, 要做事务处理就必须要手动进行事务
        :return:
        """
        return self.conn.cursor(as_dict=self.as_dict)

    def __exit__(self, exc_type, exc_val, exc_tb):
        if exc_type:
            self.conn.rollback()
            logger.warning('baseutil rollback: exc_val: {}'.format(exc_val))
        else:
            self.conn.commit()
        self.__unlock()

链接池管理对象

class DB_POOL():
    pool_list = None
    pool_id_list = None  # now_length
    pool_id_set = None
    max_length = None
    db_config = None

    def __init__(self, max_length: int, db_config: dict):
        """
        :max_length 连接池保持链接对象长度
        :base 初始化base
        """
        self.pool_list = []
        self.pool_id_list = []  # now_length
        self.pool_id_set = set()
        self.db_config = db_config
        self.max_length = max_length

    def __append(self):
        """
        根据连接池初始化的信息, 自增db对象
        :return:
        """
        if not self.db_config:
            raise ConnectionError('未初始化db链接对象参数')
        if len(self.pool_id_set) >= self.max_length:
            dbs_id = self.pool_id_list.pop(0)
            self.pool_id_set.remove(dbs_id)  # pool_id_set 集合为当前可用链接id标识
        db = DB_OBJECT(db_config=self.db_config)
        self.pool_list.append(db)
        self.pool_id_list.append(id(db))
        self.pool_id_set.add(id(db))
        logger.info('append pool database {}'.format(id(db)))

    def clearExpiredDB(self, clear_all=False):
        """
        删除连接池中不存在于集合中的db
        每次只会删除一个, 删除一个后会自增一个, 以免删的太多导致http请求过多
        :return:
        """
        for index, db in enumerate(self.pool_list):
            if id(db) not in self.pool_id_set and not db.isLock():
                logger.info('remove pool database {}'.format(id(db)))
                temp_db = self.pool_list.pop(index)
                temp_db.close()
                self.__append()
                if not clear_all:
                    return

    def getConn(self, as_dict=True):
        """
        主要操作
        每次获取链接的时候, 先遍历对象是否都被锁上
        然后找到没有被锁上的, 先检测是否过期, 没有过期就将db的引用返回出去
        如果都被锁上了, 则使用__append新建一个db对象, 并删除最早的db对象在集合中的id
        下次获取的时候就会删除掉不在集合中的db
        :return:
        """
        with lock:
            logger.info('now all database : {}'.format(self.pool_id_set))

            if not self.db_config:
                raise ConnectionError('未初始化db链接对象参数')
            self.clearExpiredDB()
            # if not self.pool_list:
            for index, db in enumerate(self.pool_list):
                if db.isLock(): continue
                if db.isExpired():
                    if id(db) in self.pool_id_set:
                        db.lock()
                        self.pool_id_set.remove(id(db))
                    continue
                else:
                    db.lock()
                    db.setGetDict(as_dict)
                    return db
            # if not self.pool_list:
            self.__append()
            return self.getConn()

链接池的使用

class BaseDao():
    def __init__(self, base='ch'):
        self.db_poll = DB_POOL(max_length=5, base=base)
        
    @catch_sql_except
    def carry_exec(self, sql, *args, log=False):
        if log:
            app.logger.info(f'{sql}  para -->{args}')
        with self.db_poll.getConn() as c:
            c.execute(sql, args)
            if c.rowcount == 0:
                return False
            else:
                return True

    @catch_sql_except
    def carry_insert(self, sql, *args, log=False):
        if log:
            app.logger.info(f'{sql}  para -->{args}')
        with self.db_poll.getConn() as c:
            c.execute(sql, args)
            if c.rowcount == 0:
                return None
            else:
                return c.lastrowid

    @catch_sql_except
    def carry_exec_return(self, sql, *args, log=False):
        if log:
            app.logger.info(f'{sql}  para -->{args}')
        with self.db_poll.getConn() as c:
            c.execute(sql, args)
            result = c.fetchall()
        return result

    @catch_sql_except
    def carry_exec_kwargs(self, sql, log=False, **kwargs):
        if log:
            app.logger.info(f'{sql}  para -->{kwargs}')
        with self.db_poll.getConn() as c:
            c.execute(sql, kwargs)
            if c.rowcount == 0:
                return False
            else:
                return True

    @catch_sql_except
    def carry_exec_return_kwargs(self, sql, log=False, **kwargs):
        if log:
            app.logger.info(f'{sql}  para -->{kwargs}')
        with self.db_poll.getConn() as c:
            c.execute(sql, kwargs)
            result = c.fetchall()
        return result