Python SQLalchemy CASE使用之分组累加统计

背景

    通过JenkinsAPI将Jenkins数据入库CMDB， 来完成开发能效数据的统计展示。通过Jenkins项目构建结果进行分组统计， 统计构建健康度： "每个项目构建的SUCCESS/项目总构建数"。

一、表结构

CREATE TABLE `tbl_jenkins_build_test` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Build ID',
`name` varchar(32) DEFAULT NULL COMMENT '项目名称 项目名称',
`result` varchar(32) DEFAULT NULL COMMENT '构建结果',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3319 DEFAULT CHARSET=utf8 COMMENT='Jenkins构建记录表';

二、数据

INSERT INTO `devops`.`tbl_jenkins_build_test`(`id`, `name`, `result`) VALUES (1, 'test-Agent', 'SUCCESS');
INSERT INTO `devops`.`tbl_jenkins_build_test`(`id`, `name`, `result`) VALUES (2, 'test-Agent', 'SUCCESS');
INSERT INTO `devops`.`tbl_jenkins_build_test`(`id`, `name`, `result`) VALUES (3, 'test-Agent', 'SUCCESS');
INSERT INTO `devops`.`tbl_jenkins_build_test`(`id`, `name`, `result`) VALUES (4, 'test-Agent', 'SUCCESS');
INSERT INTO `devops`.`tbl_jenkins_build_test`(`id`, `name`, `result`) VALUES (5, 'test-Agent', 'SUCCESS');
INSERT INTO `devops`.`tbl_jenkins_build_test`(`id`, `name`, `result`) VALUES (6, 'test-Agent', 'SUCCESS');
INSERT INTO `devops`.`tbl_jenkins_build_test`(`id`, `name`, `result`) VALUES (7, 'test-Agent', 'SUCCESS');
INSERT INTO `devops`.`tbl_jenkins_build_test`(`id`, `name`, `result`) VALUES (8, 'test-Master', 'SUCCESS');
INSERT INTO `devops`.`tbl_jenkins_build_test`(`id`, `name`, `result`) VALUES (9, 'test-Master', 'SUCCESS');
INSERT INTO `devops`.`tbl_jenkins_build_test`(`id`, `name`, `result`) VALUES (10, 'dev-Master', 'SUCCESS');
INSERT INTO `devops`.`tbl_jenkins_build_test`(`id`, `name`, `result`) VALUES (11, 'dev-Master', 'SUCCESS');
INSERT INTO `devops`.`tbl_jenkins_build_test`(`id`, `name`, `result`) VALUES (12, 'dev-Master', 'FAILURE');
INSERT INTO `devops`.`tbl_jenkins_build_test`(`id`, `name`, `result`) VALUES (13, 'dev-Master', 'SUCCESS');
INSERT INTO `devops`.`tbl_jenkins_build_test`(`id`, `name`, `result`) VALUES (14, 'dev-Master', 'FAILURE');
INSERT INTO `devops`.`tbl_jenkins_build_test`(`id`, `name`, `result`) VALUES (15, 'dev-Master', 'SUCCESS');
INSERT INTO `devops`.`tbl_jenkins_build_test`(`id`, `name`, `result`) VALUES (16, 'dev-Master', 'SUCCESS');
INSERT INTO `devops`.`tbl_jenkins_build_test`(`id`, `name`, `result`) VALUES (17, 'dev-Master', 'SUCCESS');
INSERT INTO `devops`.`tbl_jenkins_build_test`(`id`, `name`, `result`) VALUES (18, 'dev-Master', 'SUCCESS');

三、SQL代码

select name, sum(case when result='SUCCESS' then 1 else 0 end)/count(result) from tbl_jenkins_build_test GROUP BY name

四、Python化

4.1 数据模型

class TblJenkinsBuild(Base):
    __tablename__ = 'tbl_jenkins_build_test'
    __table_args__ = {'comment': 'Jenkins构建记录表'}

    id = Column(Integer, primary_key=True, comment='Build ID')
    name = Column(String(32), comment='项目名称 项目名称')
    result = Column(String(32), comment='构建结果')

4.2 查询代码

from app.models.jenkins import TblJenkinsBuild
from sqlalchemy import case, func

db.session.query(
TblJenkinsBuild.name,
func.sum(case(whens=((TblJenkinsBuild.result == 'SUCCESS', 1),), else_=0)) / func.count(TblJenkinsBuild.result)
).group_by(TblJenkinsBuild.name).all()  # 是SUCCESS则+1， 否则+0

五、参考资料

1. SQLAlchemy CASE使用

https://docs.sqlalchemy.org/en/14/core/sqlelement.html#sqlalchemy.sql.expression.case

2. CASE WHEN with ORM (SQLalchemy)

https://stackoverflow.com/questions/11258770/case-when-with-orm-sqlalchemy

3. Python sqlalchemy 模块，case() 实例源码
http://codingdict.com/sources/py/sqlalchemy/5994.html