psycopg2：游标已关闭 [英] psycopg2 : cursor already closed

问题描述

我正在使用 psycopg2 2.6.1 。我有一堆查询需要按顺序执行。

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

假设 q1 失败，原因是 SSL连接已意外关闭。然后，我的其余查询也因光标已关闭而失败。我如何确保如果一个查询失败，那么以下查询将成功执行。

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.

推荐答案

如果连接已断开，则需要重新建立连接并在异常处理程序中获取另一个光标：

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

除了捕获到的异常之外，您还应该更加具体。假设 InterfaceError 异常（如果以某种方式关闭了光标），您可以这样捕获：

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

此处尝试对非存在的表。引发 ProgrammingError 异常，如果要尝试其他查询，则必须回滚连接。第二个查询应该成功。

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.

这掩盖了异常处理程序本身中引发的其他异常的详细信息，例如 connect（...） 尝试重新建立连接时可能会失败，因此您也应该处理该问题。

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.

这篇关于psycopg2：游标已关闭的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！