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)?

Haleemur Ali · Accepted Answer · 2014-12-15 01:35:48Z

7

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

edited Dec 15, 2014 at 1:35

answered Dec 15, 2014 at 1:19

Haleemur Ali

28.6k6 gold badges67 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mkrieger1 · Accepted Answer · 2022-01-07 11:34:14Z

3

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

answered Jan 7, 2022 at 11:34

mkrieger1

24.2k7 gold badges68 silver badges84 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related