4

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
  • 501
  • 1
  • 4
  • 17
  • 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

2 Answers2

3

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
  • 501
  • 1
  • 4
  • 17
1

When you commit in the except block, usually the entire script will block.

Edit, explaining more detailed:

try:
   cur.execute(insert_q)                    
   con.commit()
except psycopg2.Error:
   self.logger.exception('Database error')
   con.rollback()
Maurice Meyer
  • 14,803
  • 3
  • 20
  • 42
  • I don't know whether you read the question. That's absolutely not an answer. That's what not I am asking. – c00der Dec 05 '16 at 21:13
  • Thanks, but rolling back would skip the entire bulk insert. If I am bulk-inserting 10,000 rows, and if one row violates the unique constraint, all the rows will not be inserted. That's what I need an answer for. To let one row ignore while the rest gets inserted. – c00der Dec 05 '16 at 21:29
  • Python-side: You can validate your data before inserting, Postgres-side: Insert via a Stored Procedure, which does the constraint check (skipping the insert) – Maurice Meyer Dec 05 '16 at 21:35
  • Maybe it's in the words. It is a multi-row insert. Sorry about that, will correct. However, would like to know exactly how to do a bulk-insert in python other than with a multi-row insert. – c00der Dec 05 '16 at 22:08