在SQLAlchemy中运行PostgreSQL函数SQL文件报错的原因与解决

我创建了一个PostgreSQL函数并保存为get_data.sql文件，函数代码如下：

create or replace function getalldata(entryDate TIMESTAMP, exitDate TIMESTAMP) RETURNS SETOF employee AS $$
BEGIN
        begin     -- try
                select employee.name, dept.name
                from employee 
                join dept on employee.dept_id=dept.id
                where  employee.name='Kumar' and 
                employee.timestamp>=entryDate and employee.timestamp<=exitDate;
        exception when others then 

            raise notice 'The transaction is in an uncommittable state. '
                         'Transaction was rolled back';

            raise notice '% %', SQLERRM, SQLSTATE;
        end ; -- try..catch
END;
$$ LANGUAGE plpgsql;

随后使用SQLAlchemy执行该SQL文件，代码如下：

try:
    from sqlalchemy import create_engine, text
    connection = engine.connect()
    with open('get_data.sql', 'r') as file:
        procedure_sql = file.read()

    procedure_sql = text(procedure_sql)

    results = connection.execute(
            procedure_sql,
            {'entryDate':start_datetime, 'exitDate':end_datetime}
        )
    results = list(results.fetchall())
except Exception as e:
    print('error', e)

执行后出现错误：

error This result object does not return rows. It has been closed automatically.

1. PostgreSQL函数定义错误：
   • 函数声明返回SETOF employee（即employee表的完整行结构），但内部查询仅返回employee.name和dept.name两个字段，与声明的返回类型不匹配。
   • PL/pgSQL函数返回数据集时，必须使用RETURN QUERY关键字触发结果返回，直接写SELECT语句不会将结果传递给调用者。
2. SQLAlchemy执行逻辑错误：
   当前代码执行的是创建函数的SQL语句，而不是调用函数。创建函数的操作本身不会生成结果集，因此调用fetchall()时会触发无结果的报错。

步骤1：修复PostgreSQL函数 #

修改函数，确保返回类型与查询结果匹配，并且使用RETURN QUERY返回数据。推荐用RETURNS TABLE明确声明返回字段，可读性更强：

create or replace function getalldata(entryDate TIMESTAMP, exitDate TIMESTAMP) 
RETURNS TABLE(emp_name text, dept_name text) AS $$
BEGIN
    BEGIN
        -- 使用RETURN QUERY返回查询结果
        RETURN QUERY
        select employee.name, dept.name
        from employee 
        join dept on employee.dept_id=dept.id
        where employee.name='Kumar' 
          and employee.timestamp >= entryDate 
          and employee.timestamp <= exitDate;
    exception when others then 
        raise notice 'The transaction is in an uncommittable state. Transaction was rolled back';
        raise notice '% %', SQLERRM, SQLSTATE;
        -- 异常时返回空结果集
        RETURN;
    END;
END;
$$ LANGUAGE plpgsql;

步骤2：修复SQLAlchemy调用代码 #

先确保函数已在数据库中创建（可通过psql工具或单独执行get_data.sql完成），然后修改代码调用函数而非创建函数：

try:
    from sqlalchemy import create_engine, text
    -- 替换为你的PostgreSQL连接字符串
    engine = create_engine('postgresql://username:password@host:port/dbname')
    connection = engine.connect()

    -- 调用函数，获取结果集
    query = text("SELECT * FROM getalldata(:entryDate, :exitDate)")
    results = connection.execute(query, {'entryDate': start_datetime, 'exitDate': end_datetime})
    results = list(results.fetchall())
    -- 处理查询结果
    print(results)
except Exception as e:
    print('error', e)
finally:
    -- 确保连接关闭
    if 'connection' in locals():
        connection.close()

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