Python connections to PostgreSQL using psycopg2 - Why need to close & re-open connection for ensuing code to work?

Question

I have encountered a mystery while using psycopg2:

• My program connects fine, and creates a cursor OK
• This cursor is used within "try ... except" constructs to drop two temp tables if they exist
• (The 'except' logic runs for both of these, because the tables don't exist.)
• However, the rest of the program that follows crashes at the first '.execute()', because it is unable to use the new cursor it seemed to have created OK from that connection.
• If I close the connection, and re-open it (or create & use a second connection), the ensuing logic runs fine.
• (If I comment out both "try ... except" constructs it runs fine, right through - understandably.)

Here is the program code:

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

If the connection is not closed and re-opened, an exception is thrown on the line:

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

Added on Nov 19 as requested: Here is the last part of the Visual Studio 2019 "Output" window, showing the last print statements, Exception message, and Stack Trace:

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:\Python\Applications\SpeedTest\TestPostgreSQLPurePython2.py", line 49, in <module>
 >    loCursor2.execute(f"create temporary table TmpSubset as select * from Jobs where RowID % 100 = 0")

Why is Python / psycopg2 unable to use that new cursor (loCursor2) on the original connection, after the 'excepts' have fired? Any ideas?

Answer 1

You should read the error messages you get:

sycopg2.errors.InFailedSqlTransaction: current transaction is aborted, commands ignored until end of transaction block

Once you got an error, you need to end the transaction (rollback) before you can continue, so add this to your exception blocks.

loConnection.rollback()