How to convert this SQL query counting months and group by the month to sqlalchemy?

Question

I'm trying to use sqlalchemy with postgresql to get data.

and I'd like to use this query

SELECT MONTH(date_field1), COUNT(*)
FROM table1
GROUP BY MONTH(date_field1)

so I can get the list of months and its count like below

month | count
1 | 3521
2 | 5222
3 | 1122
4 | 559
5 | 1664
6 | 3521
7 | 5222
8 | 1122
9 | 559
10 | 1664
11 | 559
12 | 1664

Please post the SQL-alchemy model of your table. Also please check [this question](https://stackoverflow.com/questions/1052148/group-by-count-function-in-sqlalchemy), it's in the same scope as yours — Joost, Sep 18 '19 at 09:00

score 2 · Accepted Answer · answered Sep 18 '19 at 18:51

Sqlalchemy has 2 different mode: ORM and core

Assuming I have a table with a 'created' column being a date, you can achieve what you want with the following:

Using the ORM mode:

from sqlalchemy import func

qry = session.query(func.month(Table.created), func.count(Table.created)).group_by(Table.created)

With core:

from sqlalchemy import select 
from sqlalchemy import func

qry = select([func.month(tbl.c.created), func.count(tbl.c.created)]).group_by(tbl.c.created)

Thanks. That's what i wanted to know about sqlalchemy. – Eric Lee Sep 20 '19 at 11:20 — Eric Lee, Sep 20 '19 at 11:20

How to convert this SQL query counting months and group by the month to sqlalchemy?

1 Answers1