CODEWITHSUNDEEP
pythonsqlitesqlalchemyapsw
Ryan Ginstrom
Ryan Ginstrom

Reputation: 14121

Use SQLite's backup API from Python/SQLAlchemy

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?

Upvotes: 6

Views: 4586

Answers (5)

BSalita
BSalita

Reputation: 8931

It's 2021, performance difference between memory and disk has changed. My gaming notebook can perform SQL operations, mostly INSERTs, 100x faster in memory than disk. I'm using raw connections to backup (copy in memory db to disk).

# create in-memory database. get raw_connection.
engine_memory = sqlalchemy.create_engine('sqlite://')
raw_connection_memory = engine_memory.raw_connection()

# do something to in-memory db
raw_cursor_memory.executescript(my_sql_script)

# save memory db to disk file.
engine_file = sqlalchemy.create_engine('sqlite:///myfile.sqlite')
raw_connection_file = engine_file.raw_connection()
raw_connection_memory.backup(raw_connection_file.connection)
raw_connection_file.close()
engine_file.dispose()

Upvotes: 2

Oddthinking
Oddthinking

Reputation: 25282

The python-sqlite3-backup module claims to solve this problem.

Upvotes: 1

Paul Harrington
Paul Harrington

Reputation: 782

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.

Upvotes: 3

zzzeek
zzzeek

Reputation: 75117

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.

Upvotes: 3

S.Lott
S.Lott

Reputation: 391818

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.

Upvotes: 1

Related Questions