title: Graceful Primary Key Error handling in Python/psycopg2
body: <p>Using Python 2.7 and </p>
  
  <p>In [150]: psycopg2.<strong>version</strong>
  Out[150]: '2.4.2 (dt dec pq3 ext)'</p>
  
  <p>I have a simple python scripts that processing transactions and writes data to a database. Occasionally there is an insert that violates my primary key. This is fine, i just want it to ignore that record and continue on it merry way. The problem I am having is that psycopg2 primary key error is aborting the entire transaction block and all inserts after the error fail. Here is an example error</p>
  
  <pre><code>ERROR: duplicate key value violates unique constraint "encounter_id_pkey"
  DETAIL:  Key (encounter_id)=(9012235) already exists.
  </code></pre>
  
  <p>This is on the next insert. not a violation.</p>
  
  <pre><code>Inserting: 0163168~9024065
  ERROR: current transaction is aborted, commands ignored until end of transaction block
  </code></pre>
  
  <p>The Second error repeats itself for every insert. Here is a simplified loop. I am looping through a pandas data frame, but it could be any loop.</p>
  
  <pre><code>conn = psycopg2.connect("dbname='XXXX' user='XXXXX' host='XXXX' password='XXXXX'")
  
  cur = conn.cursor()
  
  for i, val in df2.iteritems():
      try:
          cur = conn.cursor()
          cur.execute("""insert into encounter_id_table (
          encounter_id,current_date  )
          values       
          (%(create_date)s, %(encounter_id)s ) ;""",
          'encounter_id':i.split('~')[1],  
          'create_date': datetime.date.today() })           
          cur.commit()
          cur.close() 
      except Exception , e:
          print 'ERROR:', e[0]
          cur.close()
   conn.close()   
  </code></pre>
  
  <p>Again the basic idea is to gracefully handle the Error. In the dictum of Admiral Nelson of the Royal Navy: "Damn the maneuvers go straight at them". Or in our case damn the Errors go straight at them." I thought by opening a cursor on every insert that I would be resetting the transaction block. I do not want to have to reset the connection just because of a primary key error. Is there something i am just missing?</p>
  
  <p>Thanks before hand for your time.</p>
  
  <p>John</p>