CODEWITHSUNDEEP
pythonsqlpostgresqlpsycopg2
nickbusted
nickbusted

Reputation: 1109

Postgresql Python: ignore duplicate key exception

I insert items using psycopg2 in the following way:

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

Obviously, when an item is already in the database, the duplicate key exception is being thrown. Is there a way to ignore the exception? Is the whole transaction is going to be aborted when the exception is thrown? If yes, then what is the best option to rewrite the query, maybe using batch inserting?

Upvotes: 4

Views: 11720

Answers (1)

Tato Pane
Tato Pane

Reputation: 176

from 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]

Upvotes: 16

Related Questions