Python sqlite3 error handling: exceptions and error codes (tested)

To handle sqlite3 errors properly you need to know which class each one raises and how to tell two errors of the same class apart. We triggered nine common errors on Python 3.13.5 and recorded the class and SQLite error code for each.

1. The exception classes

import sqlite3
for cls in (sqlite3.IntegrityError, sqlite3.OperationalError, sqlite3.ProgrammingError,
            sqlite3.DatabaseError, sqlite3.InterfaceError, sqlite3.Warning):
    print(cls.__name__.ljust(17), "->", " > ".join(c.__name__ for c in cls.__mro__[1:-2]))

Output:

IntegrityError    -> DatabaseError > Error > Exception
OperationalError  -> DatabaseError > Error > Exception
ProgrammingError  -> DatabaseError > Error > Exception
DatabaseError     -> Error > Exception
InterfaceError    -> Error > Exception
Warning           -> Exception

Measured on Python 3.13.5: everything the database raises is a sqlite3.Error, so except sqlite3.Error is the catch-all. sqlite3.Warning is the exception: it subclasses Exception directly and isn't caught by except sqlite3.Error.

2. Which class you get for which error, with its SQLite error code

import sqlite3
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE u (id INTEGER PRIMARY KEY, email TEXT UNIQUE NOT NULL)")
con.execute("INSERT INTO u (email) VALUES ('a@x')")
cases = {
    "duplicate":   ("INSERT INTO u (email) VALUES ('a@x')", ()),
    "null":        ("INSERT INTO u (email) VALUES (NULL)", ()),
    "no table":    ("SELECT * FROM users", ()),
    "no column":   ("SELECT name FROM u", ()),
    "syntax":      ("SELEC * FROM u", ()),
    "bind count":  ("SELECT * FROM u WHERE id = ?", (1, 2)),
    "two stmts":   ("SELECT 1; SELECT 2", ()),
}
for label, (sql, params) in cases.items():
    try:
        con.execute(sql, params)
    except sqlite3.Error as e:
        print(f"{label:11} {type(e).__name__:16} {getattr(e, 'sqlite_errorname', None)!s:25} {e}")

Output:

duplicate   IntegrityError   SQLITE_CONSTRAINT_UNIQUE  UNIQUE constraint failed: u.email
null        IntegrityError   SQLITE_CONSTRAINT_NOTNULL NOT NULL constraint failed: u.email
no table    OperationalError SQLITE_ERROR              no such table: users
no column   OperationalError SQLITE_ERROR              no such column: name
syntax      OperationalError SQLITE_ERROR              near "SELEC": syntax error
bind count  ProgrammingError None                      Incorrect number of bindings supplied. The current statement uses 1, and there are 2 supplied.
two stmts   ProgrammingError None                      You can only execute one statement at a time.

import sqlite3, os
if os.path.exists("app.db"): os.remove("app.db")
a = sqlite3.connect("app.db")
a.execute("CREATE TABLE t (x)"); a.commit()
a.execute("INSERT INTO t VALUES (1)")          # a holds an open write transaction
b = sqlite3.connect("app.db", timeout=0.2)
try:
    b.execute("INSERT INTO t VALUES (2)")
except sqlite3.OperationalError as e:
    print(type(e).__name__, e.sqlite_errorname, e.sqlite_errorcode, "|", e)

Output:

OperationalError SQLITE_BUSY 5 | database is locked

Measured: the pattern is:

• IntegrityError: your data broke a constraint (UNIQUE, NOT NULL, FOREIGN KEY, CHECK). Usually something to handle: tell the user, skip the row.
• OperationalError: problems with the database or the SQL text: a missing table or column, a syntax error, a locked file. Usually a bug or an environment problem, not bad input.
• ProgrammingError: you used the Python API wrong: wrong number of ? parameters, two statements in one execute(), an unsupported parameter type, a closed connection. Raised by Python, so sqlite_errorname is None.

Since Python 3.11, exceptions from SQLite itself carry e.sqlite_errorcode (an int) and e.sqlite_errorname. The name is the fine-grained one: SQLITE_CONSTRAINT_UNIQUE vs SQLITE_CONSTRAINT_NOTNULL, SQLITE_BUSY (code 5) for a locked database. Note that "no such table", "no such column" and a syntax error all share plain SQLITE_ERROR, so for those you can only tell them apart by the message.

3. Handle one specific error, re-raise the rest

import sqlite3
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE u (email TEXT UNIQUE)")
con.execute("INSERT INTO u VALUES ('a@x')")
def add(email):
    try:
        with con:
            con.execute("INSERT INTO u VALUES (?)", (email,))
        return "added"
    except sqlite3.IntegrityError as e:
        if e.sqlite_errorname == "SQLITE_CONSTRAINT_UNIQUE":
            return "already exists"
        raise
print(add("b@x"), "|", add("a@x"))
print(con.execute("SELECT count(*) FROM u").fetchone()[0], "rows")

Output:

added | already exists
2 rows

Measured: the duplicate insert is reported as "already exists", the table still has 2 rows, and any other integrity error (e.g. NOT NULL) would still raise. with con: commits on success and rolls back on an exception, so a failed insert doesn't leave a half-open transaction behind. Checking sqlite_errorname is more reliable than matching the message text.

4. ProgrammingError has no error code

import sqlite3
con = sqlite3.connect(":memory:")
con.close()
try:
    con.execute("SELECT 1")
except sqlite3.Error as e:
    print(type(e).__name__, "|", e, "|", hasattr(e, "sqlite_errorcode"), getattr(e, "sqlite_errorcode", None))
try:
    sqlite3.connect(":memory:").execute("SELECT ?", ([1],))
except sqlite3.Error as e:
    print(type(e).__name__, "|", e)

Output:

ProgrammingError | Cannot operate on a closed database. | False None
ProgrammingError | Error binding parameter 1: type 'list' is not supported

Measured: a closed connection and an unsupported parameter type (a list) both raise ProgrammingError without sqlite_errorcode, so use getattr(e, "sqlite_errorname", None) if your handler sees both kinds.

Fixes for specific messages

We measured each of these separately: database is locked · UNIQUE constraint failed · NOT NULL constraint failed · FOREIGN KEY constraint failed · no such table · no such column · near "X": syntax error · unable to open database file · attempt to write a readonly database · database disk image is malformed.

Written with AI assistance. Measured on 2026-10-09 with Python 3.13.5 (bundled SQLite): the exception class hierarchy, the class and sqlite_errorname for duplicate, NOT NULL, missing table/column, syntax error, wrong binding count, two statements, a locked database, a closed connection and a list parameter, plus a UNIQUE-only handler. Every block was run verbatim and its output pasted from that run. PlainHeaders is our extension; everything else here is built-in Python.