How to group database records with Flask/SqlAlchemy/SQLite for each day of the last 7 days?

Question

I want to get a user's logs from a database that we use to record call logs and count how many logs they entered each day. That way I can do some nice charting using chart.js

What would be the way to head back for the last 7 days and get a single user's log count for each day?

The table looks sort of like this (simplified):

class Log(db.Model):
    __tablename__ = 'log'
    id = db.Column(db.Integer, primary_key=True)
    logType = db.Column(db.String(100), nullable=False)
    title = db.Column(db.String(100), nullable=False)
    date_posted = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    content = db.Column(db.Text, nullable=False)
    org_id = db.Column(db.Integer, db.ForeignKey('org.id'), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)

I want to compile get this kind of data:

mon:12
tue:2
wed:8
thu:15
fri:13
sat:12
sun:11

I'm totally stumped as to how to go about this.

Thanks a tonne..

These'll get you started: https://stackoverflow.com/questions/1052148/group-by-count-function-in-sqlalchemy, https://stackoverflow.com/questions/2113448/group-by-hour-in-sqlalchemy — Ilja Everilä, Sep 11 '19 at 11:10

Halvor Holsten Strand · Answer 1 · 2019-09-12T07:19:49.237

1

For SQLite you could try this, which gives you weekday (0-6) and the number of results:

import datetime
from sqlalchemy import func


result = db.session.query(func.strftime('%w', Log.date_posted).cast(db.Integer).label('weekday'), func.count(Log.date_posted).label('count'))\
    .filter(Log.date_posted > datetime.date.today() - datetime.timedelta(days=6))\
    .group_by(func.DATE(Log.date_posted)).order_by('weekday').all()

for weekday, count in result:
    print(weekday, count)

edited Sep 12 '19 at 07:19

answered Sep 11 '19 at 11:10

Halvor Holsten Strand

19,829
17
83
99

Thanks for your reply, I got this error `NameError: name 'Integer' is not defined` when trying but I can't find why that would be – robster Sep 12 '19 at 02:53
I got it! result = Log.query.with_entities(Log.date_posted, func.count(Log.date_posted)) \ .filter(Log.user_id == "2") \ .filter(Log.date_posted >= now - timedelta(days=6)) \ .group_by(extract('day', Log.date_posted)).all() – robster Sep 12 '19 at 05:36
@robster good! I think the `Integer` problem is you are probably using `db.Integer`. – Halvor Holsten Strand Sep 12 '19 at 07:00

score 0 · Answer 2 · answered Sep 12 '19 at 05:36

This code works for anyone in the future. note the group by / extract at the end.

result = Log.query.with_entities(Log.date_posted, func.count(Log.date_posted)) \
                  .filter(Log.user_id == "2") \
                  .filter(Log.date_posted >= now - timedelta(days=6)) \
                  .group_by(extract('day', Log.date_posted)).all()

How to group database records with Flask/SqlAlchemy/SQLite for each day of the last 7 days?

2 Answers2