python dbutils库数据库连接池管理

python dbutils库数据库连接池管理

1、 安装

支持 Python 3.7 到 3.12 版本
安装命令：pip install DBUtils

2、 使用

#导入PooledDB模块
from dbutils.pooled_db import PooledDB  
import pymysql
#MySQL 示例
config = {
            'host': host,
            'user': user,
            'password': password,
            'port' : port ,
            'database': db,
            'charset': 'utf8mb4',
            'cursorclass': pymysql.cursors.DictCursor,
            #'conv' : conv ,
            'mincached': 1,  # 初始化时，连接池中至少创建的空闲的数据库连接，0表示不创建
            'maxcached': 2,  # 连接池中允许的最大空闲连接数，0和None表示不限制连接池大小
            'maxshared': 0,  # 连接池中最多共享的连接数量，0和None表示全部连接都可以共享
            'maxconnections': 50,  # 最大允许创建的连接数，包括连接池中的空闲连接，0和None表示不限制连接数
            'blocking': True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
            'maxusage': None,  # 一个连接最多被重复使用的次数，None表示无限制
            'setsession': [],  # 开始会话前执行的命令列表
        }
        db_pool = PooledDB(pymysql, **config)
  
#oracle 示例
dsn = cx_Oracle.makedsn(host, port, service_name=service_name)
        db_pool = PooledDB(
            creator=cx_Oracle,
            dsn=dsn,
            user=user,
            password=password,
            maxconnections=10,  # 连接池允许的最大连接数
            mincached=2,  # 初始化时，链接池中至少创建的空闲的链接
            maxcached=9,  # 链接池中最多闲置的链接
            maxshared=10,  # 链接池中最大共享的链接数量
            blocking=True  # 连接池中如果没有可用连接后，是否阻塞等待
        )
   # PG库示例
   db_pool = PooledDB(
        creator=psycopg2,  # 使用链接数据库的模块
        maxconnections=6,  # 连接池允许的最大连接数，0和None表示不限制连接数
        mincached=2,  # 初始化时，链接池中至少创建的链接，0表示不创建
        blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
        ping=0,     # ping SQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
        host = host,
        port = port,
        user = user,
        password = password,
        database = db)
   # 从连接池取连接
   conn = db_pool.connection()  # 从连接池里取一个连接
   cursor = conn.cursor()
        try:
            cursor.execute(sql)
            result = cursor.fetchall()
            if result:
                for row in result:
                    for key, value in row.items():
                        if isinstance(value, Decimal):
                            row[key] = int(value)
            cursor.close()
            conn.close()
            if result is not None:
                return result
            else:
                # 如果没有结果或发生异常，可以选择返回None或False，具体取决于你的逻辑
                return None  # 或者 return False
        except Exception as e:
            print(sql)
            print(f"An error occurred: {e}")
            # 在这里处理异常，例如记录日志或返回错误消息
            cursor.close()
            conn.close()
            return False

3、封装成类使用

3.1 oracle示例

# -*- coding: utf-8 -*-
from dbutils.pooled_db import PooledDB
import cx_Oracle
from prettytable import from_db_cursor

