multithreading - How to share single SQLite connection in multi-threaded Python application

Question

I am trying to write a multi-threaded Python application in which a single SQlite connection is shared among threads. I am unable to get this to work. The real application is a cherrypy web server, but the following simple code demonstrates my problem.

What change or changes to I need to make to run the sample code, below, successfully?

When I run this program with THREAD_COUNT set to 1 it works fine and my database is updated as I expect (that is, letter "X" is added to the text value in the SectorGroup column).

When I run it with THREAD_COUNT set to anything higher than 1, all threads but 1 terminate prematurely with SQLite related exceptions. Different threads throw different exceptions (with no discernible pattern) including:

OperationalError: cannot start a transaction within a transaction 

(occurs on the UPDATE statement)

OperationalError: cannot commit - no transaction is active 

(occurs on the .commit() call)

InterfaceError: Error binding parameter 0 - probably unsupported type. 

(occurs on the UPDATE and the SELECT statements)

IndexError: tuple index out of range

(this one has me completely puzzled, it occurs on the statement group = rows[0][0] or '', but only when multiple threads are running)

Here is the code:

CONNECTION = sqlite3.connect('./database/mydb', detect_types=sqlite3.PARSE_DECLTYPES, check_same_thread = False)
CONNECTION.row_factory = sqlite3.Row

def commands(start_id):

    # loop over 100 records, read the SectorGroup column, and write it back with "X" appended.
    for inv_id in range(start_id, start_id + 100):

        rows = CONNECTION.execute('SELECT SectorGroup FROM Investment WHERE InvestmentID = ?;', [inv_id]).fetchall()
        if rows:
            group = rows[0][0] or ''
            msg = '{} inv {} = {}'.format(current_thread().name, inv_id, group)
            print msg
            CONNECTION.execute('UPDATE Investment SET SectorGroup = ? WHERE InvestmentID = ?;', [group + 'X', inv_id])

        CONNECTION.commit()

if __name__ == '__main__':

    THREAD_COUNT = 10

    for i in range(THREAD_COUNT):
        t = Thread(target=commands, args=(i*100,))
        t.start()

Answer 1

It's not safe to share a connection between threads; at the very least you need to use a lock to serialize access. Do also read http://docs.python.org/2/library/sqlite3.html#multithreading as older SQLite versions have more issues still.

The check_same_thread option appears deliberately under-documented in that respect, see http://bugs.python.org/issue16509.

You could use a connection per thread instead, or look to SQLAlchemy for a connection pool (and a very efficient statement-of-work and queuing system to boot).