How should I handle multiple threads accessing a sqlite database in Python?

Question

It appears that SQLite has supported multithreaded access since 3.7.13, and Python's sqlite3 module has supported it since 3.4. To use it, you might write code like this:

import sqlite3
dburi = "file:TESTING_MEMORY_DB?mode=memory&cache=shared"
connection = sqlite3.connect(dburi, uri=True, check_same_thread=False)
with connection:
    cursor = conneciton.cursor()
    cursor.execute("SQL")

This works, but the first thing you'll discover is that you need to lock access to the database or else another thread could corrupt your data. That might look like this:

import threading
import sqlite3
dburi = "file:TESTING_MEMORY_DB?mode=memory&cache=shared"
connection = sqlite3.connect(dburi, uri=True, check_same_thread=False)
# NOTE: You'll need to share this same lock object with every other thread using the database
lock = threading.Lock()
with lock:
    with connection:
        cursor = connection.cursor()
        cursor.execute("SQL")
        connection.commit()

Now, if one thread acquires the lock, another thread cannot acquire it until the first thread closes it, and so long as all threads use the same lock object and remember to with lock: before they with connection:, your data will not be corrupted.

However, now I need a way to pass the lock around with the connection. You could do this with separate arguments, or perhaps with a custom class:

import threading
import sqlite3

class LockableSqliteConnection(object):
    def __init__(self, dburi):
        self.lock = threading.Lock()
        self.connection = sqlite3.connect(dburi, uri=True, check_same_thread=False)

dburi = "file:TESTING_MEMORY_DB?mode=memory&cache=shared"
lsc = LockableSqliteConnection(dburi)
with lsc.lock:
    with lsc.connection:
        cursor = lsc.connection.cursor()
        cursor.execute("SQL")
        lsc.connection.commit()

This is pretty good, since the name of the class reminds me of what I have, so at least I'm not likely to forget the lock and corrupt my data. But is there a way to do away with the two with statements? Ideally I'd like to combine them into a single with, since I shouldn't ever use the connection without the lock anyway.

Answer 1

I was able to write something that meets my needs with the help of this article: http://effbot.org/zone/python-with-statement.htm

My code looks like this:

import threading
import sqlite3

class LockableSqliteConnection(object):
    def __init__(self, dburi):
        self.lock = threading.Lock()
        self.connection = sqlite3.connect(dburi, uri=True, check_same_thread=False)
        self.cursor = None
    def __enter__(self):
        self.lock.acquire()
        self.cursor = self.connection.cursor()
        return self
    def __exit__(self, type, value, traceback):
        self.lock.release()
        self.connection.commit()
        if self.cursor is not None:
            self.cursor.close()
            self.cursor = None

Objects of this class are now directly usable with the with statement:

dburi = "file:TESTING_MEMORY_DB?mode=memory&cache=shared"
lsc = LockableSqliteConnection(dburi)
with lsc:
    lsc.cursor.execute("SQL")

It also conveniently opens a cursor for me and automatically commits changes I make to the database, which means that code which calls my new class is shorter. That's nice, but automatically committing database changes might not actually be the best idea... In more complex scenarios where you might want to start a transaction and then roll it back partway through, that functionality might cause trouble. My SQL needs are very simple, however, and I never roll back, so I've left it in my project for now.