postgresql Python/psycopg2 中优雅的主键错误处理
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8497886/
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
Graceful Primary Key Error handling in Python/psycopg2
提问by jdennison
Using Python 2.7 and
使用 Python 2.7 和
In [150]: psycopg2.versionOut[150]: '2.4.2 (dt dec pq3 ext)'
在 [150] 中：psycopg2。版本输出 [150]: '2.4.2 (dt dec pq3 ext)'
I have a simple python scripts that processing transactions and writes data to a database. Occasionally there is an insert that violates my primary key. This is fine, i just want it to ignore that record and continue on it merry way. The problem I am having is that psycopg2 primary key error is aborting the entire transaction block and all inserts after the error fail. Here is an example error
我有一个简单的 python 脚本，用于处理事务并将数据写入数据库。偶尔有一个插入违反了我的主键。这很好，我只是希望它忽略该记录并继续愉快地进行。我遇到的问题是 psycopg2 主键错误正在中止整个事务块和错误失败后的所有插入。这是一个示例错误
ERROR: duplicate key value violates unique constraint "encounter_id_pkey"
DETAIL: Key (encounter_id)=(9012235) already exists.
This is on the next insert. not a violation.
这是在下一个插入。不是违规。
Inserting: 0163168~9024065
ERROR: current transaction is aborted, commands ignored until end of transaction block
The Second error repeats itself for every insert. Here is a simplified loop. I am looping through a pandas data frame, but it could be any loop.
每次插入都会重复第二个错误。这是一个简化的循环。我正在遍历一个 Pandas 数据框，但它可以是任何循环。
conn = psycopg2.connect("dbname='XXXX' user='XXXXX' host='XXXX' password='XXXXX'")
cur = conn.cursor()
for i, val in df2.iteritems():
try:
cur = conn.cursor()
cur.execute("""insert into encounter_id_table (
encounter_id,current_date )
values
(%(create_date)s, %(encounter_id)s ) ;""",
'encounter_id':i.split('~')[1],
'create_date': datetime.date.today() })
cur.commit()
cur.close()
except Exception , e:
print 'ERROR:', e[0]
cur.close()
conn.close()
Again the basic idea is to gracefully handle the Error. In the dictum of Admiral Nelson of the Royal Navy: "Damn the maneuvers go straight at them". Or in our case damn the Errors go straight at them." I thought by opening a cursor on every insert that I would be resetting the transaction block. I do not want to have to reset the connection just because of a primary key error. Is there something i am just missing?
同样，基本思想是优雅地处理错误。在皇家海军海军上将纳尔逊的格言中：“该死的演习直接针对他们”。或者在我们的情况下，该死的错误直接针对他们。”我想通过在每个插入上打开一个光标，我将重置事务块。我不想仅仅因为主键错误而重置连接。是有什么我只是想念？
Thanks before hand for your time.
提前感谢您的时间。
John
约翰
回答by lig
You should rollback transaction on error.
您应该在出错时回滚事务。
I've added one more try..except..elseconstruction in the code bellow to show the exact place where exception will occur.
我try..except..else在下面的代码中又添加了一个结构来显示将发生异常的确切位置。
try:
cur = conn.cursor()
try:
cur.execute("""insert into encounter_id_table (
encounter_id,current_date )
values
(%(create_date)s, %(encounter_id)s ) ;""",
'encounter_id':i.split('~')[1],
'create_date': datetime.date.today() })
except psycopg2.IntegrityError:
conn.rollback()
else:
conn.commit()
cur.close()
except Exception , e:
print 'ERROR:', e[0]
回答by Erwin Brandstetter
First of all: CURRENT_DATEis a reserved word in every SQL standard as well as in PostgreSQL. You cannot use it as identifier without double-quoting it. I would strongly advice not to use it at all. I renamed the column to curdatein my example
首先：CURRENT_DATE是每个 SQL 标准以及 PostgreSQL 中的保留字。如果没有双引号，则不能将其用作标识符。我强烈建议根本不要使用它。我curdate在我的示例中将该列重命名为
Next, I am no expert in python syntax, but you seem to have reversed the order of your insert-columns:
接下来，我不是 Python 语法方面的专家，但您似乎颠倒了插入列的顺序：
(%(create_date)s, %(encounter_id)s )
Should be:
应该：
( %(encounter_id)s, %(create_date)s)
To your main question: you can avoid the problem altogether by checking if the key is already in the table before using it in the insert command:
对于您的主要问题：您可以通过在插入命令中使用该键之前检查该键是否已经在表中来完全避免该问题：
INSERT INTO encounter_id_table (encounter_id, curdate)
SELECT 1234, now()::date
WHERE NOT EXISTS (SELECT * FROM encounter_id_table t
WHERE t.encounter_id = 1234);
In Python syntax, that should be:
在 Python 语法中，应该是：
cur.execute("""INSERT INTO encounter_id_table (encounter_id, curdate)
SELECT %(encounter_id)s, %(create_date)s,
WHERE NOT EXISTS (
SELECT * FROM encounter_id_table t
WHERE t.encounter_id = %(encounter_id)s);""",
{'encounter_id':i.split('~')[1],
'create_date': datetime.date.today()})
