DOCSEARCHbyAlgolia
Submit Docs

aiosqlite

repository·main·Indexed 23 days ago

https://github.com/omnilib/aiosqlite

An asynchronous interface to SQLite databases that replicates the standard sqlite3 module API for use with AsyncIO. It provides async versions of Connection and Cursor objects, allowing for non-blocking database operations, transaction control, and async iteration over result sets. The library supports custom adapters, converters, user-defined functions, and database backups, while re-exporting standard sqlite3 exceptions for consistent error handling.

Tokens
3.1K
Snippets
4
Records
28
Agent score
81%

What's inside aiosqlite

  1. How aiosqlite handles concurrency

    main

    aiosqlite allows interaction with SQLite databases on the main AsyncIO event loop without blocking other coroutines.

    Mechanism:

    • It uses a single, shared thread per connection.
    • This thread executes all actions within a shared request queue to prevent overlapping actions.
    • Connection objects act as proxies to real connections and contain the shared execution thread.
    • Cursors act as proxies to real cursors and provide async iterators for query results.

  2. Use aiosqlite with async context managers

    main

    The recommended way to use aiosqlite is through async context managers. This ensures that connections and cursors are automatically closed after use. aiosqlite replicates the standard sqlite3 module API but with async versions of connection and cursor methods.

    async with aiosqlite.connect(...) as db:
    await db.execute("INSERT INTO some_table ...")
    await db.commit()

    async with db.execute("SELECT * FROM some_table") as cursor:
        async for row in cursor:
            ...

  5. Use aiosqlite in a procedural manner

    main

    You can use aiosqlite in a traditional procedural style by manually managing the connection and cursor lifecycle. Note that you must explicitly call .close() on both the cursor and the connection.

    db = await aiosqlite.connect(...)
cursor = await db.execute('SELECT * FROM some_table')
row = await cursor.fetchone()
rows = await cursor.fetchall()
await cursor.close()
await db.close()

  6. Establish a database connection with connect()

    main
    Use aiosqlite.connect() to create a new connection to a SQLite database. The returned Connection object can be used as an asynchronous context manager to ensure the connection is properly closed.

  7. Access columns by name using aiosqlite.Row

    main

    To access query results using column names instead of indices, set the row_factory attribute of the connection object to aiosqlite.Row.

    async with aiosqlite.connect(...) as db:
    db.row_factory = aiosqlite.Row
    async with db.execute('SELECT * FROM some_table') as cursor:
        async for row in cursor:
            value = row['column']

  8. Register custom adapters and converters

    main

    For advanced usage, you can extend how Python types are mapped to SQLite types using:

    • register_adapter(type, adapter_function): Defines how a Python object is converted into a format SQLite understands.
    • register_converter(type_name, converter_function): Defines how a value retrieved from SQLite is converted back into a Python object.

  9. Handle aiosqlite exceptions

    main

    When working with aiosqlite, you should catch specific exceptions to handle database errors gracefully. The following exception hierarchy is available:

    • aiosqlite.Error: The base class for all aiosqlite errors.
    • aiosqlite.DatabaseError: Errors related to the database.
    • aiosqlite.IntegrityError: Errors related to relational integrity (e.g., unique constraint violations).
    • aiosqlite.OperationalError: Errors related to the database operation (e.g., connection issues).
    • aiosqlite.ProgrammingError: Errors related to SQL syntax or invalid usage.
    • aiosqlite.NotSupportedError: Errors when using features not supported by the current SQLite version.
    • aiosqlite.Warning: Non-fatal warnings.

  10. Register adapters and converters

    main
    aiosqlite provides access to register_adapter and register_converter from the standard sqlite3 module. These allow you to define how custom Python types are mapped to SQLite types and how SQLite types are converted back into Python objects.

  11. Execute queries with Connection

    main

    The Connection class provides several high-level helper methods to execute SQL queries. Most of these are designed to be used as asynchronous context managers.

    execute(sql, parameters=None)

    Creates a Cursor and executes the given query.

    async with db.execute("SELECT * FROM table WHERE id = ?", (1,)) as cursor:
    async for row in cursor:
        print(row)

    execute_insert(sql, parameters=None)

    Inserts data and returns the last_insert_rowid().

    row = await db.execute_insert("INSERT INTO table (name) VALUES (?)", ("name",))

    execute_fetchall(sql, parameters=None)

    Executes a query and returns all the data immediately.

    rows = await db.execute_fetchall("SELECT * FROM table")

    executemany(sql, parameters)

    Executes a multiquery (batch execution).

    async with db.executemany("INSERT INTO table VALUES (?)", [(1,), (2,)]) as cursor:
    pass

    executescript(sql_script)

    Executes a user-provided SQL script.

    async with db.executescript("CREATE TABLE t (i int); INSERT INTO t VALUES (1);") as cursor:
    pass

  12. Connect to a SQLite database with connect()

    main

    Use the connect() function to create and return a Connection proxy to a SQLite database. The connection is asynchronous and manages a background thread to execute queries. It is recommended to use the connection as an asynchronous context manager (async with) to ensure it is closed properly.

    Note: The loop parameter is deprecated and no longer used.