CODEWITHSUNDEEP
pythonpostgresqlpsycopg2
nish
nish

Reputation: 7280

psycopg2 : cursor already closed

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.

Upvotes: 27

Views: 55082

Answers (3)

api_nest
api_nest

Reputation: 451

A trivial mistake one could make is that the cursor is named twice for eg.

cursor1=connection.cursor()
connection.autocommit=True 

# Naming the cursor object for the second time with a different name.

cursor2=connection.cursor()  
connection.autocommit=True

Since there can only be one cursor object, the other cursor object overwrites the first one, and if one uses the first cursor object the above exception gets thrown.

Upvotes: 0

mhawke
mhawke

Reputation: 87074

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.

Upvotes: 37

Serge Ballesta
Serge Ballesta

Reputation: 148900

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

Upvotes: 6

Related Questions