class OracleDB:
    def __init__(self,user,host, password, port,service_name):
        """
        初始化数据库连接
        :param host: 数据库主机地址
        :param user: 数据库用户名
        :param password: 数据库密码
        :param db: 数据库名
        建立数据库连接
        """
        # 配置数据库连接参数
        dsn = cx_Oracle.makedsn(host, port, service_name=service_name)
        '''# 创建一个连接池
        self.db_pool = cx_Oracle.SessionPool(user, password, dsn, min=1, max=20, increment=1,
                                             homogeneous=True,
                                             encoding="UTF-8",threaded=True, getmode=cx_Oracle.SPOOL_ATTRVAL_WAIT)'''
        self.db_pool = PooledDB(
            creator=cx_Oracle,
            dsn=dsn,
            user=user,
            password=password,
            maxconnections=10,  # 连接池允许的最大连接数
            mincached=2,  # 初始化时，链接池中至少创建的空闲的链接
            maxcached=9,  # 链接池中最多闲置的链接
            maxshared=10,  # 链接池中最大共享的链接数量
            blocking=True  # 连接池中如果没有可用连接后，是否阻塞等待
        )

    def query(self, sql):
        # 以字典格式返回DB数据，[{"col2":"row1_val1","col2":"row1_val2"},{"col1":"row2_val1","col2":"row2_val2"}]
        conn = self.db_pool.connection()
        cursor = conn.cursor()
        try:
            cursor.execute(sql)
            result = cursor.fetchall()
            list_result = []
            if result:
                for i in result:
                    list_list = list(i)
                    des = cursor.description  # 获取表详情，字段名，长度，属性等
                    t = ",".join([item[0] for item in des])
                    table_head = t.split(',')  # # 查询表列名 用,分割
                    dict_result = dict(zip(table_head, list_list))  # 打包为元组的列表 再转换为字典
                    list_result.append(dict_result)  # 将字典添加到list_result中
            cursor.close()
            conn.close()
            return list_result
        except Exception as e:
            print(f"exec {sql} An error occurred: {e}")
            cursor.close()
            conn.close()
            return False

    def query_table(self,sql):
    	"""
    	按prettytable格式返回查询结果，适用仅查询，展示格式比较友好
    	"""
        try:
            conn = self.db_pool.connection()  # 从连接池里取一个连接
            cursor = conn.cursor()
            cursor.execute(sql)
            table = from_db_cursor(cursor)
            table.title = sql
            return table
        except Exception as e:
            print(sql)
            print(f"An error occurred: {e}")
            # 在这里处理异常，例如记录日志或返回错误消息
            return False

    def execute(self, sql):
        """
        单次执行非查询操作（如INSERT, UPDATE, DELETE）
        """
        conn = self.db_pool.connection()
        cursor = conn.cursor()
        try:
            cursor.execute(sql)
            conn.commit()
            cursor.close()
            conn.close()
        except Exception as e:
            print(f"exec {sql} An error occurred: {e}")
            # 在这里处理异常，例如记录日志或返回错误消息
            cursor.close()
            conn.close()
            return False

    def bat_execute(self, sql_list):
        """
        批量执行sql，update\delete\insert操作，按2000条 commit一次
        """
        conn = self.db_pool.connection()
        cursor = conn.cursor()
        count = 0
        for sql in sql_list:
            try:
                cursor.execute(sql)
                count += 1
                if count % 2000 == 0:
                    conn.commit()
                    count = 0
            except Exception as e:
                print(f"exec {sql} An error occurred: {e}")
                conn.rollback()
                conn.close()
                continue
        if count > 0:
            conn.commit()
        cursor.close()
        conn.close()
        #return cursor.rowcount

    def close(self):
        """
        关闭数据库连接
        """
        self.db_pool.close()

3.2 mysql示例

# -*- coding: utf-8 -*-
from decimal import Decimal
import pymysql
from pymysql import converters,FIELD_TYPE
from dbutils.pooled_db import PooledDB
from prettytable import from_db_cursor

