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?

score 3 · Answer 1 · answered Jul 03 '09 at 05:54

3

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.

answered Jul 03 '09 at 05:54

zzzeek

72,307
23
193
185

This sounds very promising, thanks! Can you recommend any place to research this? – Ryan Ginstrom Jul 03 '09 at 06:32

score 3 · Answer 2 · answered Aug 06 '10 at 17:21

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.

answered Aug 06 '10 at 17:21

Paul Harrington

772
4
9

I was able to use such apsw opened connection with python sqlite3 in sqlalchemy. – iny Aug 11 '10 at 17:26

score 2 · Answer 3 · answered Apr 19 '21 at 12:37

2

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()

answered Apr 19 '21 at 12:37

BSalita

8,420
10
51
68

What version of SQLAlchemy is this? Using `1.3.24` I get `AttributeError: 'Connection' object has no attribute 'backup'`. – Peter Apr 27 '21 at 15:49
Are you using a sqlite raw_connection? – BSalita Apr 27 '21 at 17:30
1

Oh that might explain it then, I designed my database to be compatible between mysql and sqlite, so I wanted to see if it would backup between them. I was thinking it seemed a bit too simple to be true lol, but hadn't considered the raw connection was an entirely different object. – Peter Apr 27 '21 at 18:23

score 1 · Answer 4 · answered Jul 02 '09 at 10:18

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.

answered Jul 02 '09 at 10:18

S.Lott

384,516
81
508
779

That changed as-of Python 3.7: https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.backup – Simon Willison Sep 10 '21 at 16:51

score 1 · Accepted Answer · answered Aug 12 '13 at 14:16

1

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

answered Aug 12 '13 at 14:16

Oddthinking

24,359
19
83
121

Use SQLite's backup API from Python/SQLAlchemy

5 Answers5

Linked