sqlalchemy - How to group and counget last 12 month data from table

Question

I have a table which contains user, category and register_date, I have a requirement to get count of user group by month and category for last 12 months.

user    register_date   category
a   1/7/2020    1
b   2/8/2020    1
c   1/9/2020    1
d   2/10/2020   2
e   1/11/2020   2
f   2/12/2020   2
g   2/13/2020   3
h   1/14/2020   3
e   1/11/2020   1
f   2/12/2020   1
g   2/13/2020   2
h   1/14/2020   3
e   1/11/2020   3
f   2/12/2020   1

I tried something like this. I literally confused with code...

 @classmethod
def cont_user(cls):
    return cls.query(cls.user.count(), ).group_by(extract('year', cls.registerd_date),
         extract('month', cls.registered_date)).first(12)

this how i achive it in MS SQL --> but requirement is to get in mysql with flask-sqlalchemy (This is only to give idea about requirement)

  SELECT count([UserName]) as user, DATENAME(month, [Registered_date]) + '-' +  CAST(YEAR([Registered_date]) AS nvarchar) as my, [category]

  FROM [database].[dbo].[user_table] group by [category], DATENAME(month, [Registered_date]) + '-' +  CAST(YEAR([Registered_date]) AS nvarchar)

I already refereed below links but failed to convert according to my requirement...

Group by & count function in sqlalchemy

MySQL get quantities from last 12 months grouped by month

expecting output something like this.

count   month-year  category
10  Jan-19  1
15  Jan-19  2
20  Jan-19  3
12  Feb-19  1
14  Feb-19  2
19  Feb-19  3
9   Mar-19  1
19  Mar-19  2
24  Mar-19  3

Instead of `first(12)` use `limit(12).all()`. – Ilja Everilä Jan 08 '20 at 07:33 — Ilja Everilä, Jan 08 '20 at 07:33

score 0 · Answer 1 · edited Jan 08 '20 at 07:33

0

Utilize sqlalchemy.func to create the native SQL function expressions. YEAR() is func.year, and COUNT is func.count, and so on.

from sqlalchemy import func

cls.query(func.count(cls.user), cls.category)
    .group_by(func.year(cls.registered_date), func.month(cls.registered_date), cls.category)
    .limit(12).all()

edited Jan 08 '20 at 07:33

Ilja Everilä

50,538
7
126
127

answered Jan 07 '20 at 21:54

Hurried-Helpful

1,850
5
15

add context to prevent down-voting and/or bot removal of answer. – ZF007 Jan 07 '20 at 23:17
I am getting error -> TypeError: 'BaseQuery' object is not callable – Praveen Mar 17 '20 at 02:14
what i am missing here...? – Praveen Mar 17 '20 at 02:32

score 0 · Answer 2 · answered Mar 17 '20 at 03:02

when we use class method, need to use with_entities.

thanks for the help....

after some experiment, below code worked for me.

added with_entities. also included month and year to match my output at the top.

from sqlalchemy import func

cls.query.with_entities(func.count(cls.user), cls.category, func.year(cls.registered_date), func.month(cls.registered_date))
    .group_by(func.year(cls.registered_date), func.month(cls.registered_date), cls.category)
    .limit(12).all()

sqlalchemy - How to group and counget last 12 month data from table

2 Answers2