pymysqlpool踩坑和应用

上周部署一个web服务时用到了多线程操作mysql数据库，虽然数据可以操作入库成功，但是一直报错‘pymysql.err.InterfaceError: (0, '')’。查了下资料是因为多线程操作引起的报错，需要使用连接池管理数据库连接。

chatGPT也给出了以下回答：

你可以使用连接池库（如pymysqlpool、DBUtils等）来实现连接池。以下是一个示例代码，使用pymysqlpool实现数据库连接池：

import pymysql
from pymysqlpool import ConnectionPool

class Database:
    def __init__(self, host, port, username, password, db_name, pool_size=5):
        self.host = host
        self.port = port
        self.username = username
        self.password = password
        self.db_name = db_name
        self.pool = ConnectionPool(
            pool_name="mypool",
            host=self.host,
            port=self.port,
            user=self.username,
            password=self.password,
            db=self.db_name,
            pool_size=pool_size,
        )

    def get_connection(self):
        return self.pool.get_connection()

    def execute(self, query, args=None):
        conn = self.get_connection()
        try:
            with conn.cursor() as cursor:
                cursor.execute(query, args)
                result = cursor.fetchall()
            conn.commit()
        finally:
            conn.close()
        return result

但是在我pip install pymysqlpool安装后，运行程序时报错无法import pymysqlpool。我查看了conda的环境，conda list后发现已经安装了PyMySQLPool，后来我把程序中import pymysqlpool改成了import PyMySQLPool，还是无法运行程序。后来我又查了一些资料发现PyMySQLPool和pymysqlpool不是同一个库_(¦3」∠)_
接下来是pymysqlpool的安装使用流程（安装和使用其实按照git的简介来就行了）：

git地址：https://github.com/luvvien/pymysqlpool

下载源码后，使用 pip 安装即可：pip3 setup.py install，注意需要使用 Python3 环境。

1.clone代码：cd到你的路径，git clone https://github.com/luvvien/pymysqlpool。

2.安装：我这里使用的是python setup.py install执行setup.py文件安装的。

原git介绍中的使用示例：

from pymysqlpool import ConnectionPool

config = {
    'pool_name': 'test',
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': 'root',
    'database': 'test'
}

def connection_pool():
    # Return a connection pool instance
    pool = ConnectionPool(**config)
    return pool

# 直接访问并获取一个 cursor 对象，自动 commit 模式会在这种方式下启用
with connection_pool().cursor() as cursor:
    print('Truncate table user')
    cursor.execute('TRUNCATE user')

    print('Insert one record')
    result = cursor.execute('INSERT INTO user (name, age) VALUES (%s, %s)', ('Jerry', 20))
    print(result, cursor.lastrowid)

    print('Insert multiple records')
    users = [(name, age) for name in ['Jacky', 'Mary', 'Micheal'] for age in range(10, 15)]
    result = cursor.executemany('INSERT INTO user (name, age) VALUES (%s, %s)', users)
    print(result)

    print('View items in table user')
    cursor.execute('SELECT * FROM user')
    for user in cursor:
        print(user)

    print('Update the name of one user in the table')
    cursor.execute('UPDATE user SET name="Chris", age=29 WHERE id = 16')
    cursor.execute('SELECT * FROM user ORDER BY id DESC LIMIT 1')
    print(cursor.fetchone())

    print('Delete the last record')
    cursor.execute('DELETE FROM user WHERE id = 16')

import pandas as pd
from pymysqlpool import ConnectionPool

config = {
    'pool_name': 'test',
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': 'root',
    'database': 'test'
}


with connection_pool().connection() as conn:
    pd.read_sql('SELECT * FROM user', conn)


# 或者
connection = connection_pool().borrow_connection()
pd.read_sql('SELECT * FROM user', conn)
connection_pool().return_connection(connection)

 最后附上我实际应用中的代码：

from pymysqlpool import ConnectionPool

class Database:
    """
    MySQL 连接池
    """

    _instance = None

    def __init__(self, host, port, username, password, db_name, pool_size=5):
        self.host = host
        self.port = port
        self.username = username
        self.password = password
        self.db_name = db_name
        self.pool = ConnectionPool(
            pool_name="mypool",
            host=self.host,
            port=self.port,
            user=self.username,
            password=self.password,
            db=self.db_name,
            max_pool_size=pool_size,
        )

    def execute_fetch(self, query, args=None):
        results = []
        with self.pool.cursor() as cursor:
            cursor.execute(query, args)
            for request in cursor:
                results.append(request)
        return results

    def execute_insert_or_update(self, query, args=None):
        with self.pool.cursor() as cursor:
            result = cursor.execute(query, args)
            return result


if __name__ == "__main__":
    db_pool = Database(host='',
                       port=3306,
                       username='',
                       password='',
                       db_name='',
                       pool_size=5)
    frequests = db_pool.execute_fetch(
        "SELECT request_id, url FROM requests WHERE status='unprocessed' ORDER BY id ASC LIMIT %s", batch_size)
    print("start batch_process, unprocessed:", len(frequests))
    db_pool.execute_insert_or_update(
                "UPDATE requests SET status = %s WHERE request_id = %s",
                ("processing", request_id)
            )