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) )
· 使用C#创建一个MCP客户端
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型,支持深度思考和联网搜索!
· ollama系列1:轻松3步本地部署deepseek,普通电脑可用
· 基于 Docker 搭建 FRP 内网穿透开源项目(很简单哒)
· 按钮权限的设计及实现