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()
Thread and process safetyThe Psycopg module and the connection objects are thread-safe: many threads can access the same database either using separate sessions and creating a connection per thread or using the same connection and creating separate cursors. In DB API 2.0 parlance, Psycopg is level 2 thread safe.
All the function arguments are Psycopg extensions to the DB API 2.0. commit() Commit any pending transaction to the database. By default, Psycopg opens a transaction before executing the first command: if commit() is not called, the effect of any data manipulation will be lost.
Asynchronous notificationsPsycopg allows asynchronous interaction with other database sessions using the facilities offered by PostgreSQL commands LISTEN and NOTIFY.
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()
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With