基于禅道统计BUG趋势图的 SQA 小工具 (python 版)
本帖已被设为精华帖！,
虽然改用java做测试开发有段时间了，但之前在使用python过程中，还是写了不少小工具；
在此跟大家分享下 基于禅道统计BUG趋势图的SQA小工具(python版)
—–背景—–
当时技术总监要求我这边按周定期输出 每条产品的BUG情况，以此来反馈 开发解决问题、测试跟进问题的情况；当时公司内部有5大产品线，每条产品线至少3个以上产品在开发；如果按照手工方案一个一个来进行数据统计，那简直就是傻子行为了。以此我这边开始着手准备编写一个小工具，最终达到目的：自动定期发送统计报告，报告维度（数据+图表展示）
—--技术选型—–
python + excel + jenkins
——-达到目标————–
1.导出当前一星期的BUG统计数据
2.导出指定时间–当前时间的BUG统计数据
3.统计维度（新增、已解决、已关闭、未解决(累计)、延期解决(累计)）
4.领导可通过报表直观的看到产品线情况
说明下各大概念指标，与常规的统计指标有一定区别：
按周统计:
1、新增，新增日期为本周内的（包括本周内被解决或关闭的BUG）
2、已解决，解决日期为本周内的。被开发设定为已解决的。其中可能有部分是上周遗留下来的，体现了开发在本周的变化情况（包括设计如此、重复BUG、外部原因、无法重现、不予解决、转为需求），不包含延期处理
3、已关闭，关闭日期为本周内的。是测试验证过，确实已经解决的，包括其中有的是上周遗留下来的
4、未解决，当前显示BUG状态为未解决的。包含当前还没被解决的、之前遗留的未解决、以及reopen的BUG（累计数据）
5、延期解决，当前显示BUG状态为 延期处理的。BUG状态中新增一个延期解决(累计数据)
最终实现效果:
核心代码:
'''
创建图表图形方法--按周
'''
def chart_series_week(self,sheet_name,type,row_len,col_len):
chart = self.workbook.add_chart({'type': '%s'%(type)})
if type == "pie":
for j in range(2,col_len-2):
chart.add_series({
'name': ['%s'%(sheet_name), 0, j],
'categories': ['%s'%(sheet_name), 1, 0, row_len, 0],
'values': ['%s'%(sheet_name), 1, j, row_len,j],
'data_labels': {'percentage': 1}, #百分比显示数值
})
else:
for j in range(2,col_len-2):
chart.add_series({
'name': ['%s'%(sheet_name), 0, j],
'categories': ['%s'%(sheet_name), 1, 0, row_len, 0],
'values': ['%s'%(sheet_name), 1, j, row_len,j],#显示数据
'data_labels': {'value': 1},#显示数据表
})
#添加数据表
chart.set_table()
# 设置图表风格.
chart.set_style(18)
#设置图表大小
chart.set_size({'width': 650, 'height': 450})
return chart
'''
创建图表图形方法-按产品或项目
'''
def chart_series_all(self,sheet_name,type,row_len,col_len):
chart = self.workbook.add_chart({'type': '%s'%(type)})
if type == "pie":
for j in range(col_len-4,col_len):
chart.add_series({
'name': ['%s'%(sheet_name), 0, j],
'categories': ['%s'%(sheet_name), 1, 0, row_len, 0],
'values': ['%s'%(sheet_name), 1, j, row_len,j],
'data_labels': {'percentage': 1}, #百分比显示数值
})
else:
for j in range(col_len-4,col_len):
chart.add_series({
'name': ['%s'%(sheet_name), 0, j],
'categories': ['%s'%(sheet_name), 1, 0, row_len, 0],
'values': ['%s'%(sheet_name), 1, j, row_len,j],#显示数据
'data_labels': {'value': 1},#显示数据表
})
#添加数据表
chart.set_table()
# 设置图表风格.
chart.set_style(18)
#设置图表大小
chart.set_size({'width': 650, 'height': 450})
return chart
'''
柱形图
哼哈BUG按周统计图
@sheet_name: Sheet页名称
@sql_date: 2016-01-04 00:00:00格式
例:今天为2016-01-04 00:00:00，输入这个时间后，会自动查询2015-12-28 00:00:00---2016-01-03 23:59:59时间段内BUG
'''
def CountBUGAsWeeklyForHuaLa(self,sheet_name,sql_date):
#计算开始时间和结束时间
dateResult = op_date.week_get(sql_date)
start_date = dateResult[0][0]
end_date = dateResult[1][0]
workbook = self.workbook
worksheet = self.workbook.add_worksheet(name=sheet_name)
bold = workbook.add_format({'bold': 1})
# 定义数据表头列表
# title = [u'按周统计图', u'统计日期',u'新增', u'已解决',u'已关闭',u'未解决(累计)',u'延期解决(累计)',u'已关闭(累计)',u'总BUG数']
# buname = [u"哼哈微信端",u"哼哈商户端(android)",u"哼哈商户端(iOS)",u"哼哈后台",u"哼哈生活(产品)"]
title = cn.bugStatusList
buname = cn.huala_week
#获取row长度
row_len = len(buname)
#获取col长度
col_len = len(title)
#定义数据列表
#花啦微信端统计所有BUG
data = []
#添加哼哈微信用户端BUG数据
result1 = cn.BugCountByProject(cn.hl_pjct[0],sql_date)
data.append(result1)
#添加哼哈商户端(android)BUG数据
result2 = cn.BugCountByProject(cn.hl_pjct[1],sql_date)
data.append(result2)
#添加哼哈商户端(iOS)BUG数据
result3 = cn.BugCountByProject(cn.hl_pjct[2],sql_date)
data.append(result3)
#添加哼哈运营后台BUG数据
result4 = cn.BugCountByProject(cn.hl_pjct[3],sql_date)
data.append(result4)
#添加哼哈生活BUG数据
result5 = cn.BugCountByProduct(cn.hl_pdct[0],sql_date)
data.append(result5)
format_title=workbook.add_format() #定义format_title格式对象
format_title.set_border(1) #定义format_title对象单元格边框加粗(1像素)的格式
format_title.set_bg_color('#cccccc') #定义format_title对象单元格背景颜色为
#'#cccccc'的格式
format_title.set_align('center') #定义format_title对象单元格居中对齐的格式
format_title.set_bold() #定义format_title对象单元格内容加粗的格式
worksheet.write_row('A1', title, format_title)
worksheet.write_column('A2', buname,bold)
for i in range(2,row_len+2):
worksheet.write_row('B%d'%(i),data[i-2])
#创建一个图表，类型是column(柱形图)
chart = self.chart_series_week(sheet_name,"column",row_len,col_len)
# Add a chart title and some axis labels.
chart.set_title ({'name': u'按周统计BUG %s--%s'%(start_date,end_date)})
chart.set_x_axis({'name': u'BUG状态'})
chart.set_y_axis({'name': u'BUG数'})
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('A9', chart, {'x_offset': 25, 'y_offset': 10})
#创建一个图表，类型是column(柱形图)
chart1 = self.chart_series_all(sheet_name,"column",row_len,col_len)
# Add a chart title and some axis labels.
chart1.set_title ({'name': u'按产品或项目统计总BUG %s'%(end_date)})
chart1.set_x_axis({'name': u'BUG状态'})
chart1.set_y_axis({'name': u'BUG数'})
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('L9', chart1, {'x_offset': 25, 'y_offset': 10})
时间处理：
#coding = utf-8
import datetime
#a = datetime.datetime.now()
'''
Create by 古月随笔
'''
class op_date(object):
def day_get(self,d):
if type(d).__name__ == "str":
d = datetime.datetime.strptime(d,'%Y-%m-%d %H:%M:%S')
oneday = datetime.timedelta(days=1)
day = d - oneday
date_from = datetime.datetime(day.year, day.month, day.day, 0, 0, 0)
date_to = datetime.datetime(day.year, day.month, day.day, 23, 59, 59)
print '---'.join([str(date_from), str(date_to)])
def week_get(self,d):
if type(d).__name__ == "str":
d = datetime.datetime.strptime(d,'%Y-%m-%d %H:%M:%S')
dayscount = datetime.timedelta(days=d.isoweekday())
dayto = d - dayscount
sixdays = datetime.timedelta(days=6)
dayfrom = dayto - sixdays
date_from = datetime.datetime(dayfrom.year, dayfrom.month, dayfrom.day, 0, 0, 0)
date_to = datetime.datetime(dayto.year, dayto.month, dayto.day, 23, 59, 59)
datelist=[[str(date_from)],[str(date_to)]]
# print '---'.join([str(date_from), str(date_to)])
return datelist
def multi_week_get(self,d,num):
if type(d).__name__ == "str":
d = datetime.datetime.strptime(d,'%Y-%m-%d %H:%M:%S')
date_num = []
# date_num = [date_to1]
for i in range(num-1,0,-1):
dayscount = datetime.timedelta(days=d.isoweekday())
dayto = d - dayscount
sixdays = datetime.timedelta(days=6*i)
dayfrom = dayto - sixdays
date_from = str(datetime.datetime(dayfrom.year, dayfrom.month, dayfrom.day, 10, 0, 0))
date_num.append(date_from)
dayscount1 = datetime.timedelta(days=d.isoweekday())
onedays = datetime.timedelta(days=1)
dayto1 = d - dayscount1 + onedays
date_to1 = str(datetime.datetime(dayto1.year, dayto1.month, dayto1.day, 10, 0, 0))
date_num.append(date_to1)
return date_num
def month_get(self,d):
if type(d).__name__ == "str":
d = datetime.datetime.strptime(d,'%Y-%m-%d %H:%M:%S')
dayscount = datetime.timedelta(days=d.day)
dayto = d - dayscount
date_from = datetime.datetime(dayto.year, dayto.month, 1, 0, 0, 0)
date_to = datetime.datetime(dayto.year, dayto.month, dayto.day, 23, 59, 59)
# print '---'.join([str(date_from), str(date_to)])
datelist=[[str(date_from)],[str(date_to)]]
return datelist
if __name__ == "__main__":
op_date = op_date()
# print op_date.week_get("2016-01-09 23:00:00")[0]
#print op_date.week_get("2016-01-3 23:00:00")[1]
print op_date.multi_week_get("2015-12-23 23:00:00",4)
统计BUG的SQL语句
#coding=utf-8
from op_date import *
from op_mysql import *
import sys
reload(sys)
sys.setdefaultencoding('utf-8')
'''
Create by 古月随笔
'''
class config(object):
def __init__(self):
self.op_date = op_date()
# self.ms = op_mysql(host="127.0.0.1",user="root",pwd="",db="zentao")
self.ms = op_mysql(host="192.168.200.60",user="root",pwd="123456",db="zentao")
#BUG状态分类:
bugStatusList = [u'按周统计图', u'统计日期',u'新增', u'已解决',u'已关闭',u'未解决(累计)',u'延期解决(累计)',u'已关闭(累计)',u'总BUG数']
#xxxx产品&项目
hengha = [[u"哼哈微信端"],[u"哼哈商户端(android)"],[u"哼哈商户端(iOS)"],[u"哼哈后台"],[u"哼哈生活(产品)"]]
#花xxxxx产品&项目
hengha_week = [u"哼哈微信端",u"哼哈商户端(android)",u"哼哈商户端(iOS)",u"哼哈后台",u"哼哈生活(产品)"]
#ERP
erpyuvmp = [[u"ERP2.0(产品)"],[u"CRM(产品)"],[u"VMP(产品)"]]
#ERP
erpyuvmp_week = [u"ERP2.0(产品)",u"CRM(产品)",u"VMP(产品)"]
def BugCountByProject(self,projectNo,sql_date):
ms = self.ms
data = []
projectNo = int(projectNo)
date_result = self.op_date.week_get(sql_date)
start_date = date_result[0][0]
end_date = date_result[1][0]
#查找一个星期内新增的BUG数openedDate 例如今天为2016-01-04 00:00:00，输入这个时间后，会自动查询2015-12-28 00:00:00---2016-01-03 23:59:59时间段内BUG
AllNewBugCount_OneWeek = "select count(*) from zt_bug where project = '%d' and deleted = '0' and openedDate >= '%s' and openedDate <= '%s'"%(projectNo,start_date,end_date)
#查找一个星期内已解决的BUG数(以最近的星期天为准，计算星期一到星期天,包含本周 解决到关闭的BUG) resolvedDate
AllResolvedBugCount_OneWeek = "select count(*) from zt_bug where project = '%d' and deleted = '0' and `status` <> 'active' and resolution <> 'postponed' and resolvedDate >= '%s' and resolvedDate <= '%s'"%(projectNo,start_date,end_date)
#查找所有未解决BUG数(以最近的星期天为准，计算星期一到星期天)（当前显示BUG状态为未解决的。包含当前还没被解决的、之前遗留的未解决、以及reopen的BUG（累计数据））
AllNotResolvedBugCount = "select count(*) from zt_bug where project = '%d' and deleted = '0' and `status` = 'active' and openedDate <= '%s'"%(projectNo,end_date)
#查找用户所有延期解决的问题
AllPostponedBugCount = "select count(*) from zt_bug where project = '%d' and deleted = '0' and `status` <> 'closed' and resolution = 'postponed' and resolvedDate <= '%s'"%(projectNo,end_date)
#查找 一个星期内已关闭的BUG数(以最近的星期天为准，计算星期一到星期天) closedDate
AllClosedBugCount_OneWeek = "select count(*) from zt_bug where project = '%d' and deleted = '0' and `status` = 'closed' and closedDate >= '%s' and closedDate <= '%s'"%(projectNo,start_date,end_date)
#查找 已关闭BUG数(累计)
AllClosedBugCount = "select count(*) from zt_bug where project = '%d' and deleted = '0' and `status` = 'closed' and closedDate <= '%s'"%(projectNo,end_date)
#查找 总BUG数
AllBugCount = "select count(*) from zt_bug where project = '%d' and deleted = '0' and openedDate <='%s'"%(projectNo,end_date)
#新增
dAllNewBugCount_OneWeek = ms.ExecQuery(AllNewBugCount_OneWeek)[0][0]
#已解决
dAllResolvedBugCount_OneWeek = ms.ExecQuery(AllResolvedBugCount_OneWeek)[0][0]
#已关闭
dAllClosedBugCount_OneWeek = ms.ExecQuery(AllClosedBugCount_OneWeek)[0][0]
#未解决(累计数据)
dAllNotResolvedBugCount = ms.ExecQuery(AllNotResolvedBugCount)[0][0]
#延期解决(累计数据)
dAllPostponedBugCount = ms.ExecQuery(AllPostponedBugCount)[0][0]
#已关闭(累计)
dAllClosedBugCount = ms.ExecQuery(AllClosedBugCount)[0][0]
#总BUG数
dAllBugCount = ms.ExecQuery(AllBugCount)[0][0]
data = ["%s~%s"%(start_date[:-9],end_date[:-9]),dAllNewBugCount_OneWeek,dAllResolvedBugCount_OneWeek,dAllClosedBugCount_OneWeek,dAllNotResolvedBugCount,dAllPostponedBugCount,dAllClosedBugCount,dAllBugCount]
return data
def BugCountByProduct(self,productNo,sql_date):
ms = self.ms
data = []
productNo = int(productNo)
date_result = self.op_date.week_get(sql_date)
start_date = date_result[0][0]
end_date = date_result[1][0]
#查找一个星期内新增的BUG数openedDate 例如今天为2016-01-04 00:00:00，输入这个时间后，会自动查询2015-12-28 00:00:00---2016-01-03 23:59:59时间段内BUG
AllNewBugCount_OneWeek = "select count(*) from zt_bug where product = '%d' and deleted = '0' and openedDate >= '%s' and openedDate <= '%s'"%(productNo,start_date,end_date)
#查找一个星期内已解决的BUG数(以最近的星期天为准，计算星期一到星期天) resolvedDate
AllResolvedBugCount_OneWeek = "select count(*) from zt_bug where product = '%d' and deleted = '0' and `status` <> 'active' and resolution <> 'postponed' and resolvedDate >= '%s' and resolvedDate <= '%s'"%(productNo,start_date,end_date)
#查找 一个星期内已关闭的BUG数(以最近的星期天为准，计算星期一到星期天) closedDate
AllClosedBugCount_OneWeek = "select count(*) from zt_bug where product = '%d' and deleted = '0' and `status` = 'closed' and closedDate >= '%s' and closedDate <= '%s'"%(productNo,start_date,end_date)
#查找所有未解决BUG数(以最近的星期天为准，计算星期一到星期天)（当前显示BUG状态为未解决的。包含当前还没被解决的、之前遗留的未解决、以及reopen的BUG（累计数据））
AllNotResolvedBugCount = "select count(*) from zt_bug where product = '%d' and deleted = '0' and `status` = 'active' and openedDate <= '%s'"%(productNo,end_date)
#查找用户所有延期解决的问题
AllPostponedBugCount = "select count(*) from zt_bug where product = '%d' and deleted = '0' and `status` <> 'closed' and resolution = 'postponed'and resolvedDate <= '%s'"%(productNo,end_date)
#查找 已关闭BUG数(累计)
AllClosedBugCount = "select count(*) from zt_bug where product = '%d' and deleted = '0' and `status` = 'closed' and closedDate <= '%s'"%(productNo,end_date)
#查找 总BUG数
AllBugCount = "select count(*) from zt_bug where product = '%d' and deleted = '0'and openedDate <='%s'"%(productNo,end_date)
#新增
dAllNewBugCount_OneWeek = ms.ExecQuery(AllNewBugCount_OneWeek)[0][0]
#已解决
dAllResolvedBugCount_OneWeek = ms.ExecQuery(AllResolvedBugCount_OneWeek)[0][0]
#已关闭
dAllClosedBugCount_OneWeek = ms.ExecQuery(AllClosedBugCount_OneWeek)[0][0]
#未解决(累计数据)
dAllNotResolvedBugCount = ms.ExecQuery(AllNotResolvedBugCount)[0][0]
#延期解决(累计数据)
dAllPostponedBugCount = ms.ExecQuery(AllPostponedBugCount)[0][0]
#已关闭(累计)
dAllClosedBugCount = ms.ExecQuery(AllClosedBugCount)[0][0]
#总BUG数
dAllBugCount = ms.ExecQuery(AllBugCount)[0][0]
data = ["%s~%s"%(start_date[:-9],end_date[:-9]),dAllNewBugCount_OneWeek,dAllResolvedBugCount_OneWeek,dAllClosedBugCount_OneWeek,dAllNotResolvedBugCount,dAllPostponedBugCount,dAllClosedBugCount,dAllBugCount]
return data
"""
花啦生活:
按project统计
花啦生活-微信用户端：37
花啦生活--商户端:39
花啦生活--运营后台:38
ERP&CRM(Product):
按照产品编号来进行统计
ERP2.0 : 3
CRM:25
VMP :7
"""
'''
花啦生活(project)
'''
#哼哈生活-微信用户端：37
henghawx_pjct = 37
#哼哈生活--商户端(android):39
henghashandroid_pjct = 39
#哼哈生活-商户端（IOS）:64
henghashios_pjct = 64
#哼哈生活--运营后台:38
henghayy_pjct = 38
#哼哈生活 ： 22(产品)
hengha_pdct = 22
hh_pjct = [henghawx_pjct,henghashandroid_pjct,henghashios_pjct,henghayy_pjct]
hh_pdct = [hengha_pdct]
'''
ERP&CRM(Product):
'''
# ERP2.0 : 3
erp_pdct = 3
# CRM:25
crm_pdct = 25
# VMP :7
vmp_pdct = 7
erp_pdct_list = [erp_pdct,crm_pdct,vmp_pdct]
if __name__ == "__main__":
cn = config()
data = []
result1 = cn.BugCountByProject(cn.henghawx_pjct,"2016-01-06 00:00:00")
data.append(result1)
result2 = cn.BugCountByProject(cn.henghash_pjct,"2016-01-06 00:00:00")
data.append(result2)
result3 = cn.BugCountByProject(cn.henghayy_pjct,"2016-01-06 00:00:00")
data.append(result3)
print data
GitHub地址: git@github.com:tigerge000/SQA_BUG_Count.git
* 注：本文来自网络投稿，不代表本站立场，如若侵犯版权，请及时知会删除