class MysqlPool:
    def __init__(self, user,host, password,port, db):
        """
        初始化数据库连接
        :param host: 数据库主机地址
        :param user: 数据库用户名
        :param password: 数据库密码
        :param db: 数据库名
        """
        '''conv = converters.conversions
        conv[FIELD_TYPE.NEWDECIMAL] = int  # convert decimals to float
        conv[FIELD_TYPE.DATE] = str  # convert dates to strings
        conv[FIELD_TYPE.TIMESTAMP] = str  # convert dates to strings
        conv[FIELD_TYPE.DATETIME] = str  # convert dates to strings
        conv[FIELD_TYPE.TIME] = str  # convert dates to strings'''
        # 配置数据库连接参数
        config = {
            'host': host,
            'user': user,
            'password': password,
            'port' : port ,
            'database': db,
            'charset': 'utf8mb4',
            'cursorclass': pymysql.cursors.DictCursor,
            #'conv' : conv ,
            'mincached': 1,  # 初始化时，连接池中至少创建的空闲的数据库连接，0表示不创建
            'maxcached': 2,  # 连接池中允许的最大空闲连接数，0和None表示不限制连接池大小
            'maxshared': 0,  # 连接池中最多共享的连接数量，0和None表示全部连接都可以共享
            'maxconnections': 50,  # 最大允许创建的连接数，包括连接池中的空闲连接，0和None表示不限制连接数
            'blocking': True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
            'maxusage': None,  # 一个连接最多被重复使用的次数，None表示无限制
            'setsession': [],  # 开始会话前执行的命令列表
        }
        self.db_pool = PooledDB(pymysql, **config)


    def query(self, sql):
        """
        执行查询操作
        :return: 查询结果列表
        """
        conn = self.db_pool.connection()  # 从连接池里取一个连接
        cursor = conn.cursor()
        try:
            cursor.execute(sql)
            result = cursor.fetchall()
            if result:
                for row in result:
                    for key, value in row.items():
                        if isinstance(value, Decimal):
                            row[key] = int(value)
            cursor.close()
            conn.close()
            if result is not None:
                return result
            else:
                # 如果没有结果或发生异常，可以选择返回None或False，具体取决于你的逻辑
                return None  # 或者 return False
        except Exception as e:
            print(sql)
            print(f"An error occurred: {e}")
            # 在这里处理异常，例如记录日志或返回错误消息
            cursor.close()
            conn.close()
            return False

    def query_table(self,sql):
        conn = self.db_pool.connection()  # 从连接池里取一个连接
        cursor = conn.cursor()
        try:
            cursor.execute(sql)
            mytable = from_db_cursor(cursor)
            return mytable
        except Exception as e:
            print(sql)
            print(f"An error occurred: {e}")
            # 在这里处理异常，例如记录日志或返回错误消息
            cursor.close()
            conn.close()
            return False

    def execute(self, sql, params=None):
        """
        执行非查询操作（如INSERT, UPDATE, DELETE）
        # execute("insert into user(id, name, password) values (?, ?, ?)", [2, "李四", "123456"])
        """
        conn = self.db_pool.connection()
        cursor = conn.cursor()
        try:
            cursor.execute(sql, params)
        except Exception as e:
            print(f"An error occurred: {e}")
            # 在这里处理异常，例如记录日志或返回错误消息
            cursor.close()
            conn.close()
            return False
        conn.commit()
        cursor.close()
        conn.close()

    def bat_execute(self, sql_list):
        """
        执行非查询操作（如INSERT, UPDATE, DELETE）
        """
        conn = self.db_pool.connection()
        cursor = conn.cursor()
        count = 0
        for sql in sql_list:
            try:
                cursor.execute(sql)
            except Exception as e:
                print(sql)
                print(f"An error occurred: {e}")
                conn.rollback()
                cursor.close()
                conn.close()
                return False
            count += 1
            if count % 2000 == 0:
                conn.commit()
                count = 0
        if count > 0:
            conn.commit()
        cursor.close()
        conn.close()

3.3 PG库示例

# -*- coding: utf-8 -*-
# @Time    : 2024/7/5 上午11:14
# @Author  : xuxin3
# @File    : Postgres_Pool.py
# @Software: PyCharm

import psycopg2
from dbutils.pooled_db import PooledDB
from prettytable import from_db_cursor
import pandas as pd

