Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
702 views
in Technique[技术] by (71.8m points)

python - How to access psycopg2 error wrapped in sqlalchemy error

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...