Python Sqlite3 attaching to a memory database

Question

I am using Python 2.7 with sqlite3 version 2.6.0. I am trying to create a memory database, attach to it from a physical database and insert data, then query it back later. I am having issues if anyone can help.

The following two fail with the error message "unable to open database file"

con = sqlite3.connect(":memory:?cache=shared")
con = sqlite3.connect("file::memory:?cache=shared")

The following works until I attempt to access a table in the attached DB. I can do this with physical databases with no problem. I suspect the issue is not having the cache=shared.

con = sqlite3.connect(":memory:")
cursor = con.cursor()
cursor.executescript("create table table1 (columna int)")
cursor.execute("select * from table1")
con2 = sqlite3.connect("anotherdb.db")
cursor2 = con2.cursor()
cursor2.execute("attach database ':memory:' as 'foo'")
cursor2.execute("select * from foo.table1")

The error from the last select is "no such table: foo.table1".

Thanks in advance.

Answer 1

The SQLite library shipped with Python 2.x does not have URI file names enabled, so it is not possible to open an in-memory database in shared-cache mode.

You should switch to apsw, or Python 3.