Setting up a Connection db utility using psycopg3

Question

I have this db_utils.py section where I define newconnect():

def newconnect():

with psycopg.connect("host=aaa port=5432 dbname=ccc user=bbb password=xxx") as conn:
    print("Connected")
   

    cur = conn.execute('SELECT version()')
    db_version = cur.fetchone() 
    print(db_version)
    print("Returning Cursor", cur) 
    return(cur)

This approach works with psycopg2, but with psyopg3, when I call this method:

 cur = newconnect()

It prints out the Postgres version number as expected, but the returned cursor seems to be operating on a closed connection. When I do

 cur.execute(some_sql)  # main program that called db_utils/newconnect() 
 results = cur.fetchall() 
 print(results)

It does not execute that some_sql string. Instead,

psycopg.OperationalError: the connection is closed

How do I adjust the above to avoid this error?

score 0 · Answer 1 · answered May 08 '23 at 19:46

It is necessary to get rid of the "with" in the newconnect() function then the cur is returned without a closed connection.

The web page https://www.psycopg.org/psycopg3/docs/basic/usage.html explains the "Connection Context" set up by the "with", which is not what we want here.

Setting up a Connection db utility using psycopg3

1 Answers1