为何SQLAlchemy调用PostgreSQL函数后fetchall结果格式异常？

问题现象 #

使用SQLAlchemy调用PostgreSQL的getTopSales函数时，db_result.fetchall()返回的结果是每个元素为包含字符串化元组的单元素元组的列表：

[('("Murder in the First (1995)",2018,57)',), ('("U-571 (2000)",2020,53)',), ('("Money Train (1995)",2019,52)',), ('("Picnic at Hanging Rock (1975)",2019,52)',)]

而预期结果应为多列组成的元组列表：

[("Murder in the First (1995)",2018,57),("U-571 (2000)",2020,53),("Money Train (1995)",2019,52), ("Picnic at Hanging Rock (1975)",2019,52)]

现有代码 #

Python调用代码 #

db_conn = None
db_conn = db_engine.connect()

db_result = db_conn.execute(select([func.getTopSales(2018,2020)]))

print(db_result.fetchall())        
return db_result

PostgreSQL函数定义 #

create or replace function getTopSales(y1 int, y2 int) 
returns table (titulo char,
               año int,
               ventas int) as $$
    with prod_year_sales as ( /*The products with their sales and the year of the sale*/
    select
        orderdetail.prodid,
        extract (year from orders.orderdate) as "year",
        count(orderdetail.prodid) as sales
    from
        orderdetail
    natural join 
        orders
    where
        extract(year from orders.orderdate) between y1 and y2 group by orderdetail.prodid, "year"),
    max_sales_year_prod as ( /*The products with most sales by year*/
    select
        a.prodid,
        a."year",
        a.sales
    from
        prod_year_sales a
    inner join (
        select
            "year",
            max(sales) sales
        from
            prod_year_sales
        group by
            ("year")) as b on
        a."year" = b."year"
        and a.sales = b.sales)
    select /*Get title of the movies that correspond to the sold products*/
        movietitle,
        main."year",
        sales
    from
        imdb_movies im
    inner join (
        select
            movieid,
            "year",
            sales
        from
            max_sales_year_prod m
        inner join products p on
            m.prodid = p.prodid) as main on im.movieid  = main.movieid order by(sales) desc;
$$ language sql;

解决方法 #

问题根源 #

原代码中select([func.getTopSales(2018,2020)])将表函数的返回结果当作单个列处理，PostgreSQL会自动把整行数据序列化为字符串形式的元组，导致返回结果格式异常。正确的做法是将表函数当作一张表来查询。

修正后的代码 #

提供两种可行的修正方案：

方案1：执行原生SQL语句

直接用SELECT * FROM getTopSales(...)的方式调用表函数，SQLAlchemy会识别出多列结果：

from sqlalchemy import text

db_conn = db_engine.connect()
# 带参数的原生SQL调用，避免SQL注入
db_result = db_conn.execute(text("SELECT * FROM getTopSales(:y1, :y2)"), {"y1": 2018, "y2": 2020})
print(db_result.fetchall())
return db_result

方案2：使用SQLAlchemy的Table-Valued函数语法

通过table_valued()方法明确指定表函数返回的列名，让SQLAlchemy正确解析结果：

from sqlalchemy import select, func

db_conn = db_engine.connect()
# 声明表函数的返回列
top_sales_func = func.getTopSales(2018, 2020).table_valued("titulo", "año", "ventas")
db_result = db_conn.execute(select(top_sales_func))
print(db_result.fetchall())
return db_result

额外说明 #

修正后，若需要将结果转为字典列表，可使用db_result.mappings().fetchall()，会得到每个元素为字典的列表，键对应函数定义的列名。

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