class Postgres:

    def __init__(self, user,host, password,port, db):
        """
        初始化数据库连接
        :param host: 数据库主机地址
        :param user: 数据库用户名
        :param password: 数据库密码
        :param db: 数据库名
        """
        '''conv = converters.conversions
        conv[FIELD_TYPE.NEWDECIMAL] = int  # convert decimals to float
        conv[FIELD_TYPE.DATE] = str  # convert dates to strings
        conv[FIELD_TYPE.TIMESTAMP] = str  # convert dates to strings
        conv[FIELD_TYPE.DATETIME] = str  # convert dates to strings
        conv[FIELD_TYPE.TIME] = str  # convert dates to strings'''
        # 配置数据库连接参数
        self.db_pool = PooledDB(
        creator=psycopg2,  # 使用链接数据库的模块
        maxconnections=6,  # 连接池允许的最大连接数，0和None表示不限制连接数
        mincached=2,  # 初始化时，链接池中至少创建的链接，0表示不创建
        blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
        ping=0,     # ping SQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
        host = host,
        port = port,
        user = user,
        password = password,
        database = db)

    def query(self, sql):
        # 以字典格式返回DB数据
        try:
            conn = self.db_pool.connection()
            cursor = conn.cursor()
            cursor.execute(sql)
            result = cursor.fetchall()
            list_result = []
            if result:
                for i in result:
                    list_list = list(i)
                    des = cursor.description  # 获取表详情，字段名，长度，属性等
                    t = ",".join([item[0] for item in des])
                    table_head = t.split(',')  # # 查询表列名 用,分割
                    dict_result = dict(zip(table_head, list_list))  # 打包为元组的列表 再转换为字典
                    list_result.append(dict_result)  # 将字典添加到list_result中
            cursor.close()
            conn.close()
            return list_result
        except Exception as e:
            print(f"An error occurred: {e}")
            return False

    def query_table(self,sql):
        try:
            conn = self.db_pool.connection()  # 从连接池里取一个连接
            cursor = conn.cursor()
            cursor.execute(sql)
            table = from_db_cursor(cursor)
            table.title = sql
            return table
        except Exception as e:
            print(sql)
            print(f"An error occurred: {e}")
            # 在这里处理异常，例如记录日志或返回错误消息
            return False

    def query_dataFrame(self,sql):
        try:
            conn = self.db_pool.connection()  # 从连接池里取一个连接
            cursor = conn.cursor()
            cursor.execute(sql)
            df = pd.DataFrame(cursor.fetchall(), columns=[desc[0] for desc in cursor.description])
            return df
        except Exception as e:
            print(sql)
            print(f"An error occurred: {e}")
            # 在这里处理异常，例如记录日志或返回错误消息
            return False

    def execute(self, sql, params=None):
        """
        执行非查询操作（如INSERT, UPDATE, DELETE）
        """
        conn = self.db_pool.connection()
        cursor = conn.cursor()
        try:
            cursor.execute(sql, params)
            conn.commit()
            cursor.close()
            conn.close()
        except Exception as e:
            print(sql)
            print(f"An error occurred: {e}")
            # 在这里处理异常，例如记录日志或返回错误消息
            cursor.close()
            conn.close()
            return False


    def bat_execute(self, sql_list):
        """
        执行非查询操作（如INSERT, UPDATE, DELETE）
        """
        conn = self.db_pool.connection()
        cursor = conn.cursor()
        count = 0
        for sql in sql_list:
            try:
                cursor.execute(sql)
            except Exception as e:
                print(sql)
                print(f"An error occurred: {e}")
                conn.rollback()
                cursor.close()
                conn.close()
                return False
            count += 1
            if count % 2000 == 0:
                conn.commit()
                count = 0
        if count > 0:
            conn.commit()
        cursor.close()
        conn.close()

4 测试

if __name__ == "__main__":
    oracle= OracleDB(oracle_info ['user'], oracle_info ['host'], oracle_info ['password'], int(oracle_info ['port']), oracle_info ['database'])
    sql = 'select sysdate from dual'
    print(oracle.query_table(sql))