APSW (Another Python SQLite Wrapper) is a comprehensive Python wrapper for the SQLite embedded relational database engine. It provides a thin, complete layer over the SQLite C API, staying up-to-date with both SQLite and Python. APSW offers extended functionality beyond the standard `sqlite3` module, including full text search, session, virtual tables, VFS, and advanced JSON support. It supports CPython 3.10 onwards and releases are approximately quarterly, mirroring SQLite's release cycle.
Install & Compatibility
Where this runs
tested against v3.53.2.0 · pip install
no network on importno background threads
Install × environment matrix
Each cell = how many times install + import succeeded across repeated harness runs. Partial = flaky.
glibc = Debian/Ubuntu slim · musl = Alpine Linux
muslpy 3.10–3.925 runs
installs and imports cleanly · install 0.0s · import 0.000s · 27.5MB
glibcpy 3.10–3.925 runs
installs and imports cleanly · install 2.0s · import 0.000s · 29MB
28MB installed
● package 28MB
Code
Verified usage
Verified import paths — ran on the pinned version, not inferred.
Connection
✓ import apsw
connection = apsw.Connection('database.db')
✗ from apsw import Connection
The primary classes like Connection and Cursor are direct attributes of the top-level 'apsw' module, not typically imported directly.
Cursor
✓ import apsw
cursor = connection.cursor()
✗ from apsw import Cursor
Similar to Connection, Cursor objects are created via a Connection instance.
This example demonstrates how to establish a connection, apply best practices, create a table, insert data using positional and named bindings, and retrieve data. It uses a context manager for connection handling, which is the recommended approach.
import apsw
import os
db_file = 'example.db'
if os.path.exists(db_file):
os.remove(db_file)
# Best practice application for optimal settings and error avoidance
apsw.bestpractice.apply(apsw.bestpractice.recommended)
# Use a context manager to ensure proper closing and transaction management
with apsw.Connection(db_file) as connection:
cursor = connection.cursor()
# Create a table
cursor.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)")
# Insert data
cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ('Alice', 'alice@example.com'))
cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ('Bob', 'bob@example.com'))
# Select data
print("All users:")
for row in cursor.execute("SELECT id, name, email FROM users"):
print(f"ID: {row[0]}, Name: {row[1]}, Email: {row[2]}")
# Select with named bindings
print("\nUser with ID 1:")
for row in cursor.execute("SELECT name FROM users WHERE id = :id", {'id': 1}):
print(f"Name: {row[0]}")
print(f"Database '{db_file}' operations complete.")
Debug
Known issues
breakingSQLite 3.52.0 withdrawal led to the withdrawal of APSW 3.52.0.0. While APSW 3.51.3.0 includes most of its features, it excludes `SQLITE_UTF8_ZT` and `sqlite3_carray_bind_v2`.fixAvoid using APSW 3.52.0.0. Be aware of the feature exclusions if migrating from a hypothetical 3.52.0.0 to 3.51.3.0, or generally ensure your SQLite features are covered by the current APSW version.
affects: 3.52.0.0 (withdrawn), 3.51.3.0
gotchaAPSW `Connection` and `Cursor` objects are not thread-safe for concurrent use. Attempting to use the same `Connection` or `Cursor` object simultaneously in multiple threads will raise a `ThreadingViolationError` or lead to hangs.fixFor concurrent database access, use a separate `apsw.Connection` object for each thread. APSW *does* release the GIL around SQLite calls, allowing Python threads to execute concurrently with different connections.
affects: All versions
gotchaWhile APSW objects automatically close on garbage collection, it is best practice to explicitly close `Connection` and `Cursor` objects or use context managers (`with apsw.Connection(...)`) to ensure timely resource release and proper transaction handling, especially if user-defined functions or collations introduce circular references preventing automatic garbage collection.fixAlways use `with apsw.Connection(...) as connection:` and manage cursors within that context. Explicitly call `.close()` on objects if context managers are not feasible.
affects: All versions
gotchaThe `apsw.bestpractice` module offers functions to enable recommended SQLite settings (e.g., WAL mode, foreign keys, busy timeout, query optimization) which are not default due to SQLite's strong backward compatibility. Not applying these can lead to common mistakes or suboptimal performance.fixCall `apsw.bestpractice.apply(apsw.bestpractice.recommended)` at the start of your application or when opening a connection to enable these best practices.
affects: All versions
gotchaStarting with APSW 3.50.2.0, the PyPI binary builds began using `cibuildwheel` version 3, which advanced the minimum supported Linux distribution. This might affect deployments to older Linux environments.fixEnsure your Linux deployment environment meets the minimum glibc version requirements introduced by `cibuildwheel` v3. Check `cibuildwheel` documentation for specifics if encountering compatibility issues.
affects: >=3.50.2.0
deprecatedAPSW follows Python's end-of-life (EOL) cycle for supported versions. Once a Python release goes EOL, there will be one final APSW release supporting that Python version, after which support is dropped.fixKeep your Python runtime updated to actively supported versions (currently >=3.10). Consult the APSW changelog for specific Python EOL-related support drops.
affects: E.g., Python 3.8 support removed in 3.47.0.0, Python 3.9 support removed in 3.50.0.0 or 3.50.4.0.
Errors
Common errors & fixes
ImportError: No module named apsw
The APSW module is not installed in the Python environment.
fixInstall APSW using pip: `pip install apsw`.
AttributeError: module 'apsw' has no attribute 'Connection'
The APSW module is installed, but the 'Connection' attribute is not found, possibly due to an incomplete installation or version mismatch.
fixEnsure APSW is correctly installed and up-to-date: `pip install --upgrade apsw`.
apsw.SQLError: no such table: my_table
Attempting to access a table that does not exist in the SQLite database.
fixVerify that the table 'my_table' exists in the database, or create it before accessing.
apsw.BusyError: database is locked
The SQLite database is locked, likely due to concurrent access.
fixEnsure that database connections are properly closed and avoid concurrent writes.
apsw.OperationalError: disk I/O error
A disk I/O error occurred while accessing the SQLite database.
fixCheck the disk for errors and ensure there is sufficient space and proper permissions.
Audit
Dependencies
PythonrequiredRequired runtime environment.