如何在SQLAlchemy中按星期几分组并求和指定列？

报错的核心原因是不同数据库的星期提取函数不统一：你用了func.dayofweek，但你的数据库（从报错信息看是PostgreSQL）没有这个内置函数，PostgreSQL用extract(dow from ...)获取星期几的数字，而MySQL等数据库才支持dayofweek()。

下面是具体实现步骤，支持两种常见场景：

1. 基础实现（Python端转换星期名称） #

步骤1：根据数据库类型编写查询 #

针对PostgreSQL：

from sqlalchemy import func
from your_models import SQL_table  # 替换为你的模型类

# 提取星期几数字（0=周日，1=周一...6=周六）
dow = func.extract('dow', SQL_table.created_at).label('dow')
# 分组汇总collected值
query = session.query(
    func.sum(SQL_table.collected).label('collected'),
    dow
).group_by(dow).order_by(dow)

针对MySQL：

# MySQL的dayofweek返回1=周日，2=周一...7=周六
dow = func.dayofweek(SQL_table.created_at).label('dow')
query = session.query(
    func.sum(SQL_table.collected).label('collected'),
    dow
).group_by(dow).order_by(dow)

步骤2：数字转星期名称并生成目标格式 #

定义对应数据库的星期映射字典，再将查询结果转换为你要的列表格式：

# PostgreSQL对应的映射
day_map = {
    0: 'Sunday',
    1: 'Monday',
    2: 'Tuesday',
    3: 'Wednesday',
    4: 'Thursday',
    5: 'Friday',
    6: 'Saturday'
}

# 执行查询并转换格式
results = query.all()
output = [{'collected': int(row.collected), 'day': day_map[row.dow]} for row in results]

2. 进阶实现（SQL端直接转换星期名称） #

如果希望在SQL查询中直接返回星期名称（避免Python端处理），可以用SQLAlchemy的func.case实现：

from sqlalchemy import func

# PostgreSQL版本：直接在查询中映射星期名称
day_name = func.case(
    (func.extract('dow', SQL_table.created_at) == 0, 'Sunday'),
    (func.extract('dow', SQL_table.created_at) == 1, 'Monday'),
    (func.extract('dow', SQL_table.created_at) == 2, 'Tuesday'),
    (func.extract('dow', SQL_table.created_at) == 3, 'Wednesday'),
    (func.extract('dow', SQL_table.created_at) == 4, 'Thursday'),
    (func.extract('dow', SQL_table.created_at) == 5, 'Friday'),
    (func.extract('dow', SQL_table.created_at) == 6, 'Saturday'),
    else_='Unknown'
).label('day')

# 分组查询，直接得到带星期名称的结果
query = session.query(
    func.sum(SQL_table.collected).label('collected'),
    day_name
).group_by(day_name).order_by(func.extract('dow', SQL_table.created_at))

# 直接转换为目标字典格式
output = [row._asdict() for row in query.all()]

注意事项 #

• 不同数据库的星期数字规则不同：PostgreSQL的dow是0=周日，MySQL的dayofweek是1=周日，一定要对应正确的映射字典。
• 如果用其他数据库（比如SQLite），可以用func.strftime('%w', SQL_table.created_at)提取星期数字（0=周日，6=周六）。

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