0

I have an app built using Flask, flask_sqlalchemy and sqlite3 db. Am building the analytics page and I want to count number of records in a table for each month. The table with the records has a column DATETIME. I have some code that generates comparison dates for this month and last month -- below

today_date = datetime.now()
first_month_date = today_date.replace(day=1)
end_last_month = first_month_date - timedelta(days=1)
end_last_month_temp = end_last_month
start_last_month = end_last_month_temp.replace(day=1)

Using sqlalchemy filter function, I can filter records for this month using first month date and for last month using the end & start last month variables.

Based on this, I think it would be possible to write a for loop that finds the next date backwards and queries the db for a count of the records. The loop knows when to stop when the encountered records reach the total row count for that table.

I think this is not an effective solution. Are there any ideas on how to count all records in table according to month in a much cleaner fashion? Maybe a less known function in sqlalchemy or even a lambda function to include in the query?

davidism
  • 121,510
  • 29
  • 395
  • 339
Patrick Mutuku
  • 1,095
  • 15
  • 13
  • 1
    This is basically an SQL problem, and the solution is to use GROUP BY. Start from here: https://stackoverflow.com/questions/1052148/group-by-count-function-in-sqlalchemy – Ilja Everilä Aug 08 '18 at 09:53
  • Seems to be the best way forward. Let me dig into flask_sqlalchemy group by to see if I can get it to group by date – Patrick Mutuku Aug 08 '18 at 10:00
  • I think one of the answers here will do: https://stackoverflow.com/questions/1370997/group-by-year-month-day-in-a-sqlalchemy – Patrick Mutuku Aug 08 '18 at 10:11

0 Answers0