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.

Solution 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"

Solution 2

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

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

Solution 3

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

Works for me

Solution 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

Solution 5

You can try this:

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

It worked for me

View more solutions

Comments

[softmarshmallow]

is this safe to use?

[merlin2011]

@uzu, I don't see why not, as long as you do your own synchronization to ensure only one thread uses the object at the same time.

[Snidhi Sofpro]

Some additional info for future readers of this thread. Per docs.python.org/3/library/sqlite3.html: 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.

[Joseph Drane]

I ran into this. I'm going through a udacity full stack dev course that appears to have some python2 stuff. So it's actually turning out to be a good learning opportunity. Either way worked for me. I had the following at the top of the app.py file and then I literally copied and pasted into that function (path) and voila problem solved. python DBSession = sessionmaker(bind=engine) session = DBSession()

[Cleb]

Does one also need a con.close() or does the with take care of this?

[user230910]

Hi J J, welcome to Stack Overflow! Please can you improve this answer? Use single backquotes ` to show code like this `code`, and explain why the code in question answers the question/does the job the person asked..

[jokoon]

@merlin2011 could you expand on that? What are things not to do? I'm not sure I completely understand

[Ramesh-X]

As long as only as a single thread is writing through the connection in a given time, this is safe to use.

[mxmlnkn]

@SnidhiSofpro So, I take it that it is safe to use the connection from multiple threads without any locking as long as all threads are only reading, not writing?

[Glenn Maynard]

This is the answer I was looking for, but it's absolutely the wrong answer to somebody who doesn't know what a thread is.

[Admin]

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

[arunppsg]

@softmarshmallow from SQLModel docs sqlmodel.tiangolo.com/tutorial/fastapi/simple-hero-api/… , it is safe to use check_same_thread = False when the same session object is not shared in more than one request. Some applications set it to True to prevent misuse.