Skip to main content
 首页 » 编程设计

python之在 psycopg2 中使用 "with statement"关闭连接的正确方法是什么

2024年11月24日8JeffreyZhao

我想知道,使用 with 语句和 psyopcg2 关闭与 Postgres 数据库连接的正确方法是什么。

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 

这是处理连接的更好方法吗?

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

警告 与文件对象或其他资源不同,退出连接的 with block 不会关闭连接,只会关闭与其关联的事务。如果你想确保连接在某个点后关闭,你仍然应该使用 try-catch block :

conn = psycopg2.connect(DSN) 
try: 
    # connection usage 
finally: 
    conn.close() 
 

请您参考如下方法:

关闭连接的正确方法:

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