APSW

APSW (Another Python SQLite Wrapper) is a Python wrapper for the SQLite embedded relational database engine. Unlike the standard sqlite3 module, APSW provides direct access to the complete SQLite C API, allowing developers to use advanced SQLite features directly from Python. It supports CPython 3.10 and onwards.

Key features supported by APSW include:

• Full Text Search
• Session extension
• Virtual Tables
• VFS (Virtual File System)
• JSON support
• CArray
• Full support for all Python async frameworks
• Both synchronous and asynchronous code execution

The APSW shell is a command-line interface for interacting with SQLite, performing administration, and executing SQL. It is modeled after the standard SQLite shell but includes improvements like colorized output, tab completion, and the ability to switch between all open APSW connections. It also allows for programmatic invocation and provides a Python REPL via the .py command.

Key Features:

• Colorized Output: Enabled by default (on modern Windows) or via the NO_COLOR environment variable.
• Tab Completion: Available for commands and SQL.
• Connection Management: You can switch between multiple open APSW connections.
• Python Integration: Run Python code or enter a REPL directly within the shell.
• Enhanced Dumps: Provides nicer text dump output including metadata like user_version.

python3 -m apsw [OPTIONS] FILENAME [SQL|CMD] [SQL|CMD]...

APSW supports multi-threaded programs by releasing the Python Global Interpreter Lock (GIL) during long-running SQLite operations like preparing and executing statements.

Key threading rules:

• Concurrency: You do not get concurrency by using multiple threads on a single Connection. To achieve true concurrency, use multiple Connection objects.
• Mutexes: SQLite uses a mutex to ensure only one thread executes at a time. If a thread attempts an operation while another is busy, Cursor.execute, Cursor.executemany, or cursor iteration will wait up to 1/3 of a second for the mutex before raising a ThreadingViolationError.
• Thread Safety: APSW checks that SQLite was compiled in threadsafe mode (the default).

SQLite does not have native date or time types; dates and times are typically stored as strings or Julian days (floating point numbers). Use SQLite's built-in functions for date/time manipulation.

For BLOB data, use any type that implements the collections.abc.Buffer interface, such as bytes.

APSW propagates contextvars from the event loop to the database worker thread and back. This allows callbacks to access context-specific values.

Important Notes:

• Memory Management: Always use contextvars.ContextVar.reset to avoid increasing memory consumption. APSW provides apsw.aio.contextvar_set as a convenient way to handle this.
• Scope: Setting a variable is not visible to code earlier in the call chain. To allow all code in a chain to see and modify values, use a mutable object like a dict as the value.

When multiple errors occur within a single SQLite control flow, APSW uses Python's exception chaining (PEP 3134).

For example, if an error occurs in a VFS (Virtual File System) operation, SQLite might attempt error recovery. If your recovery code also raises an exception, both exceptions will be chained. Python's traceback will display the full context of all exceptions involved in the sequence.

When a Python function registered as a SQLite scalar or aggregate function raises an exception, APSW correctly propagates the error and converts it into a SQLite error. This allows the traceback to show the actual line in your Python code where the error occurred. In contrast, sqlite3 often swallows these exceptions, making debugging difficult.

def badfunc(t):
    return 1/0

import apsw
con = apsw.Connection(":memory:")
con.create_scalar_function("badfunc", badfunc, 1)
cur = con.cursor()
cur.execute("select badfunc(3)")

APSW bridges the gap between Python's exception-based error handling and SQLite's integer error code system.

• Python to SQLite: If an exception is raised in Python code that is being called by SQLite (such as a custom function or VFS implementation), APSW ensures that the exception is present when control returns to Python, and SQLite is notified that an error occurred.
• SQLite to Python: APSW maps SQLite's integer error codes to appropriate Python exceptions.