python save in memory sqlite

Question

Is it possible to save my in-memory SQLite database to hard disk? If it is possible, some Python code would be awesome.

Answer 1

(Disclosure: I am the APSW author)

The only safe way to make a binary copy of a database is to use the backup API that is part of SQLite and is exposed by APSW. This does the right thing with ordering, locking and concurrency.

To make a SQL (text) copy of the a database then use the APSW shell which includes a .dump implementation that is very complete. You can use cursor.execute() to turn the SQL back into a database.

On recent platforms you are unlikely to see much of a difference between a memory database and a disk one (assuming you turned journaling off for the disk) as the operating system maintains a file system cache. Older operating systems like Windows XP did have a default configuration of only using 10MB of memory for file cache no matter how much RAM you have.

Answer 2

Per the sqlite3 python documentation, you can use the iterdump() method to make a .sql file dump of your in-memory database, and then it's trivial to apply that .sql file to a new sqlite file database.

# Convert file existing_db.db to SQL dump file dump.sql
import sqlite3, os
    
con = sqlite3.connect('existing_db.db')
with open('dump.sql', 'w') as f:
    for line in con.iterdump():
        f.write('%s\n' % line)

Answer 3

Yes. When you create the connection to the database, replace :memory: with the path where you want to save the DB.

sqlite uses caches for file based DBs, so this shouldn't be (much) slower.