我有这个代码:
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_violationso 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: raiseThat 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:区分唯一约束和非空违例
发布评论