python多线程访问sqlite3_如何在多线程Python应用程序中共享单个SQLite连接

我在编写一个简单的WSGI服务器以获得乐趣和学习时遇到了SqLite线程问题。

在Apache下运行时，WSGI本质上是多线程的。

以下代码似乎对我有效：import sqlite3

import threading

class LockableCursor:

def __init__ (self, cursor):

self.cursor = cursor

self.lock = threading.Lock ()

def execute (self, arg0, arg1 = None):

self.lock.acquire ()

try:

self.cursor.execute (arg1 if arg1 else arg0)

if arg1:

if arg0 == 'all':

result = self.cursor.fetchall ()

elif arg0 == 'one':

result = self.cursor.fetchone ()

except Exception as exception:

raise exception

finally:

self.lock.release ()

if arg1:

return result

def dictFactory (cursor, row):

aDict = {}

for iField, field in enumerate (cursor.description):

aDict [field [0]] = row [iField]

return aDict

class Db:

def __init__ (self, app):

self.app = app

def connect (self):

self.connection = sqlite3.connect (self.app.dbFileName, check_same_thread = False, isolation_level = None) # Will create db if nonexistent

self.connection.row_factory = dictFactory

self.cs = LockableCursor (self.connection.cursor ())

使用示例：if not ok and self.user: # Not logged out

# Get role data for any later use

userIdsRoleIds = self.cs.execute ('all', 'SELECT role_id FROM users_roles WHERE user_id == {}'.format (self.user ['id']))

for userIdRoleId in userIdsRoleIds:

self.userRoles.append (self.cs.execute ('one', 'SELECT name FROM roles WHERE id == {}'.format (userIdRoleId ['role_id'])))

另一个例子：self.cs.execute ('CREATE TABLE users (id INTEGER PRIMARY KEY, email_address, password, token)')

self.cs.execute ('INSERT INTO users (email_address, password) VALUES ("{}", "{}")'.format (self.app.defaultUserEmailAddress, self.app.defaultUserPassword))

# Create roles table and insert default role

self.cs.execute ('CREATE TABLE roles (id INTEGER PRIMARY KEY, name)')

self.cs.execute ('INSERT INTO roles (name) VALUES ("{}")'.format (self.app.defaultRoleName))

# Create users_roles table and assign default role to default user

self.cs.execute ('CREATE TABLE users_roles (id INTEGER PRIMARY KEY, user_id, role_id)')

defaultUserId = self.cs.execute ('one', 'SELECT id FROM users WHERE email_address = "{}"'.format (self.app.defaultUserEmailAddress)) ['id']

defaultRoleId = self.cs.execute ('one', 'SELECT id FROM roles WHERE name = "{}"'.format (self.app.defaultRoleName)) ['id']

self.cs.execute ('INSERT INTO users_roles (user_id, role_id) VALUES ({}, {})'.format (defaultUserId, defaultRoleId))

注意：上面的代码是实验性的，当将其与(许多)并发请求(例如作为WSGI服务器的一部分)一起使用时，可能会出现(基本的)问题。性能对我的应用程序并不重要。最简单的事情可能就是使用MySql，但我喜欢尝试一下，SqLite的零安装对我很有吸引力。如果有人认为上面的代码有根本性的缺陷，请做出反应，因为我的目的是学习。如果没有，我希望这对其他人有用。