CODEWITHSUNDEEP
pythonpython-asyncioasyncpg
Ryan
Ryan

Reputation: 41

asyncpg Pool.execute vs Connection.execute

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().

Upvotes: 4

Views: 3363

Answers (1)

decorator-factory
decorator-factory

Reputation: 3083

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)

Upvotes: 4

Related Questions