python - How to access psycopg2 error wrapped in sqlalchemy error

Question

I'm uploading a pandas data frame to a table in Postgres using SQLalchemy and psycopg2. How do I access the psycopg2 error that is within the SQLalchemy error?

I want to write an exception into my code only when it raises an error because of a null value in a column that violates not-null constraint. I know how to test for this exact pSQL error with psycopg2, but when I run my code it returns a SQLalchemy error.

Here's the error:

SQLalchemy.exc.IntegrityError: (psycopg2.errors.NotNullViolation) null value in column...

Here's the necessary exception:

from sqlalchemy import exc

try:
    df.to_sql(name='sql_table', con=engine, if_exists='append', index=False)
except exc.IntegrityError:

Here's what I want to do:

from sqlalchemy import exc
import psycopg2

try:
    df.to_sql(name='sql_table', con=engine, if_exists='append', index=False)
except exc.IntegrityError as ex:
    ex = ex.psycopg2error
    if ex.pgcode == '23502'
        print('Data not uploaded: null value in a column violates non-null constraint')
    else:
        raise

I know I can test sqlalchemy.exc.IntegrityEror.orig, but that is not as clean or fine-grained as using the pgcode member.

Answer 1

As you've pointed out in your question, you can access the underlying exception raised by the dbapi through the .orig attribute of the SQLAlchemy exception.

Any exception that is raised by the driver and propagated through SQLAlchemy is wrapped by a subclass of DBAPIError, where it's docs state:

The wrapped exception object is available in the orig attribute. Its type and properties are DB-API implementation specific.

(emphasis mine)

Looking at the psycopg docs for their base Error one of the attributes they name is pgcode:

String representing the error code returned by the backend, None if not available. The errorcodes module contains symbolic constants representing PostgreSQL error codes.

So, <sqla_exc>.orig.pgcode looks like it should get what you are after, but if for whatever reason psycopg doesn't make their code available in their exception state, its not really something that sqlalchemy can address as it just wraps their exception and passes it on to you.