Fixing Python sqlite3 That Returns Stale Data After a Commit

You wrote a row to an SQLite database, called connection.commit(), ran a SELECT right below it, and got back the values that existed before your insert or update. The commit appeared to succeed — no exception was raised — but the data your query returns is frozen in the past.

This is one of the more disorienting bugs in Python's sqlite3 module because the fix depends on which of several different root causes applies to your code. This article walks through each one clearly.

What You'll Learn

• How SQLite's transaction model works and where Python wraps it in ways you might not expect
• Why the same connection can see stale data even after a successful commit
• Five concrete fixes, each addressing a distinct cause
• Which isolation level to choose based on your actual use case
• Pitfalls that silently re-introduce the problem after you think you've fixed it

The Problem: You Committed, But the Query Disagrees

Here is a minimal example that reproduces the bug:

import sqlite3

conn_write = sqlite3.connect("data.db")
conn_read = sqlite3.connect("data.db")

conn_write.execute("CREATE TABLE IF NOT EXISTS events (id INTEGER PRIMARY KEY, name TEXT)")
conn_write.commit()

conn_write.execute("INSERT INTO events (name) VALUES ('login')")
conn_write.commit()

rows = conn_read.execute("SELECT * FROM events").fetchall()
print(rows)  # [] — empty, even though the row was committed

Two separate connect() calls to the same file, a write on one, a read on the other — and the read sees nothing. Variations of this pattern catch developers off guard constantly, especially in scripts that open a second connection for validation or reporting.

How SQLite Transactions Work Under the Hood

SQLite uses a file-level locking system. When a connection begins a read transaction (even implicitly, just by running a SELECT), it takes a snapshot of the database at that point in time. Any writes committed by another connection after that snapshot was taken are invisible to the reading connection until it ends its current transaction and starts a new one.

This is called deferred transaction isolation. It prevents dirty reads, which is good. But it also means a connection that opened a read transaction early will keep seeing old data until that transaction closes. The confusion in Python is that you often don't know a read transaction was even opened.

Why Python's sqlite3 Module Makes This Worse

The standard library's sqlite3 module adds its own implicit transaction management on top of SQLite's native behavior. By default, Python opens a transaction automatically before any Data Manipulation Language statement (INSERT, UPDATE, DELETE, REPLACE) — but it does not open one for SELECT. This sounds reasonable until you realize the implicit transaction started by a SELECT in one part of the code can linger silently.

The key behaviors to understand:

• Python's default isolation_level is "" (empty string), which means deferred transactions are used.
• A transaction begun implicitly by Python is not automatically closed when you call commit() on a different connection.
• Calling fetchall() on a cursor that was created before the commit keeps that cursor inside the old transaction snapshot.
• Using two separate connect() calls to the same file means two fully independent transaction scopes.

If you're running into other unexpected behaviors with SQLite and Python, the article on sqlite3 queries that return no rows despite matching data covers a related class of issues that often appear alongside stale read bugs.

Diagnosing the Exact Cause in Your Code

Before applying a fix, identify which pattern your code matches:

1. Two connections to the same file — one writes, one reads. The reading connection has an open transaction from before the write.
2. One connection, but the cursor was created before the commit — you reuse a cursor object across a commit boundary.
3. One connection, but you forgot to call commit() — the write is still pending inside an open transaction.
4. Autocommit was expected but not configured — you assumed each statement auto-committed but Python's default mode doesn't work that way.
5. A context manager closed the transaction but you kept reading from the old cursor.

Add a quick diagnostic print to confirm which connection and cursor is doing the reading, and whether commit() was actually called on the writing connection before the read.

Fix 1: Use isolation_level=None for Autocommit Mode

Setting isolation_level=None puts the connection into autocommit mode. Every statement executes and commits immediately without an implicit transaction wrapper. This is the simplest fix when your script does straightforward writes followed by reads and doesn't need explicit multi-statement transaction control.

import sqlite3

conn = sqlite3.connect("data.db", isolation_level=None)

conn.execute("CREATE TABLE IF NOT EXISTS events (id INTEGER PRIMARY KEY, name TEXT)")
conn.execute("INSERT INTO events (name) VALUES ('login')")

rows = conn.execute("SELECT * FROM events").fetchall()
print(rows)  # [('login',)] — visible immediately

The trade-off is that you lose atomicity across multiple statements. If you need all-or-nothing behavior for a batch of inserts, autocommit mode is not the right choice — use explicit BEGIN / COMMIT instead.

Fix 2: Close and Reopen the Cursor After a Commit

If you're using a single connection and want to stay in the default transaction mode, the cursor is your problem. A cursor caches its snapshot of the transaction it was created in. Closing the cursor and opening a new one after a commit forces SQLite to start a fresh read.

import sqlite3

conn = sqlite3.connect("data.db")
conn.execute("CREATE TABLE IF NOT EXISTS events (id INTEGER PRIMARY KEY, name TEXT)")
conn.commit()

conn.execute("INSERT INTO events (name) VALUES ('login')")
conn.commit()

# Create a NEW cursor after the commit
cursor = conn.cursor()
rows = cursor.execute("SELECT * FROM events").fetchall()
print(rows)  # [('login',)]

