假设我们有一个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)
1条答案
按热度按时间ryevplcw1#
mysql不支持✝️ 尝试在中使用的sql标准聚合筛选器子句
func.sum(...).filter(...)
,编译为查看原始查询时,应将 predicate 表达式作为sum的参数传递:
为什么你只有在打电话后才会出错
Query.all()
这就是查询实际发送到数据库的时间。在那之前你刚刚建立了一个Query
对象。✝️: 有一个插件通过重写查询向mysql添加了过滤器支持。