python: how to share an sqlite connection among threads with queues?

Question

I'm using Python 3.2.1 on Arch Linux x86_64.
I'm trying to update an sqlite database in a threaded, timed loop with some code similar to the following:

import sqlite3
from threading import Timer
from queue import Queue

class DBQueue(Queue):
    def give(self, item):
        self.task_done()
        self.join()
        self.put(item)
        return True


def timer():
    print('A')
    Timer(3, add).start()


def add():
    print('B')
    db = qdb.get()
    cur = db.cursor()
    cur.execute('INSERT INTO Foo (id) VALUES (NULL)')
    qdb.give(db)
    timer()

qdb = DBQueue()
# SOLUTION #1:
# qdb.put(sqlite3.connect(':memory:', check_same_thread=False))
# SOLUTION #2: see Eli Bendersky's answer
qdb.put(sqlite3.connect(':memory:'))
db = qdb.get()
cur = db.cursor()
cur.execute('CREATE TABLE Foo (id INTEGER PRIMARY KEY)')
qdb.give(db)
timer()

which unfortunately returns:

A
B
Exception in thread Thread-1:
Traceback (most recent call last):
  File "/usr/lib/python3.2/threading.py", line 736, in _bootstrap_inner
    self.run()
  File "/usr/lib/python3.2/threading.py", line 942, in run
    self.function(*self.args, **self.kwargs)
  File "/home/dario/dev/python/prova/src/prova4.py", line 27, in add
    cursor = db.cursor()
sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread.The object was created in thread id 140037302638336 and this is thread id 140037262886656

Sharing only a cursor doesn't give better results:

conn = sqlite3.connect(':memory:')
qdb.put(conn.cursor())

I'm quite sure I haven't understood at all how to use queues to share databases among threads, can anybody help me? Thank you!

Solution

You don't need the Queue - just use separate connections to the same database from the two threads. Keep in mind that you shouldn't expect much in terms of ordering when separate connections commit data into the DB. Treat it as if you had two different instances of your program accessing the DB simultaneously.

---

If for some reason you feel that you absolutely must share a connection, then try this: since you hit a problem of creating SQLite objects from one thread and using them in another, why not delegate the task of handling the db/connection in a single thread, and let it communicate with others via Queues. More specifically:

• Thread DB_thread: "owns" the connections. Gets commands in a queue from other threads, executes them, places results in a "results queue"
• Threads A, B, C: pass "commands" into the queue and take results from the "results queue".

Note that these commands are not SQLite objects.

Comments

[kynikos]

Thanks, however in the real program I'm using a file database, not :memory:, and I don't want to commit changes until I save it explicitly; python documentation says "When a database is accessed by multiple connections, and one of the processes modifies the database, the SQLite database is locked until that transaction is committed", however I'll give it a try anyway and report the results here

[kynikos]

It's as I remembered it, if I use multiple connections I have to commit the changes for them to be available to other connections, and I'd like to avoid that. Is it really impossible to use queues with sqlite? Otherwise I will have to copy the whole database in :memory: or use temp files...

[kynikos]

Cool, I'll try that, I've never passed commands in a queue, I think I will have to limit both "commands" and "results" queues to 1 item each, so that I'm always sure that I retrieve the right result. What do you think of the check_same_thread=False idea that I have commented to the question?

[Eli Bendersky]

@kynikos: I'm not familiar with that option

[kynikos]

Wow, after much struggling I've managed to implement your idea, and it seems to work well ^^ However I have also tested the check_same_thread way, and it seems cleaner and even faster (for those reading, in this case be sure to implement a way to avoid simultaneous accesses to the database, like queueing). Thank you again Eli, your answer works, so I'm accepting it.

[dofine]

@kynikos Hi this has been years but I'm struggling with a similar problem now... Could you please share what you've finally implemented? Thanks!

[kynikos]

@dofine I used this on a rather big project: here's the DBQueue like in my question above; then there's a Protection object that prevents simultaneous accesses to the database; this is a timed event that blocks and releases the database. If I were writing it today, though, I would use Eli's second idea instead.

[dofine]

@kynikos Thank you for sharing! :)