原始 SQL
SELECT TO_CHAR(date_published, 'yyyymm') AS yo
FROM blog
GROUP BY yo;
方案一：使用 extract() 組合年月
year_field = db.func.extract('year', ArticleModel.date_published)
month_field = db.func.extract('month', ArticleModel.date_published)
yonth_field = year_field * 100 + month_field
query = db.session.query(
yonth_field.label('yonth'),
db.func.count(1)
).group_by('yonth')
# 生成 SQL：
# SELECT EXTRACT(year FROM date_published)*:param_1 + EXTRACT(month FROM date_published) AS yonth,
# count(:param_2) AS count_1
# FROM article_meta JOIN article ON article_meta.id = article.id
# GROUP BY yonth
方案二：直接呼叫資料庫原生函數
yonth = db.func.to_char(ArticleModel.date_published, 'yyyymm').label('yonth')
archive_list = db.session.query(
yonth,
db.func.count(1)
).group_by('yonth').all()
關鍵要點
db.func 可直接對應資料庫任意內建函數或預存程序- 跨資料庫可攜性：
extract() 較標準，to_char() 為 PostgreSQL/Oracle 專用
- 標籤
.label() 讓結果可用屬性存取
