python应用程序开发者共享_如何在多线程Python应用程序中共享单个SQLite连接

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()

解决方案

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).