如何在多线程Python应用程序中共享单个SQLite连接 [英] How to share single SQLite connection in multi-threaded Python application
问题描述
我正在尝试编写一个多线程Python应用程序，其中在线程之间共享一个SQlite连接.我无法使它正常工作.真正的应用程序是一个小巧的Web服务器，但是下面的简单代码演示了我的问题.
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?
当我在THREAD_COUNT设置为1的情况下运行此程序时，它可以正常工作，并且数据库已按预期进行更新(即，字母"X"被添加到SectorGroup列的文本值中).
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).
当我将THREAD_COUNT设置为大于1的任何值运行它时，除1之外的所有线程都会因SQLite相关异常而提前终止.不同的线程抛出不同的异常(没有可识别的模式)，包括:
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
(发生在UPDATE语句上)
OperationalError: cannot commit - no transaction is active
(发生在.commit()调用上)
(occurs on the .commit() call)
InterfaceError: Error binding parameter 0 - probably unsupported type.
(出现在UPDATE和SELECT语句上)
IndexError: tuple index out of range
(这让我完全困惑，它出现在语句group = rows[0][0] or ''上，但仅在运行多个线程时才出现)
(this one has me completely puzzled, it occurs on the statement group = rows[0][0] or '', but only when multiple threads are running)
这是代码:
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()
推荐答案
在线程之间共享连接是不安全的.至少您需要使用锁来序列化访问.还要阅读 http://docs.python.org/2/library/sqlite3. html#multithreading ，因为较旧的SQLite版本仍然存在更多问题.
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.
在这方面，check_same_thread选项故意未充分记录在案，请参见 http://bugs.python.org /issue16509 .
The check_same_thread option appears deliberately under-documented in that respect, see http://bugs.python.org/issue16509.
您可以改为使用每个线程的连接，或使用SQLAlchemy作为连接池(以及用于引导的非常有效的工作说明和排队系统).
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).
这篇关于如何在多线程Python应用程序中共享单个SQLite连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
