python - ProgrammingError: SQLite objects created in a thread can only be used in that same thread

Question

i'm fairly new to programming. I've tried MySQL before, but now it's my first time using SQLite in a python flask website. So maybe I'm using MySQL syntax instead of SQLite, but I can't seem to find the problem.

Piece of my code: 

@app.route('/register', methods=['GET', 'POST'])
def register():
    form = RegisterForm(request.form)
    if request.method=='POST' and form.validate():
        name =  form.name.data 
        email = form.email.data
        username = form.username.data
        password = sha256_crypt.encrypt(str(form.password.data))

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

        conn.commit

        conn.close()

The error:
 File "C:\Users\app.py", line 59, in register c.execute("INSERT INTO users(name,email,username,password) VALUES(?,?,?,?)", (name, email, username, password))
 ProgrammingError: SQLite objects created in a thread can only be used in that 
 same thread.The object was created in thread id 23508 and this is thread id 
 22640

Does this mean I can't use the name, email username & password in an HTML file? How do I solve this?

Thank you.

Answer 1

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"

Answer 2

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

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

Answer 3

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

Works for me

Answer 4

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

Answer 5

You can try this:

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

It worked for me

Answer 6

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

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