如何访问包含在 sqlalchemy 错误中的 psycopg2 错误 [英] How to access psycopg2 error wrapped in sqlalchemy error

问题描述

我正在使用 SQLalchemy 和 psycopg2 将 Pandas 数据框上传到 Postgres 中的表.如何访问 SQLalchemy 错误中的 psycopg2 错误?

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?

我只想在我的代码中写入一个异常，因为它在列中的空值违反了非空约束而引发错误.我知道如何使用 psycopg2 测试这个确切的 pSQL 错误，但是当我运行我的代码时，它返回一个 SQLalchemy 错误.

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.

错误如下:

SQLalchemy.exc.IntegrityError: (psycopg2.errors.NotNullViolation) 列中的空值...

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

这是必要的例外:

from sqlalchemy import exc

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

这是我想要做的:

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

我知道我可以测试 sqlalchemy.exc.IntegrityEror.orig，但这不像使用 pgcode 成员那样干净或细粒度.

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

推荐答案

正如您在问题中指出的，您可以通过 .orig 属性访问 dbapi 引发的底层异常SQLAlchemy 异常.

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.

由驱动程序引发并通过 SQLAlchemy 传播的任何异常都由 DBAPIError，它的文档状态:

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

包装的异常对象在 orig 属性中可用.它的类型和属性是特定于 DB-API 实现的.

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

(强调我的)

查看 psycopg 文档以了解其基础 Error 他们命名的属性之一是 pgcode:

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

表示后端返回的错误码的字符串，如果是None无法使用.errorcodes 模块包含符号常量表示 PostgreSQL 错误代码.

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

所以，<sqla_exc>.orig.pgcode 看起来它应该得到你想要的东西，但是如果出于某种原因 psycopg 没有让他们的代码在它们的异常状态下可用，它不是确实是 sqlalchemy 可以解决的问题，因为它只是包装他们的异常并将其传递给您.

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.

这篇关于如何访问包含在 sqlalchemy 错误中的 psycopg2 错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！