Python / psycopg2中的优美主键错误处理

本文介绍了Python / psycopg2中的优美主键错误处理的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

使用Python 2.7和

Using Python 2.7 and

在[150]中：psycopg2。版本 Out [150]：'2.4.2 （dt dec pq3 ext）'

In [150]: psycopg2.version Out[150]: '2.4.2 (dt dec pq3 ext)'

我有一个简单的python脚本，可以处理事务并将数据写入数据库。有时有一个插入违反我的主键。很好，我只想让它忽略该记录，然后继续愉快地进行下去。我遇到的问题是psycopg2主键错误正在中止整个事务块，并且错误失败后所有插入。这是一个示例错误

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

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.

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() }) curmit() 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?

谢谢您的时间。

John

推荐答案

您应该在出错时回滚事务。

You should rollback transaction on error.

我又添加了一个 try..except..else 构造来显示发生异常的确切位置。

I've added one more try..except..else construction in the code bellow to show the exact place where exception will occur.

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: connmit() cur.close() except Exception , e: print 'ERROR:', e[0]