本地连接render.com Postgres服务器SQL脚本执行异常求助

使用Python脚本连接Render上的PostgreSQL服务器时，第一个SQL脚本负责创建表，执行日志显示成功，但第二个插入数据的脚本报错表不存在。

代码实现 #

from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import sessionmaker
from os import getenv
import psycopg2
from sqlalchemy import text

# 执行SQL脚本的函数
def execute_sql_script(engine, script_file):
    """Execute SQL script"""
    with open(script_file, 'r', encoding="utf-8") as file:
        sql_script = file.read()
        print("File read")

    with engine.connect() as connection:
        try:
            print("trying to execute sql script")
            connection.execute(text(sql_script))
            print("SQL script executed successfully.")
        except SQLAlchemyError as e:
            print("Error executing SQL script:", e)

def main():
    db_url = f"postgresql://user:xxxxx@dpg-cov771o21fec73ffrp8g-a.frankfurt-postgres.render.com/cheese_db"

    try:
        # 创建SQLAlchemy引擎
        engine = create_engine(db_url)

        # 创建会话
        Session = sessionmaker(bind=engine)
        session = Session()

        # SQL脚本文件路径
        script_files = ['website/static/sql/set_up.sql', 'website/static/sql/add_data.sql']

        # 依次执行脚本
        for script_file in script_files:
            execute_sql_script(engine, script_file)

    except SQLAlchemyError as e:
        print("Error:", e)

if __name__ == '__main__':
    main()

执行输出 #

File read
trying to execute sql script
SQL script executed successfully.
File read
trying to execute sql script
Error executing SQL script: (psycopg2.errors.UndefinedTable) relation "source" does not exist
LINE 1: INSERT INTO source (name) VALUES

解决方案 #

1. 手动提交事务 #

SQLAlchemy通过engine.connect()创建的连接默认开启事务，所有操作在提交前不会持久化到数据库，脚本执行结束后会自动回滚。需要在执行SQL后手动提交事务：

修改execute_sql_script函数：

def execute_sql_script(engine, script_file):
    """Execute SQL script"""
    with open(script_file, 'r', encoding="utf-8") as file:
        sql_script = file.read()
        print("File read")

    with engine.connect() as connection:
        try:
            print("trying to execute sql script")
            connection.execute(text(sql_script))
            connection.commit()  # 提交事务，将操作持久化到数据库
            print("SQL script executed successfully.")
        except SQLAlchemyError as e:
            connection.rollback()  # 出错时回滚事务，避免脏数据
            print("Error executing SQL script:", e)

2. 使用自动提交模式 #

也可以通过设置连接的isolation_level为AUTOCOMMIT，让每个SQL语句执行后自动提交：

with engine.connect().execution_options(isolation_level="AUTOCOMMIT") as connection:
    try:
        print("trying to execute sql script")
        connection.execute(text(sql_script))
        print("SQL script executed successfully.")
    except SQLAlchemyError as e:
        print("Error executing SQL script:", e)

3. 验证SQL脚本正确性 #

检查set_up.sql中创建source表的语句是否存在语法错误，比如是否遗漏分号、表名拼写是否正确，确保该脚本能单独在数据库客户端执行成功。

4. 确认数据库连接正确性 #

核对db_url中的数据库名称、用户名、密码是否正确，确保脚本连接的是目标数据库，而非其他同名或错误的数据库实例。

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