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='''

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='''

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) #启动服务命令

运行效果：

备注：忽略内容啊，测试随意写的文字，文字可自行修改，哈哈哈哈