Roll back a transaction after a constraint failure

By Batu · English technical notes

Also published in our primary archive.

A constraint failure is a useful case for letting a transaction context manager clean up a multi-step change. The table requires each code to be unique. Inside with con:, the first insert adds A17, while the second attempts the same value and raises sqlite3.IntegrityError. The except clause catches that specific exception after the context manager has rolled back the open transaction.

The final COUNT(*) query returns zero, showing that the first insert did not remain in this example's transaction. The assertion is deliberately stronger than merely observing an exception: it checks the database state after recovery. It does not mean every SQLite error has identical handling, nor does it replace a policy for reporting or retrying errors such as locks.

This uses Python's standard-library sqlite3 module and requires Python 3.6 or later because the example uses an f-string; no newer sqlite3 API is involved. The Python documentation describes rollback when an exception escapes the connection context manager. The table's UNIQUE declaration supplies the constraint that makes the duplicate deterministic.

AI-assistance disclosure: this article was drafted with AI assistance and should be adapted to an application's error-handling requirements.

Sources: Python sqlite3 connection context manager, Python formatted string literals, and SQLite transactions.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE coupon(code TEXT UNIQUE)")

try:
    with con:
        con.execute("INSERT INTO coupon(code) VALUES (?)", ("A17",))
        con.execute("INSERT INTO coupon(code) VALUES (?)", ("A17",))
except sqlite3.IntegrityError:
    pass

count = con.execute("SELECT COUNT(*) FROM coupon").fetchone()[0]
assert count == 0
print(f"rows after rollback: {count}")

con.close()

rows after rollback: 0