If you have ever used the built-in sqlite3 module in a multithreaded Python application, you may have seen this message.

sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 4316169600 and this is thread id 6190804992.

This code can be used to reproduce the error:

from threading import Thread
import sqlite3

conn = sqlite3.connect(":memory:")


def f():
    conn.execute("select 1").fetchall()


Thread(target=f).start()

The Python docs

After seeing those errors, I checked the Python docs. The sqlite3.connect method accepts a check_same_thread parameter, which can be set to False and the error will disappear.

from threading import Thread
import sqlite3

conn = sqlite3.connect(":memory:", check_same_thread=False)


def f():
    conn.execute("select 1").fetchall()


Thread(target=f).start()

But this may leave you with the impression that you’re doing something dangerous.

The SQLite docs

The next step is reading the SQLite docs. The page Using SQLite In Multi-Threaded Applications enumerates the following modes:

• Single-thread. In this mode, all mutexes are disabled and SQLite is unsafe to use in more than a single thread at once.
• Multi-thread. In this mode, SQLite can be safely used by multiple threads, provided that no single database connection is used simultaneously in two or more threads.
• Serialized. In serialized mode, SQLite can be safely used by multiple threads with no restriction. The default mode is serialized.

Checking the THREADSAFE mode from Python

You can check the compile-time options used in your sqlite3 library with the following query:

SELECT * FROM pragma_compile_options
WHERE compile_options LIKE 'THREADSAFE=%'

The result in my case is THREADSAFE=1.

This new information is conflicting with the Python docs. The default mode appears to be safe to use from multiple threads, but Python says otherwise. The answer is in the Python docs in the sqlite3.threadsafety option.

Integer constant required by the DB-API, stating the level of thread safety the sqlite3 module supports. Currently hard-coded to 1, meaning “Threads may share the module, but not connections.” However, this may not always be true.

Real safety

Searching for other sources, I found the Python discussion 45613 and the commit c273986.

From the linked commit:

So, it seems that the problem was that sqlite3.threadsafety was hard-coded. Not only that, but the attribute is not used to verify the thread safety, and the sqlite3.ProgrammingError exception is raised when the connection is used in a different thread, regardless of the threadsafety value.

Conclusion

Is it safe to share an sqlite3 connection between threads?

Yes, unless you change the default THREADSAFE option.

Once Python 3.11 is released, and based on the commit linked above, you will be able to use the sqlite3 module with code similar to:

import sqlite3

if sqlite3.threadsafety == 3:
    check_same_thread = False
else:
    check_same_thread = True

conn = sqlite3.connect(":memory:", check_same_thread=check_same_thread)

If you are using a lower Python version but still want to make sure that you’re not going to corrupt your database, you can check the THREADSAFE option before creating a connection:

import sqlite3


def get_sqlite3_thread_safety():

    # Mape value from SQLite's THREADSAFE to Python's DBAPI 2.0
    # threadsafety attribute.
    sqlite_threadsafe2python_dbapi = {0: 0, 2: 1, 1: 3}
    conn = sqlite3.connect(":memory:")
    threadsafety = conn.execute(
        """
select * from pragma_compile_options
where compile_options like 'THREADSAFE=%'
"""
    ).fetchone()[0]
    conn.close()

    threadsafety_value = int(threadsafety.split("=")[1])

    return sqlite_threadsafe2python_dbapi[threadsafety_value]


if get_sqlite3_thread_safety() == 3:
    check_same_thread = False
else:
    check_same_thread = True

conn = sqlite3.connect(":memory:", check_same_thread=check_same_thread)