authored by Wayne Witzel III

SQLalchemy Cleanup Challenge

On October 13, 2011 In python, sqlalchemy Permalink

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)
        )
Read and Post Comments
blog comments powered by Disqus