Search code examples
pythonpeewee

How do I convert this into peewee

I have this SQLquery, and I'm currently executing it in peewee like so

sql = "select DATE(inserted) Date, COUNT(*) totalCount FROM tbl_rss_region GROUP BY DATE(inserted)"

db.execute_sql(sql)

Would it be possible to write this off as as a db.select(DATE(tbl_rss_region.inserted)....) statement?

I have tried this, but it's not grouping them by date, and also taking into consideration the time, so it's getting me the wrong values:

for l in TBL_RSS_REGION.select(TBL_RSS_REGION.inserted, fn.COUNT(TBL_RSS_REGION.id).alias("count")).group_by(TBL_RSS_REGION.inserted):
    print l.inserted, l.count

2015-08-31 09:31:33.530000 1
2015-08-31 09:31:33.538000 1
2015-08-31 09:31:33.549000 1
2015-08-31 09:31:33.561000 1
2015-08-31 09:31:33.569000 1
Solution

  • Another option would be to write:

    TRS = TBL_RSS_REGION  # ew all caps, wtf bro
query = (TRS
         .select(
             fn.DATE(TRS.inserted).alias('day'),
             fn.COUNT(TRS.id).alias('count'))
         .group_by(fn.DATE(TRS.inserted)))

    You used fn.COUNT, perhaps you didn't know you could use it with any arbitrary SQL function?