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

score 4 · Accepted Answer · edited Apr 10 '19 at 14:40

4

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

edited Apr 10 '19 at 14:40

unutbu

777,569
165
1,697
1,613

answered Apr 10 '19 at 14:28

Piotr Rybiński

91
8

is it not advisable to put the "conn = psycopg2.connect(DSN)" inside the try statement incase there is an except with trying to connect? ie. wrong password or something. – Zaffer May 29 '21 at 14:25
@Zaffer, I don't think it is necessary because the connection would not have been made in the first place. You could use another try/except block to catch exceptions related to the connection though – pelelter Sep 09 '21 at 15:07

score -1 · Answer 2 · answered Mar 25 '19 at 09:28

-1

The whole point of a with statement is that the resources are cleaned up automatically when it exits. So there is no need to call conn.close() explicitly at all.

answered Mar 25 '19 at 09:28

Daniel Roseman

567,968
59
825
842

this is the answer, if the `conn` supports an `__exit__` call to invoke `close()` then it will take care of it for you. https://stackoverflow.com/questions/1984325/explaining-pythons-enter-and-exit – Nathan McCoy Mar 25 '19 at 09:38
So assuming that `conn` doesn't supoort `__exit__` , my with statement is out of sense? – Piotr Rybiński Mar 25 '19 at 10:12
I don't understand your question. It *does* support it. – Daniel Roseman Mar 25 '19 at 10:14
I try to use with statements as much as I can. But I do have some `unexpected EOF on client connection with an open transaction` warning from PSQL with this way of doing. Is it possible that this happens, or should I go see elsewhere because it's impossible? – s.k Jan 29 '21 at 20:50
1

@DanielRoseman, Psycopg2 Docs say that "the connection is not closed by the context" [Docs - Connection Class](https://www.psycopg.org/docs/connection.html#connection) – pelelter Sep 09 '21 at 15:11

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

2 Answers2