调用函数触发RuntimeError：关闭连接池后无法获取连接（未手动关闭池）

调用函数时触发错误：

RuntimeError: Cannot acquire connection after closing pool

但代码中并未主动关闭连接池。

相关代码 #

from aiomysql import create_pool, Cursor
import asyncio
from env import DATABASE, HOST, PORT, USER, PASSWORD


class Table:
    def __init__(self) -> None:
        l = asyncio.get_event_loop()
        self.pool = l.run_until_complete(create_pool(
            host=HOST,
            port=PORT,
            user=USER,
            password=PASSWORD,
            db=DATABASE,
            cursorclass=Cursor,
            autocommit=True,
            loop = asyncio.get_event_loop(),
            maxsize=15
        ))


    async def f(self):
        async with self.pool as pool:
            async with pool.acquire() as conn:
                async with conn.cursor() as cursor:
                    QUERY = f"""SELECT 1;"""
                    await cursor.execute(QUERY, (user_id))
                    return (await cursor.fetchone())[0]

报错回溯 #

File "C:\Users\User\AppData\Local\Programs\Python\Python311\Lib\site-packages\aiogram\dispatcher\dispatcher.py", line 415, in _process_polling_updates
    for responses in itertools.chain.from_iterable(await self.process_updates(updates, fast)):
                                                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\User\AppData\Local\Programs\Python\Python311\Lib\site-packages\aiogram\dispatcher\dispatcher.py", line 235, in process_updates
    return await asyncio.gather(*tasks)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\User\AppData\Local\Programs\Python\Python311\Lib\site-packages\aiogram\dispatcher\handler.py", line 117, in notify
    response = await handler_obj.handler(*args, **partial_data)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\User\AppData\Local\Programs\Python\Python311\Lib\site-packages\aiogram\dispatcher\dispatcher.py", line 256, in process_update
    return await self.message_handlers.notify(update.message)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\User\AppData\Local\Programs\Python\Python311\Lib\site-packages\aiogram\dispatcher\handler.py", line 117, in notify
    response = await handler_obj.handler(*args, **partial_data)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\Users\User\Desktop\main.py", line 137, in start
    test = await table.f()
           ^^^^^^^^^^^^^^^
  File "c:\Users\User\Desktop\main.py", line 43, in f
    async with pool.acquire() as conn:
  File "C:\Users\User\AppData\Local\Programs\Python\Python311\Lib\site-packages\aiomysql\utils.py", line 134, in __aenter__
    self._conn = await self._coro
                 ^^^^^^^^^^^^^^^^
  File "C:\Users\User\AppData\Local\Programs\Python\Python311\Lib\site-packages\aiomysql\pool.py", line 139, in _acquire
    raise RuntimeError("Cannot acquire connection after closing pool")
RuntimeError: Cannot acquire connection after closing pool

问题出在f方法里的async with self.pool as pool这行代码。aiomysql的连接池对象在使用async with上下文管理器时，退出上下文会自动调用close()方法关闭连接池。这意味着第一次调用f()方法后，连接池就被关闭了，后续再调用f()尝试获取连接时，就会触发报错。

去掉多余的async with self.pool as pool，直接使用self.pool获取连接即可。同时注意代码中user_id变量未定义、单元素元组格式错误的问题，一并修正。

修正后的代码：

from aiomysql import create_pool, Cursor
import asyncio
from env import DATABASE, HOST, PORT, USER, PASSWORD


class Table:
    def __init__(self) -> None:
        l = asyncio.get_event_loop()
        self.pool = l.run_until_complete(create_pool(
            host=HOST,
            port=PORT,
            user=USER,
            password=PASSWORD,
            db=DATABASE,
            cursorclass=Cursor,
            autocommit=True,
            loop = asyncio.get_event_loop(),
            maxsize=15
        ))


    async def f(self, user_id):  # 补充user_id参数
        async with self.pool.acquire() as conn:
            async with conn.cursor() as cursor:
                QUERY = """SELECT 1;"""  # 无需f-string，无变量替换需求
                await cursor.execute(QUERY, (user_id,))  # 单元素元组需加逗号，避免被识别为单个值
                result = await cursor.fetchone()
                return result[0] if result else None

另外可以优化连接池初始化方式，避免在__init__中阻塞事件循环：

class Table:
    def __init__(self) -> None:
        self.pool = None

    async def init_pool(self):
        self.pool = await create_pool(
            host=HOST,
            port=PORT,
            user=USER,
            password=PASSWORD,
            db=DATABASE,
            cursorclass=Cursor,
            autocommit=True,
            loop=asyncio.get_event_loop(),
            maxsize=15
        )

# 使用时先初始化连接池
table = Table()
await table.init_pool()

内容的提问来源于stack exchange，提问作者izen02