Python / psycopg2中的优美主键错误处理 [英] Graceful Primary Key Error handling in 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() })           
        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?

谢谢您的时间。

John

推荐答案

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

You should rollback transaction on error.

我又添加了一个<$在下面的代码中，用c $ c> 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:
        conn.commit()

    cur.close() 
except Exception , e:
    print 'ERROR:', e[0]

这篇关于Python / psycopg2中的优美主键错误处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！