reusing aiosqlite connection

user_4137942 0
Viewed 3410

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?

contextmanager sqlite python
2 Answers

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.

user_7781800

Two points. Firstly a modifying statement locks the whole DB, as per:

When a database is accessed by multiple connections, and one of the processes modifies the database, the SQLite database is locked until that transaction is committed. The timeout parameter specifies how long the connection should wait for the lock to go away until raising an exception. The default for the timeout parameter is 5.0 (five seconds).

(https://docs.python.org/3/library/sqlite3.html#connection-objects) so read-only statements will not be able to execute anyway, as long as a "write" is in progress. Consequently, I'd open a new connection for each write operation.

Secondly, for a file-based DB such as SQLite the cost of opening a connection is obviously much lower then for a network-accessed DB. You may profile it; a simple test like:

    x = 0
    for i in range(1000):
        async with aiosqlite.connect('pic_db.db') as db:
            async with db.execute('SELECT 12', ()) as cursor:
                x += 1

shows the two statements take together ~550ms; if the for and connect are interchanged, it takes ~150ms; so each connect costs you about 0.4ms. If that's an issue, I'd try to reuse a connection for read-only statements (and open a new one for each modifying statement).

user_12110180
Related