DB connection not closed (ARCH2084)

Key python.performance.db-connection-not-closed · Severity error · Category performance

A connection opened via connect/cursor and used without with is not returned to the pool on error, leaking the connection. Use a context manager.

Incorrect

def count_rows(pool):
    conn = pool.getconn()
    cur = conn.cursor()
    cur.execute("SELECT count(*) FROM orders")
    return cur.fetchone()[0]

Correct

def count_rows(pool):
    with pool.connection() as conn, conn.cursor() as cur:
        cur.execute("SELECT count(*) FROM orders")
        return cur.fetchone()[0]

Fix

Open connections and cursors with with so they are returned to the pool and closed on exit.