如何在SQLAlchemy中根据列值筛选符合日期条件的多行数据

场景说明 #

现有数据库表结构如下：

phase_type phase_start phase_end
Obsolete 01/01/2021 02/02/2022
Obsolete 01/03/2021 02/07/2022
Obsolete 05/01/2021 09/02/2022
Available 05/07/2021 09/02/2027
Available 05/07/2023 09/02/2025
Available 05/07/2024 09/02/2029

原需求为筛选当前日期位于phase_end前30天至phase_end区间内的数据，对应的SQLAlchemy查询代码如下：

from datetime import date,timedelta
from sqlalchemy import select

past   = my_table.phase_end - timedelta(30)
future = my_table.phase_end

query = select(my_table).where(date.today() >= past,date.today() <= future)

session.exec(query).fetchall()

现需调整筛选逻辑：

• 当phase_type为Obsolete时，以phase_start为基准，筛选当前日期位于phase_start前30天至phase_start区间内的行
• 其他类型仍以phase_end为基准，保持原逻辑不变

解决方案 #

利用SQLAlchemy的case表达式实现动态基准日期的选择，代码如下：

from datetime import date, timedelta
from sqlalchemy import select, case
from sqlalchemy.sql.expression import and_

# 动态选择基准日期：Obsolete类型用phase_start，其余用phase_end
base_date = case(
    (my_table.phase_type == "Obsolete", my_table.phase_start),
    else_=my_table.phase_end
)

# 计算目标日期区间：基准日期前30天 到 基准日期
range_start = base_date - timedelta(days=30)
range_end = base_date

# 构建查询条件，确保当前日期落在目标区间内
query = select(my_table).where(
    and_(
        date.today() >= range_start,
        date.today() <= range_end
    )
)

# 执行查询并获取结果
filtered_rows = session.exec(query).fetchall()

逻辑说明 #

• case表达式会根据phase_type的值动态切换使用phase_start或phase_end作为基准日期
• 基于基准日期计算出30天的时间窗口，再通过and_组合条件，筛选出当前日期落在该窗口内的数据行

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