Frequent "OperationalError: unable to open database file" with in memory sqlite3 database
Question: I'm using an in-memory database for a Python app, and am hitting pretty
frequent "unable to open database file" errors when attempting to access the
same in memory database from multiple threads.

Because it says that the in memory database is automatically cleaned up when
the last connection closes, I initialize and leave a connection open to the
database like this:

    
    
    staging_cache = None
    
    MEMORY_CACHE_PATH = "file::memory:?cache=shared"
    
    def init_staging():
      global staging_cache
      staging_cache = sqlite3.connect(MEMORY_CACHE_PATH)
      staging_cache.execute("""CREATE TABLE sigcache (path TEXT NOT NULL COLLATE NOCASE, sig BLOB,
        updated_date REAL, sha512 TEXT DEFAULT "", item_type INT, depth INT, mtime REAL, size INT, revision_id INT)""")
      staging_cache.execute("CREATE INDEX sigcache_rev_id_index on sigcache(path COLLATE NOCASE, revision_id)")
    

Then from some other threads:

    
    
    with sqlite3.connect(MEMORY_CACHE_PATH, timeout=20) as db_conn:
      db_conn.execute(query_string, param_tuple)
    

It seems like I keep hitting OperationalError: cannot open database file

I'm looking at the documentation at <http://www.sqlite.org/inmemorydb.html>
but I can't figure out if I'm doing something wrong or not.

EDIT: Apparently sqlite3 does not accept "file::memory:?cache=shared" as a
dbname, it only accepts :memory:. It was creating a file called
"file::memory:?cache=shared"

In any case, with sqlalchemy, there was a problem with persisting the data. I
solved the problem by just using sqlite3 with :memory:, creating a single
connection with check_same_thread=False, and wrapping all accesses in a single
threaded mutex.


Answer: SQLite and multiple threads don't mix well. I'd suggest using SQLAlchemy's
[connection
pooling](http://docs.sqlalchemy.org/en/rel_0_9/core/pooling.html#pooling-
plain-db-api-connections) (or even the whole package) to go around the
problem. Maybe something like this if you're not willing to rewrite
everything:

    
    
    import sqlite3
    import sqlalchemy.pool
    
    sqlite = sqlalchemy.pool.manage(sqlite3, poolclass=sqlalchemy.pool.SingletonThreadPool)
    connection = sqlite.connect(':memory:')
    

They also have some [notes about
multithreading](http://docs.sqlalchemy.org/en/rel_0_9/dialects/sqlite.html#threading-
pooling-behavior):

> So while even :memory: databases can be shared among threads in modern
> SQLite, Pysqlite doesn’t provide enough thread-safety to make this usage
> worth it.

