How to save my in-memory database to hard disk?

Is i开发者_C百科t possible to save my in-memory SQLite database to hard disk? If it is possible, some Python code would be awesome.

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

There is a way to backup the in-memory database without using apsw. backup() is a non-blocking way to save a copy of the in-memory database on disk. Use conn.backup(dest) as shown below:

import sqlite3

conn = sqlite3.connect('file:existing_db.db?mode=memory',detect_types=sqlite3.PARSE_DECLTYPES,uri=True)
bckup = sqlite3.connect('file:backup.db',detect_types=sqlite3.PARSE_DECLTYPES,uri=True)
with bckup:
    conn.backup(bckup)
bckup.close()
conn.close()

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.

import apsw

memconn = apsw.Connection(":memory:")
memcursor = memconn.cursor()
memcursor.execute("create table foo(x,y,z)")
memcursor.execute("insert into foo values(?,?,?)", (1, 1.1, None))

thediskconn = apsw.Connection("adisk.db")
with thediskconn.backup("main", memconn, "main") as backup:
    backup.step() # copy whole database in one go

memcursor.close()
memconn.close()
#continue with thediskconn

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)

My 2021 gaming notebook can perform SQL operations, mostly INSERTs, 100x faster in memory than disk. I'm using sqlalchemy's 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()

Open a disk based database and just copy everything from one to the other.