不太确定这是怎么回事。
表格如下所示:
class LogTable(Base):
__tablename__ = 'log_table'
id = Column(INTEGER(11), primary_key=True)
version = Column(VARCHAR(100))
status = Column(VARCHAR(20))
“漂亮的” SQL Alch查询:
query = session.query(LogTable.version,
label('total_jobs', func.count(LogTable.version)),
label('success_jobs', func.sum(LogTable.status == 'SUCCESS')),
label('fail_jobs', func.sum(LogTable.status == 'FAILURE'))
).group_by(JobLog.version)
调用“ .all()”方法之前的查询值。()
print(query)
SELECT log_table.version AS log_table_version,
count(log_table.version) AS total_jobs,
sum(log_table.status = %(status_1)s) AS success_jobs,
sum(log_table.status = %(status_2)s) AS fail_jobs
FROM job_logs GROUP BY log_table.version
查询应返回的内容(并假设您使用正确的值填写%(status_)s,SQL控制台仍会返回该查询。):
version, total_jobs, success_jobs, fail_jobs
0421f178c39df89890f97663509ce2a931a44fba,206,181,25
07e3ee0af26603c9a2543d1644742abcde664391,208,187,21
08eaf7ec41c022d77d04d108d3844fca2fa426a0,78,53,25
0cce212ddf351acfbdfae795ce0c5402e1a24540,484,314,170
15223fb4dcc4312bceac75755662a7d2eeb4fbb3,2345,1463,882
1940ad381c10fd3e7fce3e9f6e13210807e95eb8,208,164,44
由于某种原因,SQL alch返回什么:
print(query.all())
[('0421f178c39df89890f97663509ce2a931a44fba', 206, True, True),
('07e3ee0af26603c9a2543d1644742abcde664391', 208, True, True),
('08eaf7ec41c022d77d04d108d3844fca2fa426a0', 78, True, True),
('0cce212ddf351acfbdfae795ce0c5402e1a24540', 484, True, True),
('15223fb4dcc4312bceac75755662a7d2eeb4fbb3', 2345, True, True),]
在#sqlalchemy freenode中经过一段时间后。通知我,因为我使用布尔作为参数,所以需要覆盖类型。
标签代码位置的更改可以忽略。
这是有效的解决方案。
from sqlalchemy.types import Integer
query = session.query(LogTable.version,
func.count(LogTable.version).label('total_jobs'),
func.sum(LogTable.status == 'SUCCESS', type_=Integer).label('success_jobs'),
func.sum(LogTable.status == 'FAILURE', type_=Integer).label('fail_jobs'))
.group_by(LogTable.version).order_by(desc(LogTable.create_time))
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句