How do I recover from error in a transaction with psycopg2?

Question

I have a script that imports data from old database. Somewhere along the way I run into unique constraint violation. I want to amend the query and execute it again, but it says, "psycopg2.InternalError: current transaction is aborted, commands ignored until end of transaction block":

try:
    pcur.execute(sql, values)
except psycopg2.IntegrityError:
    value = ...
    pcur.execute(sql, values)

How do I do that without switching to autocommit mode?

score 5 · Accepted Answer · answered Jun 19 '17 at 10:16

Inspired by this answer:

pcur.execute('SAVEPOINT sp1')
try:
    pcur.execute(sql, values)
except psycopg2.IntegrityError:
    pcur.execute('ROLLBACK TO SAVEPOINT sp1')
    value = ...
    pcur.execute(sql, values)
else:
    pcur.execute('RELEASE SAVEPOINT sp1')

How do I recover from error in a transaction with psycopg2?

1 Answers1