6

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.
Randrian
  • 1,055
  • 12
  • 25
  • 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`). – Martijn Pieters Apr 06 '16 at 10:04
  • 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 https://peewee.readthedocs.org/en/2.0.2/peewee/cookbook.html#multi-threaded-applications – Martijn Pieters Apr 06 '16 at 10:10
  • 1
    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 – Randrian Apr 06 '16 at 11:30
  • This may be a Peewee limitation then; will reopen. – Martijn Pieters Apr 06 '16 at 11:35
  • Perhaps related: [sqlalchemy and SQLite shared cache](https://stackoverflow.com/q/27910829) – Martijn Pieters Apr 06 '16 at 11:37
  • 1
    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. – Randrian Apr 06 '16 at 11:55
  • 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](https://stackoverflow.com/q/15720700) only uses Python 2.7. – Martijn Pieters Apr 06 '16 at 11:56

1 Answers1

10

Working!!

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

Link

traeper
  • 452
  • 6
  • 9