python-mysql数据库链接池

import pymysql

# 多个主机的数据库信息
db_info = {
    'rds': {
        'host': '127.0.0.1',
        'port': 3306,
        'user': 'root',
        'password': '123',
        'db': 'user'
    },
    'ads': {
        'host': '127.0.0.1',
        'port': 3306,
        'user': 'root',
        'password': '123',
        'db': 'host'
    },
    'system': {
        'host': '127.0.0.1',
        'port': 3306,
        'user': 'root',
        'password': '123',
        'db': 'address'
    },
}


class DatabaseConnector:
    """
    数据库连接池类  支持多库多池
    """

    def __init__(self, module, credentials, size=10):
        """
        :param module: 数据库驱动 例如pymysql
        :param credentials: 数据库连接信息
        credentials={
                'DB_INFO' = {
                'host': '-',
                'port': 3306,
                'user': '-',
                'password': '-',
                'db': '-'
            }
        }
        :param size: 每个连接池最大连接数量
        """
        # pymysql 类
        self.__module = module
        # 数据库配置
        self.__credentials = credentials
        # 数据库连接池
        self.__databases = {}
        # 连接池大小
        self.__size = size

    # 获取连接
    def get(self, pool_name):
        # 若rds 不在数据库配置中
        if pool_name not in self.__credentials:
            raise Exception('数据库信息不存在')
        # 若有配置 且 连接池内无 当前rds连接
        elif pool_name not in self.__databases:
            # 初始化连接池
            self.__databases[pool_name] = []
            # 创建连接
            self.create_connection(pool_name)
        # 若有配置 连接在连接池内, 但当前连接无对应对象
        elif not self.__databases[pool_name]:
            # 创建连接
            self.create_connection(pool_name)
        # 取出rds连接池内最后一个对象
        return self.__databases[pool_name].pop()

    # 创建连接
    def create_connection(self, pool_name):
        # 若无配置信息
        if pool_name not in self.__credentials:
            raise Exception('数据库信息不存在')
        # 取出配置信息
        db_info = self.__credentials[pool_name]
        # pymysql.connect(settings) 加载配置信息
        con = self.__module.connect(**db_info)
        # 将连接加入连接池
        self.__databases[pool_name].append(con)

    # 取出连接
    def put(self, pool_name, con):
        # 判断是当前连接与驱动默认类型 是否一致
        if isinstance(con, self.__module.connections.Connection):
            # 如果 当前已超出连接池长度
            if len(self.__databases[pool_name]) > self.__size:
                # 关闭当前连接
                con.close()

            # 未超出长度 则重新放回当前连接
            self.__databases[pool_name].insert(0, con)
        else:
            try:
                con.close()
            except:
                pass


db_pool = DatabaseConnector(pymysql, db_info)


def search_sql_pool(total_sql, pool_name='rds', result_type='dict'):
    """
    多库查询
    :param total_sql: 被执行的sql
    :param pool_name: 连接池名称
    :param db_name: 数据库名
    :param result_type: 返回结果类型  默认字典
    :return:
    """
    # 从数据库连接池池中获取连接池
    db = db_pool.get(pool_name)
    while True:
        try:
            # ping 检验数据库是否连通
            db.ping(False)
            break
        except:
            # 记录日志
            try:
                # 关闭连接
                db.close()
                try:
                    db = db_pool.get(pool_name)
                except:
                    db_pool.put(pool_name, db)
                    db = db_pool.get(pool_name)
                    continue
            except:
                try:
                    db_pool.put(pool_name, db)
                    db = db_pool.get(pool_name)
                    continue
                except:
                    # 再次尝试获取连接
                    db = db_pool.get(pool_name)
                    continue

    if result_type == 'dict':
        try:
            with db.cursor(pymysql.cursors.DictCursor) as cursor:
                # 使⽤execute⽅法执⾏SQL语句
                cursor.execute(total_sql)
                # 获取数据
                data = cursor.fetchall()
            # 提交
            db.commit()
        except Exception as e:
            data = e.args[1]
        finally:
            db_pool.put(pool_name, db)
    # 返回列表类型查询结果
    else:
        try:
            with db.cursor() as cursor:
                # 使⽤execute⽅法执⾏SQL语句
                cursor.execute(total_sql)
                # 获取数据
                data = cursor.fetchall()
            # 确认提交
            db.commit()
        except Exception as e:
            data = e.args[1]
        finally:
            db_pool.put(pool_name, db)
    return data


def search_sql(total_sql):
    """
    RDS查询  返回字典
    :param total_sql:
    :return:
    """
    return search_sql_pool(total_sql, pool_name='rds')