在线程中创建的对象只能在同一个线程中使用

小开

最佳答案

Your cursor 'c' is not created in the same thread; it was probably initialized when the Flask app was run.

You probably want to generate SQLite objects (the conneciton, and the cursor) in the same method, such as:

  @app.route('/')
def dostuff():
with sql.connect("database.db") as con:
name = "bob"
cur = con.cursor()
cur.execute("INSERT INTO students (name) VALUES (?)",(name))
con.commit()
msg = "Done"

小开

Where you make your connection to the database add the following.

conn = sqlite3.connect('your.db', check_same_thread=False)

小开

In my case, I have the same issue with two python files creating sqlite engine and therefore possibly operating on different threads. Reading SQLAlchemy doc here, it seems it is better to use singleton technique in both files:

# maintain the same connection per thread
from sqlalchemy.pool import SingletonThreadPool
engine = create_engine('sqlite:///mydb.db',
poolclass=SingletonThreadPool)

It does not solve all cases, meaning I occasionally getting the same error, but i can easily overcome it, refreshing the browser page. Since I'm only using this to debug my code, this is OK for me. For more permanent solution, should probably choose another database, like PostgreSQL or other database

小开

engine = create_engine(
'sqlite:///restaurantmenu.db',
connect_args={'check_same_thread': False}
)

Works for me

小开

You can try this:

engine=create_engine('sqlite:///data.db', echo=True, connect_args={"check_same_thread": False})

It worked for me

小开

I had the same problem and I fixed it by closing my connection after every call:

results = session.query(something, something).all()
session.close()

小开

As mentioned in https://docs.python.org/3/library/sqlite3.html and pointed out by @Snidhi Sofpro in a comment

By default, check_same_thread is True and only the creating thread may use the connection. If set False, the returned connection may be shared across multiple threads. When using multiple threads with the same connection writing operations should be serialized by the user to avoid data corruption.

One way to achieve serialization:

import threading
import sqlite3
import queue
import traceback
import time
import random


work_queue = queue.Queue()


def sqlite_worker():
con = sqlite3.connect(':memory:', check_same_thread=False)
cur = con.cursor()
cur.execute('''
CREATE TABLE IF NOT EXISTS test (
id INTEGER PRIMARY KEY AUTOINCREMENT,
text TEXT,
source INTEGER,
seq INTEGER
)
''')
while True:
try:
(sql, params), result_queue = work_queue.get()
res = cur.execute(sql, params)
con.commit()
result_queue.put(res)
except Exception as e:
traceback.print_exc()


threading.Thread(target=sqlite_worker, daemon=True).start()


def execute_in_worker(sql, params):
# you might not really need the results if you only use this
# for writing unless you use something like https://www.sqlite.org/lang_returning.html
result_queue = queue.Queue()
work_queue.put(((sql, params), result_queue))
return result_queue.get(timeout=5)


def insert_test_data(seq):
time.sleep(random.randint(0, 100) / 100)
execute_in_worker(
'INSERT INTO test (text, source, seq) VALUES (?, ?, ?)',
['foo', threading.get_ident(), seq]
)


threads = []
for i in range(10):
thread = threading.Thread(target=insert_test_data, args=(i,))
threads.append(thread)
thread.start()


for thread in threads:
thread.join()


for res in execute_in_worker('SELECT * FROM test', []):
print(res)


# (1, 'foo', 139949462500928, 9)
# (2, 'foo', 139949496071744, 5)
# (3, 'foo', 139949479286336, 7)
# (4, 'foo', 139949487679040, 6)
# (5, 'foo', 139949854099008, 3)
# (6, 'foo', 139949470893632, 8)
# (7, 'foo', 139949862491712, 2)
# (8, 'foo', 139949845706304, 4)
# (9, 'foo', 139949879277120, 0)
# (10, 'foo', 139949870884416, 1)

As you can see, the data is inserted out of order but it's still all handled one by one in a while loop.

小开

The error doesn't lie on the variables called in your .execute(), but rather the object instances that SQLite uses to access the DB.
I assume that you have:

conn = sqlite3.connect('your_database.db')
c = conn.cursor()

somewhere at the top of the Flask script, & this would be initialized when you first run the script.
When the register function is called, a new thread, different from the initial script run handles the process. Thus, in this new thread, you're utilizing object instances that are from a different thread, which SQLite captures as an error: rightfully so, because this may lead to data corruption if you anticipate for your DB to be accessed by different threads during the app run.
So a different method, instead of disabling the check-same-thread SQLite functionality, you could try initializing your DB connection & cursor within the HTTP Methods that are being called.
With this, the SQLite objects & utilization will be on the same thread at runtime.

The code would be redundant, but it might save you in situations where the data is being accessed asynchronously, & will also prevent data corruption.

小开

Create "database.py":

import sqlite3


def dbcon():
return sqlite3.connect("your.db")

Then import:

from database import dbcon


db = dbcon()


db.execute("INSERT INTO users(name,email,username,password)
VALUES(?,?,?,?)", (name, email, username, password))

You probably won't need to close it because the thread will be killed right away.