How can I add a SQLite database in memory in Python?

Question

I would like to combine SQLite databases, and some of them may be in memory. I create databases in memory by specifying the database path as :memory: After this post, using the attach SQLite function seems simple and efficient. But how can I specify my database in memory as a source for attachment?

For example, I would like to do something like:

 c1 = sqlite3.connect(":memory:") c1.execute(...create table, insert a bunch, commit...) c2 = sqlite3.connect(":memory:") c2.execute(""" ATTACH ? AS ToMerge; BEGIN; INSERT INTO Records SELECT * FROM ToMerge.Records; COMMIT; """, (c1.get_attach_id(), )) 

but, of course, c1.get_attach_id() is the method I used for demo purposes, since using the string :memory: would be ambiguous. How can I specify an existing c1 database?

Answer 1

Simple :memory: string connecting to a database in memory cannot be shared or joined to other connections.

You need to use file: connection string with the name file: URI with the parameter ?cache=shared order to be able to share the database in memory between the connections; then you can also attach to it:

 # first connection c1 = sqlite3.connect("file::memory:?cache=shared", uri=True) # second connection, to the *same database* c2 = sqlite3.connect("file::memory:?cache=shared", uri=True) # third connection, to a different database altogether c3 = sqlite3.connect('/tmp/sqlite3.db', uri=True) # can attach to the shared in-memory database, but only if you used # uri=True on the original connection c3.execute("ATTACH DATABASE 'file::memory:?cache=shared' AS inmem") 

See in-memory database documentation .

Note that there can only be one such shared database in memory; all other databases in memory must remain closed to connect. Use databases with real file system storage if you need more complex settings; In any case, they are easy enough to clean if you create them in the tempfile.mkdtemp() temporary directory each.

Demo version:

 >>> import sqlite3 >>> c1 = sqlite3.connect("file::memory:?cache=shared", uri=True) >>> c1.execute('CREATE TABLE foo (bar, baz)') <sqlite3.Cursor object at 0x106839490> >>> c1.execute("INSERT INTO foo VALUES ('spam', 'ham')") <sqlite3.Cursor object at 0x106839500> >>> c1.commit() >>> c2 = sqlite3.connect("file::memory:?cache=shared", uri=True) >>> list(c2.execute('SELECT * FROM foo')) [(u'spam', u'ham')] >>> c3 = sqlite3.connect('/tmp/sqlite3.db', uri=True) >>> c3.execute("ATTACH DATABASE 'file::memory:?cache=shared' AS inmem") <sqlite3.Cursor object at 0x1068395e0> >>> list(c3.execute('SELECT * FROM inmem.foo')) [(u'spam', u'ham')] 

Support for connections with a shared cache in memory was added in SQLite version 3.7.13; for Python, you can check the version of the base library using sqlite3.sqlite_version (string) or sqlite3.sqlite_version_info (integer tuple):

 >>> sqlite3.sqlite_version_info (3, 8, 10, 2)