SQLalchemy Cleanup Challenge
October 13, 2011 at 02:40 PM | categories: python, sqlalchemy | View Comments
Yesterday I found myself writing some very interesting SQLalchemy. The problem is I have a date column in PostgreSQL that is stored as epoch time, so it is just an Interger column. I need to group by year,month and grab the total count of status='A' groups for that year,month combination.
Here is what I came up with, can you make it cleaner? Faster? I am curious to see the different variations people come up with.
pg_date_part_month = sa.func.date_part('month',
sa.func.to_timestamp(Group.register_time))
pg_date_part_year = sa.func.date_part('year',
sa.func.to_timestamp(Group.register_time))
group_month_select = (
db.query(
sa.sql.label('year', pg_date_part_year),
sa.sql.label('month', pg_date_part_month),
sa.sql.label('total', sa.func.count(Group.status))
)
.filter_by(status='A')
.group_by(pg_date_part_year)
.group_by(pg_date_part_month)
.group_by(Group.status)
.order_by(pg_date_part_year)
.order_by(pg_date_part_month)
)