How to find the offending attribute with a sqlalchemy IntegrityError

Question

I have a very simple SqlAlchemy model

class User(Base):
    """ The SQLAlchemy declarative model class for a User object. """
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    phone = Column(String, unique=True)
    email = Column(String, unique=True)

When inserting a new User, an IntegrityError could occur if the email or phone is a duplicate.

Is there any way to detect which of the columns was violating the integrity error? Or is the only way to do a separate query to see or a value is present?

Answer 1

You can use the below way to get the underlying code, message, and format the message accordingly.

except exc.IntegrityError as e:
       errorInfo = e.orig.args
       print(errorInfo[0])  #This will give you error code
       print(errorInfo[1])  #This will give you error message

BTW, you have to import exc from sqlalchemy: from sqlalchemy import exc Let me know if you need any other info. I can try it out.

For more info on sqlalchemy exc, please find the code: https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/exc.py

Answer 2

try:
    ....
except IntegrityError as e:
    print(e.orig.diag.message_detail)

This worked for me.

Answer 3

There's no clean way to do this unfortunately but I use the orig attribute on the IntegrityError and the parse module:

try:
    db.session.add(user)
    db.session.commit()
except IntegrityError, e:
    dupe_field = parse('duplicate key value violates unique constraint "{constraint}"\nDETAIL:  Key ({field})=({input}) already exists.\n', str(e.orig))["field"]

This may not be the only error string IntegrityError throws and it could change in future updates to SQLAlchemy so its not ideal