Postgresql Python: ignore duplicate key exception

Question

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?

stackoverflow.com/questions/1109061/…

khajvah
– khajvah

2015-03-21 18:22:10 +00:00
Commented Mar 21, 2015 at 18:22 — khajvah
– khajvah, Commented Mar 21, 2015 at 18:22

Community · Accepted Answer · 2017-05-23 11:46:47Z

16

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]

edited May 23, 2017 at 11:46

CommunityBot

11 silver badge

answered Apr 10, 2015 at 15:00

Tato Pane

1761 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql Python: ignore duplicate key exception

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related