Python，SQLite和线程 [英] Python, SQLite and threading

问题描述

我正在开发一个应用程序，该应用程序将通过HTTP从多个位置收集数据，在本地缓存数据，然后通过HTTP提供数据.

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.

所以我在看以下内容.我的应用程序将首先创建几个线程，这些线程将以指定的时间间隔收集数据并将这些数据本地缓存到SQLite数据库中.

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.

然后在主线程中启动CherryPy应用程序，该应用程序将查询该SQLite数据库并提供数据.

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

我的问题是:如何处理从线程和CherryPy应用程序到SQLite数据库的连接?

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?

推荐答案

简短答案:请勿在线程化应用程序中使用Sqlite3.

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

Sqlite3数据库可以很好地扩展大小，但是并发性却非常差.您会遇到数据库已锁定"错误的困扰.

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

如果这样做，则每个线程将需要一个连接，并且必须确保这些连接在使用完后即可清除.传统上，这是使用线程本地会话来处理的，并且使用SQLAlchemy的ScopedSession可以很好地执行(例如).如果您是我，即使您没有使用SQLAlchemy ORM功能，也要使用它.

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.

这篇关于Python，SQLite和线程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！