随笔 - 7  文章 - 0  评论 - 0  阅读 - 8061

pymysqlpool踩坑和应用

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

chatGPT也给出了以下回答:

你可以使用连接池库(如pymysqlpoolDBUtils等)来实现连接池。以下是一个示例代码,使用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)
            )
复制代码

 

posted on   Jimmy996  阅读(1532)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 使用C#创建一个MCP客户端
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型,支持深度思考和联网搜索!
· ollama系列1:轻松3步本地部署deepseek,普通电脑可用
· 基于 Docker 搭建 FRP 内网穿透开源项目(很简单哒)
· 按钮权限的设计及实现
< 2025年3月 >
23 24 25 26 27 28 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31 1 2 3 4 5

点击右上角即可分享
微信分享提示