上周部署一个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)
标签:pymysqlpool,self,cursor,connection,user,应用,pool From: https://www.cnblogs.com/jimmy996/p/17302841.html