sqlite - works with file, dies with :memory:

Question

My script fails tests when working with SQLite base (via sqlalchemy) created in :memory: and passes tests when working with a base created with physical file.

The script is multi-threaded. I know it is not the best idea to use SQLite with multiple threads (locking, etc.), but I use SQLite to test script's DB interface only.

When I use :memory:, the script dies complaining that there is no table:

OperationalError: (OperationalError)
    no such table: content_info u'INSERT INTO content_info ...

The testing procedure (with nose) is as follows:

def setup_database():
    global global_engine
    # create database columns
    engine = sqlalchemy.create_engine(...)
    Base.metadata.create_all(engine)
    global_engine = engine

@with_setup(setup_database)
def test_task_spoolers():
    check_database_content_from_another_thread()

def check_database_content_from_another_thread():
    global global_engine
    # from within other thread
    # create new session using global_engine
    # do some inserts

So in the setup I do create the database and the columns. I can see it also in logs (echo=True):

12:41:08 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE content_info (...

12:41:08 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
12:41:08 INFO sqlalchemy.engine.base.Engine INSERT INTO
    content_info (base_id, request_id, timestamp, ...
12:41:08 INFO sqlalchemy.engine.base.Engine (0, 0, 0, 'dummy_variant',
    None, 0)
12:41:08 INFO sqlalchemy.engine.base.Engine ROLLBACK
Exception in thread Thread-1:
Traceback (most recent call last):
OperationalError: (OperationalError)
    no such table: content_info u'INSERT INTO ...

My guess was that when I create the base in thread A, and then I use it in thread B, then B starts to operate on the base before it was actually created. But, I added time.sleep(3) after create_all and it didn't work.

And, as mentioned before, it works when I use a file instead of :memory:, even if the file is placed on virtual partition (so, in fact in memory). Any idea why is that?

Answer 1

You cannot create multiple connections to an in-memory database. Instead, a new connection to :memory: creates a new database.

From the SQLite documentation:

Every :memory: database is distinct from every other. So, opening two database connections each with the filename ":memory:" will create two independent in-memory databases.

This is different from an on-disk database, where creating multiple connections with the same connection string means you are connecting to one database.

You are creating a new connection for the thread, thus creating a new database that does not have the tables created.

Answer 2

You can use this approach to share the the connection between multiple threads:

from sqlalchemy.pool import StaticPool
engine = create_engine('sqlite://',
                connect_args {'check_same_thread':False},
                poolclass=StaticPool)

https://docs.sqlalchemy.org/en/13/dialects/sqlite.html#using-a-memory-database-in-multiple-threads