Multiple SQLite connections to a database in :memory:

Question

Is it possible to access an in-memory SQLite database from different threads?

In the following sample code I create a SQLite database in memory and create a table. When I now go to a different execution context, which I think have to do when I go to a different thread, the created table isn't there anymore. If I would open a file based SQLite database, the table would be there.

Can I achieve the same behavior for an in-memory database?

from peewee import *
db = SqliteDatabase(':memory:')

class BaseModel(Model):
    class Meta:
        database = db

class Names(BaseModel):
    name = CharField(unique=True)

print(Names.table_exists())  # this returns False 
Names.create_table()
print(Names.table_exists())  # this returns True

print id(db.get_conn())  # Our main thread's connection.

with db.execution_context():
    print(Names.table_exists())  # going to another context, this returns False if we are in :memory: and True if we work on a file *.db
    print id(db.get_conn())  # A separate connection.

print id(db.get_conn())  # Back to the original connection.

Groundling

Use file::memory:?cache=shared to share an in-memory database. Requires sqlite 3.7.13 or newer (inspect sqlite3.sqlite_version or sqlite3.sqlite_version_info). – Ragwort

Note that normally Peewee and sqlite3 will let you share the connection across threads; just create one connection and use that across all the threads. See peewee.readthedocs.org/en/2.0.2/peewee/… – Ragwort

the file::memory:?cache=shared unfortunately doesn't work: from peewee import * db = SqliteDatabase('file::memory:?cache=shared') db.connect() gives me a file not found error. My SQLite version is 3.11.0 – Groundling

This may be a Peewee limitation then; will reopen. – Ragwort

Perhaps related: sqlalchemy and SQLite shared cache – Ragwort

Hmm, db = sqlite3.connect("file::memory:?cache=shared", uri=True) complains that uri is no valid keyword and when I omit it, a "file could not be opened" exception is thrown. It seems that I shouldn't use the in-memory database if I want to do threaded access. – Groundling

Interesting; there may be a minimal Python sqlite3 library version that I may not be aware of. uri=True is a Python 3.4 addition, but my file::memory:?cache=shared answer only uses Python 2.7. – Ragwort

Answer 1

Working!!

cacheDB = SqliteDatabase('file:cachedb?mode=memory&cache=shared')

Link

• http://charlesleifer.com/blog/managing-database-connections-with-peewee/
• https://groups.google.com/forum/#!topic/peewee-orm/78invrt3xyo

Panache