AsyncIO SQLite

0.22.1 · active · verified Sat Mar 28

aiosqlite provides a friendly, async interface to SQLite databases, replicating the standard `sqlite3` module but with asynchronous versions of all connection and cursor methods. It enables interaction with SQLite databases on the main AsyncIO event loop without blocking, utilizing a single, shared thread per connection for query execution. The current version is 0.22.1, and it maintains an active release cadence.

Warnings

gotcha Encountering 'Database is Locked' errors, especially in concurrent scenarios, is a common SQLite issue. While `aiosqlite` provides an async interface, SQLite itself is a single-file database and can face contention.
Fix: Increase the `timeout` parameter when calling `aiosqlite.connect()` to allow SQLite more time to acquire locks (e.g., `aiosqlite.connect('db.db', timeout=10)`). Consider optimizing database schemas, queries, or using a connection pool (`aiosqlitepool`) for high-concurrency applications.
breaking Older Python versions are no longer supported. Python 3.7 and 3.8 support was dropped in versions 0.20.0 and 0.21.0 respectively.
Fix: Ensure your project runs on Python 3.9 or newer. Upgrade your Python environment if using an older version.
breaking When used with SQLAlchemy, `aiosqlite` versions 0.22.0 and above changed the internal connection class, which is no longer a daemon thread. This can cause applications to hang on exit if SQLAlchemy's `aiosqlite` dialect doesn't explicitly call `await engine.dispose()` to close connections.
Fix: If using SQLAlchemy with `aiosqlite >= 0.22.0`, ensure you explicitly call `await engine.dispose()` at the end of your application's main function to properly close all connections and avoid hanging processes. Alternatively, disable connection pooling by using `NullPool`.

Install

pip install aiosqlite Install stable version

Imports

connect
```
import aiosqlite
async with aiosqlite.connect(...)
```
The primary way to interact is through the `connect` function, often used as an async context manager.

Quickstart

This quickstart demonstrates creating a database, defining a table, inserting, querying, updating, and deleting data using `aiosqlite`'s async context managers for connections and cursors. It ensures proper resource management by automatically committing transactions and closing connections.

import asyncio
import aiosqlite
import os

async def main():
    db_path = os.environ.get('AIOSQLITE_DB_PATH', 'my_database.db')
    async with aiosqlite.connect(db_path) as db:
        # Create a table
        await db.execute('''
            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                age INTEGER
            )
        ''')
        await db.commit()

        # Insert data
        await db.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Alice", 30))
        await db.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Bob", 24))
        await db.commit()

        # Query data
        async with db.execute("SELECT id, name, age FROM users WHERE age > ?", (25,)) as cursor:
            print("Users older than 25:")
            async for row in cursor:
                print(f"  ID: {row[0]}, Name: {row[1]}, Age: {row[2]}")

        # Update data
        await db.execute("UPDATE users SET age = ? WHERE name = ?", (31, "Alice"))
        await db.commit()

        # Delete data
        await db.execute("DELETE FROM users WHERE name = ?", ("Bob",))
        await db.commit()

        async with db.execute("SELECT COUNT(*) FROM users") as cursor:
            (count,) = await cursor.fetchone()
            print(f"Total users remaining: {count}")

if __name__ == "__main__":
    asyncio.run(main())

view raw JSON →