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:

import sqlite3
db = sqlite.connect(":memory:")

Or with sqlite-utils like this:

import sqlite_utils
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:

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("create table foo (bar text)")
db.execute("vacuum main into '/tmp/saved.db'")
# Or with sqlite-utils
import sqlite_utils
db = sqlite_utils.Database(memory=True)
db["foo"].insert({"bar": "Example record"})
db.execute("vacuum main into '/tmp/saved2.db'")

Related

• spatialite Creating a minimal SpatiaLite database with Python - 2021-12-17
• sqlite SQLite VACUUM: database or disk is full - 2022-08-29
• python Running PyPy on macOS using Homebrew - 2022-09-14
• python Using the sqlite3 Python module in Pyodide - Python WebAssembly - 2021-10-18
• sqlite Compile a new sqlite3 binary on Ubuntu - 2020-04-30
• sqlite Importing CSV data into SQLite with .import - 2021-07-13
• python os.remove() on Windows fails if the file is already open - 2022-10-25
• sqlite One-liner for running queries against CSV files with SQLite - 2022-06-20
• sqlite Loading SQLite extensions in Python on macOS - 2023-01-07
• sqlite Enabling WAL mode for SQLite database files - 2020-08-09

Created 2023-04-08T17:15:54-07:00 · Edit