异步Connection.execute Pool.execute对Connection.execute

Question

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

在池子类的文档中，显示了以下示例：

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

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

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的docs甚至声明如下：

执行SQL命令(或多个命令)。池使用它的一个连接执行此操作。除此之外，它的行为与Connection.execute()相同。

Answer 1

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

当您显式地获得一个连接时，您可以启动一个事务并在事务中执行几个查询：

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
        )

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

Pool.execute是如何实现的(GitHub链路)：

    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)