CODEWITHSUNDEEP
pythonpostgresqlpsycopg2
yigal
yigal

Reputation: 4725

With psycopg2 how to avoid using the connection context manager

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?

Upvotes: 2

Views: 13515

Answers (4)

Marty
Marty

Reputation: 392

Adding some context here. Psycopg 3 works differently than 2. At the end of the context block it will close the connection.

https://www.psycopg.org/psycopg3/docs/basic/usage.html

Using these objects as context managers (i.e. using with) will make 
sure to close them and free their resources at the end of the block 
(notice that this is different from psycopg2).

Upvotes: 0

Pratyush Raizada
Pratyush Raizada

Reputation: 173

In psycopg, the Context manager has been implemented in such a way that the with statement will only terminate the transaction and not close the connection for you. The connection needs to be closed by you separately.

In case of an error with your transaction, you have the option to rollback and raise the error.

One way to do this is to write the connection closing logic yourself.

def with_connection(func):
    """
    Function decorator for passing connections
    """
    def connection(*args, **kwargs):
        # Here, you may even use a connection pool
        conn = psycopg.connect(DSN)
        try:
            rv = func(conn, *args, **kwargs)
        except Exception as e:
            conn.rollback()
            raise e
        else:
            # Can decide to see if you need to commit the transaction or not
            conn.commit()
        finally:
            conn.close()
        return rv
    return connection

@with_connection
def run_sql(conn, arg1):
    cur = conn.cursor()
    cur.execute(SQL, (arg1))

Upvotes: 4

riggedCoinflip
riggedCoinflip

Reputation: 485

Since Version 2.5, psycopg2 should support the with statement like you expect it to behave.
Docs

conn = psycopg2.connect(DSN)

with conn:
    with conn.cursor() as curs:
        curs.execute(SQL1)

with conn:
    with conn.cursor() as curs:
        curs.execute(SQL2)

conn.close()

Upvotes: 2

Stephane B.
Stephane B.

Reputation: 580

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

Upvotes: 12

Related Questions