python 之数据库连接池

一、连接池的作用

1、优势(省时间、省资源、稳定、简化)

省时间：建立数据库连接（含 TCP 握手、认证等）通常耗时 20-100ms。连接池通过复用连接，避免重复开销，高并发下 QPS 可提升 5-10 倍。

省资源：数据库支持的并发连接数有限（如 MySQL 默认 151）。设置最大连接数，防止连接过多导致数据库崩溃。

增强稳定性：支持连接超时、空闲回收、断线重连、健康检查等机制，比手动管理连接更可靠（需合理配置，如启用 pre-ping）。

简化开发：连接的获取与归还被封装，结合 with 语句可自动管理生命周期，避免遗漏 close() 导致连接泄露。

2、数据库并发设置

# 配置文件
max_connections = 1000

3、查看当前已使用的连接数

SHOW STATUS LIKE 'Threads_connected';

二、连接池的常用方案

1、DBUtils 

适用所有 DB-API 2.0，轻量级、纯 Python 实现，灵活通用

DBUtils是一组Python数据库工具，提供两种连接池：

• PersistentDB：为每个线程提供持久连接

• PooledDB：共享连接池，适配 MySQLdb / pymysql 等驱动

1、安装

pip install pymysql DBUtils

2、案例

 创建连接池
pool = PooledDB(
    creator=pymysql,  # 使用 pymysql
    maxconnections=10,  # 连接池最大连接数
    mincached=2,        # 初始化连接数
    blocking=True,      # 无连接时是否等待
    host='localhost',
    port=3306,
    user='root',
    password='password',
    database='test_db',
    charset='utf8mb4'
)

# 从连接池获取连接
def query_data():
    conn = pool.connection()  # 自动复用或创建连接
    cursor = conn.cursor()
    try:
        cursor.execute("SELECT * FROM users LIMIT 5")
        result = cursor.fetchall()
        for row in result:
            print(row)
    finally:
        cursor.close()
        conn.close()  # 实际上是将连接放回连接池

if __name__ == '__main__':
    query_data()　

2、SQLAlchemy

适用所有主流数据库，ORM 框架，内置连接池支持

from sqlalchemy import create_engine
from sqlalchemy.pool import QueuePool

# 创建带连接池的引擎
engine = create_engine(
    'mysql+pymysql://user:password@localhost/mydb',
    poolclass=QueuePool,  # 使用队列池
    pool_size=5,         # 连接池中保持的连接数
    max_overflow=10,     # 超出pool_size时允许创建的最大连接数
    pool_timeout=30,     # 获取连接的超时时间(秒)
    pool_recycle=3600    # 连接自动回收时间(秒)
)

# 使用连接
with engine.connect() as conn:
    result = conn.execute("SELECT * FROM users")
    for row in result:
        print(row)

补充：

1、with engine.begin() 用法 适合需要事物的场景

   engine.begin() 会开启一个事务，在只读查询场景下是多余的。

2、查询只读数据用  engine.connect() as conn:

3、 echo=True,  # 打印 SQL 日志

engine = create_engine(
    "mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8mb4",
    echo=True,  # 打印 SQL 日志
    pool_size=10,
    max_overflow=20,
    pool_recycle=3600
)

3、asyncpg + asyncio

适用PostgreSQL ，支持异步连接池

4、aiomysql的连接池 (异步)

适用mysql，异步连接池方案

import asyncio
from aiomysql import create_pool

async def main():
    # 创建连接池
    pool = await create_pool(
        host='localhost',
        user='user',
        password='password',
        db='mydb',
        minsize=1,    # 最小连接数
        maxsize=10   # 最大连接数
    )
    
    # 使用连接
    async with pool.acquire() as conn:
        async with conn.cursor() as cursor:
            await cursor.execute("SELECT * FROM users")
            result = await cursor.fetchall()
            for row in result:
                print(row)
    
    # 关闭连接池
    pool.close()
    await pool.wait_closed()

asyncio.run(main())

5、psycopg2的连接池 (PostgreSQL专用)

from psycopg2 import pool

# 创建连接池
postgresql_pool = pool.SimpleConnectionPool(
    minconn=1,      # 最小连接数
    maxconn=10,     # 最大连接数
    host='localhost',
    user='user',
    password='password',
    database='mydb'
)

# 使用连接
conn = postgresql_pool.getconn()
try:
    with conn.cursor() as cursor:
        cursor.execute("SELECT * FROM users")
        for record in cursor:
            print(record)
finally:
    postgresql_pool.putconn(conn)  # 放回连接池

asyncpg的连接池 (PostgreSQL异步)

import asyncpg
import asyncio

async def main():
    # 创建连接池
    pool = await asyncpg.create_pool(
        host='localhost',
        user='user',
        password='password',
        database='mydb',
        min_size=1,    # 最小连接数
        max_size=10    # 最大连接数
    )
    
    # 使用连接
    async with pool.acquire() as conn:
        rows = await conn.fetch("SELECT * FROM users")
        for row in rows:
            print(row)
    
    # 关闭连接池
    await pool.close()

asyncio.run(main())