Python数据统计之禅道BUG统计且自动发送测试报告
项目参考文献：https://testerhome.com/topics/6135
思路：
- 1、读取禅道数据库数据及状态，把各维度统计数据通过dic格式返回
- 2、读取数据通过pyecharts类，生成不同类型的报表HTML放入image_html文件中
- 3、读取image_html文件中本次生成的HTML并生成图片，放入static文件中
- 4、把图片写入HTML文件中，因为邮件正文是HTML格式
- 5、文字及图片HTML文件组合邮件正文，进行发送邮件
- 6、上述功能对外提供服务接口，接口传参为产品ID及项目ID，调取接口即可自动发送测试报告
主要用到pyecharts，os，flask，pymysql，yagmail等模块，目前就是初步的小程序，后续再更新一版有界面，可自行选择产品及项目，查看项目各维度情况报表，另外有些日志和异常处理还没有完善
目录结构：
主要代码：
1、数据库数据读取
import pymysql from auto_report.o_util import spread,list_dic from auto_report.setting import db #建立数据库连接及执行 def connect_db(sql): conn = pymysql.connect( **db,charset='utf8', autocommit=True) cur = conn.cursor() # 建立游标 sql = sql cur.execute(sql) r_result =spread(cur.fetchall()) cur.close() conn.close() return r_result #BUG状态统计SQL封装 def sql_pakeage(productid,projectid): bug_sql = "select count(*) from zt_bug where product='%d' and project='%d' and deleted='0'"% ( productid, projectid) resolved_bug_sql = "select count(*) from zt_bug where product = '%d' and project='%d' and deleted = '0' and `status` = 'resolved' and resolution <> 'postponed' " % ( productid, projectid) not_resolved_bug_sql = "select count(*) from zt_bug where product = '%d' and project='%d' and deleted = '0' and `status` = 'active' " % ( productid, projectid) postponed_bug_sql = "select count(*) from zt_bug where product = '%d' and project='%d' and deleted = '0' and `status` <> 'closed' and resolution = 'postponed' " % ( productid, projectid) closed_bug_sql = "select count(*) from zt_bug where product='%d'and project='%d' and deleted = '0' and `status` = 'closed' " % ( productid, projectid) return bug_sql,resolved_bug_sql,not_resolved_bug_sql,postponed_bug_sql,closed_bug_sql #总的项目BUG情况统计 def test_project_bug(productid,projectid): #总bug数 all_bug=connect_db(sql_pakeage(productid,projectid)[0]) #已解决bug数 resolved_bug = connect_db(sql_pakeage(productid,projectid)[1]) # 未解决BUG数（当前显示BUG状态为未解决的。包含当前还没被解决的、之前遗留的未解决、以及reopen的BUG（累计数据）） not_resolved_bug = connect_db(sql_pakeage(productid,projectid)[2]) # 延期BUG数 postponed_bug= connect_db( sql_pakeage(productid,projectid)[3]) # 已关闭BUG数 closed_bug = connect_db(sql_pakeage(productid,projectid)[4]) statistics_bug = { "总BUG数":all_bug[0],"已解决BUG": resolved_bug[0], "未解决BUG": not_resolved_bug[0], "已关闭BUG": closed_bug[0], "延期解决BUG": postponed_bug[0]} return statistics_bug #未解决及待验证BUG人均分布 def test_pepole_bug(productid,projectid): #未解决BUG分布sql not_resolved_sql="select assignedTo,count(*) from zt_bug where product = '%d' and project='%d' and deleted = '0' and `status` = 'active' and assignedTo <> 'closed' group by assignedTo"%(productid,projectid) # 未解决BUG分布 not_resolved_bug=connect_db(not_resolved_sql) return list_dic(not_resolved_bug) #未解决BUG严重程度统计 def test_level_bug(productid,projectid): level_bug_sql="select severity,count(*) from zt_bug where product ='%d' and project='%d' and deleted = '0' and `status` = 'active' and assignedTo <> 'closed' group by severity"%(productid,projectid) level_bug=connect_db(level_bug_sql) return list_dic(level_bug) #已解决待验证BUG分布 def test_verify_bug(productid,projectid): #已解决待验证BUG分布sql not_verify_sql="select assignedTo,count(*) from zt_bug where product = '%d' and project='%d' and deleted = '0' and `status` ='resolved' and resolution='fixed' group by assignedTo"%(productid,projectid) # 已解决待验证BUG分布 not_verify_bug = connect_db(not_verify_sql) return list_dic(not_verify_bug) #获取项目名称 def test_projectname(projectid): projectname="select `name` from zt_project where id='%d'"%(projectid) return connect_db(projectname)[0] #验证项目名称及产品名称是否存在及是对应关系 def test_product_verify(productid,projectid): if productid: p_sql = "select project from zt_projectproduct where product=%d" % productid projectlist=connect_db(p_sql) if projectid & projectid in projectlist: return True else: return False else: return False
2、生成报表，报表基类较简单就不贴码了
from auto_report.setting import html_path,ip,report_path import os from auto_report.o_table import charts from auto_report.o_data import test_level_bug,test_project_bug,test_pepole_bug,test_verify_bug b_img=charts() #声明图表类 class create_report: #调取图表方法，生成项目BUG状态统计表生成 def test_project_bug_html(self,productid,projectid,t_time): if test_project_bug(productid,projectid): b_img.p_render("项目BUG状态统计情况表", test_project_bug,'test_project_bug',productid,projectid,t_time) else: return False # 调取图表方法，生成开发人员待解决BUG统计情况表生成 def test_pepole_bug_html(self,productid,projectid,t_time): if test_pepole_bug(productid,projectid): b_img.p_render("项目开发人员待解决BUG统计情况表", test_pepole_bug,'test_pepole_bug',productid,projectid,t_time) else: return False # 调取图表方法，生成测试人员待验证BUG统计情况表生成 def test_verify_bug_html(self,productid,projectid,t_time): if test_verify_bug(productid,projectid): b_img.line_render("项目测试人员待验证BUG统计情况表", test_verify_bug,'test_verify_bug',productid,projectid,t_time) else: return False # 调取图表方法，生成未解决BUG严重程度统计情况表生成 def test_level_bug_html(self,productid,projectid,t_time): if test_level_bug(productid,projectid): b_img.pie_render("项目未解决BUG严重程度统计情况表", test_level_bug,'test_level_bug',productid,projectid,t_time) else: return False #获取HTML文件中文件，且生成图片 def html_image(self,html_file_list): if html_file_list: for filename in html_file_list: self.new_filename=filename.replace('.html','') b_img.p_image(os.path.join(html_path,filename),self.new_filename) else: return False #获取图片文件中的图片，且生成report文件 def img_report(self,img_file_list): if img_file_list: for img in img_file_list: self.f=open(report_path,"a+",encoding='utf-8') html='''<div><img src="%s/static/%s"></div>'''%(ip,img) self.f.write(html) self.f.close() else: return False
3、邮件发送
import yagmail import traceback from auto_report.setting import mail_info,to,cc,log from auto_report.o_data import sql_pakeage,test_projectname,connect_db from auto_report.o_util import read_file #发送邮件 def send_mail(productid,projectid): bug_sql=sql_pakeage(productid,projectid) bug_num=[] for sql in bug_sql: bug_num.append(int(connect_db(sql)[0])) project_name=test_projectname(projectid) subject="%s项目测试报告"%project_name str='''<div style="color:#000; font-size: 14px;font-family: arial;"> <span style="font-family: 微软雅黑, Microsoft YaHei; font-size: 16px;">大家好：</span></div><div style="color:#000; font-size: 14px;font-family: arial;"> <span style="font-family: 微软雅黑, Microsoft YaHei; font-size: 16px;"> %s项目，目前存在%s个BUG，已解决状态%s个BUG，未解决状态%s个BUG，延期处理状态%s个BUG，已关闭状态%s个BUG；请对应开发注意查收及修改自己名下的BUG。</span></div><div style="color:#000; font-size: 14px;font-family: arial;"><br></div><div style="color:#000; font-size: 14px;font-family: arial;"> <span style="font-family: 微软雅黑, Microsoft YaHei; font-size: 16px;">各维度测试情况统计如下图：</span><div><br></div></div>'''%(project_name,bug_num[0],bug_num[1],bug_num[2],bug_num[3],bug_num[4]) file_content=read_file("./report.html") content=str+file_content try: mail = yagmail.SMTP(**mail_info) # 解包方式传入参数 mail.send(to=to, cc=cc, subject=subject, contents=content) # 发送邮件 except Exception as e: log.error("发送邮件出错了，错误信息是：\n%s" % traceback.format_exc()) # 捕获错误信息 else: log.info("发送邮件成功") # 发送成功日志
4、接口服务文件
from auto_report.o_mail import send_mail from auto_report.o_report import create_report from auto_report.setting import html_path,image_path,report_path import os,time from auto_report.o_data import test_product_verify from auto_report.o_util import clear,file_list import flask import json report=create_report() server=flask.Flask(__name__) #当前Python为一个服务 @server.route("/report") #get请求，且连接带参数 def table_data(): productid=int(flask.request.args.get("productid")) #获取参数产品ID projectid = int(flask.request.args.get("projectid")) #获取参数项目ID t_time = str(int(time.time())) #获取当前时间戳，作为本次文件的唯一标识 #判断产品及项目输入是否正确 if test_product_verify(productid, projectid): report.test_project_bug_html(productid, projectid, t_time) #项目BUG状态统计表生成 report.test_level_bug_html(productid, projectid,t_time) #项目BUG待解决BUG严重程度统计表生成 report.test_pepole_bug_html(productid, projectid, t_time) #项目待解决BUG人员分布统计表生成 report.test_verify_bug_html(productid, projectid, t_time) #项目已解决待验证BUG人员分布统计表成 #生成的图表HTML转图片 html_file_list = file_list(html_path,t_time) report.html_image(html_file_list) #图片组成报告 img_file_list = file_list(image_path,t_time) report.img_report(img_file_list) #发送报告 send_mail(productid,projectid) data = {"code": 200, "msg": "发送成功"} #清除本次报告report.html页面产生的数据 clear(report_path) else: data={"code":-2,"msg":"参数错误"} #接口传参错误 return json.dumps(data,ensure_ascii=False) #返回JSON格式 server.run(host="0.0.0.0",port=5000,debug=True) #启动服务命令
运行效果：
备注：忽略内容啊，测试随意写的文字，文字可自行修改，哈哈哈哈
