sqlalchemy查询与多个过滤聚合

6jygbczu  于 2021-06-21  发布在  Mysql
关注(0)|答案(1)|浏览(383)

假设我们有一个mysql pivot查询(取自pivto table question mysql pivot table的答案),带有内部连接。

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;

正在查找有关构建sqlalchemy查询的帮助:

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)

如果我运行它,它将接受查询,但是如果我调用

response.all()

programmingerror:(mysql.connector.errors.programmingerror)1064(42000):您的sql语法有错误;检查与您的mysql服务器版本相对应的手册,以获得正确的语法,将near'(其中ivt\u incidents.severity='sev3')用作anon\u 1,projects.org用作projects\u org',在第1行[sql:u'select sum(ivt\u incidents.severity)filter(其中ivt\u incidents.severity=%(severity\u 1)s)用作anon\u 1,projects.org as projects\u org\n from ivt\u incidents inner join projects on projects.jira\u key=ivt\u incidents.jira\u key group by projects.org'][参数:{u'severity\u 1':'sev3'}](此错误的背景信息:http://sqlalche.me/e/f405)

ryevplcw

ryevplcw1#

mysql不支持✝️ 尝试在中使用的sql标准聚合筛选器子句 func.sum(...).filter(...) ,编译为

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

查看原始查询时,应将 predicate 表达式作为sum的参数传递:

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

为什么你只有在打电话后才会出错 Query.all() 这就是查询实际发送到数据库的时间。在那之前你刚刚建立了一个 Query 对象。
✝️: 有一个插件通过重写查询向mysql添加了过滤器支持。

相关问题