psycopg2 : cursor already closed

Question

I am using psycopg2 2.6.1. I have a bunch of queries that I need to execute in sequence.

conn = psycopg2.connect(database=redshift_database,
                        user=redshift_user,
                        password=os.environ.get("PGPASSWORD"),
                        host=redshift_cluster,
                        port=redshift_port)
cursor = conn.cursor()

queries = [q1, q2, q3....] ## a list of queries 
for query in queries:
    try:
        cursor.execute(query)
    except:
        print e.message

Suppose q1 fails with SSL connection has been closed unexpectedly. Then my rest of the queries also fail with cursor already closed. How can I ensure that if one query fails then the following queries are executed successfully.

Answer 1

You should explicitely regenerate the cursor in the except bloc in case something went wrong at a lower level that the query:

for query in queries:
    try:
        cursor.execute(query)
    except:
        print e.message
        try:
            cursor.close()
            cursor = conn.cursor()
        except:
            conn.close()
            conn = psycopg2.connect(...)
        cursor = conn.cursor()

Answer 2

Presumably if the connection has dropped you would need to reestablish it and get another cursor in the exception handler:

for query in queries:
    try:
        cursor.execute(query)
    except Exception as e:
        print e.message
        conn = psycopg2.connect(....)
        cursor = conn.cursor()

You should be more specific with the exceptions that you catch. Assuming a InterfaceError exception if the cursor is somehow closed you can catch that like this:

except psycopg2.InterfaceError as e:

There can be other less drastic problems that will prevent subsequent queries from executing, e.g. the transaction is aborted. In that case you need to rollback the current transaction and then try the next query:

queries = ['select count(*) from non_existent_table', 'select count(*) from existing_table']
for query in queries:
    try:
        cursor.execute(query)
    except psycopg2.ProgrammingError as exc:
        print exc.message
        conn.rollback()
    except psycopg2.InterfaceError as exc:
        print exc.message
        conn = psycopg2.connect(....)
        cursor = conn.cursor()

Here a query is tried against a non-existent table. A ProgrammingError exception is raised, and the connection must be rolled back if another query is to be attempted. The second query should succeed.

This glosses over the details of further exceptions being raised in the exception handlers themselves, e.g.connect(...) might fail when attempting to reestablish the connection, so you should handle that too.