Use SQLite's backup API from Python/SQLAlchemy

Question

I'm using an SQLite database from python (with SQLAlchemy). For performance reasons, I'd like to populate an in-memory database in the application, and then back up that database to disk.

SQLite has a backup API, which seems would do this transparently.

The APSW documentation says that it wraps the backup API, but I'd like to access this functionality from Python's standard sqlite3 module, or in the best case from SQLAlchemy. Is this possible?

Answer 1

When I do the following

import sqlite3
dir(sqlite3)

I see none of the backup API methods.

Therefore, the answer is no; you cannot access the API from the sqlite3 module. It appears that no one implemented it.

Answer 2

the APSW dialect can be added to SQLAlchemy pretty easily as well. It would be very easy to achieve in 0.6, which allows multiple kinds of DBAPI adapters to make use of a common dialect for the database in use.

Answer 3

If pysqlite and apsw are linked against the same sqlite library then pysqlite can accept apsw connections. See:

http://docs.pysqlite.googlecode.com/hg/sqlite3.html#combining-apsw-and-pysqlite

I will try to do work on this (and other) approaches to getting apsw to work with SQLAlchemy as they are a very useful combination.