postgresql IntegrityError：区分唯一约束和非空违规
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12490172/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
IntegrityError: distinguish between unique constraint and not null violations
提问by warvariuc
I have this code:
我有这个代码：
try:
principal = cls.objects.create(
user_id=user.id,
email=user.email,
path='something'
)
except IntegrityError:
principal = cls.objects.get(
user_id=user.id,
email=user.email
)
It tries to create a user with the given id and email, and if there already exists one - tries to get the existing record.
它尝试使用给定的 id 和电子邮件创建一个用户，如果已经存在 - 尝试获取现有记录。
I know this is a bad construction and it will be refactored anyway. But my question is this:
我知道这是一个糟糕的结构，无论如何它都会被重构。但我的问题是：
How do i determine what kind of IntegrityErrorhas happened: the one related to uniqueconstraint violation (there is unique key on (user_id, email)) or the one related to not nullconstraint (pathcannot be null)?
我如何确定IntegrityError发生了什么样的事情：与unique约束冲突相关的一个（在（user_id，email）上有唯一键）或与not null约束相关的一个（path不能为空）？
采纳答案by Craig Ringer
psycopg2 provides the SQLSTATEwith the exception as the pgcodemember, which gives you quite fine-grained error information to match on.
psycopg2 提供了SQLSTATE作为pgcode成员的异常，它为您提供了非常细粒度的错误信息来匹配。
python3
>>> import psycopg2
>>> conn = psycopg2.connect("dbname=regress")
>>> curs = conn.cursor()
>>> try:
... curs.execute("INVALID;")
... except Exception as ex:
... xx = ex
>>> xx.pgcode
'42601'
See Appendix A: Error Codesin the PostgreSQL manualfor code meanings. Note that you can match coarsely on the first two chars for broad categories. In this case I can see that SQLSTATE 42601 is syntax_errorin the Syntax Error or Access Rule Violationcategory.
有关代码含义，请参阅PostgreSQL 手册中的附录 A：错误代码。请注意，您可以粗略地匹配广泛类别的前两个字符。在这种情况下，我可以看到 SQLSTATE 42601syntax_error在Syntax Error or Access Rule Violation类别中。
The codes you want are:
你想要的代码是：
23505 unique_violation
23502 not_null_violation
so you could write:
所以你可以写：
try:
principal = cls.objects.create(
user_id=user.id,
email=user.email,
path='something'
)
except IntegrityError as ex:
if ex.pgcode == '23505':
principal = cls.objects.get(
user_id=user.id,
email=user.email
)
else:
raise
That said, this is a bad way to do an upsertor merge. @pr0gg3d is presumably right in suggesting the right way to do it with Django; I don't do Django so I can't comment on that bit. For general info on upsert/merge see depesz's article on the topic.
也就是说，这是执行upsertor的糟糕方法merge。@pr0gg3d 建议使用 Django 的正确方法可能是正确的；我不做 Django，所以我不能评论那一点。有关 upsert/merge 的一般信息，请参阅depesz 关于该主题的文章。
回答by Mitch Kuchenberg
Update as of 9-6-2017:
2017 年 9 月 6 日更新：
A pretty elegant way to do this is to try/except IntegrityError as exc, and then use some useful attributes on exc.__cause__and exc.__cause__.diag(a diagnostic class that gives you some other super relevant information on the error at hand - you can explore it yourself with dir(exc.__cause__.diag)).
一个非常优雅的方法是try/ except IntegrityError as exc，然后在exc.__cause__and上使用一些有用的属性exc.__cause__.diag（一个诊断类，它为您提供有关手头错误的一些其他超级相关信息 - 您可以自己探索它dir(exc.__cause__.diag)）。
The first one you can use was described above. To make your code more future proof you can reference the psycopg2 codes directly, and you can even check the constraint that was violated using the diagnostic class I mentioned above:
上面描述了您可以使用的第一个。为了使您的代码更具前瞻性，您可以直接引用 psycopg2 代码，您甚至可以使用我上面提到的诊断类检查违反的约束：
except IntegrityError as exc:
from psycopg2 import errorcodes as pg_errorcodes
assert exc.__cause__.pgcode == pg_errorcodes.UNIQUE_VIOLATION
assert exc.__cause__.diag.constraint_name == 'tablename_colA_colB_unique_constraint'
edit for clarification: I have to use the __cause__accessor because I'm using Django, so to get to the psycopg2 IntegrityError class I have to call exc.__cause__
编辑澄清：我必须使用__cause__访问器，因为我使用的是 Django，所以要访问我必须调用的 psycopg2 IntegrityError 类exc.__cause__
回答by pr0gg3d
It could be better to use:
最好使用：
try:
obj, created = cls.objects.get_or_create(user_id=user.id, email=user.email)
except IntegrityError:
....
as in https://docs.djangoproject.com/en/dev/ref/models/querysets/#get-or-create
如https://docs.djangoproject.com/en/dev/ref/models/querysets/#get-or-create
The IntegrityErrorshould be raised only in the case there's a NOT NULLconstraint violation.
Furthermore you can use createdflag to know if the object already existed.
该IntegrityError只应在有一个的情况下提出的NOT NULL约束冲突。此外，您可以使用created标志来了解对象是否已经存在。
