SQLAlchemy + pyTelegramBotAPI: SQLite objects created in a thread can only be used in that same thread
multithreading, py-telegram-bot-api, python, sqlalchemy, sqlite
Solution
As you can see in the source, sqlite will raise this exception inside `pysqlite_check_thread` if the connection object is reused across any threads.
By using a `QueuePool`, you are telling SQLAchemy it is safe to reuse connections across multiple threads. It will therefore just pick a connection from the pool for any session no matter which thread it is on. This is why you're hitting the error. The first time you create and use a connection, you'll be fine; however the next use will probably be on a different thread and so fail the check.
This is why SQLAlchemy mandates the use of other pools such as `SingletonThreadPool` and `NullPool`.
Assuming you are using a file based database, you should use the `NullPool`. This will give you good concurrency on reads. Write access concurrency is always going to be an issue for sqlite; if you need this, you probably want a diffenet database.
Problem
i have a real headache from trying to understand the cause of the following problem. We are using a combination of the following libraries: - pyTelegramBotAPI to process requests in a multi-threaded way - SQLAlchemy - sqlite The `SQLAlchemy` was first using `NullPool` and now is configured to utilize `QueuePool`. I am also using the following idiom to have a new DB session firing up for each thread (as per my understanding) ``` Session = sessionmaker(bind=create_engine(classes.db_url, poolclass=QueuePool)) @contextmanager def session_scope(): session = Session() try: yield session session.commit() except: session.rollback() raise finally: session.close() @bot.message_handler(content_types=['document']) def method_handler: with session_scope() as session: do_database_stuff_here(session) ``` Nevertheless I am still getting this annoying exception: `(sqlite3.ProgrammingError) SQLite objects created in a thread can only be used in that same thread` Any ideas? ;) In particular, i don't get how it is possible for another tread to get somewhere in between the db operations...this is probably the reason of the pesky exception update 1: if i change the `poolclass` to `SingletonThreadPool`, then there seems to be no more errors coming up. However, the documentation of `SQLAlchemy` tells that it's not production rife.