SQL在重复键违例后继续执行查询 [英] SQL continue executing queries after duplicate key violation

问题描述

我有一种情况，我想插入一个行，如果它不存在，并且不插入它，如果它已经。我尝试创建阻止这种情况发生的sql查询（请参阅 here ），但是我被告知一个解决方案是创建约束并在违反时捕获异常。

。我的问题是 - 如何捕获异常并继续执行更多的查询？如果我的代码如下所示：

  cur = transaction.cursor（）
 
 #execute一些查询成功
 
 try：
 cur.execute（fooquery，bardata）＃这个查询可能失败，但是没关系
除了psycopg2.IntegrityError：
 pass 
 
 cur.execute（fooquery2，bardata2）
  

执行：

<$ c $ p> psycopg2.InternalError：当前事务中止，命令被忽略，直到事务块结束


如何告诉计算机我希望它继续执行查询？我不想 transaction.commit（），因为我可能想要回滚整个事务（以前成功的查询）。

解决方案

我想你可以做的是使用 SAVEPOINT 违规。如果发生违规，您可以回滚到 SAVEPOINT ，但保留原始交易。

可能有帮助：
在主键违规后继续事务错误

I have a situation where I want to insert a row if it doesn't exist, and to not insert it if it already does. I tried creating sql queries that prevented this from happening (see here), but I was told a solution is to create constraints and catch the exception when they're violated.

I have constraints in place already. My question is - how can I catch the exception and continue executing more queries? If my code looks like this:

cur = transaction.cursor()

#execute some queries that succeed

try:
    cur.execute(fooquery, bardata)  #this query might fail, but that's OK
except psycopg2.IntegrityError:
    pass

cur.execute(fooquery2, bardata2)

Then I get an error on the second execute:

psycopg2.InternalError: current transaction is aborted, commands ignored until end of transaction block

How can I tell the computer that I want it to keep executing queries? I don't want to transaction.commit(), because I might want to roll back the entire transaction (the queries that succeeded before).

解决方案

I think what you could do is use a SAVEPOINT before trying to execute the statement which could cause the violation. If the violation happens, then you could rollback to the SAVEPOINT, but keep your original transaction.

Here's another thread which may be helpful: Continuing a transaction after primary key violation error

这篇关于SQL在重复键违例后继续执行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！