SQLAlchemy IntegrityError: UNIQUE constraint failed (tested)

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed means an insert or update broke a database constraint. After catching it you must call session.rollback(), or every later commit raises PendingRollbackError. To skip expected duplicates, use on_conflict_do_nothing() or a begin_nested() savepoint.

Every block imports this common.py (an in-memory SQLite database with a unique email column):

from sqlalchemy import create_engine, String
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session

class Base(DeclarativeBase): pass
class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(String, unique=True)

engine = create_engine("sqlite://")
Base.metadata.create_all(engine)

1. The error

from common import *
with Session(engine) as s:
    s.add(User(email="a@example.com")); s.commit()
    s.add(User(email="a@example.com")); s.commit()

Output (last lines; the full traceback has 28 SQLAlchemy frames above them):

    ~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: users.email
[SQL: INSERT INTO users (email) VALUES (?)]
[parameters: ('a@example.com',)]
(Background on this error at: https://sqlalche.me/e/21/gkpj)

SQLAlchemy wraps the driver's exception: the part in parentheses (sqlite3.IntegrityError, or psycopg.errors.UniqueViolation on PostgreSQL) is the real database error, and the [SQL: ...] and [parameters: ...] lines show the exact row that broke the constraint. Other IntegrityErrors are the same pattern: NOT NULL constraint failed and FOREIGN KEY constraint failed.

2. Catching it is not enough: PendingRollbackError

from common import *
from sqlalchemy.exc import IntegrityError
with Session(engine) as s:
    s.add(User(email="a@example.com")); s.commit()
    try:
        s.add(User(email="a@example.com")); s.commit()
    except IntegrityError as e:
        print("caught:", e.orig)
    s.add(User(email="b@example.com"))          # carry on with the same session...
    try:
        s.commit()
    except Exception as e:
        print(type(e).__name__ + ":", str(e)[:205])

Output:

caught: UNIQUE constraint failed: users.email
PendingRollbackError: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (sqlite3.Inte

After a failed flush, the session's transaction is dead. Every later commit(), query or flush raises PendingRollbackError until you call rollback(). This is the usual cause of "everything fails after one duplicate" in web apps that share a session.

3. Fix: roll back in the except block

from common import *
from sqlalchemy import select
from sqlalchemy.exc import IntegrityError
with Session(engine) as s:
    s.add(User(email="a@example.com")); s.commit()
    try:
        s.add(User(email="a@example.com")); s.commit()
    except IntegrityError as e:
        s.rollback()                              # required before the session is usable again
        print("duplicate:", e.orig)
    s.add(User(email="b@example.com")); s.commit()
    print(s.scalars(select(User.email)).all())

Output:

duplicate: UNIQUE constraint failed: users.email
['a@example.com', 'b@example.com']

e.orig is the underlying driver exception, handy for logging or for checking which constraint failed. Note that rollback() discards everything not yet committed in that session, not just the bad row.

4. Avoid the error: INSERT ... ON CONFLICT DO NOTHING

from common import *
from sqlalchemy import select
from sqlalchemy.dialects.sqlite import insert       # postgresql has the same API
with Session(engine) as s:
    for email in ["a@example.com", "a@example.com", "c@example.com"]:
        s.execute(insert(User).values(email=email).on_conflict_do_nothing(index_elements=["email"]))
    s.commit()
    print(s.scalars(select(User.email)).all())

Output:

['a@example.com', 'c@example.com']

If duplicates are expected, let the database skip them. sqlalchemy.dialects.postgresql.insert has the same on_conflict_do_nothing / on_conflict_do_update methods; MySQL uses on_duplicate_key_update.

5. Keep the rest of the transaction: begin_nested()

from common import *
from sqlalchemy.exc import IntegrityError
with Session(engine) as s:
    s.add(User(email="a@example.com")); s.commit()
    try:
        with s.begin_nested():                     # SAVEPOINT: only this part is undone
            s.add(User(email="a@example.com"))
    except IntegrityError:
        print("skipped duplicate")
    s.add(User(email="d@example.com")); s.commit()
    print(s.query(User.email).order_by(User.id).all())

Output:

skipped duplicate
[('a@example.com',), ('d@example.com',)]

A SAVEPOINT (session.begin_nested()) undoes only the failed insert, and the session stays usable without a full rollback(). Use it when you insert many rows in one transaction and want to skip the bad ones.

Related: SQLite UNIQUE constraint failed · SQLite database is locked · sqlite3 OperationalError: near syntax error.

Written with AI assistance. Measured on 2026-10-23 with SQLAlchemy 2.1.6 and Python 3.14.7 (python:3-alpine in Docker), SQLite in-memory database. Each block was run verbatim and the output pasted unedited (block 1 shows the last 5 lines of the traceback). PlainTables is our extension; Python and SQLAlchemy are free.