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 encounter_id_table (            encounter_id,current_date  )            values                   (%(create_date)s, %(encounter_id)s ) ;""",            'encounter_id':i.split('~')[1],              'create_date': datetime.date.today() })    except psycopg2.IntegrityError:        conn.rollback()    else:        conn.commit()    cur.close() except Exception , e:    print 'ERROR:', e[0]

First of all: CURRENT_DATE is a reserved word in every SQL standard as well as in PostgreSQL. You cannot use it as identifier without double-quoting it. I would strongly advice not to use it at all. I renamed the column to curdate in my example

Next, I am no expert in python syntax, but you seem to have reversed the order of your insert-columns:

(%(create_date)s, %(encounter_id)s )

Should be:

( %(encounter_id)s, %(create_date)s)

To your main question: you can avoid the problem altogether by checking if the key is already in the table before using it in the insert command:

INSERT INTO encounter_id_table (encounter_id, curdate)SELECT 1234, now()::dateWHERE  NOT EXISTS (SELECT * FROM encounter_id_table t                   WHERE t.encounter_id = 1234);

In Python syntax, that should be:

cur.execute("""INSERT INTO encounter_id_table (encounter_id, curdate)    SELECT %(encounter_id)s, %(create_date)s,    WHERE  NOT EXISTS (           SELECT * FROM encounter_id_table t           WHERE t.encounter_id = %(encounter_id)s);""",  {'encounter_id':i.split('~')[1],    'create_date': datetime.date.today()})