Hello list,
I am refactoring some RAW SQL to use SQL Alchemy ORM Query object
(session.query).
What I have so far is a SQL to reproduce using SQL Alchemy:
My SQL is this:
1. SELECT DATE_FORMAT(DATE, '%Y%m%d') AS `date`,
2. `Hour` AS `hour`,
3. `Minute` AS `minute`, (MAX(TotalMBs)*8/5/60) AS total_mbs,
4. (MAX(TotalMissMBs)*8/5/60) AS total_mbs_miss
5. FROM (SELECT `Date`, `Hour`, `Minute`, SUM(TotalMBs) AS TotalMBs,
6. SUM(TotalMissMBs) AS TotalMissMBs, `ClientID`, `Type`
7. FROM reports.ClientsGlobalCounters
8. WHERE DATE >= :date_from AND DATE <= :date_to
9. <IN() expression here>
10. AND `Type` LIKE :service
11. GROUP BY `Date`, `Hour`, `Minute`, `ClientID`, `Type`)
12. AS t1 GROUP BY <GROUP_BY here> ORDER BY `Date`
And my Python code is this:
1. def select_total_mbps(self, date_from, date_to, service, clients=
None):
2. sub_qs = reports_session.query(
3. (func.max(ClientGlobalCounter) *
4. 8 / 5 / 60).label('total_miss_mbs'),
5. ClientGlobalCounter.client_id,
6. ClientGlobalCounter.type,
7. ClientGlobalCounter.date,
8. ClientGlobalCounter.hour,
9. ClientGlobalCounter.minute,
10. func.sum(ClientGlobalCounter.total_mbs).label('total_mbs'
),
11. func.sum(ClientGlobalCounter.total_miss_mbs).label(
12. 'total_miss_mbs')) \
13. .filter(ClientGlobalCounter.date.between(date_from,
date_to))
14. if clients:
15. sub_qs = sub_qs.filter(ClientGlobalCounter.client_id.in_(
clients))
16. sub_qs = sub_qs.filter(ClientGlobalCounter.type.like(service)
) \
17. .group_by(ClientGlobalCounter.date,
18. ClientGlobalCounter.hour,
19. ClientGlobalCounter.minute,
20. ClientGlobalCounter.client_id,
21. ClientGlobalCounter.type)
22.
23. qs = reports_session.query(
24. ClientGlobalCounter.client_id,
25. ClientGlobalCounter.type,
26. func.date_format(ClientGlobalCounter.date, 'Ymd'),
27. ClientGlobalCounter.hour,
28. ClientGlobalCounter.minute,
29. (func.max(ClientGlobalCounter.total_mbs) * 8 / 5 / 60).
label('total_mbs'),
30. sub_qs)
31. qs = qs.group_by(
32. *ClientGlobalCounterAPI.date_group(ClientGlobalCounter,
33. date_from,
34. date_to))
35.
36. return [d.__dict__ for d in qs.all()]
I am not understanding the docs because they only talk about a subselect
inside the WHERE. What I am doing here is retrieving a MAX(field) from a
SELECT.
There is a related problem
here: https://groups.google.com/forum/#!topic/sqlalchemy/LBEyRe3w-8Q
Can someone guide me to the right direction? Thanks!
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
