在SQLAlchemy中为提取的日期月份添加前导零

我有一段可正常运行的代码：cast(func.extract('month', cast(metric.table.c[date_field], Date)), String(2))
我想给单数字的月份添加前导零。
我试过用.zfill(2)，但报错：zfill cannot be applied to CASE or Comparable object.
还试过通过判断长度添加'0'，也报错：Boolean value of this clause is not defined.

补充说明：使用的数据库是DuckDB（兼容PostgreSQL）
相关代码片段：

elif agg == DATE_AGG.MONTHLY.value:
    select_field = (cast(func.extract('year', cast(metric.table.c[date_field], Date)), String(5))
                    + ' '
                    + cast(func.extract('month', cast(metric.table.c[date_field], Date)), String(3))
                    ).label("datefield")

    order_field = [func.extract('year', cast(metric.table.c[date_field], Date)),
                   func.extract('month', cast(metric.table.c[date_field], Date))]
    return dict(select_val=select_field, order_by=order_field)

解决方案 #

因为你用的是兼容PostgreSQL的DuckDB，直接用数据库内置的字符串填充或格式化函数即可，无需在Python层面操作：

方法1：使用LPAD函数 #

LPAD可给字符串左侧填充指定字符到目标长度，刚好适合给单数字月份补零。修改月份转换部分：

func.lpad(cast(func.extract('month', cast(metric.table.c[date_field], Date)), String(2)), 2, '0')

对应到你的代码片段中，select_field可改写为：

select_field = (cast(func.extract('year', cast(metric.table.c[date_field], Date)), String(5))
                + ' '
                + func.lpad(cast(func.extract('month', cast(metric.table.c[date_field], Date)), String(2)), 2, '0')
                ).label("datefield")

方法2：使用TO_CHAR函数（更简洁） #

TO_CHAR能直接将日期格式化为指定字符串，一步完成年月拼接和补零：

func.to_char(cast(metric.table.c[date_field], Date), 'YYYY MM').label("datefield")

替换后完整代码片段：

elif agg == DATE_AGG.MONTHLY.value:
    select_field = func.to_char(cast(metric.table.c[date_field], Date), 'YYYY MM').label("datefield")

    order_field = [func.extract('year', cast(metric.table.c[date_field], Date)),
                   func.extract('month', cast(metric.table.c[date_field], Date))]
    return dict(select_val=select_field, order_by=order_field)

为什么之前的方法失效？ #

.zfill是Python字符串的方法，而你操作的是SQLAlchemy的表达式对象（并非Python字符串），因此无法直接调用。通过判断长度添加零的逻辑如果用Python语法实现，同样不适用于SQL层面的表达式，必须借助数据库支持的内置函数来完成。

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