What is proper way to close connection in psyopcg2 with "with statement"?

Question

I want to konw, what is a proper way to closing connection with Postgres database using with statement and psyopcg2.

import pandas as pd
import psycopg2
def create_df_from_postgres(params: dict,
                                   columns: str,
                                   tablename: str,
                                   ) -> pd.DataFrame:

    with psycopg2.connect(**params) as conn:
        data_sql = pd.read_sql_query(
          "SELECT " + columns + ", SUM(total)"
          " AS total FROM " + str(tablename),
          con=conn
          )
    # i need to close conection here:
        # conn.close()

    # or here:
    conn.close()
    return data_sql

Is this a better way to handle connection ?

def get_ci_method_and_date(params: dict,
                           columns: str,
                           tablename: str,
                           ) -> pd.DataFrame:

    try:
        connection = psycopg2.connect(**params)
        data_sql = pd.read_sql_query('SELECT ' + columns +
                                     ' FROM ' + str(tablename),
                                     con=connection
                                     )
    finally:
        if(connection):
            connection.close()
    return data_sql

From official psycopg docs

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

Answer 1

Proper way to close a connection:

From official psycopg docs:

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