5

I am executing the following code, however, occasionally, a duplicate key violation would occur and the entire insert would stop. How to ignore such errors and let the query execute for the valid entries?

code:

query_data = ','.join(cur.mogrify('(%s,%s)', row) for row in data)
insert_q = "INSERT INTO <table> VALUES {0};".format(query_data)

try:
   cur.execute(insert_q)                    
except psycopg2.Error:
   self.logger.exception('Database error')

con.commit()

UPDATE 2:

I posted my own answer below which solved the problem. It uses the new ON CONFLICT syntax in Postgres.

UPDATE 1:

There was a problem about commiting inside the except block, however, what I found was, if you don't all the other inserts won't execute giving the following error:

ERROR: current transaction is aborted, commands ignored until end of transaction block

To avoid the confusion, added the commit after the try except

c00der
  • 543
  • 1
  • 4
  • 20
  • That's not something you do in the query. It's something you set up when creating the table (or can tack on by altering the table). –  Dec 05 '16 at 20:55
  • 1
    Also, why are you committing inside of the `except` block? –  Dec 05 '16 at 20:56
  • If you don't, all the other queries that follow will not get executed too. This is only a sample of the code. – c00der Dec 05 '16 at 21:00
  • Well yes, that would be the point of not committing... –  Dec 05 '16 at 21:15

1 Answers1

5

Following is the working code (with the query):

query_data = ','.join(cur.mogrify('(%s,%s)', row) for row in data)
insert_q = "INSERT INTO <table> VALUES {0} ON CONFLICT DO NOTHING;".format(query_data)

try:
   cur.execute(insert_q)                    
except psycopg2.Error:
   self.logger.exception('Database error')

con.commit()

Find more on this here: What happens with duplicates when inserting multiple rows?

Community
  • 1
  • 1
c00der
  • 543
  • 1
  • 4
  • 20