peewee select() return SQL query, not the actual data

Question

I'm trying sum up the values in two columns and truncate my date fields by the day. I've constructed the SQL query to do this(which works):

SELECT date_trunc('day', date) AS Day, SUM(fremont_bridge_nb) AS 
Sum_NB, SUM(fremont_bridge_sb) AS Sum_SB FROM bike_count GROUP BY Day 
ORDER BY Day;

But I then run into issues when I try to format this into peewee:

Bike_Count.select(fn.date_trunc('day', Bike_Count.date).alias('Day'),
fn.SUM(Bike_Count.fremont_bridge_nb).alias('Sum_NB'),
fn.SUM(Bike_Count.fremont_bridge_sb).alias('Sum_SB'))
.group_by('Day').order_by('Day')

I don't get any errors, but when I print out the variable I stored this in, it shows:

 <class 'models.Bike_Count'> SELECT date_trunc(%s, "t1"."date") AS 
Day, SUM("t1"."fremont_bridge_nb") AS Sum_NB, 
SUM("t1"."fremont_bridge_sb") AS Sum_SB FROM "bike_count" AS t1 ORDER 
BY %s ['day', 'Day']

The only thing that I've written in Python to get data successfully is:

 Bike_Count.get(Bike_Count.id == 1).date

Answer 1

If you just stick a string into your group by / order by, Peewee will try to parameterize it as a value. This is to avoid SQL injection haxx.

To solve the problem, you can use SQL('Day') in place of 'Day' inside the group_by() and order_by() calls.

Another way is to just stick the function call into the GROUP BY and ORDER BY. Here's how you would do that:

day = fn.date_trunc('day', Bike_Count.date)
nb_sum = fn.SUM(Bike_Count.fremont_bridge_nb)
sb_sum = fn.SUM(Bike_Count.fremont_bridge_sb)

query = (Bike_Count
         .select(day.alias('Day'), nb_sum.alias('Sum_NB'), sb_sum.alias('Sum_SB'))
         .group_by(day)
         .order_by(day))

Or, if you prefer:

query = (Bike_Count
         .select(day.alias('Day'), nb_sum.alias('Sum_NB'), sb_sum.alias('Sum_SB'))
         .group_by(SQL('Day'))
         .order_by(SQL('Day')))