Best way to open/close DB Connection with async/await

Question

In tutorials I found, there is always opening and closing the connection for every request, for example :

import asyncio
import asyncpg

async def run():
    conn = await asyncpg.connect(user='user', password='password',
                             database='database', host='127.0.0.1')
    values = await conn.fetch('''SELECT * FROM mytable''')
    await conn.close()

loop = asyncio.get_event_loop()
loop.run_until_complete(run())

While this works for one single function, How about web application ?

IE: for example in Tornado, every URL is a class, which leads to lot of classes/methods.

I have the habit to open the connection in a blocking way, then use the wrapper to make asynchronous DB calls, and close the connection only to shut down gracefuly the server, what is the best practice in that case with async/await ?

Answer 1

Without having used asyncpg, I assume like in most asyncio compliant packages that there is an async context manager allowing exactly what you are asking for.

Something like:

async with asyncpg.create_pool(**kwargs) as pool:
    async with pool.acquire() as connection:
        async with connection.transaction():
            result = await connection.fetchval(fetch stuff)
            connection.execute(insert stuff with result)

(as taken from this question)

Check the docs for mentions of context managers or examples with async with statements or if nothing else then check classes in the source code which have implement the __aenter__, __aexit__ methods.

Edit 1:

The example above is partly taken from the question I've linked to and partly contrived for completeness. But to address your comments about what the with statements are doing:

async with asyncpg.create_pool(**kwargs) as pool:
    #in this block pool is created and open
    async with pool.acquire() as connection:
        # in this block connection is acquired and open
        async with connection.transaction():
            # in this block each executed statement is in a transaction
            execute_stuff_with_connection(connection)
        # now we are back up one logical block so the transaction is closed
        do_stuff_without_transaction_but_with_connection(connection)
    # now we are up another block and the connection is closed and returned to the pool
    do_more_stuff_with_pool(pool)
# now we are up another level and the pool is closed/exited/cleaned up
done_doing_async_stuff()

I'm not sure how good of an explanation this is, perhaps you should read up on context managers.