pymysql数据库连接池应用

from DBUtils.PooledDB import PooledDB, SharedDBConnection

POOL = PooledDB(
    creator=pymysql,   # 使用链接数据库的模块
    maxconnections=6,  # 连接池允许的最大连接数，0和None表示不限制连接数
    mincached=2,  # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
    maxcached=5,  # 链接池中最多闲置的链接，0和None不限制
    maxshared=3,  # 链接池中最多共享的链接数量，0和None表示全部共享。PS: 无用，因为pymysql和MySQLdb等模块的 threadsafety都为1，所有值无论设置为多少，_maxcached永远为0，所以永远是所有链接都共享。
    blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
    maxusage=None,  # 一个链接最多被重复使用的次数，None表示无限制
    setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
    ping=0, # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
    host='127.0.0.1',  # 数据库服务器IP
    port=3306,  # 数据库服务端口
    user='root',  # 数据库用户名
    password='123456',  # 数据库密码
    database='db2',  # 数据库名
    charset='utf8'  # 数库库编码
    )

DBUtils安装  pip install DBUtils==1.3
可去官网查看与自己python版本相匹配的DBUtils版本号
# https://pypi.org/project/DBUtils/

PoolDB+PyMySQL项目中的封装和应用

# -*- coding: utf-8 -*-
import pymysql
from DBUtils.PooledDB import PooledDB


class MySQLPool(PooledDB):
    def __init__(self, host='localhost', port=3306, user='root', password='*', database='test', charset='utf8'):
        """功能：初始化连接池配置信息"""
        super().__init__(creator=pymysql, maxconnections=6, mincached=2, maxcached=5, maxshared=3, blocking=True,
                         maxusage=None, setsession=[], ping=0,
                         host=host, port=port, user=user, password=password, database=database, charset=charset)

    def __new__(cls, *args, **kwargs):
        """功能：连接池对象保持单例"""
        if not hasattr(cls, '_instance'):
            cls._instance = object.__new__(cls)
        return cls._instance


class MySQLClient:
    def __init__(self, pool):
        """功能：初始化数据配置信息"""
        self.conn = pool.connection()

    def __new__(cls, *args, **kw):
        """功能：连接池保持单例模式"""
        if not hasattr(cls, '_instance'):
            cls._instance = object.__new__(cls)
        return cls._instance

    def select(self, sql_str, fmt_args=None, data_type=pymysql.cursors.Cursor):
        """功能：查询数据，返回（数据列表，字段列表，数据长度）"""
        cursor = self.conn.cursor(cursor=data_type)
        try:
            effect_count = cursor.execute(query=sql_str, args=fmt_args)
            data_lines = list(cursor.fetchall())
            return data_lines, [column[0] for column in cursor.description], effect_count
        except Exception as error:
            raise error
        finally:
            cursor.close()

    def insert(self, sql_str, insert_lines=None, commit=True):
        """功能：插入数据, 返回（受影响的行数，执行插入后最大行ID）"""
        cursor = self.conn.cursor()
        try:
            if insert_lines and isinstance(insert_lines[0], (list, tuple)):
                effect_count = cursor.executemany(query=sql_str, args=insert_lines)
            else:
                effect_count = cursor.execute(query=sql_str, args=insert_lines)
            if commit:
                self.commit()
            return effect_count, cursor.lastrowid
        except Exception as error:
            self.conn.rollback()
            raise error
        finally:
            cursor.close()

    def execute(self, sql_str, fmt_args=None, commit=True):
        """功能：删除数据或修改数据，返回（受影响的行数）"""
        cursor = self.conn.cursor()
        try:
            count = cursor.execute(sql_str, args=fmt_args)
            if commit:
                self.commit()
            return count
        except Exception as error:
            self.conn.rollback()
            raise error
        finally:
            cursor.close()

    def commit(self):
        """功能：提交数据到数据库"""
        self.conn.commit()

    def close(self):
        """功能：关闭数据库连接"""
        self.conn.close()

    def __enter__(self):
        """功能：返回给as后变量"""
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        """功能：断开数据库连接"""
        self.close()