Sqlean Stubs

Read as Markdown

sqlean is a drop-in replacement for SQLite that adds many useful extensions to the SQLite core. The Python package has no type information. LiteCLI uses sqlean when available, if missing, falls back to SQLite. LiteCLI now supports type information for sqlean via sqlean-stubs.

How to use sqlean-stubs?

  1. Install sqlean-stubs using uv:

    uv add sqlean-stubs sqlean.py
# Do not install sqlean, there is a library in the same name but for a different purpose.
uv sync --all-extras

  2. Since sqlean is compatible with sqlite3, all the sqlite3 type signatures should work with sqlean. But the type import should come from sqlean.

  3. Here is an example of how to use sqlean with type signatures:

from typing import Optional
import sqlean

def main_sqlean():
    print("Hello from try-sqlean-stubs!")
    conn: sqlean.Connection = sqlean.connect(":memory:")
    cursor: sqlean.Cursor = conn.cursor()

    # Create repos table
    cursor.execute("""
        CREATE TABLE repos (
            id INTEGER PRIMARY KEY,
            package_name TEXT NOT NULL,
            github_url TEXT NOT NULL
        )
    """)

    # Insert test data
    cursor.execute(
        "INSERT INTO repos (package_name, github_url) VALUES (?, ?)",
        ("sqlean.py", "https://github.com/nalgeon/sqlean.py")
    )
    cursor.execute(
        "INSERT INTO repos (package_name, github_url) VALUES (?, ?)",
        ("sqlean-stubs", "https://github.com/kracekumar/sqlean-stubs")
    )

    # Query the table
    cursor.execute("SELECT * FROM repos")

    # fetchone() returns Optional[Any]
    row: Optional[sqlean.Row] = cursor.fetchone()
    if row is not None:
        print(f"ID: {row[0]}, Package: {row[1]}, URL: {row[2]}")

    # User-defined functions
    def double(x: int) -> int:
        return x * 2

    conn.create_function("double", 1, double)
    conn.close()

if __name__ == "__main__":
    main_sqlean()

Note: The entire stubs package was generated using Amp with several iterations.