With Python's sqlite3 module, can I save the in-memory DB to disk after creating it?

Question

The sqlite3 command line tool allows me to .save <file> the in-memory database without having to dump the SQL in its string/script form.

I'd like to do something similar without writing too much boilerplate code. However, I do not always want to save it, which is why I would like to avoid an on-disk DB before I actually need it.

Is there a way to do this in Python, short of dumping the SQL in string form and importing it to the on-disk file (which I know how to do and have already implemented)?

Answer 1

There doesn't seem to be a single line solution.

Backing up an in-memory sqlite database can be done using the iterdump method of the connection object. Of course, this is only just short of dumping the sql text and importing it.

import sqlite3
# set up a database in memory

c = sqlite3.connect(':memory:')
c.execute('CREATE TABLE my_table (id int, name text);')
c.execute("INSERT INTO my_table VALUES (1, 'bruce'), (2, 'wayne'), (3, 'BATMAN');")
c.commit()

# write database to disk

c2 = sqlite3.connect('mydb.db')
with c2:
  for line in c.iterdump():
    if line not in ('BEGIN;', 'COMMIT;'): # let python handle the transactions
      c2.execute(line)
c2.commit()

Answer 2

Since Python 3.7, you can use the Connection.backup method:

db_mem = sqlite3.connect(':memory:')
# ...
db_disk = sqlite3.connect('result.db')
db_mem.backup(db_disk)

This is the inverse operation of How to load existing db file to memory in Python sqlite3?.