开源测试工具 基于禅道统计 BUG 趋势图的 SQA 小工具 (python 版) 已开源

飞狐 · October 18, 2016 · Last by 飞狐 replied at January 30, 2019 · 4836 hits
本帖已被设为精华帖!

虽然改用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]
#查找一个星期内新增的BUGopenedDate 例如今天为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状态为未解决的。包含当前还没被解决的、之前遗留的未解决、以及reopenBUG(累计数据))
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]
#查找一个星期内新增的BUGopenedDate 例如今天为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状态为未解决的。包含当前还没被解决的、之前遗留的未解决、以及reopenBUG(累计数据))
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

如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
共收到 21 条回复 时间 点赞
Monkey 将本帖设为了精华贴 18 Oct 15:35

加精理由。支持开源,SQA工具也很实用

#2楼 @monkey monkey给俺加精啦,哈哈,第一篇加精的,多谢

#3楼 @hu_qingen 必须支持的

楼主来的太及时了,我正好需要经常统计这个数据给技术总监看。

支持开源,支持开园发展

#5楼 @JasonChiang 哈哈,你试试看,因为我这段时间太忙了,没时间仔细的说明,不过该有的注释都有,你看看

#7楼 @hu_qingen 我在看,不过我现在还不会用,老报错。

#8楼 @JasonChiang 开发工具建议用pycharm, python版本2.7 ,然后根据错误提示下载对应的 插件,问题应该不大

不能Clone,亲

#10楼 @tuxiaotu

运行最后1行的clone命令会出现权限不足的错误提示;
尝试以下命令: git clone https://github.com/tigerge000/SQA_BUG_Count.git

这个是不是最长只能统计1个月的啊?

飞狐 #13 · November 07, 2016 作者

#12楼 @JasonChiang 不止,可以统计任意日期开始截止当前时间

—— 来自TesterHome官方 安卓客户端

顶起来,这个不错。可以

@hu_qingen 你好,按照操作配置后,表格生成了,但是表里面没有数据,CountBUGMethod.py、config.py、op_mysql.py处的数据库都配了。。 请问问题大概出在哪😓 另禅道里面有前一个星期BUG数据。

飞狐 #16 · November 24, 2016 作者

#15楼 @lancelot 你可以单测下数据库语句,是不是有数据返回,一般你这情况,应该是没有数据库记录返回,你看下你对应项目在数据库表里的值是多少

—— 来自TesterHome官方 安卓客户端

@hu_qingen 谢谢,昨天再研究了下代码,现在已经有数据了😁

我想使用你的这个SQA程序,配置后报错这个:
File "C:\Users\Administrator\Desktop\ChanDao\SQA_BUG_Count-master\SQA\WeeklyBugCount.py", line 95, in CountBUGAsWeeklyForHuaLa
buname = cn.huala_week
AttributeError: 'config' object has no attribute 'huala_week'

飞狐 #19 · December 27, 2016 作者

#18楼 @heyt 抱歉,因为我这边数据处理不干净,你看下提示 config属性中没有huala_week,因为已经变成了hengha_week,你这边改下

—— 来自TesterHome官方 安卓客户端

可惜,公司的禅道是收费的高级版,没有连接数据库的权限,做不了

我在Pycharm中运行,各种报错,最主要的是ImportError: No module named PyMySQL ,这个在moudle目前工具中已经没有相应下载的版本了,楼主大大能给一份能运行的吗?谢谢
系统:win10或者Linux python版本:2.7.10

飞狐 #22 · January 30, 2019 作者
VogAnly 回复

已经不维护这块了,建议真的需要,或者自己替换成现有流行的mysql库,或自己写下sql,然后用任意代码编写搞定即可

需要 Sign In 后方可回复, 如果你还没有账号请点击这里 Sign Up