APSW: Using the Full SQLite C API from Python Instead of sqlite3

Python's standard library ships the sqlite3 module, and for most applications it is enough. Its job is to make SQLite look like any other database behind the Python DB-API: connect, get a cursor, execute, fetch. That uniformity is useful when you might swap SQLite for another engine later. It also means the driver reflects what databases have in common, not everything SQLite can do.

SQLite has a large C API beyond running SQL. It includes full text search, a session extension for recording changes, virtual tables, a pluggable virtual file system layer, JSON functions and more. If your application treats SQLite as the database, and not as a stand-in for a server you might use later, you will eventually want parts of that API that a generic driver does not prioritise.

APSW, short for Another Python SQLite Wrapper, is the library for that case. This article covers what it wraps, when its own author tells you to use sqlite3 instead, and what to check before you adopt it.

What APSW actually wraps

The project's README describes APSW as gluing together the complete SQLite C API and Python's C API, and staying current with both. The stated goal works in both directions. You get the most out of SQLite from Python, and the most out of Python from SQLite. The second half matters. Several of SQLite's extension points expect you to provide code, not just call functions, and a thin wrapper makes it possible to write that code in Python.

The README names the areas covered: full text search, session, virtual tables, VFS (virtual file system), JSON and CArray. These are not convenience helpers added on top. They are SQLite subsystems that APSW exposes because it tracks the C API as a whole and does not pick a subset.

The practical effect is a different mental model from sqlite3. Instead of asking 'what does the DB-API let me do', you ask 'what does SQLite let me do', and look up the APSW binding for it.

When sqlite3 is the better choice

APSW's README is unusually direct about this. It recommends the built-in sqlite3 module if you want SQLite to appear interchangeable with other database drivers. That is the right call in several common situations:

• Your code or an ORM targets the DB-API so you can switch between SQLite in tests and a server database in production.
• You are writing a library and do not want to add a compiled dependency for users who just need a local store.
• Your SQLite usage is plain tables, indexes and queries, with none of the extension features listed above.

In those cases APSW adds a dependency and a different API without giving you anything you will use. The interchangeability that sqlite3 provides is a real feature, not a limitation to work around.

The README's case for APSW is to use it 'when you want to use SQLite fully, and have an improved developer experience.' The APSW documentation includes a dedicated section on the differences between APSW and sqlite3. Read it before you migrate existing code, because the two libraries do not behave identically, and code written against one will not simply run on the other.

The SQLite features APSW puts within reach

Each item in APSW's feature list maps to a part of SQLite that changes what an embedded database can do:

• Full text search: SQLite's built-in text indexing. You can search document content inside the same database file, without running a separate search service.
• Session: SQLite's session extension records changes made to tables so you can extract them as a changeset and apply them elsewhere. This is the building block for sync and replication between copies of a database.
• Virtual tables: lets you present data that is not stored in SQLite tables as if it were, so you can query it with ordinary SQL. With APSW, the code behind the table can be Python.
• VFS: the layer SQLite uses for all file and OS access. A custom VFS changes where and how bytes are stored, for example to add encryption, inspection or an unusual storage backend.
• JSON: SQLite's JSON functions, for storing and querying semi-structured data in columns.
• CArray: lets you pass an array of values into a query as a table-valued function. This is the clean way to express a large IN list without building SQL strings.

Virtual tables and VFS show most clearly why the 'Python from SQLite' direction matters. Both require you to implement callbacks that SQLite invokes. A driver that only sends SQL and returns rows cannot express them at all.

Synchronous and async code

According to the README, all of APSW's functionality is available in regular synchronous code, and it also has full support for all the async frameworks. This matters because the usual pattern for using SQLite inside an async service is to push blocking calls onto a thread pool by hand, or to add yet another wrapper library on top of the driver.

Because APSW keeps one feature set across both modes, you do not give up full text search or virtual tables to get async support. The README does not list which frameworks are tested or how the async integration works internally. Check the current documentation for the exact entry points before you design around them, especially if you run something other than the mainstream event loops.

For a command-line tool, a batch job or a desktop application, the synchronous API is the simpler path, and nothing in APSW pushes you towards async.

Getting started

APSW supports CPython 3.11 onwards. Confirm your interpreter version before anything else, because an older runtime is the most likely reason an install fails:

python -c 'import sys; print(sys.version_info)'

Releases are published to PyPI and GitHub, so a normal pip install works:

python -m pip install apsw

A minimal session looks familiar if you have used sqlite3, though the details differ once you go beyond simple queries:

import apsw

conn = apsw.Connection(':memory:')
conn.execute('create table t(x, y)')
conn.execute('insert into t values(?, ?)', (1, 'one'))
for row in conn.execute('select x, y from t'):
    print(row)

Do not reverse-engineer the rest of the API from habit. The project keeps a tour with example code that walks through the features, and the full documentation is the reference for everything else. The tour is the fastest way to see how the less familiar parts look in Python, such as virtual tables, VFS and sessions.

Tracking releases and getting help

APSW follows both SQLite and Python closely, so it is worth watching releases, not pinning a version and forgetting about it. New release announcements go to the Python SQLite discussion group, and PyPI provides an RSS feed for the package. The repository also links a full, detailed list of changes. Read it when you upgrade, since a wrapper over a moving C API will change as that API does.

For questions, the README names the Python SQLite discussion group as the preferred channel, with GitHub Discussions as an alternative. The author also gives an email address. Bugs go through GitHub Issues: search existing and closed issues first, then open a new one for anything unreported.

The discussion group is also where you will find people solving the same problems with SQLite from Python. That is often more useful than a general-purpose forum, where the default answer is to use the standard library.

Licensing

The repository's LICENSE file is summarised in the README as, in essence, any OSI-approved open source license. For most commercial and open source projects this removes the usual licensing review friction, because you can treat APSW under whichever approved licence fits your distribution.

Read the LICENSE file yourself, not just the one-line summary, if your organisation keeps a formal register of dependency licences. Your compliance team will want the exact text, not a paraphrase.

Where APSW does not fit, and what to watch for

The clearest mismatch is any codebase built on DB-API interchangeability, including ORMs and frameworks that expect a sqlite3-compatible connection. APSW's author recommends sqlite3 for exactly that case. Forcing APSW into such a stack means fighting the abstraction you chose.

Other things to check before committing:

• Interpreter floor: CPython 3.11 onwards. If you deploy to older runtimes or a non-CPython interpreter, confirm support first.
• Migration cost: APSW is not a drop-in replacement. Work through the documented differences from sqlite3 before porting existing data access code.
• Upgrade cadence: tracking the latest SQLite and Python is a feature, but it also means you should read the change list on each upgrade, not bump versions blindly.
• Feature need: if you are not using full text search, sessions, virtual tables, a custom VFS, JSON or CArray, measure what APSW actually gives you before adding the dependency.

Where APSW does fit, the gain is substantial. If SQLite is your application's real storage engine and you want its full feature set from Python, a wrapper that tracks the complete C API is a more honest tool than a generic driver that hides most of it.

This article draws on What does the "yield" keyword do in Python? and answers to it (1, 2), on the Stack Exchange network, licensed under CC BY-SA 4.0.