Psycopg2: cursor is already closed

Question

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

 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 the rest of the requests also fail with cursor already closed . How can I guarantee that if one request fails, the following requests will succeed.

Answer 1

Presumably, if the connection fell, you will need to restore 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 clarify the exceptions that you catch. Assuming an InterfaceError exception, if the cursor is somehow closed, you can catch this:

 except psycopg2.InterfaceError as e: 

There may be other less serious problems that will prevent subsequent requests from being executed, for example. transaction aborted. In this case, you need to cancel the current transaction, and then try the following 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 the query is checked for a nonexistent table. A ProgrammingError exception has been thrown, and the connection must be canceled if another request is made. The second request must be successful.

This will mask the details of further exceptions that occur in the exception handlers themselves, for example, connect(...) may not work when trying to reconnect, so you should also handle this.

Answer 2

You must explicitly regenerate the cursor in the except block if something went wrong at a lower level that the request is:

 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()