Sqlite objects created in a thread can only be used in that same thread

sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread. In django 2.2

Mentioned in the document.

This the error message look like,

sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 140000522213120 and this is thread id 140000744696768.

Please suggest me a solution to rectify this problem, Anyone faced this issue before. Help me to solve this issue.

1 Answer 1

The problem here is that SQLite has to deal with conflicts arising due to concurrent access by multiple threads i.e., SQLite database created and accessed by one thread cannot allow another thread to access it. This may result from following scenarios:

Its always recommended that an ORM is used to deal with databases and efficiently manage their connection lifecycles. For Sqlite, the most widely used ORM is SqlAlchemy. Using an ORM can probably fix the issue.

However, for very simple applications, where using an ORM is just an overkill, you can tweak the way connection is created to the Sqlite database by allowing concurrent access. This can be done by setting check_same_thread parameter to False while establishing the connection:

Having said that, setting up Sqlite connection this way lays responsibility to handle concurrency on the application instead of the database and user should ensure that write operations to the database are serialized in order to avoid any dirty writes/updates.

Note: When using sqlalchemy, its important to use the right libraries and code segregation. I have particularly found this post helpful as well.

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

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.

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

9 Answers 9

Trending sort

Trending sort is based off of the default sorting method — by highest score — but it boosts votes that have happened recently, helping to surface more up-to-date answers.

It falls back to sorting by highest score if no posts are trending.

Switch to Trending sort

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

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:

You can try this:

It worked for me

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:

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

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

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:

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

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.

Using SQLAlchemy session from Flask raises «SQLite objects created in a thread can only be used in that same thread»

I have a Flask view which uses SQLAlchemy to query and display some blog posts. I am running my app using mod_wsgi. This view works the first time I go to the page, but returns a 500 error next time. The traceback shows the error ProgrammingError: SQLite objects created in a thread can only be used in that same thread. Why am I getting this error and how do I fix it?

3 Answers 3

Trending sort

Trending sort is based off of the default sorting method — by highest score — but it boosts votes that have happened recently, helping to surface more up-to-date answers.

It falls back to sorting by highest score if no posts are trending.

Switch to Trending sort

Taking a hint from this SO answer I searched SA docs and found out you can do this:

scoped_session wasn’t really suitable in my case since Flask-SQLAlchemy only takes a connection string argument:

SQLAlchemy (and in this case SQLite also) doesn’t work if you share a session across threads. You may not be using threads explicitly, but mod_wsgi is, and you’ve defined a global session object. Either use scoped_session to handle creating a unique session for each thread.

Preferably, use Flask-SQLAlchemy which handles this and other things for you. The SQLAlchemy docs recommend you use the integration library rather than doing this yourself.

Also note that you should only be defining the engine, session, etc. once and importing it elsewhere, rather than redefining it in each file like your current code does.

Python: sqlite threading error on api calls

So I have an app structured something like this (highly simplified):

the relevant main.py stuff looks like:

All the handler methods work fine. The api methods are just a wrapped calls to handler methods. Now the problem is api which starts new worker threads on receiving a request:

which is turn causes SQlite errors:

No cursors or database objects are ever passed out of the database module, all the cursors are closed. But even though the handler object is the same across both threads sqlite raises an error when any of the api methods are accessed? IDK what the idea is, do I have to make a new database connection for every thread? Why is it not enough to have one database object referenced by different threads? The documentation is really sparse on this.

1 Answer 1

Older SQLite versions had issues with sharing connections between threads. That’s why the Python module disallows sharing connections and cursors between threads. If you still try to do so, you will get an exception at runtime.

The portable approach, of course, is to not share connections and cursors between threads, as the docs say.

Another way to do it is to run all your SQLite queries on a single thread, and have your other threads just submit a query and get back a future they can block on. If you can require either Python 3.2+, or the PyPI backport (which works back to 2.6, IIRC), concurrent.futures is the easiest way to do this. If not, you can build futures and a single-thread executor yourself pretty easily out of, e.g., a thread, a queue, and a condition per future.

I’ve personally used the futures solution a few times. It may seem like you’re throwing away a lot of parallelism, but you’re actually not; if you use a connection from multiple threads, it uses its own mutexes and also requires you to add mutexes on top of it; if you use multiple connections, it uses file locks, which are even heavier. If the single-thread executor isn’t giving you enough concurrency, getting rid of it probably won’t either, and you’ll need to use MySQL or something similar instead of SQLite.

sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread. #38

Comments

Nefelim4ag commented Jan 30, 2014

Distribution: Arch Linux

00.00 Committing tracking stateException closing connection
Traceback (most recent call last):
File «/usr/lib/python3.3/site-packages/sqlalchemy/pool.py», line 536, in _finalize_fairy
fairy._reset(pool, echo)
File «/usr/lib/python3.3/site-packages/sqlalchemy/pool.py», line 666, in _reset
pool._dialect.do_rollback(self)
File «/usr/lib/python3.3/site-packages/sqlalchemy/engine/default.py», line 395, in do_rollback
dbapi_connection.rollback()
sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread.The object was created in thread id 140693399103232 and this is thread id 140693533701888

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File «/usr/lib/python3.3/site-packages/sqlalchemy/pool.py», line 243, in _close_connection
self._dialect.do_close(connection)
File «/usr/lib/python3.3/site-packages/sqlalchemy/engine/default.py», line 401, in do_close
dbapi_connection.close()
sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread.The object was created in thread id 140693399103232 and this is thread id 140693533701888
00.03 Committing tracking state

The text was updated successfully, but these errors were encountered:

Nefelim4ag commented Mar 16, 2014

Maybe, i can provide more information?

andrwp commented Apr 16, 2014

marcin-github commented May 9, 2014

bedup dedup

Skipping filesystem <893334c4-57fc-4df7-84b9-40af3616c977>, not mounted
Not scanning /tmp/src, generation is still 74
Not scanning /usr/src, generation is still 111
Scanning volume /var/tmp generations from 583 to 595, with size cutoff 8388608
00.00 Scanned retained 0/usr/lib64/python3.3/site-packages/cffi/vengine_cpy.py:166: UserWarning: reimporting ‘_cffi__x8e7359e8x51fe3b45’ might overwrite older defini
tions
% (self.verifier.get_module_name()))
00.02 Scanned 316 retained 0
Not scanning /usr/portage, generation is still 557
Scanning volume /home/farmbuild generations from 595 to 602, with size cutoff 8388608
00.64 Scanned 14301 retained 0
Scanning volume /dane/coredumps generations from 562 to 601, with size cutoff 8388608
00.06 Scanned 333 retained 1
Deduplicating filesystem <7dfcf227-2c63-466d-8e4f-f8ccb4e8bb26>
Deduplicated:

During handling of the above exception, another exception occurred: