Fixing Python sqlite3 That Returns Wrong Values After ALTER TABLE

You run ALTER TABLE to rename a column or add a new one, re-run your Python script, and the query returns garbled data, None where values should be, or values from the wrong column entirely. The database file looks fine in a GUI tool, but the Python side keeps lying to you.

The root causes are almost always the same: positional column indexing, a stale connection, or a missing row factory. This guide walks through each one and shows you exactly how to fix it.

What you'll learn

• Why SQLite's ALTER TABLE is more limited than other databases and what that means for your code
• How positional indexing silently returns wrong data after a column rename or reorder
• How to use sqlite3.Row so your code is immune to column-order changes
• Why a new column added by ALTER TABLE may not appear in an open connection
• How to verify the live schema from inside Python so you're not guessing

Prerequisites

• Python 3.8 or later with the standard sqlite3 module
• A basic understanding of SQL SELECT and ALTER TABLE syntax
• An existing SQLite database file you can experiment on (or a test database you create inline)

Why SQLite's ALTER TABLE support is limited

SQLite's ALTER TABLE implementation is intentionally minimal compared to PostgreSQL or MySQL. Until SQLite 3.25.0 (released 2018), you couldn't rename a column at all. Even today, you cannot drop a column in older SQLite builds, change a column's type, or reorder columns without recreating the table.

This matters because many workarounds for SQLite schema changes involve creating a new table and copying data, which can subtly change the column order. If your Python code references columns by position rather than by name, that column-order shift is enough to return the wrong values for every row.

You can check the SQLite version your Python is using like this:

import sqlite3
print(sqlite3.sqlite_version)  # e.g. '3.39.2'

If you're on an old SQLite version, some ALTER TABLE operations you expect to work silently do nothing or raise an error you're swallowing somewhere.

The schema-cache and connection reuse trap

SQLite caches the schema of an open connection. If you alter the table in one connection while another connection (or the same connection opened earlier in the same script) is still alive, the older connection may not see the new schema immediately.

Here's a minimal reproduction:

import sqlite3

# First connection — sets up the table
con1 = sqlite3.connect("test.db")
con1.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER, name TEXT)")
con1.execute("INSERT INTO users VALUES (1, 'Alice')")
con1.commit()

# Alter the table in a separate connection
con2 = sqlite3.connect("test.db")
con2.execute("ALTER TABLE users ADD COLUMN email TEXT DEFAULT 'none'")
con2.commit()
con2.close()

# con1 is still open — what does it see?
row = con1.execute("SELECT * FROM users WHERE id = 1").fetchone()
print(row)  # May not include the email column
con1.close()

The fix is straightforward: close and reopen the connection after a schema-changing operation, or perform the ALTER TABLE through the same connection that will subsequently query the table.

import sqlite3

with sqlite3.connect("test.db") as con:
    con.execute("ALTER TABLE users ADD COLUMN email TEXT DEFAULT 'none'")
    con.commit()

# Reopen so the connection picks up the new schema
with sqlite3.connect("test.db") as con:
    row = con.execute("SELECT * FROM users WHERE id = 1").fetchone()
    print(row)

This is closely related to a broader class of sqlite3 data-freshness problems. If you've also seen old data appear even after a commit, the guide on fixing stale data after a commit in sqlite3 covers the isolation-level mechanics in detail.

Positional indexing silently returns the wrong column

This is the most common silent failure. When you access a row using a numeric index like row[2], you're relying on the column being in exactly that position in the result set. The moment a column is added before it, or a rename shifts things, row[2] now points to a different column with no error raised.

Consider this before-and-after scenario:

# Before ALTER TABLE
# Table schema: id (0), name (1), score (2)
row = cursor.fetchone()
print(row[2])  # Correctly prints score

# After: ALTER TABLE products ADD COLUMN category TEXT AFTER name
# Table schema (if recreated): id (0), name (1), category (2), score (3)
row = cursor.fetchone()
print(row[2])  # Now silently prints category — wrong!

The fix is to never use positional indexing. Access columns by name instead. The cleanest way to do this is to set con.row_factory = sqlite3.Row on your connection before you run any queries.

import sqlite3

con = sqlite3.connect("test.db")
con.row_factory = sqlite3.Row  # <-- add this line

cursor = con.cursor()
cursor.execute("SELECT * FROM users WHERE id = 1")
row = cursor.fetchone()

print(row["score"])   # Always correct regardless of column order
print(row["name"])    # Ditto

sqlite3.Row acts like both a tuple and a dictionary. You keep the performance of a lightweight row type while gaining name-based access that survives schema changes.

Row factory is missing and column names are stale

Even after setting row_factory, you can still get stale column names if your cursor was created before the schema change and you're reusing it. A cursor caches the column descriptions from the last query it executed. After an ALTER TABLE, the cursor.description attribute still reflects the old schema until the cursor runs a new query.

import sqlite3

con = sqlite3.connect("test.db")
con.row_factory = sqlite3.Row
cursor = con.cursor()

# First query — cursor.description captures old schema
cursor.execute("SELECT * FROM users")
print([col[0] for col in cursor.description])  # ['id', 'name']

# Schema changes elsewhere...
con.execute("ALTER TABLE users ADD COLUMN email TEXT")
con.commit()

# Reuse the same cursor object without re-executing
# cursor.description is STILL the old schema here!
print([col[0] for col in cursor.description])  # Still ['id', 'name'] !

# Re-execute the query — now cursor.description refreshes
cursor.execute("SELECT * FROM users")
print([col[0] for col in cursor.description])  # ['id', 'name', 'email']

