How to Share Single Sqlite Connection in Multi-Threaded Python Application

How to share single SQLite connection in multi-threaded Python application

It's not safe to share a connection between threads; at the very least you need to use a lock to serialize access. Do also read http://docs.python.org/2/library/sqlite3.html#multithreading as older SQLite versions have more issues still.

The check_same_thread option appears deliberately under-documented in that respect, see http://bugs.python.org/issue16509.

You could use a connection per thread instead, or look to SQLAlchemy for a connection pool (and a very efficient statement-of-work and queuing system to boot).

Getting SQLite3 to work with multiple threads

The Python sqlite3 module has a threadsafety level of 1, which means that although you can't share database connections between threads, multiple threads can use the module simultaneously. So, you could have each thread create its own connection to the database.

The problem with this approach is that SQLite has poor write concurrency, so having multiple threads doing a ton of INSERTs at once will give you the dreaded “database is locked” error. You can improve things somewhat by using PRAGMA JOURNAL_MODE = 'WAL', but that only goes so far.

If performance is an issue and switching to a client-server database isn't an option, then what you'll probably have to do is keep an in-memory cache of your URLs, and arrange your program so that you have one thread that syncs this cache with the SQLite database.

using sqlite3 in multithreading

One connection has exactly one transaction, so your program is likely to blow up when multiple threads try to share the same connection without locking around all transactions.

Use one connection per thread.

(If you need high concurrency, SQLite might not be the best choice.)

Python sqlite3 and concurrency

You can use consumer-producer pattern. For example you can create queue that is shared between threads. First thread that fetches data from the web enqueues this data in the shared queue. Another thread that owns database connection dequeues data from the queue and passes it to the database.

Inserting in sqlite with each thread in Python

You need to pass in parameters as one sequence: c.execute("INSERT INTO tabl VALUES (?, ?)", [y, z]) or c.execute("INSERT INTO tabl VALUES (?, ?)", (y, z)), not as two separate arguments.

You also don't want to share the connection between threads, and certainly don't share cursors between threads; I'd use a separate connection per thread. See How to share single SQLite connection in multi-threaded Python application

Python, SQLite and threading

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.

Using sql across multiple threads Python

You could set the check_same_thread parameter to false. Like this in your case:

schoolDBConn = sqlite3.connect("SCHOOL_DB.db", check_same_thread=False)

From the docs:

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.

---

Related Topics