21

I would like to create a :memory: database in python and access it from different threads. Essentially something like:

class T(threading.Thread):
    def run(self):
        self.conn = sqlite3.connect(':memory:')
        # do stuff with the database

for i in xrange(N):
    T().start()

and have all the connections referring to the same database.

I am aware of passing check_same_thread=True to the connect function and sharing the connection between threads but would like to avoid doing that if possible. Thanks for any help.

EDIT: corrected a typo. I originally said "have all the connections referring to the same thread" substituting thread for database.

Improve this question
2
  • Can you please describe the scenario for which this is needed? May be there are other options rather than using sqlite from multiple threads? Commented Aug 15, 2010 at 18:37
  • 1
    @Muhammad Alkarouri I needed it for unit testing a multi threaded database application. If a file is used (as it would be in actual application) then I can open multiple connections to if just fine. I ended up wrapping the database logic in one thread that uses the consumer pattern and returns deferreds that it fills when it gets to the request. Commented Aug 15, 2010 at 19:09

2 Answers 2

Reset to default
40

SQLite had improved over last 4 years, so now shared in-memory databases are possible. Check the following code:

import sqlite3

foobar_uri = 'file:foobar_database?mode=memory&cache=shared'
not_really_foobar_uri = 'file:not_really_foobar?mode=memory&cache=shared'

# connect to databases in no particular order
db2 = sqlite3.connect(foobar_uri, uri=True)
db_lol = sqlite3.connect(not_really_foobar_uri, uri=True)
db1 = sqlite3.connect(foobar_uri, uri=True)

# create cursor as db2
cur2 = db2.cursor()

# create table as db2
db2.execute('CREATE TABLE foo (NUMBER bar)')

# insert values as db1
db1.execute('INSERT INTO foo VALUES (42)')
db1.commit()

# and fetch them from db2 through cur2
cur2.execute('SELECT * FROM foo')
print(cur2.fetchone()[0])  # 42

# test that db_lol is not shared with db1 and db2
try:
    db_lol.cursor().execute('SELECT * FROM foo')
except sqlite3.OperationalError as exc:
    print(exc)  # just as expected

Database accesses are entangled intentionally, to show that two connections to the in-memory database with the same name are the same from SQLite's point of view.

References:

  1. SQLite URIs
  2. SQLite shared cache

Unfortunately, connection by URI is only available since Python 3.4. However, if you have Python 2.6 or later (but not Python 3), builtin sqlite3 module is still capable of importing APSW connections, which can be used to achieve same effect. Here goes the drop-in sqlite3 module replacement:

from sqlite3 import *
from sqlite3 import connect as _connect
from apsw import Connection as _ApswConnection
from apsw import SQLITE_OPEN_READWRITE as _SQLITE_OPEN_READWRITE
from apsw import SQLITE_OPEN_CREATE as _SQLITE_OPEN_CREATE
from apsw import SQLITE_OPEN_URI as _SQLITE_OPEN_URI

# APSW and pysqlite use different instances of sqlite3 library, so initializing
# APSW won't help pysqlite. Because pysqlite does not expose any way to
# explicitly call sqlite3_initialize(), here goes an ugly hack. This only has
# to be done once per process.
_connect(':memory:').close()

def connect(database, timeout=5.0, detect_types=0, isolation_level=None,
            check_same_thread=True, factory=Connection, cached_statements=100,
            uri=False):
    flags = _SQLITE_OPEN_READWRITE | _SQLITE_OPEN_CREATE

    if uri:
        flags |= _SQLITE_OPEN_URI

    db = _ApswConnection(database, flags, None, cached_statements)
    conn = _connect(db, timeout, detect_types, isolation_level, 
                    check_same_thread, factory, cached_statements)

    return conn
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

thanx a lot! I was looking at python 3.3 docs, so i overlooked the uri option of sqlite3.connect(), which is only available since Python 3.4.
what is the best alternative (if any) for anyone still on Python 2.7?
unfortunately this is causing my python to crash....will be updating to Python 3 unless you have another suggestion for an in-memory db that i can access from different threads
@baconwichsand if it crashes, please, triple check you had not thrown out _connect(':memory:').close() line — it is critically important to do a connection with pysqlite once, because it will call sqlite3_initialize() implicitly. Without this line, sqlite instance of pysqlite will have no mutexes created and will die when trying to lock on connection.
@baconwichsand if it will still crash, please tell your OS and exact Python version, and I will update an answer to reflect your case.
|
6

Without hacking sqlite3 library itself you cannot reuse :memory: database, because it's guaranteed to be exclusive and private for every connection. To hack access to it, look closer at src/pager.c in sqlite3 distribution (not Python module distribution). Maybe, most convenient way to implement this would be make :memory:00, :memory:something, :memory:okay_hai etc. aliases to address different pPager->memDb pointers through some simple C-side mapping.

Improve this answer

3 Comments

Hi there, any new information on this almost 4 years later? Is it still not possible with current sqlite3 in Python?
@compostus, it's now possible with specifying database URI, refer to this sqlite.org/uri.html, look for mode=memory and cache=shared.
@compostus, see my new answer to this question.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.