postgresql psycopg2 : 游标已经关闭
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35651586/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
psycopg2 : cursor already closed
提问by nish
I am using psycopg2 2.6.1. I have a bunch of queries that I need to execute in sequence.
我正在使用psycopg2 2.6.1. 我有一堆需要按顺序执行的查询。
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 q1fails 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.
假设q1失败了SSL connection has been closed unexpectedly。然后我的其余查询也失败了cursor already closed。我如何确保如果一个查询失败，那么以下查询将成功执行。
回答by mhawke
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 InterfaceErrorexception if the cursor is somehow closed you can catch that like this:
您应该更具体地处理您捕获的异常。假设InterfaceError游标以某种方式关闭时出现异常，您可以像这样捕获它：
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 ProgrammingErrorexception is raised, and the connection must be rolled back if another query is to be attempted. The second query should succeed.
这里针对不存在的表尝试查询。一个ProgrammingError例外被引发，并且连接必须回滚如果另一个查询被尝试。第二个查询应该会成功。
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.
这掩盖了异常处理程序本身引发的进一步异常的细节，例如，connect(...)在尝试重新建立连接时可能会失败，因此您也应该处理它。
回答by Serge Ballesta
You should explicitely regeneratethe cursor in the except bloc in case something went wrong at a lower level that the query:
您应该明确地重新生成except 块中的游标，以防在查询的较低级别出现问题：
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()
