group by year, month, day in a sqlalchemy

Question

I want "DBSession.query(Article).group_by(Article.created.month).all()"

But this query can't using

How do I do this using SQLAlchemy?

Answer 1

assuming you db engine actually supports functions like MONTH(), you can try

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

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

Answer 2

THC4k answer works but I just want to add that query_result need to be already sorted to get itertools.groupby working the way you want.

query_result = DBSession.query(Article).order_by(Article.created).all()

Here is the explanation in the itertools.groupby docs:

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.