How can I copy an in-memory SQLite database to another in-memory SQLite database in Python?

Question

I'm writing a test suite for Django that runs tests in a tree-like fashion. For example, Testcase A might have 2 outcomes, and Testcase B might have 1, and Testcase C might have 3. The tree looks like this

      X
     /
A-B-C-X
 \   \
  B   X
   \   X
    \ /
     C-X
      \
       X

For each path in the tree above, the database contents may be different. So at each fork, I'm thinking of creating an in-memory copy of the current state of the database, and then feeding that parameter into the next test.

Anyone have an idea about how to essentially copy the in-memory database to another one, and then get a reference to pass that database around?

Thanks!

what is an in-memory database (in terms of django or python)? — akonsu
– akonsu, Commented Nov 8, 2011 at 3:38
One not stored on disk. In Django you can specify SQLite to use an in-memory DB by specifying None or :memory: as the database name. — Dan Loewenherz
– Dan Loewenherz, Commented Nov 8, 2011 at 5:35
thanks. which means your question is how to replicate a database that is being used by a django application, from this same application, correct? — akonsu
– akonsu, Commented Nov 8, 2011 at 14:36

Arnaud P · Accepted Answer · 2017-06-07 06:53:30Z

4

Alright, after a fun adventure I figured this one out.

from django.db import connections
import sqlite3

# Create a Django database connection for our test database
connections.databases['test'] = {'NAME': ":memory:", 'ENGINE': "django.db.backends.sqlite3"}

# We assume that the database under the source_wrapper hasn't been created
source_wrapper = connections['default']  # put alias of source db here
target_wrapper = connections['test'] 

# Create the tables for the source database
source_wrapper.creation.create_test_db()

# Dump the database into a single text query
query = "".join(line for line in source_wrapper.connection.iterdump())

# Generate an in-memory sqlite connection
target_wrapper.connection = sqlite3.connect(":memory:")
target_wrapper.connection.executescript(query)

And now the database called test will be a carbon copy of the default database. Use target_wrapper.connection as a reference to the newly created database.

edited Jun 7, 2017 at 6:53

Arnaud P

12.7k8 gold badges59 silver badges71 bronze badges

answered Nov 8, 2011 at 22:17

Dan Loewenherz

11.3k7 gold badges54 silver badges85 bronze badges

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

1 Comment

Arnaud P Over a year ago

I know it's five and a half years later, but man, you just made my day ! I couldn't find a way to dump my test memory db to a file, now it's done. I took the liberty to bring a few improvements to your answer too, if you don't mind.

Raymond Hettinger · Accepted Answer · 2015-07-03 19:52:49Z

Here is a function that copies databases. Both the source and destination can be in-memory or on-disk (the default destination is a copy in-memory):

import sqlite3

def copy_database(source_connection, dest_dbname=':memory:'):
    '''Return a connection to a new copy of an existing database.                        
       Raises an sqlite3.OperationalError if the destination already exists.             
    '''
    script = ''.join(source_connection.iterdump())
    dest_conn = sqlite3.connect(dest_dbname)
    dest_conn.executescript(script)
    return dest_conn

And here is an example of how it applies to your use case:

from contextlib import closing

with closing(sqlite3.connect('root_physical.db')) as on_disk_start:
    in_mem_start = copy_database(on_disk_start)

a1 = testcase_a_outcome1(copy_database(in_mem_start))
a2 = testcase_a_outcome1(copy_database(in_mem_start))
a1b = test_case_b(a1)
a2b = test_case_b(a2)
a1bc1 = test_case_c_outcome1(copy_database(a1b))
a1bc2 = test_case_c_outcome2(copy_database(a1b))
a1bc3 = test_case_c_outcome3(copy_database(a1b))
a2bc1 = test_case_c_outcome1(copy_database(a2b))
a2bc2 = test_case_c_outcome2(copy_database(a2b))
a2bc3 = test_case_c_outcome3(copy_database(a2b))

Collectives™ on Stack Overflow

How can I copy an in-memory SQLite database to another in-memory SQLite database in Python?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related