Get the constraint name out of an IntegrityError in SQLAlchemy+Postgres

error-handling, postgresql, python, unique-constraint

Solution

Oh, I found it. It's here:

try:
    db.session.commit()
except sqlalchemy.exc.IntegrityError, e:
    constraint = e.orig.diag.constraint_name

In fact, the diag object has a lot of useful stuff in it:

>>> dir(e.orig.diag)[15:]
['column_name', 'constraint_name', 'context', 'datatype_name', 'internal_position', 'internal_query', 'message_detail', 'message_hint', 'message_primary', 'schema_name', 'severity', 'source_file', 'source_function', 'source_line', 'sqlstate', 'statement_position', 'table_name']

Problem

Some types of constraints are best checked by the database, as attempting to check them manually could lead to race conditions. You'd think, then, that database drivers would make this easy, right? The database driver pq for postgres for golang parses out the whole error, including the name of the constraint. Knowing the name of the constraint makes it easy to map that to what went wrong. Is there a postgres driver for Python that gives you the constraint name without requiring you to parse it out of a string yourself?

Original source