asyncpg Pool.execute vs Connection.execute

user_10038574 0
Viewed 363

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?

In the docs for the Pool class, this example is shown:

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

A connection is acquired from the pool and used to execute a statement. What is the benefit of doing that over just calling the execute method directly on the Pool object such as the below?

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};')

The docs for Pool.execute even state the following:

Execute an SQL command (or commands).

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

asyncpg python-asyncio python
1 Answers

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?

When you acquire a connection explicitly, you can start a transaction and execute several queries inside of the 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
            )

If you did this:

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
    )

it would be a shorthand to:

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
        )

If, for example, the program crashed after the first query, one user would get the money but the other would not lose it. In other words, the function would not be atomic (the "A" in ACID)

This is how Pool.execute is implemented (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)
user_102957290
Related