With psycopg2 how to avoid using the connection context manager

Question

With psycopg2, connection and querying the database works like so

conn = psycopg2.connect('connection string')
with conn:
    cur=conn.cursor()
    cur.execute("SELECT * FROM pg_stat_activity") #simple query 
    rows = cur.fetchall()
    for row in rows:
       print (row)

After trial and error, I found out that with conn is absolutely necessary or you will get many unexplained locks.

My question is: is there a way to setup the connection to avoid the need to use it?

Answer 1

From https://www.psycopg.org/docs/usage.html,

Warning
Unlike file objects or other resources, exiting the connection’s with block doesn’t close the connection, but only the transaction associated to it. If you want to make sure the connection is closed after a certain point, you should still use a try-catch block:

   conn = psycopg2.connect(DSN)
   try:  
     # connection usage 
   finally:  
     conn.close()