cannot catch SQLAlchemy IntegrityError

Question

Try as I might, I can't seem to catch the sqlalchemy IntegrityError correctly:

from sqlalchemy import exc

try:
    insert_record()
except exc.IntegrityError, exc:
    print exc # this is never called
    handle_elegantly() # this is never called

As what one might expect:

IntegrityError: (IntegrityError) insert or update on table "my_table" 
                violates foreign key constraint "my_table_some_column_fkey"

I've tried to explicitly:

from sqlalchemy.exc import IntegrityError

UPDATE:

I found something that seems to fit what's happening here, where Integrity Error isn't thrown until the session is flushed to the db, and after the try/exceptblocks have been executed: Trying to catch integrity error with SQLAlchemy

However, adding session.flush() in the try block yields an InvalidRequestError:

ERROR:root: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: (IntegrityError)

Also, does changing `IntegrityError` to `DatabaseError` help? Thanks. — alecxe, Jul 02 '14 at 03:22
@alecxe `DatabaseError` doesn't seem to help, and I'm using Postgres. — Chrispy, Jul 02 '14 at 03:51
@MartinKonecny Yep, I'm pretty sure. Print statements abound. There's something I'm missing here, but what? Maybe in the way the transaction is handled in the session, where IntegrityError isn't thrown until the session is flushed to the db, as mentioned here: http://stackoverflow.com/questions/11313935/trying-to-catch-integrity-error-with-sqlalchemy — Chrispy, Jul 02 '14 at 03:57
Where are you getting the message `"ERROR:root:This Session's...`. Seems to me the `print exc` is generating that message? — Martin Konecny, Jul 02 '14 at 04:14
@MartinKonecny unfortunately, it's not the print statement (that would seem easier to troubleshoot), but through my server logs. — Chrispy, Jul 02 '14 at 04:30
If you're using `pymysql` driver you might be able to catch `IntegrityError` from `pymysql.err` — Stefano Messina, Jul 25 '19 at 15:47

score 67 · Answer 1 · edited Mar 20 '20 at 19:33

67

I have the same need in my Flask application, I handle it like below and it works:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import exc

db = SQLAlchemy(Flask(__name__))

try:
     db.session.add(resource)
     return db.session.commit()
except exc.IntegrityError:
     db.session.rollback()

edited Mar 20 '20 at 19:33

nageeb

2,002
1
13
25

answered Jul 05 '16 at 02:21

Max Xu

2,403
1
15
13

2

Thanks, I was trying to use `exc.IntegrityError` without importing it from sqlalchemy first – Paul Tibbetts Nov 24 '16 at 19:24

Jason · Answer 2 · 2014-09-16T20:42:04.490

As soon as the IntegrityError is raised, regardless of whether or not you've caught the error, the session you were working in is invalidated. As the second error message is instructing you, To begin a new transaction with this Session, first issue Session.rollback()., to continue using the session you'll need to issue a session.rollback()

I cannot say for sure, but I am guessing you or your web framework is attempting to continue using the session which raised the IntegrityError in some way. I recommend you issue a session.rollback() either after you catch the exception or in your handle_elegantly function.

If you run the below you'll see what I mean:

from sqlalchemy import types
from sqlalchemy import exc
from sqlalchemy import create_engine
from sqlalchemy.schema import Column
from zope.sqlalchemy import ZopeTransactionExtension
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, scoped_session

Base = declarative_base()


class User(Base):
    __tablename__ = 'user'
    name = Column(types.String, primary_key=True)


def handle_elegantly(name):
    session = DBSession()
    session.add(User(name=name))
    session.flush()
    print 'Exception elegantly handled!!\n'


def pretend_view(request):
    """Pretend view in a Pyramid application using pyramid_tm"""
    session = DBSession()
    user = User()
    print '\n-------Here we rollback before continuing -------'
    try:
        session.add(user)
        session.flush()
    except exc.IntegrityError:
        session.rollback()
        handle_elegantly('This will run fine')

    print '\n------- Here we do not, and this will error -------'
    try:
        session.add(user)
        session.flush()
    except exc.IntegrityError:
        handle_elegantly('Exception will be raised')


if __name__ == '__main__':
    engine = create_engine('sqlite://')
    global DBSession
    DBSession = scoped_session(
        sessionmaker(extension=ZopeTransactionExtension()))
    DBSession.configure(bind=engine)
    Base.metadata.bind = engine
    Base.metadata.create_all()
    pretend_view("dummy request")

score 2 · Answer 3 · edited May 17 '21 at 10:24

2

You have to add session.rollback() after you catch an error:

try:
    session.flush()
except IntegrityError:
    session.rollback()

edited May 17 '21 at 10:24

Tomerikoo

18,379
16
47
61

answered May 17 '21 at 09:09

xie Faiz

49
4

score 0 · Answer 4 · answered Nov 13 '22 at 17:28

The answer is the same as the answers above, I want just to add an extra piece of code if you want to display some dynamic error on except to return it as a JsonResponse or whatever.

Note: outputting this type of error is not good in terms of security.

from sqlalchemy import exc


try:
     db.session.add(resource)
     return db.session.commit()
except exc.IntegrityError as e:
     err_msg = str(e.orig).split(':')[-1].replace('\n', '').strip()
     print(err_mg)
     return err_mg

exc.IntegrityError has a key called orig containing this message:

duplicate key value violates unique constraint "ix_table_column"
DETAIL:  Key (column)=(some_data) already exists.

This message has the type of <class 'driver.errors.UniqueViolation'> and not str, so we should convert it to str then with some simple string manipulation we got only the exact error, which is in this case:

Key (column)=(some_data) already exists.

score -4 · Answer 5 · edited Sep 09 '19 at 22:28

-4

SQLALCHEMY_COMMIT_ON_TEARDOWN = False

edited Sep 09 '19 at 22:28

MarredCheese

17,541
8
92
91

answered Sep 05 '18 at 21:25

ivan

48
5

cannot catch SQLAlchemy IntegrityError

5 Answers5

Linked