SQLAlchemy + pyTelegramBotAPI: SQLite objects created in a thread can only be used in that same thread

Question

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.

Answer 1

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.