Fixing Python sqlite3 Queries That Return No Rows Despite Matching Data

You write a SELECT query, call fetchall(), and get back an empty list. You open the database in DB Browser for SQLite, run the exact same query, and the rows are right there. This is one of the most disorienting bugs in Python's sqlite3 module — no error, no warning, just silence.

The good news is that this failure mode almost always comes down to one of a small set of root causes. Work through them methodically and you'll find it.

What You'll Learn

• Why type mismatches between Python values and SQLite columns silently drop rows
• How uncommitted transactions hide writes from subsequent queries
• Why connecting to the wrong .db file is more common than you'd think
• How parameterized query mistakes can make SQLite ignore your filters entirely
• How to use a quick diagnostic loop to rule out causes one by one

Prerequisites

This article assumes you're using Python 3.6 or later and the standard-library sqlite3 module. No third-party packages required. You should be comfortable with basic SQL SELECT syntax and Python functions.

1. Type Mismatch Between Python Value and Column Type

SQLite uses dynamic typing, but that doesn't mean type mismatches are harmless. When the value you pass in Python doesn't match what's stored, the comparison silently fails and zero rows come back.

The most common case: your column stores integers but you're comparing against a string, or vice versa.

# The column 'user_id' stores integers, but you're passing a string
cursor.execute("SELECT * FROM users WHERE user_id = ?", ("42",))  # Returns nothing!

# Fix: pass the correct type
cursor.execute("SELECT * FROM users WHERE user_id = ?", (42,))  # Works

A subtler version of this appears with booleans. Python's True and False are stored as 1 and 0 in SQLite. If your column holds the text "True" (from a CSV import, for example), comparing it to Python's True will match nothing.

Run this quick diagnostic to see exactly what types SQLite has stored:

import sqlite3

conn = sqlite3.connect("mydb.db")
cursor = conn.cursor()

# Inspect actual stored types for the first few rows
cursor.execute("SELECT user_id, typeof(user_id) FROM users LIMIT 5")
for row in cursor.fetchall():
    print(row)

typeof() is a SQLite function that tells you what affinity each stored value actually has. If you see text where you expect integer, you've found your bug.

2. Uncommitted or Rolled-Back Transactions

Python's sqlite3 module operates in a transactional mode by default. When you INSERT or UPDATE rows and don't call conn.commit(), those writes exist only inside the current transaction. A second connection — or even the same connection in certain configurations — won't see them.

import sqlite3

conn = sqlite3.connect("mydb.db")
cursor = conn.cursor()

cursor.execute("INSERT INTO products (name, price) VALUES (?, ?)", ("Widget", 9.99))
# Missing conn.commit() here!

# Now try to read it back — returns nothing
cursor.execute("SELECT * FROM products WHERE name = 'Widget'")
print(cursor.fetchall())  # []

Add conn.commit() after your write, or use the connection as a context manager so commits and rollbacks happen automatically:

import sqlite3

with sqlite3.connect("mydb.db") as conn:
    conn.execute("INSERT INTO products (name, price) VALUES (?, ?)", ("Widget", 9.99))
    # Commit happens automatically when the with block exits cleanly

# Now this will find the row
with sqlite3.connect("mydb.db") as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM products WHERE name = ?" , ("Widget",))
    print(cursor.fetchall())

This pattern is safer. An exception inside the block triggers a rollback, while a clean exit commits. You never forget to call commit().

This type of silent data loss is structurally similar to the row-loss issues you can run into with Pandas — if you're also working with DataFrames, debugging silent row loss in a Pandas merge left join covers a complementary set of gotchas.

3. Using the Wrong Cursor After a Write

If you call execute() on a cursor that's partway through iterating over a previous result set, SQLite may reset the cursor state. The symptom is an empty result or a partial result that looks like no rows matched.

import sqlite3

conn = sqlite3.connect("mydb.db")
cursor = conn.cursor()

cursor.execute("SELECT id FROM users")
# ... some logic ...
cursor.execute("INSERT INTO logs (msg) VALUES (?)", ("done",))  # Reuses same cursor
cursor.execute("SELECT * FROM users WHERE active = 1")           # Cursor state is now messy
print(cursor.fetchall())  # Potentially empty or wrong

The fix is straightforward: use a new cursor for each independent operation, or finish consuming one result set before issuing another query on the same cursor.

conn = sqlite3.connect("mydb.db")

read_cursor = conn.cursor()
write_cursor = conn.cursor()

write_cursor.execute("INSERT INTO logs (msg) VALUES (?)", ("done",))
conn.commit()

read_cursor.execute("SELECT * FROM users WHERE active = 1")
print(read_cursor.fetchall())

4. Case Sensitivity Tripping Up Text Comparisons

SQLite's LIKE operator is case-insensitive for ASCII characters by default, but the = equality operator is case-sensitive. If your stored value is "Alice" and you query for "alice", you get zero rows.

# Stored: 'Alice'
cursor.execute("SELECT * FROM users WHERE name = ?", ("alice",))  # Returns nothing
cursor.execute("SELECT * FROM users WHERE name = ?", ("Alice",))  # Works

There are two clean ways to handle this. Use LOWER() on both sides for a case-insensitive match:

cursor.execute("SELECT * FROM users WHERE LOWER(name) = LOWER(?)", ("alice",))

Or use LIKE when you control the pattern and know the value won't contain SQL wildcards:

cursor.execute("SELECT * FROM users WHERE name LIKE ?", ("alice",))

Normalizing to lowercase before insertion is the most robust long-term fix if your data doesn't require mixed case.

