Postgresql Python:忽略重复键异常

Question

我使用psycopg2按以下方式插入项目：

cursor = connection.cursor()
for item in items:
    try:
        cursor.execute(
            "INSERT INTO items (name, description) VALUES (%s, %s)  RETURNING id",
            (item[0], item[1])
        )
        id = cursor.fetchone[0]
        if id is not None:
            cursor.execute(
                "INSERT INTO item_tags (item, tag) VALUES (%s, %s)  RETURNING id",
                (id, 'some_tag')
            )    
    except psycopg2.Error:
        connection.rollback()
        print("PostgreSQL Error: " + e.diag.message_primary)
        continue
    print(item[0])
connection.commit()

显然，当一个项已经在数据库中时，会抛出duplicate key exception。有没有办法忽略这个例外？当抛出异常时，整个事务是否将被中止？如果是，那么重写查询的最佳选项是什么，可能使用批插入？

Answer 1

来自Graceful Primary Key Error handling in Python/psycopg2：

You should rollback transaction on error.

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 items (name, description) 
                      VALUES (%s, %s)  RETURNING id""", (item[0], item[1]))
    except psycopg2.IntegrityError:
        conn.rollback()
    else:
        conn.commit()

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