Psycopg2连接PostgreSQL无响应求助：VPN成功但SQL函数未执行

问题描述 #

在PyCharm中通过Psycopg2连接PostgreSQL时，调用run_openvpn函数成功建立VPN连接，但后续execute_sql_request函数完全未执行——无报错、无返回结果，添加调试输出也无响应，推测函数未被调用。stop_openvpn函数可正常关闭VPN连接。

相关代码 #

VPN连接函数 #

def run_openvpn(config_path):
    try:
        subprocess.run(['openvpn', '--config', config_path])
        print('OpenVPN connection established.')
    except FileNotFoundError:
        print("OpenVPN executable not found. Please ensure it is installed and in your system's PATH.")

    except subprocess.CalledProcessError as e:
        return f"OpenVPN connection failed with error: {e}"

SQL执行函数 #

def execute_sql_request(host, port, database, user, password, sql_query):
    try:
        # Connect to the PostgreSQL database
        connection = psycopg2.connect(
            host=host,
            port=port,
            database=database,
            user=user,
            password=password
        )
    
        # Create a cursor to execute SQL queries
        cursor = connection.cursor()

        formatted_query = cursor.mogrify(sql_query)
        print("Formatted SQL Query:", formatted_query.decode('utf-8'))

        # Execute the SQL query
        cursor.execute(sql_query)

        # Fetch all the results
        results = cursor.fetchall()

        # Close the cursor and the database connection
        connection.commit()
        cursor.close()
        connection.close()

        return results  # Return the result of the SQL request as a list of tuples

    except psycopg2.Error as e:
        error_msg = str(e)
        return error_msg  # Return the error message if there was a problem

函数执行代码 #

def test_execute_sql_query(browser):
    sql.run_openvpn(config_path=**)
    sql.execute_sql_request(host=**, port=**, database=**, user=**, password=**, sql_query=**)
    sql.stop_openvpn()

原因分析与解决方案 #

核心问题 #

subprocess.run()是阻塞式调用，会等待OpenVPN进程结束后才执行后续代码。但OpenVPN启动后会持续运行在前台，不会自动退出，导致run_openvpn函数卡在subprocess.run()行，后续的execute_sql_request永远无法被触发。

解决方案 #

修改run_openvpn函数，用subprocess.Popen()替代subprocess.run()，让OpenVPN在后台运行：

import subprocess
import time

def run_openvpn(config_path):
    try:
        # 启动后台进程，按需处理输出
        process = subprocess.Popen(
            ['openvpn', '--config', config_path],
            stdout=subprocess.PIPE,
            stderr=subprocess.PIPE,
            text=True
        )
        # 等待几秒确保VPN连接建立完成
        time.sleep(5)
        print('OpenVPN connection established.')
        return process  # 返回进程对象，方便后续关闭
    except FileNotFoundError:
        print("OpenVPN executable not found. Please ensure it is installed and in your system's PATH.")
    except Exception as e:
        return f"OpenVPN connection failed with error: {e}"

同步调整stop_openvpn函数，通过进程对象终止VPN：

def stop_openvpn(process):
    process.terminate()
    process.wait()
    print('OpenVPN connection closed.')

最后修改执行代码，确保流程正常推进：

def test_execute_sql_query(browser):
    vpn_process = sql.run_openvpn(config_path=**)
    # 检查VPN连接是否成功
    if isinstance(vpn_process, str):
        print(f"VPN连接失败: {vpn_process}")
        return
    try:
        results = sql.execute_sql_request(host=**, port=**, database=**, user=**, password=**, sql_query=**)
        print("SQL查询结果:", results)
    finally:
        # 无论SQL执行结果如何，确保VPN被关闭
        sql.stop_openvpn(vpn_process)

额外注意事项 #

• time.sleep()的时长可根据实际VPN连接速度调整，避免SQL连接时VPN未就绪
• Popen不会抛出CalledProcessError，改用Exception捕获更全面的错误
• 使用finally块保证VPN连接一定会被关闭，避免资源泄漏

内容的提问来源于stack exchange，提问作者H8Umate