使用psycopg2与PostgreSQL的Python连接 - 为什么需要关闭并重新打开连接才能使后续代码正常工作？

Question

我在使用psycopg2:时遇到了一个谜

• 我的程序连接良好，并创建一个光标OK
• 该光标用在"；尝试除了"；构造以删除两个临时表(如果存在(
• ("except"逻辑对这两个表都运行，因为表不存在。(
• 然而，后面的程序的其余部分在第一个".execute(("处崩溃，因为它无法使用新的游标，它似乎已经从该连接创建了OK
• 如果我关闭连接，然后重新打开它(或者创建并使用第二个连接(，那么接下来的逻辑运行良好
• (如果我把这两个"尝试……除了"的结构都评论掉，它运行得很好，贯穿始终——这是可以理解的。(

这是程序代码：

import psycopg2             # PostgreSQL module - need to install.  See https://www.psycopg.org/docs/
lcConnectionString = "...obfuscated..."
loConnection = psycopg2.connect(lcConnectionString)
print(f"loConnection after '.connect()' is: {loConnection}")
loCursor = loConnection.cursor()
print(f"loCursor is {loCursor}")
try:
loCursor.execute("drop table TmpJobs")
print("Dropped TmpJobs table")
except Exception as exc:
print("Did not need to drop TmpJobs table table")
try:
loCursor.execute("drop table TmpSubset")
print("Dropped TmpSubset table")
except Exception as exc:
print("Did not need to drop TmpSubset table")
print(f"loConnection after 'exceptions' is: {loConnection}")
print(f"loCursor after 'exceptions' is {loCursor}")
# The rest of the program runs fine if close and reopen the connection. But crashes if don't.
llCloseAndReopen = False            # Testing: True / False
if llCloseAndReopen:
loConnection.close()
print(f"loConnection after '.close()' is: {loConnection}")
loConnection = loCursor = None
loConnection = psycopg2.connect(lcConnectionString)
print(f"loConnection after 're-connect' is: {loConnection}")
print("n-----------------------------------------nSelecting from Jobs into subset result...")
loCursor2 = loConnection.cursor()
print(f"loCursor2 (just created): {loCursor2}")
loCursor2.execute(f"create temporary table TmpSubset as select * from Jobs where RowID % 100 = 0")
loCursor2.execute(f"select * from TmpSubset")
loResult = loCursor2.fetchall() 
print(f"{len(loCursor2.description)} columns in Subset result")
lnRowCount = 0
for Row in loResult:
lnRowCount += 1
print(f"{lnRowCount}: {Row[0]}, {Row[1]}, {Row[2]}, {Row[3]}")
print(f"{lnRowCount} rows in Subset result")

如果连接没有关闭并重新打开，则会在以下行引发异常：

loCursor2.execute(f"create temporary table TmpSubset as select * from Jobs where RowID % 100 = 0")

应要求于11月19日添加：这是Visual Studio 2019的最后一部分"输出"；窗口，显示最后的打印语句、异常消息和堆栈跟踪：

Did not need to drop TmpSubset table
loConnection after 'exceptions' is: <connection object at 0x0579D878; dsn: 'user= ...obfuscated... host=localhost', closed: 0>
loCursor after 'exceptions' is <cursor object at 0x04815028; closed: 0>
-----------------------------------------
Selecting from Jobs into subset result...
loCursor2 (just created): <cursor object at 0x047B2F28; closed: 0>
current transaction is aborted, commands ignored until end of transaction block
Stack trace:
>  File "J:PythonApplicationsSpeedTestTestPostgreSQLPurePython2.py", line 49, in <module>
>    loCursor2.execute(f"create temporary table TmpSubset as select * from Jobs where RowID % 100 = 0")

为什么Python/psycopg2在"excepts"触发后无法在原始连接上使用新光标(loCursor2(？有什么想法吗？

Answer 1

您应该阅读收到的错误消息：

sycopg2.errors.InFailedSqlTransaction：当前事务中止，命令被忽略，直到事务块结束

一旦出现错误，您需要结束事务(回滚(才能继续，因此请将其添加到异常块中。

loConnection.rollback()