Unable to catch SQLAlchemy IntegrityError

Question

If I try to commit a record to my database and it already exists then I want to use a try/except block to handle it:

from sqlalchemy.exc import IntegrityError

try:
    session.commit()
except IntegrityError:
    # do something else

However, I can't seem to catch the error. Here's a typical error that occurs on session.commit():

Exception has occurred: IntegrityError
(mysql.connector.errors.IntegrityError) 1062 (23000): Duplicate entry '88306-1' for key 'match_feat.idx_match_feat__composite'

I wondered if I was trying to catch the wrong error so I tried using:

from mysql.connector.errors import IntegrityError

But I get the same error message.

The answers to the question here focus on adding session.rollback() to the except: block however this doesn't help me (or the OP?) as I'm getting the error in the try: block and so the except: block isn't ever triggered.

I am unable to reproduce your issue. [This code](https://pastebin.com/QF09P7j5) works fine for me. — Gord Thompson, Dec 17 '20 at 12:41
Hey Gord. Thanks. Weirdly that code works fine for me too. Not quite sure where I go from here - will do some more investigating. — Jossy, Dec 17 '20 at 15:10

score 1 · Answer 1 · answered Dec 28 '20 at 11:44

Worked this out. The answers to the question here were correct. A rollback() is needed at the start of the except block. I'm not sure on the exact mechanics but the error was being caught on the first iteration of the code however if the transaction wasn't then rolled back then the second time through the error occurred in the try block and wasn't caught. This solved the issue:

try:
    session.commit()
except IntegrityError:
    session.rollback()
    # do something else

score 0 · Answer 2 · answered Dec 28 '20 at 12:09

I assume that the pass statement was used in the except block to tell Python to do nothing, for example:

try:
    session.commit()
except IntegrityError:
    pass

As the example in the question would result in an IndentationError being raised.

Based on your answer, it seems that the IntegrityError is being raised elsewhere in the code and not by session.commit() inside the try block. I suggest reviewing the stack trace generated when the exception is raised to find out where in your code this exception is being raised. The Python debugger can also be used to step through the code line-by-line.

Unable to catch SQLAlchemy IntegrityError

2 Answers2