How do I group by day of week in SQLAlchemy and sum a certain column for each day of week?

Question

I have a created_at column which stores the datetime.datetime object.

And I have a column that stores an integer for each day called as collected.

What I want is to group this table by day of week (Mon, Tue, Wed.. etc) and sum up the collected value for each day.

The output should be like this ->

 [{'collected': 11, 'day': 'Sunday'}, {'collected': 9, 'day': 'Monday'}, {'collected': 1, 'day': 'Tuesday'}, {'collected': 10, 'day': 'Wednesday'}, {'collected': 7, 'day': 'Saturday'}]

I tried using session.query(func.dayofweek( SQL_table.datetime_column).all()

and that throws error function dayofweek(timestamp without time zone) does not exist.

Please help me how can I make a query like this in SQLAlchemy. Thanks...

Answer 1

Measurement is a made up ORM class with columns created_at and collected.

This is using to_char.

This uses "FM" prefix of "Day" to suppress padding the day names to be the same length:

with Session(engine) as session:
    # by name, like "Sunday"
    for dow_name, sum_of_collected in session.query(func.to_char(Measurement.created_at, 'FMDay').label('dow_name'), func.sum(Measurement.collected)).group_by("dow_name"):
        print (dow_name, sum_of_collected)
    # by index, like 0
    for dow_index, sum_of_collected in session.execute(select(func.to_char(Measurement.created_at, 'D').label('dow_index'), func.sum(Measurement.collected)).group_by("dow_index")):
        print (dow_index, sum_of_collected)

Answer 2

I was able to get the output using this.

Session.query(sqlalchemy.extract("dow",Column.created_at).label("day_of_week",
                sqlalchemy.func.sum(Column.collected).label("collected"))

Then I just ran a for loop over the query and replaced the index with the names of days using a dict.

day_name = {
    0: "Sunday",
    1: "Monday",
    2: "Tuesday",
    3: "Wednesday",
    4: "Thursday",
    5: "Friday",
    6: "Saturday",
}

result = []
for row in query:
    result.append(
        {"collected": row["collected"], "day": day_name[row["day_of_week"]]}
    )