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.

Micah R Ledbetter · Accepted Answer · 2016-12-18 08:51:44Z

6

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.

answered Dec 18, 2016 at 8:51

Micah R Ledbetter

1,1161 gold badge12 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

K Thorngren Over a year ago

Tried the code and it seems to work good. Haven't fully tested it but did find one issue. The self.lock.release() should be the last line in the "_ exit _" function. Otherwise the lock can be released while a new connection is being made causing the cursor for the new connection to be set to None.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related