IntegrityError：区分唯一约束和非空违例

本文介绍了IntegrityError：区分唯一约束和非空违例的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有这个代码：

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 / 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.