2017 0426 LockableSqliteConnection

I wrote a webapp that frequently needs to read from, but rarely needs to write to, a sqlite database. I was not using an ORM for the project, and frequently had to write boilerplate code by hand to open a database connection, get a cursor, lock the database if I was doing a write, and so forth. I got tired of doing this, and eventually wrote a with-able class to handle both read-only and read/write connections to my database, called LockableSqliteConnection.

From its documentation:

LockableSqliteConnection: A with-able class that wraps a SQLite connection, a cursor, and a lock

When the with statement is begun, the internal cursor object is allocated, and the internal lock is acquired. When the with statements terminates, the internal cursor object is closed, the internal connection object is committed, and the internal lock object is released. Exiting the with statement does not close the connection; the caller is responsible for this, but we do provide a convenience method to do it.

Usable like so:

lockableconn = LockableSqliteConnection(
    "file:///some/database.sqlite?cache=shared")

with lockableconn.ro as connection:
    connection.cursor.execute("SELECT * FROM SOMEWHERE")
    results = connection.cursor.fetchall()

with lockableconn.rw as connection:
    connection.cursor.execute("INSERT SOMETHING INTO SOMEWHERE")

lockableconn.close()

WARNING: This class has the read/write property LockableSqliteConnection.rw and the read-only property LockableSqliteConnection.ro, but the read-only property is not a security boundary. It exists so that the class knows whether it needs to lock the database, NOT to enforce that database changes don’t happen in SQL that you pass to its cursor. Using the .rw property will also handle commiting changes made in read/write mode, so if you’re doing complicated database transactions which you might have to roll back halfway through, you will have to handle that yourself (or perhaps LockableSqliteConnection isn’t a good tool for your use case).

WARNING: Inside of the with statement, take care not to call other code that will use a with statement on the same LockableSqliteConnection object in read/write mode. This sounds obvious, but it’s easy to do when the with statement might be in another function which is itself called inside a with statement. For instance, this code will deadlock, because func2() locks the connection until func1() completes, but func1() will not finish until the lock is released:

lockableconn = LockablesqliteConnection(
    "file:///some/database.sqlite?cache=shared")
def func1():
    with lockableconn.rw as connection:
        connection.cursor.execute("SOME SQL HERE")
        results = connection.cursor.fetchall()
def func2():
    with lockableconn.rw as connection:
        func1()

This class is intended to take the place of more cumbersome syntax like:

lock = threading.Lock()
dbconn = sqlite3.connect(
    "file:///some/database.sqlite?cache=shared",
    uri=True, check_same_thread=False)
with lock:
    with dbconn as connection:
        cursor = connection.cursor()
        cursor.execute("SOME SQL HERE")
        results = cursor.fetchall()
        connection.commit()
        cursor.close()