python - 异步 Pool.execute 与 Connection.execute

Question

从池中获取连接然后在连接上调用 execute 而不是直接在池对象上调用 execute 的用例是什么？

在 Pool 的文档中类，显示此示例:

con = await pool.acquire()
try:
    await con.execute(...)
finally:
    await pool.release(con)

从池中获取连接并用于执行语句。这样做比直接在 Pool 对象上调用 execute 方法有什么好处，如下所示？

async with asyncpg.create_pool(user=pg_user,
                               password=pg_pass,
                               host=pg_host,
                               port=pg_port,
                               database=pg_db,
                               command_timeout=60) as pool:
    pool.execute(f'TRUNCATE TABLE {table};')

Pool.execute 的文档甚至陈述以下内容:

Execute an SQL command (or commands).

Pool performs this operation using one of its connections. Other than that, it behaves identically to Connection.execute().

Answer 1

What are the use cases for acquiring a connection from a pool and then calling execute on the connection rather than calling execute on a pool object directly?

当您显式获取连接时，您可以启动 transaction并在交易中执行多个查询:

async def transfer_money(pool, from_id, to_id, amount):
    async with pool.acquire() as conn:
        async with conn.transaction():
            await conn.execute(
                "UPDATE balance SET money = money + $2 WHERE user_id = $1",
                to_id, amount
            )
            await conn.execute(
                "UPDATE balance SET money = money - $2 WHERE user_id = $1",
                from_id, amount
            )

如果你这样做:

async def transfer_money(pool, from_id, to_id, amount):
    await pool.execute(
        "UPDATE balance SET money = money + $2 WHERE user_id = $1",
        to_id, amount
    )
    await pool.execute(
        "UPDATE balance SET money = money - $2 WHERE user_id = $1",
        from_id, amount
    )

这将是以下内容的简写:

async def transfer_money(pool, from_id, to_id, amount):
    async with pool.acquire() as conn:
        await conn.execute(
            "UPDATE balance SET money = money + $2 WHERE user_id = $1",
            to_id, amount
        )
    async with pool.acquire() as conn:
        await conn.execute(
            "UPDATE balance SET money = money - $2 WHERE user_id = $1",
            from_id, amount
        )

例如，如果程序在第一次查询后崩溃，一个用户会得到钱，但另一个用户不会丢失它。换句话说，函数不会是原子的(ACID 中的“A”)

这就是 Pool.execute 的实现方式 (GitHub link):

    async def execute(self, query: str, *args, timeout: float=None) -> str:
        async with self.acquire() as con:
            return await con.execute(query, *args, timeout=timeout)