reusing aiosqlite connection

Question

I just can't figure out how to use the aiosqlite module so that I can keep the connection around for later use.

The example based on the aiosqlite project page

async with aiosqlite.connect('file.db') as conn:
    cursor = await conn.execute("SELECT 42;")
    rows = await cursor.fetchall()
    print('rows %s' % rows)

works fine, but I want to keep the connection around so that I can use it throughout my program.

Typically, with sqlite, I open a connection, squirrel it away and then use it throughout the life of the program.

I also tried things like:

conn = aiosqlite.connect('file.db')
c = await conn.__enter__()
AttributeError: 'Connection' object has no attribute '__enter__'

Is there a way to use this module without a context manager?

Answer 1

The "best" way would be for the entry-point of your application to create the aiosqlite connection using the context manager method, store a reference to the connection object somewhere, and then run the application's "run loop" method from within that context. This would ensure that when your application exits, the sqlite connection is cleaned up appropriately. This could look something like this:

async def main():
    async with aiosqlite.connect(...) as conn:
        # save conn somewhere
        await run_loop()

Alternately, you can await the appropriate enter/exit methods:

try:
    conn = aiosqlite.connect(...)
    await conn.__aenter__()
    # do stuff
finally:
    await conn.__aexit__()

Regardless, do beware that the asynchronous nature of aiosqlite does mean that shared connections will potentially result in overlap on transactions. If you need the assurance that concurrent queries take place with separate transactions, then you will need a separate connection per transaction.

According to the Python sqlite docs on sharing connections:

When using multiple threads with the same connection writing operations should be serialized by the user to avoid data corruption.

This applies equally to aiosqlite and asyncio. For example, the following code will potentially overlap both inserts into a single transaction:

async def one(db):
    await db.execute("insert ...")
    await db.commit()

async def two(db):
    await db.execute("insert ...")
    await db.commit()

async def main():
    async with aiosqlite.connect(...) as db:
        await asyncio.gather(one(db), two(db))

The correct solution here would be to either create a connection for each transaction, or use something like executescript to execute the entire transaction at once.