The rule: always re-execute your SELECT after a schema change, even if you're reusing the same cursor. Better still, create a fresh cursor. Cursor objects are cheap.

The new column isn't visible in an open connection

SQLite uses deferred schema loading under certain configurations. When you add a column with ALTER TABLE ... ADD COLUMN and then immediately query within the same connection, the column should be visible — but there's a subtle gotcha with in-memory databases and with connections opened in autocommit mode (where isolation_level=None) versus the default deferred transaction mode.

If you're using a file-based database and the column isn't showing up after an ADD COLUMN in the same connection, check whether the ALTER TABLE actually committed. In Python's default mode, DDL statements like ALTER TABLE are not automatically committed — they participate in the ambient transaction.

import sqlite3

con = sqlite3.connect("test.db")

# This ALTER TABLE is inside an implicit transaction
con.execute("ALTER TABLE users ADD COLUMN email TEXT")
# Without commit(), a crash or close() here rolls it back!
con.commit()  # Explicitly commit DDL changes

cursor = con.cursor()
cursor.execute("SELECT * FROM users LIMIT 1")
print([col[0] for col in cursor.description])  # Now includes 'email'

Always call con.commit() after DDL statements when using the default isolation level. If you want DDL to auto-commit, open the connection with isolation_level=None, but be aware that this turns off Python's automatic transaction management entirely.

This same commit discipline applies across other sqlite3 operations. The article on fixing duplicate rows inserted on every script run covers how unintended transaction behavior can cause subtle data bugs.

Checking your schema mid-script to confirm the real shape

When you're not sure what schema the database thinks it has, ask it directly. SQLite exposes table metadata through the PRAGMA table_info command and through the sqlite_master table.

import sqlite3

con = sqlite3.connect("test.db")
con.row_factory = sqlite3.Row

def print_schema(connection, table_name):
    cursor = connection.execute(f"PRAGMA table_info({table_name})")
    columns = cursor.fetchall()
    for col in columns:
        print(f"  [{col['cid']}] {col['name']}  {col['type']}  "
              f"NOT NULL={col['notnull']}  DEFAULT={col['dflt_value']}")

print_schema(con, "users")

This prints each column with its ordinal position (cid), data type, nullability, and default value. Run this before and after your ALTER TABLE to confirm the change landed and the column order is what you expect.

You can also inspect the raw CREATE statement SQLite stored:

row = con.execute(
    "SELECT sql FROM sqlite_master WHERE type='table' AND name='users'"
).fetchone()
print(row["sql"])

This is the authoritative definition. If the new column isn't in the sql output, the ALTER TABLE did not commit successfully.

If your queries are still returning no rows at all after a schema change, the separate issue of sqlite3 queries returning no rows despite matching data is worth reading — it covers WHERE clause type mismatches that become more likely after column renames.

Common pitfalls and gotchas

SELECT * is fragile after schema changes

SELECT * returns columns in the order they appear in the schema. After a table rebuild (the workaround for unsupported ALTER TABLE operations), that order may have changed. Always name columns explicitly in production queries: SELECT id, name, email FROM users. Combined with row_factory = sqlite3.Row, this makes your code completely immune to column-order surprises.

Using a table-rebuild workaround without updating indexes

When you manually recreate a table to simulate an unsupported ALTER TABLE, you must also recreate any indexes and triggers that referred to the old table. SQLite won't do this automatically, and missing indexes won't raise an error — they'll just slow your queries silently or cause lookups to miss rows.

Default values for added columns aren't stored per-row

When you use ALTER TABLE ... ADD COLUMN ... DEFAULT 'x', SQLite does not update existing rows in storage. The default is served on-the-fly during reads. This is efficient but means if you then run a migration that dumps and reloads the table, you must explicitly populate those values or they'll be stored as NULL.

Thread safety and shared connections

If multiple threads share a single connection and one thread runs ALTER TABLE while another is mid-query, you'll get unpredictable results. By default, sqlite3.connect() has check_same_thread=True. If you've disabled that, make sure your schema-changing operations are serialized with a lock.

Forgetting to update INSERT statements

After adding a column, any INSERT statements that use positional placeholders — INSERT INTO users VALUES (?, ?) — will fail or insert into wrong columns if the column count changes. Prefer named inserts: INSERT INTO users (id, name) VALUES (:id, :name). This is also the right pattern to avoid the kind of duplicate-row issues described in the sqlite3 duplicate rows guide.

Wrapping up

Wrong values after ALTER TABLE almost always trace back to one of three things: positional column indexing that breaks when the schema changes, a stale or unreopened connection that doesn't see the new schema, or a DDL statement that was never committed. Here's what to do next:

1. Set con.row_factory = sqlite3.Row on every connection and switch all row access from numeric indexes to column names.
2. Always call con.commit() after DDL statements, then close and reopen the connection before running queries that depend on the new schema.
3. Run PRAGMA table_info(your_table) from within Python whenever you're unsure what schema the live connection sees — it takes one line and removes all guesswork.
4. Replace SELECT * and positional INSERT VALUES (?) with explicit column lists and named parameters throughout your codebase.
5. Create a fresh cursor after any schema change rather than reusing an existing one, to ensure cursor.description reflects the current schema.

Adopt sqlite3.Row as a default habit on all your projects and most of these bugs become impossible to introduce in the first place. For a broader look at how connection and transaction state can produce surprising behavior, the sqlite3 stale data after commit article is a good companion read.