Python, SQLite and threading

Question

I'm working on an application that will gather data through HTTP from several places, cache the data locally and then serve it through HTTP.

So I was looking at the following. My application will first create several threads that will gather data at a specified interval and cache that data locally into a SQLite database.

Then in the main thread start a CherryPy application that will query that SQLite database and serve the data.

My problem is: how do I handle connections to the SQLite database from my threads and from the CherryPy application?

If I'd do a connection per thread to the database will I also be able to create/use an in memory database?

Answer 1

Short answer: Don't use Sqlite3 in a threaded application.

Sqlite3 databases scale well for size, but rather terribly for concurrency. You will be plagued with "Database is locked" errors.

If you do, you will need a connection per thread, and you have to ensure that these connections clean up after themselves. This is traditionally handled using thread-local sessions, and is performed rather well (for example) using SQLAlchemy's ScopedSession. I would use this if I were you, even if you aren't using the SQLAlchemy ORM features.