SQLAlchemy Query with multiple filtered aggregates

Question

Say we have a mysql pivot query (taken from an answer to pivto table question MySQL pivot table) with inner join.

select projects.org,  SUM(ivt_incidents.severity = 'SEV3') as 
sev3_count, 
SUM(ivt_incidents.severity = 'SEV2') as sev2_count, 
SUM(ivt_incidents.severity = 'SEV1') as sev1_count, 
SUM(ivt_incidents.severity = 'SEV0') as sev0_count, 
SUM(ivt_incidents.severity like '%SEV%') as total_count  from 
ivt_incidents inner join projects on ivt_incidents.jira_key = 
projects.jira_key group by projects.org order by total_count;

Looking for help in building sqlalchemy query:

response = db.session.query(
        func.sum(Incidents.severity).filter(Incidents.severity == 'SEV3'),
        Projects.org).\
    join(Projects, Projects.jira_key == Incidents.jira_key).\
    group_by(Projects.org)

If I run it, it will accept the query, however if I call

response.all()

ProgrammingError: (mysql.connector.errors.ProgrammingError) 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(WHERE ivt_incidents.severity = 'SEV3') AS anon_1, projects.org AS projects_org ' at line 1 [SQL: u'SELECT sum(ivt_incidents.severity) FILTER (WHERE ivt_incidents.severity = %(severity_1)s) AS anon_1, projects.org AS projects_org \nFROM ivt_incidents INNER JOIN projects ON projects.jira_key = ivt_incidents.jira_key GROUP BY projects.org'] [parameters: {u'severity_1': 'SEV3'}] (Background on this error at: http://sqlalche.me/e/f405)

db.session.query(func.count(Incidents.severity),Incidents.severity, Projects.org).filter(Incidents.severity.in_(['SEV2','SEV3','SEV1','SEV0',''])).join(Projects, Projects.jira_key == Incidents.jira_key).group_by(Projects.org, Incidents.severity) Gives me the results I want. — osse, Jul 09 '18 at 13:21

Ilja Everilä · Answer 1 · 2018-07-10T10:55:04.920

MySQL does not support^✝️ the SQL standard aggregate FILTER clause you're trying to use in func.sum(...).filter(...), which gets compiled to

SUM(...) FILTER (WHERE ...)

Looking at your original query, you should pass the predicate expression as the argument of SUM:

response = db.session.query(
        func.sum(Incidents.severity == 'SEV3'),
        Projects.org).\
    join(Projects, Projects.jira_key == Incidents.jira_key).\
    group_by(Projects.org)

The reason why you get the error only after calling Query.all() is that that's when the query is actually emitted to the database. Before that you've just built a Query object.

^✝️: There's a plugin that adds FILTER support to MySQL by rewriting queries.

SQLAlchemy Query with multiple filtered aggregates

1 Answers1