asqlite3

An asynchronous wrapper for sqlite3. The library wraps the entirety of sqlite3 (as provided on your system) for every version of Python since Python 3.8.

The current version is py-asqlite3 0.7.

The project is hosted on GitHub. and uses Azure Pipelines for continuous integration.

Author and License

The code was written by Neil Booth. Python version at least 3.8 is required.

The code is released under the MIT Licence.

Library Installation

$ pip install py-asqlite3

Getting Started

Client example

import asqlite3
import asyncio

async def main():
    async with asqlite3.connect(':memory:') as conn:
        await conn.execute('CREATE TABLE T(x, y);')
        await conn.executemany('INSERT INTO T(x, y) VALUES(?, ?);',
                               ((n, n * 2) for n in range(100)))
        cursor = await conn.execute('SELECT * FROM T;')
        n = 0
        # Cursors can be used as async iterators
        async for row in cursor:
            assert row == (n, n * 2)
            n += 1
        assert n == 100

asyncio.run(main())

See also

Reference

Refer to the Python sqlite3 documentation for full details of functions, constants and methods; this documentation only covers points specific to asqlite3.

Module functions

async asqlite3.connect(database, *, timeout=5.0, detect_types=0, isolation_level='DEFERRED', check_same_thread=True, factory=sqlite3.Connection, cached_statements=128, uri=False, autocommit=sqlite3.LEGACY_TRANSACTION_CONTROL)

Open a connection to the database and start a thread to handle database requests. This function is only intended to be used as part of an async with asynchronous context managercode block, in which case the target, conn below, is a Connection object:

async with asqlite3.connect(filename) as conn:
    a code block

When control leaves the block, the library closes the database connection and shuts down the thread cleanly, after waiting for pending operations to complete. Note that leaving the block does not necessarily commit pending transactions. For more information see sqlite3 module’s documentation about Transaction Control.

The autocommit argument is only present For Python versions 3.12 and later.

See also How to use the connection context manager.

Module constants

asqilte3 provides all constants in the sqlite3 module, in addition to some new ones:

asqlite3.asqlite3_version_str

A string giving the asqlite3 version, e.g., ‘0.9’.

asqlite3.asqlite3_version

A tuple giving the asqlite3 version, e.g., (0, 9).

Connection

class asqlite3.Connection

The Connection class wraps the Connection class of sqlite3 and provides all of its methods and properties. Connection objects should be created by calling the connect() function.

A connection can be used as as an asynchronous context manager, in which case the connection will be closed when control leaves the block via the __aexit__ method.

Successful entering of the context of a Connection via the __aenter__ method starts a thread in which all uses of the database connection must happen - this is enforced by sqlite itself. The only exception is interrupt().

Python 3.12 introduces the autocommit property of sqlite3 database connections. This property can only be accessed in the connection’s thread, so explicit asynchronous autocommit_get() and autocommit_set() methods must be used to access this property.

Python 3.13 deprecates the use of named arguments for some of these methods, intending to remove their support in Python 3.15. Being a new library, asqlite3 does not support the deprecated method signatures.

async cursor(factory=Cursor)
async commit()
async rollback()
close()
:async:

Close the underlying database connection after waiting for pending operations to complete, and shut down the database thread. Idempotent.

async execute(sql, parameters=(), /)
async executemany(sql, parameters, /)
async executescript(sql_script, /)
async create_function(name, narg, func, /, *, deterministic=False)
async create_aggregate(name, narg, aggregate_class, /)
async create_collation(name, callable, /)
async set_authorizer(authorizer_callback, /)
async set_progress_handler(handler, /, n)
async set_trace_callback(trace_callback, /)
async backup(target, *, pages=-1, progress=None, name='main', sleep=0.250)
async iterdump()

Returns an asynchronous iterator which can be used as follows:

async for line in await conn.iterdump():
    print(line)

See also iterdump_sync().

async iterdump_sync()

Returns a synchronous iterator. As the iterator accesses the database connection, it must be used via a call to schedule(). For example:

def print_lines(lines):
    for line in lines:
        print(line)

sync_iter = await conn.iterdump_sync()
await conn.schedule(print_lines, sync_iter)
schedule(func, *args, **kwargs)

Schedule the synchronous function func to run in the thread of the database connection, passing it the given arguments. Returns a future, which can be await-ed if the caller wishes to wait for the invocation to complete before continuing.

interrupt()

Note this method is synchronous.

The following methods are available if loadable extension support is compiled into Python’s sqlite3 module:

async enable_load_extension(enable)
async load_extension(path)

The following methods are available in Python versions 3.11 and later:

create_window_function(name, num_params, aggregate_class, /):
async blobopen(table, column, row, /, *, readonly=False, name='main')
async serialize(*, name='main')
async deserialize(data, /, *, name='main')
async getlimit(category, /)
async setlimit(category, limit, /)

The following methods are available in Python versions 3.12 and later:

async getconfig(op, /)
async setconfig(op, enable=True, /)
async autocommit_get()

Return the autocommit property of the underlying sqlite3 connection.

async autocommit_set(value)

Set the autocommit property of the underlying sqlite3 connection.

property isolation_level
property in_transaction
property row_factory
property text_factory
property total_changes

Cursor objects

class asqlite3.Cursor

The Cursor class is an asynchronous wrapper of the Cursor class of sqlite3, and provides all its methods and properties. Cursor objects should be created by calling the cursor() method on an asqlite Connection object.

A cursor object can be used as as an asynchronous context manager, in which case the cursor will be closed when control leaves the block via the __aexit__ method.

A cursor can be used as as an asynchronous iterator. In such cases, rows are fetched arraysize rows at a time.

The following methods are asyncronous versions of the underlying sqlite3 Cursor methods. The properties, except for connection and sqlite3_connection, pass through to the underlying sqlite3 Cursor. Refer to the Python sqlite3 documentation for more details.

async close()
async execute(sql, parameters=(), /)
async executemany(sql, parameters, /)
async executescript(sql_script, /)
async fetchall()
async fetchmany(size=cursor.arraysize)
async fetchone()
property arraysize
property connection

Returns an asqlite3 Connection object.

property sqlite3_connection

Returns the underlying sqlite3 Connection object.

property description
property lastrowid
property rowcount
property row_factory

How to use the connection context manager

Just like for the sqlite3 module, a Connection object can be used as a context manager that automatically commits or rolls back open transactions when leaving the body of the context manager. If the body of the async with statement finishes without exceptions, the transaction is committed. If this commit fails, or if the block body raises an uncaught exception, the transaction is rolled back. If the underlying connection’s autocommit attribute is False, a new transaction is implicitly opened after committing or rolling back.

If there is no open transaction upon leaving the body of the async with statement, or if autocommit is True, the context manager does nothing.

For example:

async with connect(filename) as conn:
     async with conn:
         # Start transaction 1
         await conn.execute('CREATE TABLE T(x)')
         await conn.executemany('INSERT INTO T VALUES(?)', ((n, ) for n in range(10)))

     # transaction 1 is now committed

     async with conn:
         # Start transaction 2
         ....

Note that the context manager neither implicitly opens a new transaction nor closes the connection.

Indices and tables