IntegrityError：区分唯一约束和非空违例 [英] IntegrityError: distinguish between unique constraint and not null violations

问题描述

我有这个代码：

  try：
 principal = cls.objects.create（
 user_id = user.id，
 email = user.email，
 path ='something'
）
除了IntegrityError：
 principal = cls.objects.get（
 user_id = user.id，
 email = user.email 
）
  

它尝试创建一个具有给定id和电子邮件的用户，如果已经存在一个尝试获取现有记录。

我知道这是一个糟糕的建设，它将被重构。但是我的问题是这样的：

如何确定什么样的 IntegrityError 已经发生： code> unique 约束违规（有（user_id，email）上的唯一键）或与不相关的那个约束（路径不能为空）？

解决方案

psycopg2提供 SQLSTATE 除了 pgcode 成员之外，这给您提供了相当细粒度的错误信息以匹配。

  python3 
>>>进口psycopg2 
>>> conn = psycopg2.connect（dbname = regress）
>>> curs = conn.cursor（）
>>>尝试：
 ... curs.execute（INVALID;）
 ...除了例外例外：
 ... xx = ex 
>>> xx.pgcode 
'42601'
  

请参阅中的代码含义.postgresql.org / docs / current / static / errcodes-appendix.htmlrel =nofollow> 请注意，您可以粗略地匹配广泛类别的前两个字符。在这种情况下，我可以看到SQLSTATE 42601是语法错误或访问规则违例类别中的 syntax_error 。 >

你想要的代码是：

  23505 unique_violation 
 23502 not_null_violation 
  

所以你可以写：

  try：
 principal = cls.objects.create（
 user_id = user.id，
 email = user.email，
 path ='something '
）
除了IntegrityError如ex：
如果ex.pgcode =='23505'：
 principal = cls.objects.get（
 user_id = user.id ，
 email = user.email 
）
 else：
 raise 
  

这就是说，这是一个不好的方式来执行 upsert 或 merge 。 @ pr0gg3d可能是正确的建议正确的方法来做到Django;我不做Django，所以我不能评论那一点。有关upsert / merge的一般信息，请参阅 depesz关于主题。

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.

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 IntegrityError has happened: the one related to unique constraint violation (there is unique key on (user_id, email)) or the one related to not null constraint (path cannot be null)?

解决方案

psycopg2 provides the SQLSTATE with the exception as the pgcode member, which gives you quite fine-grained error information to match on.

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 Codes in the PostgreSQL manual for 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_error in the Syntax Error or Access Rule Violation category.

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 upsert or 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.

这篇关于IntegrityError：区分唯一约束和非空违例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！