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

问题描述

我正在使用SQLalchemy和psycopg2将熊猫数据框上传到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.

推荐答案

正如您在问题中指出的那样，您可以通过SQLAlchemy异常的 .orig 属性访问dbapi引发的基础异常。

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实现的。

（重点是我的）

查看psycopg文档的基础 错误 他们命名的属性之一是 pgcode ：

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

表示后端返回的错误代码的字符串，如果
不可用，则为None。错误代码模块包含代表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屋！