5. Parameterized Query Syntax Errors That Silently Return Nothing

Python's sqlite3 uses ? as the placeholder for positional parameters. If you accidentally use %s (the MySQL/psycopg2 style) or format strings incorrectly, SQLite either raises an error or — worse — treats the literal text as the search value.

# Wrong: using %s placeholder (MySQL style) with sqlite3
cursor.execute("SELECT * FROM users WHERE name = %s", ("Alice",))  # OperationalError

# Wrong: f-string that compares against a literal including the quotes
name = "Alice"
cursor.execute(f"SELECT * FROM users WHERE name = {name}")  # OperationalError or wrong result

# Correct
cursor.execute("SELECT * FROM users WHERE name = ?", (name,))

A less obvious pitfall: passing a single value as a plain string instead of a one-element tuple.

# Wrong: sqlite3 iterates over the string, treating each character as a parameter
cursor.execute("SELECT * FROM users WHERE name = ?", "Alice")  # Passes 'A' as the value

# Correct: wrap in a tuple
cursor.execute("SELECT * FROM users WHERE name = ?", ("Alice",))

Always pass parameters as a tuple or list, even for a single value. The trailing comma in ("Alice",) is not optional.

If you're used to comparing notes between SQLite and PostgreSQL behavior, the PostgreSQL query performance guide is a useful reference for understanding where the two databases diverge in their handling of parameters and types.

6. Connecting to the Wrong Database File

This one sounds obvious, but it catches experienced developers. If the path you pass to sqlite3.connect() doesn't exist, SQLite creates a brand-new empty database at that path rather than raising an error. Every query on that fresh database returns no rows — because there are no tables.

import sqlite3
import os

db_path = "data/mydb.db"

# Defensive check before connecting
if not os.path.exists(db_path):
    raise FileNotFoundError(f"Database not found at: {os.path.abspath(db_path)}")

conn = sqlite3.connect(db_path)

Also verify which tables actually exist in the database you connected to:

cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
print(cursor.fetchall())  # If this is empty, you're in the wrong file

Relative paths are especially risky. If your working directory changes (common in scripts called by task schedulers or web frameworks), the same relative path resolves to a different file. Use os.path.abspath() or pathlib.Path(__file__).parent to anchor paths to your script's location.

from pathlib import Path

DB_PATH = Path(__file__).parent / "data" / "mydb.db"
conn = sqlite3.connect(DB_PATH)

7. Row Factory and Column Name Confusion

By default, sqlite3 returns rows as plain tuples. If you've set conn.row_factory = sqlite3.Row to get dict-like access, your code might be filtering or printing results incorrectly and mistaking the output for empty results.

import sqlite3

conn = sqlite3.connect("mydb.db")
conn.row_factory = sqlite3.Row
cursor = conn.cursor()

cursor.execute("SELECT * FROM users WHERE active = 1")
rows = cursor.fetchall()

# rows is not empty — it's a list of Row objects
# Accessing them as tuples by index still works, but column names are case-sensitive
for row in rows:
    print(dict(row))  # Convert to dict to see all fields clearly

If you're checking if rows: after setting a row factory that wraps results in a custom object, make sure your factory's __bool__ behavior doesn't evaluate to False unexpectedly. Sticking with sqlite3.Row or plain tuples avoids this entirely.

This issue has a close cousin in the Pandas world: misread column types that cause filters to silently drop rows. Fixing Pandas read_csv silently misreading date columns as strings shows the same diagnostic mindset applied to DataFrames.

Common Pitfalls to Watch For

• Trailing whitespace in stored strings. If your data was loaded from a CSV or user input, values like "Alice " (with a trailing space) won't match "Alice". Use TRIM(name) in your query or strip values before inserting them.
• NULL comparisons. WHERE column = NULL never matches anything in SQL. Use WHERE column IS NULL instead.
• Integer primary keys vs. string IDs. If your application layer receives IDs as strings from a URL or form and passes them straight to SQLite, the integer column comparison fails silently. Cast with int() before querying.
• Isolation level set to None. Setting conn.isolation_level = None enables autocommit. Writes you expect to be in a transaction are committed immediately, which can cause confusion if you're debugging transactional behavior.
• Using executemany() and forgetting to commit. The same commit rules apply to batch inserts. Don't assume executemany() auto-commits.

This kind of silent filtering failure is surprisingly common across data tools. The pattern of "data exists but query returns nothing" also appears in Excel — fixing Excel SUMIF that returns zero when criteria look correct is worth a read if you're working across both environments.

Wrapping Up: Next Steps

When your sqlite3 query returns no rows and you're sure the data exists, work through this checklist in order — it covers the vast majority of real-world cases:

1. Check types first. Run SELECT typeof(your_column) FROM your_table LIMIT 5 and compare to what you're passing from Python.
2. Verify you committed. Add conn.commit() after any write, or switch to the with sqlite3.connect(...) as conn: pattern throughout your codebase.
3. Confirm the database file. Print os.path.abspath(db_path) and list tables with SELECT name FROM sqlite_master WHERE type='table' to make sure you're connected to the right file.
4. Audit your parameter syntax. Every positional parameter needs a ? placeholder and a tuple argument — even single values.
5. Check for trailing spaces and case mismatches. Use TRIM() and LOWER() in queries, or normalize data at insert time.

If you work through all five and the query still returns nothing, add a broad fallback query — SELECT * FROM your_table LIMIT 10 — and print the raw rows to confirm the data is present. From there you can narrow down which filter is too restrictive.