This works because the new cursor operates within a new implicit read transaction that starts after the write transaction closed. Reusing the old cursor is a common mistake, especially in loops where the cursor is created once at the top of the function.

Fix 3: Use a Single Connection Consistently

If you're writing from one connection and reading from another (even in the same process), stop. Use one connection for both operations. This is the most common architectural mistake that produces stale reads.

import sqlite3

# Wrong — two connections see independent transaction states
conn_write = sqlite3.connect("data.db")
conn_read = sqlite3.connect("data.db")

# Right — one connection, consistent view
conn = sqlite3.connect("data.db")
conn.execute("INSERT INTO events (name) VALUES ('login')")
conn.commit()
rows = conn.execute("SELECT * FROM events").fetchall()

SQLite is designed for single-writer, potentially multi-reader workloads. Within a single process, one connection is almost always the right pattern. If you need true concurrent access from multiple processes or threads, consider switching to PostgreSQL — the article on PostgreSQL performance under high row counts is a useful read before making that decision.

Fix 4: Set the Correct Isolation Level for Your Use Case

Python's sqlite3 module accepts a string for isolation_level that maps to SQLite's transaction types. Understanding these gives you fine-grained control:

For most single-process scripts that write then read, isolation_level=None (autocommit) or "IMMEDIATE" will eliminate stale reads. Use "EXCLUSIVE" only if your script truly needs to block other processes from reading during a batch.

Fix 5: Call connection.commit() at the Right Time

This sounds obvious, but a missing or misplaced commit() is frequently the actual bug. Python's implicit transaction management means your INSERT or UPDATE sits inside an open, uncommitted transaction until you explicitly commit. If your read happens before that commit — or on a different connection — it won't see the change.

import sqlite3

conn = sqlite3.connect("data.db")
conn.execute("INSERT INTO events (name) VALUES ('logout')")
# Missing commit here! The row is not visible to any other reader.

# Even on the same connection, some cursor states won't see this.
rows = conn.execute("SELECT * FROM events").fetchall()

The safest pattern is to use the connection as a context manager, which commits automatically on success and rolls back on exception:

import sqlite3

with sqlite3.connect("data.db") as conn:
    conn.execute("INSERT INTO events (name) VALUES ('logout')")
    # commit() is called automatically when the with block exits cleanly

# Open a fresh connection or cursor here to read back the committed data
with sqlite3.connect("data.db") as conn:
    rows = conn.execute("SELECT * FROM events").fetchall()
    print(rows)

One nuance: the context manager commits or rolls back the transaction, but it does not close the connection. If you reuse conn outside the with block, you're still in the same connection scope. This is worth double-checking if you see the bug re-appear after switching to context managers.

If you're also running into issues with duplicate rows appearing when a script runs more than once, the guide on fixing sqlite3 duplicate row inserts on every script run addresses the companion problem to stale reads.

Common Pitfalls That Bring the Bug Back

Reusing a module-level connection across requests in a web app. If your Flask or FastAPI handler shares a single global sqlite3 connection, transactions from one request can bleed into another. Use per-request connections or a proper connection pool instead.

Mixing execute() on the connection object with cursors. Calling conn.execute() is shorthand that creates a temporary cursor internally. If you then create a named cursor and query with it, they may be in different transaction states. Be consistent: either use the connection shorthand throughout, or always use named cursors.

Using threads without check_same_thread=False. By default, a sqlite3 connection can only be used in the thread that created it. Passing it to another thread without setting check_same_thread=False raises an error — but if you suppress those errors, the transaction state becomes unpredictable. Use one connection per thread, or a thread-safe abstraction.

# Thread-safe connection per thread
import sqlite3
import threading

local_storage = threading.local()

def get_conn():
    if not hasattr(local_storage, "conn"):
        local_storage.conn = sqlite3.connect("data.db", isolation_level=None)
    return local_storage.conn

Forgetting that DDL statements (CREATE TABLE, ALTER TABLE) implicitly commit. In Python's sqlite3 module, DDL statements cause any pending transaction to commit before the DDL runs. This can shift where your transaction boundaries are in ways that are hard to see at a glance.

Data-related bugs in Python often have counterparts in other tools. If you're doing post-processing of SQLite results in Pandas, the article on debugging silent row loss in a Pandas merge is worth bookmarking — stale reads and lost rows often appear together in data pipeline scripts.

Wrapping Up

Stale data after a commit in Python's sqlite3 is almost always a transaction isolation or connection architecture problem, not a SQLite bug. Here are your concrete next steps:

1. Identify your pattern — two connections, a reused cursor, or a missing commit. Print connection IDs and cursor states temporarily if you need to confirm.
2. Switch to a single connection if you're reading and writing in the same process. This eliminates the largest class of stale-read bugs immediately.
3. Set isolation_level=None (autocommit) for simple scripts where you don't need multi-statement atomicity.
4. Use context managers (with conn:) to ensure commits and rollbacks happen deterministically.
5. Create new cursors after commits if you must stay in non-autocommit mode and need to read your own writes immediately.