SQLAlchemy如何在映射类查询中获取未映射的COUNT(*)等聚合结果

核心问题说明 #

你遇到的问题主要来自两点：

• 使用Query(Actor).from_statement()仅指定了Actor映射类作为返回实体，SQL中额外的聚合列不会被自动解析返回
• 直接构造独立Query时的排序写法存在问题，才会提示找不到my_count列

无原生SQL、Query独立无Session绑定的实现方案 #

无需提前绑定Session，直接通过sqlalchemy.orm.Query类构造查询即可，代码如下：

from sqlalchemy.orm import Query
from sqlalchemy import func

# 构造过滤条件，支持年份区间等任意复杂过滤
current_filter = Movie.year == 1999
# 构造完全不绑定Session的独立Query
query = Query(
    Actor,
    func.count(Movie.id).label("my_count")
).join(
    # 直接用已定义的relationship关联，不用手动写关联表条件
    Actor.movies
).filter(
    current_filter
).group_by(
    Actor.id
).order_by(
    # 直接用聚合函数排序，避免引用自定义label找不到列的问题
    func.count(Movie.id).desc()
)

执行时再绑定Session即可，有两种常用方式：

1. 给Query对象绑定Session后执行

query.session = db.session
results = query.all()
# 返回结果格式为[(Actor对象, 参演数量), ...]，直接遍历即可
for actor, count in results:
    print(actor.name, count) # 完全匹配你需要的输出格式

2. 直接用Session执行Query，无需给Query单独绑定Session

results = db.session.execute(query).all()
# 返回结果结构和上面完全一致

如果需要把参演数量临时挂载到Actor对象上，遍历赋值即可：

actor_list = []
for actor, count in results:
    actor.my_count = count # 临时新增属性，不会影响原有映射定义
    actor_list.append(actor)
# 后续直接访问 actor.my_count 即可获取统计值

原生SQL方案的修正方法 #

如果你坚持用原生文本查询，只需要修改Query的返回实体配置即可拿到统计值：

stmt = text(
    f"SELECT actor.*, COUNT(*) as my_count FROM "
    f"actor JOIN movie_actors on actor.id = movie_actors.actor_id "
    f"WHERE movie_actors.movie_id IN ({query_to_txt_stmt(query_movies_id)}) "
    f"GROUP BY actor.id ORDER BY my_count DESC"
    )
# Query同时指定返回Actor对象和my_count列
final_query = Query(Actor, func.column('my_count')).from_statement(stmt)
final_query.session = db.session
results = final_query.all() # 返回结果同样是(actor, count)的元组

额外注意点 #

你现有映射代码中存在错误：Movie类的actors关系的back_populates参数值写错了，当前写的是back_populates="actors"，应该改为back_populates="movies"，对应Actor类中的movies属性，否则关联查询会出现异常。

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