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

Question

使用 Python 2.7 和

[150]：psycopg2.版本 Out[150]: '2.4.2 (dt dec pq3 ext)'

我有一个简单的 python 脚本，用于处理事务并将数据写入数据库。偶尔会有一个插入违反了我的主键。这很好，我只是希望它忽略该记录并继续快乐的方式。我遇到的问题是 psycopg2 主键错误正在中止整个事务块，并且错误后的所有插入都会失败。这是一个示例错误，

ERROR: duplicate key value violates unique constraint "encounter_id_pkey"
DETAIL:  Key (encounter_id)=(9012235) already exists.

这是在下一个插入中。不构成违规。

Inserting: 0163168~9024065
ERROR: current transaction is aborted, commands ignored until end of transaction block

每次插入都会重复第二个错误。这是一个简化的循环。我正在循环遍历 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()   

同样，基本思想是优雅地处理错误。英国皇家海军海军上将纳尔逊的名言是：“该死的演习直接向他们袭来”。或者在我们的例子中，该死的错误直接针对它们。”我想通过在每次插入上打开光标来重置事务块。我不想仅仅因为主键错误就必须重置连接。我缺少什么吗？

先谢谢你的时间

。

Answer 1

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

我在下面的代码中又添加了一个 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]

Answer 2

首先：CURRENT_DATE是每个 SQL 标准以及 PostgreSQL 中的保留字。如果不使用双引号，则无法将其用作标识符。我宁愿避免这种情况，因此我在示例中使用列名称 curdate。

接下来，您似乎颠倒了插入列的顺序：

(%(create_date)s, %(encounter_id)s )

应该是：

( %(encounter_id)s, %(create_date)s)

对于您的主要问题：完全避免该问题！

从 Postgres 9.5 开始，INSERT ... ON CONFLICT DO NOTHING 是正确的选择。这也避免了并发写入负载下的竞争条件：

INSERT INTO encounter_id_table (encounter_id, curdate)
VALUES (1234, CURRENT_DATE)
ON CONFLICT DO NOTHING;

请参阅：

• 如何在 PostgreSQL 中进行 UPSERT（合并、插入...重复更新）？
• 如何在 PostgreSQL 中使用 RETURNING 和 ON CONFLICT？

在 Python 语法中，应该是：

cur.execute("""INSERT INTO encounter_id_table (encounter_id, curdate)
    VALUES (%(encounter_id)s, CURRENT_DATE);""",
  {'encounter_id':i.split('~')[1]})       

在旧版本中，您大部分可以通过检查之前表中是否已存在键来避免该问题插入它：

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);

不过，在并发写入负载下可能会失败。