Saving an in-memory SQLite database to a file in Python#

I was messing around in Python with an in-memory SQLite database, when I decided I actually wanted to save my experimental database to a file so I could explore it using Datasette.

In-memory databases can be created using sqlite3 like this:

1
import sqlite3
2

3
db = sqlite.connect(":memory:")

Or with sqlite-utils like this:

1
import sqlite_utils
2

3
db = sqlite_utils.Database(memory=True)

The VACUUM INTO command can be used to save a copy of the database to a new file. Here’s how to use it:

1
import sqlite3
2

3
db = sqlite3.connect(":memory:")
4
db.execute("create table foo (bar text)")
5

6
db.execute("vacuum main into '/tmp/saved.db'")
7

8
# Or with sqlite-utils
9
import sqlite_utils
10

11
db = sqlite_utils.Database(memory=True)
12

13
db["foo"].insert({"bar": "Example record"})
14

15
db.execute("vacuum main into '/tmp/saved2.db'")