python 在 sqlalchemy 中按年、月、日分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1370997/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
group by year, month, day in a sqlalchemy
提问by gamp
I want
我想
DBSession.query(Article).group_by(Article.created.month).all()
But this query can't using
但是这个查询不能使用
How do I do this using SQLAlchemy?
如何使用 SQLAlchemy 执行此操作？
回答by Jochen Ritzel
assuming you db engine actually supports functions like MONTH(), you can try
假设您的数据库引擎实际上支持类似的功能MONTH()，您可以尝试
import sqlalchemy as sa
DBSession.query(Article).group_by( sa.func.year(Article.created), sa.func.month(Article.created)).all()
else you can group in python like
否则你可以在 python 中分组
from itertools import groupby
def grouper( item ):
return item.created.year, item.created.month
for ( (year, month), items ) in groupby( query_result, grouper ):
for item in items:
# do stuff
回答by raben
THC4k answer worksbut I just want to add that query_resultneed to be already sortedto get itertools.groupbyworking the way you want.
THC4k 答案有效，但我只想补充一点，query_result需要已经排序才能itertools.groupby按照您想要的方式工作。
query_result = DBSession.query(Article).order_by(Article.created).all()
Here is the explanation in the itertools.groupby docs:
这是itertools.groupby 文档中的解释 ：
The operation of groupby() is similar to the uniq filter in Unix. It generates a break or new group every time the value of the key function changes (which is why it is usually necessary to have sorted the data using the same key function). That behavior differs from SQL's GROUP BY which aggregates common elements regardless of their input order.
groupby() 的操作类似于Unix 中的uniq 过滤器。每次键函数的值发生变化时，它都会生成一个中断或新组（这就是为什么通常需要使用相同的键函数对数据进行排序的原因）。该行为与 SQL 的 GROUP BY 不同，后者聚合公共元素而不管它们的输入顺序。
回答by Matt
I know this question is ancient, but for the benefit of anyone searching for solutions, here's another strategy for databases that don't support functions like MONTH():
我知道这个问题很古老，但为了任何寻找解决方案的人的利益，这是不支持 MONTH() 等函数的数据库的另一种策略：
db.session.query(sa.func.count(Article.id)).\
group_by(sa.func.strftime("%Y-%m-%d", Article.created)).all()
Essentially this is turning the timestamps into truncated strings that can then be grouped.
本质上，这是将时间戳转换为截断的字符串，然后可以对其进行分组。
If you just want the most recent entries, you can add, for example:
如果您只想要最近的条目，您可以添加，例如：
order_by(Article.created.desc()).limit(7)
Following this strategy, you can easily create groupings such as day-of-week simply by omitting the year and month.
按照此策略，您只需省略年和月即可轻松创建诸如星期几之类的分组。
