def setUp(self):
self.parseyaml = ParseYaml()
self.testdata_path = self.parseyaml.ReadParameter('ImportAddress')
self.parseexcel = ParseExcel(self.testdata_path)
self.pageaction = PageAction()
self.sheetnames = self.parseexcel.wb.sheetnames
self.parameter = CONFIG_PATH + 'Parameter.yaml'
self.CaseNum = 0
# 创建六个字典，分别储存步骤测试结果，用例测试结果，用例测试时间，错误信息，截图信息，步骤测试时间
self.time_dic = {}
self.result_dic = {}
self.error_dic = {}
self.picture_dic = {}
self.caseResult_dic = {}
self.caseTime_dic = {}
self.font = Font(color=None)
def main():
# 新建一个解析excel工具类的实例对象
parseE = ParseExcel()
parseE.loadWorkBook(FILEPATH)
sheetObj = parseE.getSheetByName("API")
# 获取active列的列对象
activeList = parseE.getColumn(sheetObj, API_active)
for idx, cell in enumerate(activeList[1:], 2):
if cell.value == "y":
# 需要执行的接口所在行的行对象
rowObj = parseE.getRow(sheetObj, idx)
apiName = rowObj[API_apiName - 1].value
requestUrl = rowObj[API_requestUrl - 1].value
requestMethed = rowObj[API_requestMethod - 1].value
paramsType = rowObj[API_paramsType - 1].value
apiTestCaseFileName = rowObj[API_apiTestCaseFileName - 1].value
# 下一步需要读取接口用例表，获取接口的测试用例
caseSheelObj = parseE.getSheetByName(apiTestCaseFileName) # obj数据类型
caseActiveObj = parseE.getColumn(caseSheelObj, CASE_active) # tuple 数据类型
for c_idx, c_cell in enumerate(caseActiveObj[1:], 2):
if c_cell.value == "y":
# 说明此case行需要被执行
caseRowObj = parseE.getRow(caseSheelObj, c_idx)
requestData = caseRowObj[CASE_requestData - 1].value
requestData = eval(requestData) if requestData else {}
relyData = caseRowObj[CASE_relyData - 1].value
responseCode = caseRowObj[CASE_responseCode - 1].value
dataStore = caseRowObj[CASE_dataStore - 1].value
checkPoint = caseRowObj[CASE_checkPoint - 1].value
# 接口发送请求之前需要先做依赖数据的处理
if relyData:
requestData = GetRely.get(requestData, eval(relyData))
# 构造接口请求需要的数据
httpC = HttpClient()
response = httpC.request(requestUrl, requestMethed, paramsType, requestData)
http_code = response.status_code
#print(response.status_code)
print("response.json: ",response.json())
if http_code == responseCode:
if dataStore:
RelyDataStore.do(eval(dataStore), apiName, c_idx - 1, requestData, response.json())
else:
print("第%s个接口的第%s条用例不需要依赖数据存储！")
else:
print("第%s个接口的第%s条用例的协议code码【%s】不符合预期值【%s】" % (idx - 1, c_idx - 1, http_code, responseCode))
#下面进行校验点检测功能的实现
if checkPoint:
errorInfo = CheckResult.check(response.json(),eval(checkPoint))
#print("errorInfo: ",errorInfo)
write_result(parseE,caseSheelObj,response.json(),errorInfo,c_idx)
else:
print("第%s个接口的第%s条用例没有设置校验点！"%(idx - 1, c_idx - 1))
else:
print("第%s个接口的第%s条用例被忽略执行！" %(idx - 1, c_idx - 1))
else:
print("第%s个接口被设置忽略执行！" %(idx - 1))
def runScript():
excel = ParseExcel()
excel.loadWorkBook(data_file_path)
sheetObj = excel.getSheetByName("测试用例")
caseActive = excel.getColumn(sheetObj, testCaseIsExecute)
for id, id_c in enumerate(caseActive[1:], 2):
testCaseName = excel.getCellOfValue(sheetObj,
rowNo=id,
colsNo=testCaseDesc)
testCaseSheet = excel.getCellOfValue(sheetObj,
rowNo=id,
colsNo=testCaseSheetName)
if id_c.value == "y":
"需要执行的用例"
info("{name}-->测试用例需要执行".format(name=testCaseName))
caseObj = excel.getSheetByName(testCaseSheet)
caseStepsNum = excel.getRowsNumber(caseObj)
caseSuccessNum = 0
for i in range(2, caseStepsNum + 1):
rowObj = excel.getRow(caseObj, i)
stepDescrible = rowObj[testStepDesc - 1].value
stepKeyword = rowObj[testStepKeyWords - 1].value
stepLocateMode = rowObj[testStepLocateMode - 1].value
stepLocateExp = rowObj[testStepLocateExp - 1].value
stepOperateValue = rowObj[testStepOperateValue - 1].value
# print (stepKeyword,stepLocateMode,stepLocateExp,stepOperateValue)
if stepKeyword is not None and stepLocateMode is None and stepLocateExp is None \
and stepOperateValue is None:
command = "{word}()".format(word=stepKeyword)
elif stepKeyword is not None and stepLocateMode is not None and stepLocateExp is not None \
and stepOperateValue is None:
command = "{word}('{id}','{exp}')".format(
word=stepKeyword, id=stepLocateMode, exp=stepLocateExp)
elif stepKeyword is not None and stepLocateMode is not None and stepLocateExp is not None \
and stepOperateValue is not None:
command = "{word}('{id}','{exp}','{content}')".format(
word=stepKeyword,
id=stepLocateMode,
exp=stepLocateExp,
content=stepOperateValue)
elif stepKeyword is not None and stepLocateMode is None and stepLocateExp is None \
and stepOperateValue is not None:
command = "{word}('{content}')".format(
word=stepKeyword, content=stepOperateValue)
try:
eval(command)
except Exception as e:
info("执行测试步骤失败:{step}".format(step=command))
excel.writeCell(caseObj,
content="fail",
rowNo=i,
colsNo=testStepExecuteResult,
style="red")
excel.writeCell(caseObj,
content=traceback.format_exc(),
rowNo=i,
colsNo=testStepErrorInfo,
style="red")
# 写截图路径
sreenShotPath = capture_screen()
excel.writeCell(caseObj,
content=sreenShotPath,
rowNo=i,
colsNo=testStepErrorScreenshot,
style="red")
else:
info("执行测试步骤成功:{step}".format(step=stepDescrible))
caseSuccessNum += 1
excel.writeCell(caseObj,
content="pass",
rowNo=i,
colsNo=testStepExecuteResult,
style="green")
finally:
excel.writeCellCurrentTime(caseObj,
rowNo=i,
colsNo=testStepExecuteTime)
sheetObj = excel.getSheetByName("测试用例")
if caseSuccessNum == caseStepsNum - 1:
excel.writeCell(sheetObj,
content="pass",
rowNo=id,
colsNo=testCaseExecuteResult,
style="green")
else:
excel.writeCell(sheetObj,
content="fail",
rowNo=id,
colsNo=testCaseExecuteResult,
style="red")
excel.writeCellCurrentTime(sheetObj,
rowNo=id,
colsNo=testCaseExecuteTime)
else:
info("{name}-->测试用例忽略执行".format(name=testCaseName))
def main():
# 新建一个解析excel工具类的实例对象
parseE = ParseExcel()
parseE.loadWorkBook(FILEPATH)
sheetObj = parseE.getSheetByName("API")
#print(sheetObj)
# 获取active列的列对象
activeList = parseE.getColumn(sheetObj, API_active)
#下面第一层遍历是为了读取excel主表api信息，读取测试用例
for idx, cell in enumerate(activeList[1:], 2):
if cell.value == "y":
# 需要执行的接口所在的行的对象
rowObj = parseE.getRow(sheetObj, idx)
apiName = rowObj[API_apiName - 1].value
requestUrl = rowObj[API_requestUrl - 1].value
requestMethed = rowObj[API_requestMothod - 1].value
paramsType = rowObj[API_paramsType - 1].value
apiTestCaseFileName = rowObj[API_apiTestCaseFileName - 1].value
# 下一步需要读取接口用例表，获取接口的测试用例
caseSheetObj = parseE.getSheetByName(apiTestCaseFileName) # obj类型
caseActiveObj = parseE.getColumn(caseSheetObj, CASE_active) # tuple类型
print(caseActiveObj)
#第二层for循环是读取每个测试用例（注册接口用例，登录接口用例， 查询博文用例）里面的信息
for c_idx, c_cell in enumerate(caseActiveObj[1:], 2):
if c_cell.value == "y":
# 说明此case需要被执行
caseRowObj = parseE.getRow(caseSheetObj, c_idx)
requestData = caseRowObj[CASE_requestData - 1].value
requestData = eval(requestData) if requestData else {}
relyData = caseRowObj[CASE_relyData - 1].value
responseCode = caseRowObj[CASE_responseCode - 1].value
dataStore = caseRowObj[CASE_dataStore - 1].value
checkPoint = caseRowObj[CASE_checkPoint - 1].value
print(requestData, relyData, responseCode, dataStore, checkPoint)
#接口发送请求之前需要先做数据依赖处理
if relyData:
requestData = GetRely.get(requestData,eval(relyData))
#构造接口欧请求需要的数据，并调用HttpClient类中的方法发送接口，获取响应结果
hc = HttpClient()
response= hc.request(requestUrl,requestMethed,paramsType,requestData)
if response.status_code == responseCode:
responseBody = response.json()
#print(responseBody)
#获取到接口响应的body后，接下来处理依赖数据的存储
if dataStore:
RelyDataStore.do(eval(dataStore),apiName,c_idx-1,requestData,responseBody)
else:
print("第%s个接口的第%s条用例不需要存储数据依赖" %(idx - 1, c_idx - 1))
#接下来进行接口响应结果的校验
if checkPoint:
errorInfo = CheckResult.Check(responseBody,eval(checkPoint))
WriteTestResult.write(parseE,caseSheetObj,responseBody,errorInfo,c_idx)
# if errorInfo:
# print("第%s个接口的第%s条校验失败，错误与信息：%s" %(idx - 1, c_idx - 1,errorInfo))
else:
print("第%s个接口的第%s条用例未设置校验点" %(idx - 1, c_idx - 1))
else:
print("第%s个接口的第%s条用例所调用的接口响应失败，响应code=%s,而预期响应code=%s" %(idx - 1, c_idx - 1,response.status_code,\
responseCode))
else:
print("第%s个接口的第%s条用例被忽略执行！" %(idx - 1, c_idx - 1))
else:
print("第%s个接口被设置忽略执行！" %(idx - 1))
def run_test():
# 解析测试数据excel表
pe = ParseExcel()
pe.loadWorkBook(r'%s' % test_data_file_path)
# 获取接口表数据
apisheet_obj = pe.getSheetByName('API')
# 获取是否执行列
apisheet_isExecute = pe.getCol(apisheet_obj, API_isExecute)
# print(apisheet_isExecute)
# 统计总数
execute_case_no = 0
faild_case_no = 0
success_case_no = 0
# 生成报告数据
test_results_for_html_report = []
# info("-----------------执行api--------------------")
for idx, cell in enumerate(apisheet_isExecute[1:],
2): # 从第2行开始，遍历要是否执行列，设置idx从2开始
# 接口需要执行的接口
if cell.value and cell.value.strip().lower() == 'y':
# 需要执行的接口所在的行对象
apisheet_rowObj = pe.getRow(apisheet_obj, idx)
# api名称
apisheet_apiName = apisheet_rowObj[API_apiName - 1].value
apisheet_requestPath = apisheet_rowObj[API_requestPath - 1].value
apisheet_requestMethod = apisheet_rowObj[API_requestMethod -
1].value
apisheet_paramsType = apisheet_rowObj[API_paramsType - 1].value
apisheet_headers = apisheet_rowObj[API_headers - 1].value
apisheet_cookies = apisheet_rowObj[API_cookies - 1].value
info("---------------执行api：%s------------------" %
apisheet_apiName)
# 测试用例表名
apisheet_caseSheetName = apisheet_rowObj[API_caseSheetName -
1].value
# 处理api请求url
apisheet_requestUrl = get_url(apisheet_requestPath)
# 统计每个api的执行结果
api_case_no = 0
api_case_success_no = 0
api_case_fail_no = 0
info("------------------执行用例：%s-----------------" %
apisheet_caseSheetName)
# 获取测试用例表对象
casesheet_obj = pe.getSheetByName(apisheet_caseSheetName)
# 获取是否需要执行的用例列对象
casesheet_isExecute = pe.getCol(casesheet_obj, CASE_isExecute)
for c_idx, c_cell in enumerate(casesheet_isExecute[1:], 2):
# 用例是否执行
if c_cell.value and c_cell.value.strip().lower() == 'y':
execute_case_no += 1
api_case_no += 1
# 获取要执行的用例表的行对象
casesheet_rowObj = pe.getRow(casesheet_obj, c_idx)
casesheet_caseName = casesheet_rowObj[CASE_caseName -
1].value
casesheet_requestData = casesheet_rowObj[CASE_requestData -
1].value
# 依赖字段
casesheet_dependApiNoCaseNo = casesheet_rowObj[
CASE_dependApiNoCaseNo - 1].value
casesheet_dependRequestDataFields = casesheet_rowObj[
CASE_dependRequestDataFields - 1].value
casesheet_dependResponseDataFields = casesheet_rowObj[
CASE_dependResponseDataFields - 1].value
casesheet_dependStoreRequestDataFields = casesheet_rowObj[
CASE_dependStoreRequestDataFields - 1].value
casesheet_dependStoreResponseDataFields = casesheet_rowObj[
CASE_dependStoreResponseDataFields - 1].value
casesheet_storeDataFields = casesheet_rowObj[
CASE_storeDataFields - 1].value
casesheet_assertData = casesheet_rowObj[CASE_assertData -
1].value
# info("----------------------请求前数据处理-----------------------")
# 处理url中的参数
if '{' in apisheet_requestUrl:
apisheet_requestUrl = handle_url_params(
apisheet_requestUrl, eval(casesheet_requestData))
# 获取headers
if apisheet_headers and apisheet_headers.strip().lower(
) == 'y':
apisheet_headers = get_headers()
# 获取cookies
if apisheet_cookies and apisheet_cookies.strip().lower(
) == 'y':
apisheet_cookies = get_cookies()
# 处理唯一数
if "${unique" in casesheet_requestData:
casesheet_requestData = handle_unique_num(
casesheet_requestData)
# 处理加密数据
if "${encrypt" in casesheet_requestData:
casesheet_requestData = handler_encrypt(
casesheet_requestData)
# 处理依赖数据
if casesheet_dependApiNoCaseNo:
casesheet_requestData = get_request_data(
pe, apisheet_obj, casesheet_dependApiNoCaseNo,
casesheet_requestData,
casesheet_dependRequestDataFields,
casesheet_dependResponseDataFields,
casesheet_dependStoreRequestDataFields,
casesheet_dependStoreResponseDataFields)
if not isinstance(casesheet_requestData, dict):
casesheet_requestData = eval(casesheet_requestData)
info("----------------------发送请求-----------------------")
info("请求 url：%s" % apisheet_requestUrl)
info("请求 方法：%s" % apisheet_requestMethod)
info("请求参数类型：%s" % apisheet_paramsType)
info("请求数据：%s" % casesheet_requestData)
info("请求头数据：%s" % apisheet_headers)
info("请求cookies数据：%s" % apisheet_cookies)
httpc = HttpClient()
# 请求计时开始
start_time = time.time()
# 发送请求
response = httpc.request(
requestUrl=apisheet_requestUrl,
requestMethod=apisheet_requestMethod,
paramsType=apisheet_paramsType,
requestData=casesheet_requestData,
headers=apisheet_headers,
cookies=apisheet_cookies)
# 耗时
escape_time = time.time() - start_time
info("响应结果：%s" % response)
# 请求成功
if response.status_code == 200:
info("------------------处理响应数据-------------------")
# 响应数据
casesheet_responseData = response.json()
response_content = response.content.decode('utf-8')
info("响应数据：%s" % response.content.decode('utf-8'))
# 存储依赖数据
if casesheet_storeDataFields:
store_result = store_data(
pe, casesheet_obj, c_idx,
eval(casesheet_storeDataFields),
casesheet_requestData, response)
info("存储依赖数据执行结果：%s" % store_result)
# 比对结果
error_key = {}
if casesheet_assertData:
error_key = assert_result(
casesheet_responseData,
eval(casesheet_assertData))
info("断言结果error_key：%s" % error_key)
if error_key:
faild_case_no += 1
api_case_fail_no += 1
test_results_for_html_report.append(
(apisheet_requestUrl, casesheet_requestData,
response_content, int(escape_time * 1000),
casesheet_assertData, "失败"))
else:
success_case_no += 1
api_case_success_no += 1
test_results_for_html_report.append(
(apisheet_requestUrl, casesheet_requestData,
response_content, int(escape_time * 1000),
casesheet_assertData, "成功"))
# 写测试结果
write_result(pe, casesheet_obj, casesheet_responseData,
error_key, c_idx, escape_time)
else:
info('响应失败，响应状态码：%s' % response.status_code)
clear_result(pe, casesheet_obj, row_no=c_idx)
else:
clear_result(pe, casesheet_obj, row_no=c_idx)
info('case：%s 被忽略执行' % casesheet_caseName)
# 写接口的执行结果
write_api_result(pe, apisheet_obj, idx, api_case_no,
api_case_success_no, api_case_fail_no)
else:
#清空忽略执行的接口结果
clear_api_result(pe, apisheet_obj, idx)
info('api：%s 被忽略执行' % apisheet_apiName)
# 写报告
info("-------------------生成报告--------------------")
report_name = get_report_name()
report_html(data=test_results_for_html_report, html_name=report_name)
time.sleep(3)
# 发送邮件
info("-------------------发送邮件--------------------")
send_file = report_name + '.html'
send_content="本次自动化测试共执行用例数：%s，成功数：%s，失败数：%s,成功率：%.2f%%"\
%(execute_case_no,success_case_no,faild_case_no,success_case_no/execute_case_no*100)
send_mail(send_file, send_content)
def main():
#file_path=r"D:\20170721exercise\interfaceFrame201807\TestData\inter_test_data.xlsx"
parseE = ParseExcel()
parseE.loadWorkBook(file_path)
sheetObj = parseE.getSheetByName(u"API")
#print sheetObj
activeList = parseE.getColumn(sheetObj, 7)
#print activeList
#for i in activeList:
#print i.value
#外层for循环，遍历的api的sheet表
for idx, cell in enumerate(activeList[1:],2):
#print idx
if cell.value == "y":
# 需要执行的接口所在行的行对象
rowObj = parseE.getRow(sheetObj, idx)
#print type(rowObj)
apiName = rowObj[API_apiName-1].value
requestUrl = rowObj[API_requestUrl-1].value
requestMethod = rowObj[API_requestMothod-1].value
paramsType = rowObj[API_paramsType-1].value
apiTestCaseFileName = rowObj[API_apiTestCaseFileName-1].value
#print apiName, requestUrl, requestMothod, paramsType, apiTestCaseFileName
# 下一步读用例sheet表，准备执行测试用例
caseSheetObj = parseE.getSheetByName(apiTestCaseFileName)
caseActiveObj = parseE.getColumn(caseSheetObj, CASE_active)
# for i in caseActiveObj:
# print i.value
#内层for循环，遍历的测试用例的sheet
for c_idx, col in enumerate(caseActiveObj[1:], 2):
if col.value == "y":
# 说明此case行需要执行
caseRowObj = parseE.getRow(caseSheetObj, c_idx)
requestData = caseRowObj[CASE_requestData - 1].value
relyData = caseRowObj[CASE_relyData - 1].value
# print requestData
# print relyData
dataStore=caseRowObj[CASE_dataStore-1].value
checkPoint=caseRowObj[CASE_checkPoint-1].value
if relyData:
#发送接口请求之前，先做依赖数据的处理
requestData = "%s" %GetKey.get(eval(requestData),eval(relyData))
#print type(requestData)
#print requestData
# 拼接接口请求参数，发送接口请求
#print type(requestData)
httpC = HttpClient()
#print requestMethod, requestUrl, paramsType, requestData
response = httpC.request(requestMethod=requestMethod,requestUrl=requestUrl,paramsType=paramsType,requestData=requestData)
if response.status_code==200:
responseData = response.json()
#存储依赖数据
if dataStore:
RelyDataStore.do(eval(dataStore),apiName,c_idx-1,eval(requestData),responseData)
#比对结果
errorKey = CheckResult.check(responseData,eval(checkPoint))
write_result(parseE,caseSheetObj,responseData,errorKey,c_idx)
# if not errorKey:
# print "校验结果通过"
# else:
# print "校验失败",errorKey
else:
print response.status_code
else:
print "用例被忽略执行"
else:
print "接口被设置忽略执行"
def main():
parE = ParseExcel()
parE.loadWorkBook(interfaceFilePath)
# 通过sheet名称获取sheet的实例对象
sheetObj = parE.getSheetByName('API')
# print sheetObj
# 获取APIsheet表中的Activit列中的全部单元格对象，返回list
activelist = parE.getColumn(sheetObj, Api_active)
# print activelist
for index, active_cell in enumerate(activelist[1:], 2):
# print row
row_value = active_cell.value
if row_value.lower() == "y":
# print row_value, index
row = parE.getRow(sheetObj, index)
APIName = row[Api_apiName - 1].value
RequestUrl = row[Api_requestUrl - 1].value
RequestMethod = row[Api_requestMethod - 1].value
paramsType = row[Api_paramsType - 1].value
ApiTestCase = row[Api_ApiTestCase - 1].value
# print APIName, RequestUrl, paramsType, RequestMethod, ApiTestCase
caseSheetObj = parE.getSheetByName(ApiTestCase)
# print caseSheetObj.title
case_active_list = parE.getColumn(caseSheetObj, Case_active)
for case_index, case_row_active in enumerate(
case_active_list[1:], 2):
# row = parE.getRow(caseSheetObj, index)
case_row_value = case_row_active.value
if case_row_value.lower() == "y":
case_row = parE.getRow(caseSheetObj, case_index)
RequestData = case_row[Case_requestData - 1].value
ResponseCode = case_row[Case_responseCode - 1].value
ResponseData = case_row[Case_responseData - 1].value
DataStore = case_row[Case_dataStore - 1].value
CheckPoint = case_row[Case_checkPoint - 1].value
RelyData = case_row[Case_relyData - 1].value
# print RelyData
# print RequestData, ResponseCode, ResponseData, CheckPoint
# 获取接口case依赖的数据，并更新case里面的值
if RelyData and RequestData:
RequestData = "%s" % GetRely.get(
eval(RequestData), eval(RelyData))
if APIName == u"用户登录":
print type(RequestData)
request_data = eval(RequestData)
request_data["password"] = EncryptMD5.encrypt_md5(
request_data["password"])
RequestData = "%s" % request_data
httpC = HttpClient()
response = httpC.request(requestMethod=RequestMethod,
requestUrl=RequestUrl,
paramsType=paramsType,
requestData=RequestData)
# 存储依赖数据
if response.status_code == 200 and DataStore:
storePoint = eval(DataStore)
dataS = RelyDataStore()
dataS.do(storePoint, eval(RequestData),
response.json(), APIName, case_index - 1)
# print Request_data, Response_data
else:
if response.status_code != 200:
Log.info("接口响应失败")
else:
Log.info("不需要存储依赖数据")
if response.status_code == 200 and CheckPoint:
errorKey = CheckResult.check(response,
eval(CheckPoint))
# 写入case执行结果
WriteResult.write(parE, caseSheetObj, response.text,
errorKey, case_index)
else:
Log.info("接口【%s】的第%s被忽略执行了" % (APIName, index - 1))
def main():
parseE = ParseExcel()
parseE.loadWorkBook(excelPath)
sheetObj = parseE.getSheetByName(TestExcelName)
activeList = parseE.getColumn(sheetObj, TestCaseActive)
#print(activeList)
EX_resdict = {}
IsPass = True
errorInf = None
for idx, cell in enumerate(activeList[1:], 2):
#print(cell.value)
if cell.value == "y" or cell.value == "Y":
rowObj = parseE.getRow(sheetObj, idx)
TestCaseName = rowObj[TestCaseSheetName -1].value
#print(TestCaseName)
OrderNo = rowObj[TestOrderNo -1].value
#print(OrderNo)
EX_buyerid = rowObj[TestBuyerid-1].value
print("EX_buyerid",EX_buyerid)
EX_orderpaid = rowObj[TestOrderpaid-1].value
#print("EX_orderpaid",EX_orderpaid)
caseSheetObj = parseE.getSheetByName(TestCaseName)
#print(caseSheetObj)
write_time(parseE, sheetObj, idx, TestTime, coordinate=None) # 用例执行时间
EX_zhenxuanid_list =[v.value for v in parseE.getColumn(caseSheetObj, CASE_ZhenxuanID+1)[1:]] # excel中取出甄选师列中所有数据
#for buyerid in buyerid_tuple[1:]:
# print(buyerid.value)
#print(EX_zhenxuanid_list)
DB_zhanxuanid = get_leaderid_for_user_by_userid(EX_buyerid)
#print('DB', DB_zhanxuanid)
DB_orderpaid = get_orderpaid_for_team_rebate(OrderNo) # 获取订单实际支付金额
print('DB_orderpaid', DB_orderpaid)
DB_zhenxuan_and_type_rate_amount = get_userid_type_rate_amount_for_team_rebate(OrderNo, EX_buyerid)
print(DB_zhenxuan_and_type_rate_amount) # 从数据库中获取甄选师id，返利类型，返利比例，返利金额
# type返利类型0甄选收益，1一级奖励，2二级奖励，3育成奖励
DB_unique_id = get_unique_id(DB_zhanxuanid)
print('DB_unique_id', DB_unique_id)
if DB_orderpaid == EX_orderpaid:
print("DB订单金额与EX一致")
else:
print("DB订单金额与EX不一致！！！")
errorInf = "DB订单金额与EX不一致！！！"
write_result(parseE, sheetObj, idx, errorInf)
continue
if DB_zhanxuanid in EX_zhenxuanid_list: # 按甄选师id找到excel中所在行中全部内容，可以从这个列表中找到上级和所有收益多少
Zidx = EX_zhenxuanid_list.index(DB_zhanxuanid) # 找到甄选师行号
#print(Zidx)
EX_cassrow = parseE.getRow(caseSheetObj, Zidx + 1 + 1) # 获得该甄选师行的所有内容
#print(EX_cassrow)
for index, EX_cell in enumerate(EX_cassrow, 1): # 通过枚举得到甄选师列列号与内容的字典
# print(idx, EX_cell.value)
EX_resdict[index] = EX_cell.value
print('111', EX_resdict)
print('2222', EX_resdict[1])
#ZX_amount = DB_orderpaid * 1
#IsPass = True
if TestCaseName == 'b0_b1_b2':
result = b0_b1_b2_result(DB_zhenxuan_and_type_rate_amount, EX_resdict, DB_orderpaid)
errorInf = result[1]
elif TestCaseName == 'b0_b2':
result = b0_b2_result(DB_zhenxuan_and_type_rate_amount, EX_resdict, DB_orderpaid)
errorInf = result[1]
elif TestCaseName == 'b1-b1-b2':
result = b1_b1_b2_result(DB_zhenxuan_and_type_rate_amount, EX_resdict, DB_orderpaid)
errorInf = result[1]
elif TestCaseName == 'b1-b2':
result = b1_b2_result(DB_zhenxuan_and_type_rate_amount, EX_resdict, DB_orderpaid)
errorInf = result[1]
elif TestCaseName == 'b2-b1':
result = b2_b1_result(DB_zhenxuan_and_type_rate_amount, EX_resdict, DB_orderpaid)
errorInf = result[1]
else:
print(DB_zhanxuanid, "不在用例中")
errorInf = "不在用例中"
IsPass = False
#print(Zidx)
#print(errorInf)
write_result(parseE, sheetObj, idx, errorInf)
class RunnerTestCase(unittest.TestCase):
def setUp(self):
self.parseyaml = ParseYaml()
self.testdata_path = self.parseyaml.ReadParameter('ImportAddress')
self.parseexcel = ParseExcel(self.testdata_path)
self.pageaction = PageAction()
self.sheetnames = self.parseexcel.wb.sheetnames
self.parameter = CONFIG_PATH + 'Parameter.yaml'
self.CaseNum = 0
# 创建六个字典，分别储存步骤测试结果，用例测试结果，用例测试时间，错误信息，截图信息，步骤测试时间
self.time_dic = {}
self.result_dic = {}
self.error_dic = {}
self.picture_dic = {}
self.caseResult_dic = {}
self.caseTime_dic = {}
self.font = Font(color=None)
def test_Case(self):
try:
# self.setUp()
# 获取循环次数
loop = int(self.parseyaml.ReadParameter('loop'))
# 获取模块名
moudle = self.parseyaml.ReadParameter('Moudle')
# 清除用例旧数据
self.parseexcel.clearCaseColumnValue(self.sheetnames[0])
# 清除步骤旧数据
for i, v in enumerate(self.sheetnames):
if i == 0:
continue
else:
self.parseexcel.clearStepColumnValue(v)
for l in range(loop):
# 用例运行数
try:
# 获取'是否执行'列
isimplement = self.parseexcel.getColumnValue(self.sheetnames[0], testCase_Isimplement)
# 循环'是否执行'列
# 如果执行，且模块名符合，则获取用例编号，并切换到对应的工作表，执行用例
for index, value in enumerate(isimplement):
if moudle == '全部':
pd = "value.lower() == 'y'"
else:
pd = 'value.lower() == "y" and moudle ' \
'== self.parseexcel.getCellValue(self.sheetnames[0], index + 2, testCase_Sheet)'
try:
# 如果是否执行为空则跳过执行
if value is None or value == '':
continue
elif eval(pd):
# 根据'是否执行'；列索引获取对应的工作表名
sheetname = self.parseexcel.getCellValue(self.sheetnames[0], index + 2, testCase_Sheet)
# 根据'是否执行'列索引获取对应的用例编号
testcasenum = self.parseexcel.getCellValue(self.sheetnames[0], index + 2, testCase_Num)
# 切换到用例对应的工作表
# sheetnames = self.parseexcel.wb[sheetname]
'''
根据用例编号(testcasenum)获取预置条件编号
'''
# 获取用例步骤的用例编号类，并执行对应用例编号的用例步骤（增加表内是否有合并单元格的判断）
if self.parseexcel.ismerge(sheetname):
teststepnum = self.parseexcel.getMergeColumnValue(sheetname, testStep_Num)
else:
teststepnum = self.parseexcel.getColumnValue(sheetname, testStep_Num)
# 循环用例步骤编号，根据索引获取预置条件编号
testPrenum = ''
for i, v in enumerate(teststepnum):
if v == testcasenum:
# 用例前置条件编号
testPrenum = self.parseexcel.getCellValue(sheetname, i + 2, testStep_Preset)
break
# 循环用例步骤编号，找到与预置条件编号相同的用例步骤编号
# 循环所有的步骤编号
# 获取对应用例编号的步骤编号的关键字，定位方式，表达式，操作值
url = ParseYaml().ReadParameter('IP')
while re.match(r"^(?:[0-9]{1,3}\.){3}[0-9]{1,3}$", url) is None and re.match(r'[^\s]*[.com|.cn]', url) is None:
url = ParseYaml().ReadParameter('IP') # 从输入框获取浏览器地址
# 先打开浏览器，进入指定IP地址
time.sleep(1)
self.pageaction.openBrowser()
self.pageaction.getUrl('http://%s' % url)
# 执行预置条件
for t, v in enumerate(teststepnum):
if v == testPrenum:
# 用例执行步骤
pre_stepname = self.parseexcel.getCellValue(sheetname, t + 2, testStep_Describe)
# 获取预置条件关键字
pre_keyword = self.parseexcel.getCellValue(sheetname, t + 2, testStep_KeyWord)
# 去除前后空格
if pre_keyword is not None:
pre_keyword = pre_keyword.strip()
# 获取定位方式
pre_location = self.parseexcel.getCellValue(sheetname, t + 2, testStep_Location)
# 去除前后空格
if pre_location is not None:
pre_location = pre_location.strip()
# 获取定位表达式
pre_locator = self.parseexcel.getCellValue(sheetname, t + 2, testStep_Locator)
if type(pre_locator) is int:
pre_locator = str(self.parseexcel.getCellValue(sheetname, t + 2, testStep_Locator))
# 获取输入值
pre_testvalue = self.parseexcel.getCellValue(sheetname, t + 2, testStep_Value)
# 如果输入值为 int 类型，则强转为 str 类型，用于字符串拼接
if type(pre_testvalue) is int:
pre_testvalue = str(self.parseexcel.getCellValue(sheetname, t + 2, testStep_Value))
# 总共有四种情况可以正常执行，其他情况则会将用例判断为运行失败
# 1.关键字，定位方式，表达式，输入值全部不为空的情况 例：send_keys
# 2.关键字，输入值不为空，定位方式，表达式为空的情况 例：assert（断言）
# 3.关键字，定位方式，表达式不为空，输入值为空的情况 例：click
# 4.关键字不为空，定位方式，表达式，输入值为空的情况 例 getTitle
if pre_keyword and pre_location and pre_locator and pre_testvalue:
pre_fun = 'self.pageaction' + '.' + pre_keyword + '(' + '"' + pre_location + '"' + ', ' + '"' + pre_locator + '"' + ', ' + '"' + \
pre_testvalue + '"' + ')'
elif pre_keyword and pre_testvalue and pre_location is None or pre_location == '' \
and pre_locator is None or pre_location == '':
pre_fun = 'self.pageaction' + '.' + pre_keyword + '(' + '"' + pre_testvalue + '"' + ')'
elif pre_keyword and pre_location and pre_locator and pre_testvalue is None or pre_testvalue == '':
pre_fun = 'self.pageaction' + '.' + pre_keyword + '(' + '"' + pre_location + '"' + ', ' + '"' + pre_locator + '"' + ')'
elif pre_keyword and pre_location is None or pre_location == '' and pre_locator is None \
or pre_locator == '' and pre_testvalue is None or pre_testvalue == '':
pre_fun = 'self.pageaction' + '.' + pre_keyword + '(' + ')'
elif pre_keyword is None or pre_keyword == '' and pre_location is None or pre_location == '' \
and pre_locator is None or pre_locator == '' and pre_testvalue is None or pre_testvalue == '':
continue
else:
# 将结果以及错误信息存入字典
self.result_dic.setdefault(sheetname, {})[t+2] = 'Skip'
self.error_dic.setdefault(sheetname, {})[t+2] = '关键字对应参数错误'
# self.parseexcel.writeCellValue(sheetname, t+2, testStep_Result+l, 'Skip')
# self.parseexcel.writeCellValues(sheetname, t + 2, '关键字对应参数错误')
logger.info('关键字对应参数错误')
print('关键字对应参数错误')
continue
# 执行用例
try:
# eval 将字符串转换为可执行的python语句
eval(pre_fun)
# 抛出异常的情况，将失败结果写入excel表格中
except TypeError:
# 将结果以及错误信息存入字典
self.result_dic.setdefault(sheetname, {})[t+2] = 'Skip'
self.error_dic.setdefault(sheetname, {})[t+2] = '关键字参数个数错误，请检查参数'
# 写入测试时间，测试结果，错误信息，错误截图
# self.parseexcel.writeCellValue(sheetname, t+2, testStep_Result+l, 'Skip')
# self.parseexcel.writeCellValues(sheetname, t + 2, '关键字参数个数错误，请检查参数')
logger.info('步骤"{}"执行失败'.format(pre_stepname))
logger.info('关键字参数个数错误，请检查参数')
print('步骤"{}"执行失败'.format(pre_stepname))
print('关键字参数个数错误，请检查参数')
except TimeoutException:
# 将结果以及错误信息存入字典
self.result_dic.setdefault(sheetname, {})[t+2] = 'Skip'
self.error_dic.setdefault(sheetname, {})[t+2] = '元素定位超时，' \
'请检查上一步是否执行成功，或元素定位方式'
# 写入测试时间，测试结果，错误信息，错误截图
# self.parseexcel.writeCellValue(sheetname, t+2, testStep_Result+l, 'Skip')
# self.parseexcel.writeCellValues(sheetname, t + 2, '元素定位超时，'
# '请检查上一步是否执行成功，或元素定位方式')
logger.info('步骤"{}"执行失败'.format(pre_stepname))
logger.info('元素定位超时，请检查上一步是否执行成功，或元素定位方式')
print('步骤"{}"执行失败'.format(pre_stepname))
print('元素定位超时，请检查上一步是否执行成功，或元素定位方式')
except TimeoutError as e:
# 将结果以及错误信息存入字典
self.result_dic.setdefault(sheetname, {})[t + 2] = 'Failed'
self.error_dic.setdefault(sheetname, {})[t + 2] = '断言失败'
logger.info('步骤"{}"执行失败'.format(pre_stepname))
print('步骤"{}"执行失败'.format(pre_stepname))
logger.info(e)
except AttributeError as e:
# 将结果以及错误信息存入字典
self.result_dic.setdefault(sheetname, {})[t + 2] = 'Skip'
self.error_dic.setdefault(sheetname, {})[t + 2] = '元素定位超时，请检查元素定位'
logger.info('步骤"{}"执行失败'.format(pre_stepname))
print('步骤"{}"执行失败'.format(pre_stepname))
logger.info(e)
except AssertionError:
# 将结果以及错误信息存入字典
self.result_dic.setdefault(sheetname, {})[t+2] = 'Failed'
self.error_dic.setdefault(sheetname, {})[t+2] = '断言失败'
# 写入测试时间，测试结果，错误信息，错误截图
# self.parseexcel.writeCellValue(sheetname, t+2, testStep_Result+l, 'Failed')
# self.parseexcel.writeCellValues(sheetname, t + 2, '断言失败')
logger.info('步骤"{}"执行失败'.format(pre_stepname))
print('步骤"{}"执行失败'.format(pre_stepname))
except WebDriverException:
# 将结果以及错误信息存入字典
self.result_dic.setdefault(sheetname, {})[t+2] = 'Skip'
self.error_dic.setdefault(sheetname, {})[t+2] = '浏览器异常，' \
'请检查浏览器驱动或运行过程中是否被强制关闭'
# 写入测试时间，测试结果，错误信息，错误截图
# self.parseexcel.writeCellValue(sheetname, t+2, testStep_Result+l, 'Skip')
# self.parseexcel.writeCellValues(sheetname, t + 2, '浏览器异常，'
# '请检查浏览器驱动或运行过程中是否被强制关闭')
logger.info('步骤"{}"执行失败'.format(pre_stepname))
logger.info('浏览器异常，请检查浏览器驱动或运行过程中是否被强制关闭')
print('步骤"{}"执行失败'.format(pre_stepname))
print('浏览器异常，请检查浏览器驱动或运行过程中是否被强制关闭')
except Exception:
error_info = traceback.format_exc()
# 将结果以及错误信息存入字典
self.result_dic.setdefault(sheetname, {})[t+2] = 'Skip'
self.error_dic.setdefault(sheetname, {})[t+2] = error_info
# # 写入测试时间，测试结果，错误信息，错误截图
# self.parseexcel.writeCellValue(sheetname, t+2, testStep_Result+l, 'Skip')
# self.parseexcel.writeCellValues(sheetname, t + 2, error_info)
logger.info('步骤"{}"执行失败'.format(pre_stepname))
print('步骤"{}"执行失败'.format(pre_stepname))
else:
# 写入测试结果
# 将结果以及错误信息存入字典
self.result_dic.setdefault(sheetname, {})[t+2] = 'Pass'
# self.parseexcel.writeCellValue(sheetname, t + 2, testStep_Result+l, 'Pass')
logger.info('步骤"{}"执行成功'.format(pre_stepname))
print('步骤"{}"执行成功'.format(pre_stepname))
finally:
# 截图
bf.add_test_img()
pic = self.pageaction.saveScreeShot(sheetname, testcasenum)
# 将截图信息以及测试时间存入字典中
Time = datetime.now()
Time.strftime('%Y:%m:%d %H:%M:%S')
self.picture_dic.setdefault(sheetname, {})[t+2] = pic
self.time_dic.setdefault(sheetname, {})[t + 2] = Time
# self.parseexcel.writeCellValue(sheetname, t + 2, testStep_Picture, pic)
# self.parseexcel.writeCellTime(sheetname, t + 2, testStep_EndTime)
else:
continue
# 将用例步骤工作表内的用例编号以字典的方式循环
for t, v in enumerate(teststepnum):
# 用例步骤（用例编号） 与 用例列表（用例编号）相同的
if v == testcasenum:
# 用例执行步骤
stepname = self.parseexcel.getCellValue(sheetname, t + 2, testStep_Describe)
# 获取关键字
keyword = self.parseexcel.getCellValue(sheetname, t + 2, testStep_KeyWord)
# 去除前后空格
if keyword is not None:
keyword = keyword.strip()
# 获取定位方式
location = self.parseexcel.getCellValue(sheetname, t + 2, testStep_Location)
# 去除前后空格
if location is not None:
location = location.strip()
# 获取定位表达式
locator = self.parseexcel.getCellValue(sheetname, t + 2, testStep_Locator)
if type(locator) is int:
locator = str(self.parseexcel.getCellValue(sheetname, t + 2, testStep_Locator))
# 获取输入值
testvalue = self.parseexcel.getCellValue(sheetname, t + 2, testStep_Value)
# 如果输入值为 int 类型，则强转为 str 类型，用于字符串拼接
if testvalue is not None and type(testvalue) is not str:
testvalue = str(self.parseexcel.getCellValue(sheetname, t + 2, testStep_Value))
# if testvalue.lower() == 'none':
# testvalue = ''
# 进行关键字拼接
# 总共有四种情况可以正常执行，其他情况则会将用例判断为运行失败
# 1.关键字，定位方式，表达式，输入值全部不为空的情况 例：send_keys
# 2.关键字，输入值不为空，定位方式，表达式为空的情况 例：assert（断言）
# 3.关键字，定位方式，表达式不为空，输入值为空的情况 例：click
# 4.关键字不为空，定位方式，表达式，输入值为空的情况 例 getTitle
if keyword and location and locator and testvalue:
fun = 'self.pageaction' + '.' + keyword + '(' + '"' + location + '"' + ', ' + '"' + locator + '"' + ', ' + '"' + \
testvalue + '"' + ')'
elif keyword and testvalue and location is None or location == '' \
and locator is None or location == '':
fun = 'self.pageaction' + '.' + keyword + '(' + '"' + testvalue + '"' + ')'
elif keyword and location and locator and testvalue is None or testvalue == '':
fun = 'self.pageaction' + '.' + keyword + '(' + '"' + location + '"' + ', ' + '"' + locator + '"' + ')'
elif keyword and location is None or location == '' and locator is None \
or locator == '' and testvalue is None or testvalue == '':
fun = 'self.pageaction' + '.' + keyword + '(' + ')'
elif keyword is None or keyword == '' and location is None or location == '' \
and locator is None or locator == '' and testvalue is None or testvalue == '':
continue
else:
# 将结果以及错误信息存入字典
self.result_dic.setdefault(sheetname, {})[t + 2] = 'Skip'
self.error_dic.setdefault(sheetname, {})[t + 2] = '关键字对应参数错误'
# self.parseexcel.writeCellValue(sheetname, t+2, testStep_Result+l, 'Skip')
# self.parseexcel.writeCellValues(sheetname, t + 2, '关键字对应参数错误')
logger.info('关键字对应参数错误')
print('关键字对应参数错误')
continue
# 执行用例
try:
# eval 将字符串转换为可执行的python语句
eval(fun)
# 抛出异常的情况，将失败结果写入excel表格中
except TypeError as e:
# 将结果以及错误信息存入字典
self.result_dic.setdefault(sheetname, {})[t + 2] = 'Skip'
self.error_dic.setdefault(sheetname, {})[t + 2] = '关键字参数个数错误，请检查参数'
# 写入测试时间，测试结果，错误信息，错误截图
# self.parseexcel.writeCellValue(sheetname, t+2, testStep_Result+l, 'Skip')
# self.parseexcel.writeCellValues(sheetname, t + 2, '关键字参数个数错误，请检查参数')
logger.info('步骤"{}"执行失败'.format(stepname))
logger.info('关键字参数个数错误，请检查参数')
print('步骤"{}"执行失败'.format(stepname))
print('关键字参数个数错误，请检查参数')
logger.info(e)
except TimeoutException as e:
# 将结果以及错误信息存入字典
self.result_dic.setdefault(sheetname, {})[t + 2] = 'Skip'
self.error_dic.setdefault(sheetname, {})[t + 2] = '元素定位超时，' \
'请检查上一步是否执行成功，或元素定位方式'
# 写入测试时间，测试结果，错误信息，错误截图
# self.parseexcel.writeCellValue(sheetname, t+2, testStep_Result+l, 'Skip')
# self.parseexcel.writeCellValues(sheetname, t + 2, '元素定位超时，'
# '请检查上一步是否执行成功，或元素定位方式')
logger.info('步骤"{}"执行失败'.format(stepname))
logger.info('元素定位超时，请检查上一步是否执行成功，或元素定位方式')
print('步骤"{}"执行失败'.format(stepname))
print('元素定位超时，请检查上一步是否执行成功，或元素定位方式')
logger.info(e)
except TimeoutError as e:
# 将结果以及错误信息存入字典
self.result_dic.setdefault(sheetname, {})[t + 2] = 'Failed'
self.error_dic.setdefault(sheetname, {})[t + 2] = '断言失败'
logger.info('步骤"{}"执行失败'.format(stepname))
print('步骤"{}"执行失败'.format(stepname))
logger.info(e)
except AttributeError as e:
# 将结果以及错误信息存入字典
self.result_dic.setdefault(sheetname, {})[t + 2] = 'Skip'
self.error_dic.setdefault(sheetname, {})[t + 2] = '元素定位超时，请检查元素定位'
logger.info('步骤"{}"执行失败'.format(stepname))
print('步骤"{}"执行失败'.format(stepname))
logger.info(e)
except AssertionError as e:
# 将结果以及错误信息存入字典
self.result_dic.setdefault(sheetname, {})[t + 2] = 'Failed'
self.error_dic.setdefault(sheetname, {})[t + 2] = '断言失败'
# 写入测试时间，测试结果，错误信息，错误截图
# self.parseexcel.writeCellValue(sheetname, t+2, testStep_Result+l, 'Failed')
# self.parseexcel.writeCellValues(sheetname, t + 2, '断言失败')
logger.info('步骤"{}"执行失败'.format(stepname))
print('步骤"{}"执行失败'.format(stepname))
logger.info(e)
except WebDriverException as e:
# 将结果以及错误信息存入字典
self.result_dic.setdefault(sheetname, {})[t + 2] = 'Skip'
self.error_dic.setdefault(sheetname, {})[t + 2] = '浏览器异常，' \
'请检查浏览器驱动或运行过程中是否被强制关闭'
# 写入测试时间，测试结果，错误信息，错误截图
# self.parseexcel.writeCellValue(sheetname, t+2, testStep_Result+l, 'Skip')
# self.parseexcel.writeCellValues(sheetname, t + 2, '浏览器异常，'
# '请检查浏览器驱动或运行过程中是否被强制关闭')
logger.info('步骤"{}"执行失败'.format(stepname))
logger.info('浏览器异常，请检查浏览器驱动或运行过程中是否被强制关闭')
print('步骤"{}"执行失败'.format(stepname))
print('浏览器异常，请检查浏览器驱动或运行过程中是否被强制关闭')
logger.info(e)
except Exception:
error_info = traceback.format_exc()
# 将结果以及错误信息存入字典
self.result_dic.setdefault(sheetname, {})[t + 2] = 'Skip'
self.error_dic.setdefault(sheetname, {})[t + 2] = error_info
# # 写入测试时间，测试结果，错误信息，错误截图
# self.parseexcel.writeCellValue(sheetname, t+2, testStep_Result+l, 'Skip')
# self.parseexcel.writeCellValues(sheetname, t + 2, error_info)
logger.info('步骤"{}"执行失败'.format(stepname))
print('步骤"{}"执行失败'.format(stepname))
else:
# 写入测试结果
# 将结果以及错误信息存入字典
self.result_dic.setdefault(sheetname, {})[t + 2] = 'Pass'
# self.parseexcel.writeCellValue(sheetname, t + 2, testStep_Result+l, 'Pass')
logger.info('步骤"{}"执行成功'.format(stepname))
print('步骤"{}"执行成功'.format(stepname))
finally:
# 截图
bf.add_test_img()
pic = self.pageaction.saveScreeShot(sheetname, testcasenum)
# 将截图信息以及测试时间存入字典中
Time = datetime.now()
Time.strftime('%Y:%m:%d %H:%M:%S')
self.picture_dic.setdefault(sheetname, {})[t + 2] = pic
self.time_dic.setdefault(sheetname, {})[t + 2] = Time
# self.parseexcel.writeCellValue(sheetname, t + 2, testStep_Picture, pic)
# self.parseexcel.writeCellTime(sheetname, t + 2, testStep_EndTime)
else:
continue
self.CaseNum += 1
YamlWrite().Write_Yaml_Updata(self.parameter, 'CaseNum', self.CaseNum)
self.pageaction.quitBrowser()
# 写入测试结果
for r, v in self.result_dic.items():
for a, b in v.items():
if b == 'Pass':
self.parseexcel.wb[r].cell(int(a), testStep_Result + l).font = Font(color='33ff33')
self.parseexcel.wb[r].cell(int(a), testStep_Result + l, b)
elif b == 'Failed':
self.parseexcel.wb[r].cell(int(a), testStep_Result + l).font = Font(color='cc0000')
self.parseexcel.wb[r].cell(int(a), testStep_Result + l, b)
elif b == 'Skip':
self.parseexcel.wb[r].cell(int(a), testStep_Result + l).font = Font(color='D1D1D1')
self.parseexcel.wb[r].cell(int(a), testStep_Result + l, b)
else:
continue
# 通过循环对应 用例编号的步骤的结果，全部为pass的则写入用例Pass，有一条失败的则写入Failed
for s, b in enumerate(teststepnum):
# 获取测试结果
if b == testcasenum:
if self.parseexcel.getCellValue(sheetname, s+2, testStep_Result) is None \
or self.parseexcel.getCellValue(sheetname, s+2, testStep_Result) == '':
continue
elif self.parseexcel.getCellValue(sheetname, s+2, testStep_Result) == 'Pass':
# 将用例测试结果存入字典
self.caseResult_dic.setdefault(self.sheetnames[0], {})[index+2] = 'Pass'
# self.parseexcel.writeCellValue(self.sheetnames[0], index+2, testCase_Result+l, 'Pass')
elif self.parseexcel.getCellValue(sheetname, s+2, testStep_Result) == 'Skip':
self.caseResult_dic.setdefault(self.sheetnames[0], {})[index+2] = 'Failed'
break
# self.parseexcel.writeCellValue(self.sheetnames[0], index + 2, testCase_Result+l,
# 'Failed')
else:
self.caseResult_dic.setdefault(self.sheetnames[0], {})[index+2] = 'Failed'
break
# self.parseexcel.writeCellValue(self.sheetnames[0], index + 2, testCase_Result+l,
# 'Failed')
Time = datetime.now()
Time.strftime('%Y:%m:%d %H:%M:%S')
# 增加时间写入，以及已运行数量统计
self.caseTime_dic.setdefault(self.sheetnames[0], {})[index+2] = Time
# self.parseexcel.writeCellTime(self.sheetnames[0], index + 2, testCase_EndTime)
# 增加用例时间运行间隔，默认1秒（通过配置文件进行修改）
time.sleep(self.parseyaml.ReadTimeWait('casetime'))
else:
continue
except Exception as e:
Time = datetime.now()
Time.strftime('%Y:%m:%d %H:%M:%S')
self.caseResult_dic.setdefault(self.sheetnames[0], {})[index + 2] = 'Failed'
self.caseTime_dic.setdefault(self.sheetnames[0], {})[index + 2] = Time
# self.parseexcel.writeCellTime(self.sheetnames[0], index + 2, testCase_EndTime)
# self.parseexcel.writeCellValue(self.sheetnames[0], index + 2, testCase_Result+l,
# 'Failed')
self.CaseNum += 1
YamlWrite().Write_Yaml_Updata(self.parameter, 'CaseNum', self.CaseNum)
logger.info(e)
logger.info('正在写入测试结果，请勿关闭界面...')
# 读取所有字典，将结果写入excel中
for t, v in self.time_dic.items():
for a, b in v.items():
self.parseexcel.wb[t].cell(int(a), testStep_EndTime, b)
for e, v in self.error_dic.items():
for a, b in v.items():
self.parseexcel.wb[e].cell(int(a), testStep_Error, b)
for p, v in self.picture_dic.items():
for a, b in v.items():
self.parseexcel.wb[p].cell(int(a), testStep_Picture).value = '=HYPERLINK("{}", "{}")'.format(b, b)
for ct, v in self.caseTime_dic.items():
for a, b in v.items():
self.parseexcel.wb[ct].cell(int(a), testCase_EndTime, b)
for cr, v in self.caseResult_dic.items():
for a, b in v.items():
if b == 'Pass':
self.parseexcel.wb[cr].cell(int(a), testCase_Result + l).font = Font(color='33ff33')
self.parseexcel.wb[cr].cell(int(a), testCase_Result + l, b)
elif b == 'Failed':
self.parseexcel.wb[cr].cell(int(a), testCase_Result + l).font = Font(color='cc0000')
self.parseexcel.wb[cr].cell(int(a), testCase_Result + l, b)
else:
continue
# 获取excel中'用例工作表'列的不为None的总行数
total_case = list(filter(None, self.parseexcel.getColumnValue(self.sheetnames[0], testCase_Sheet)))
# 写入excel表的总用例数单元格中
self.parseexcel.writeCellValue(self.sheetnames[0], 1, 2, len(total_case)-1)
# 循环执行结果列中为pass的列
pass_case = []
faild_case = []
for pi in list(filter(None, self.parseexcel.getColumnValue(self.sheetnames[0], testCase_Result))):
if pi.lower() == 'pass':
pass_case.append(pi)
elif pi.lower() == 'failed':
faild_case.append(pi)
else:
continue
# 写入excel表中的通过用例数单元格中
self.parseexcel.writeCellValue(self.sheetnames[0], 1, 4, len(pass_case))
# 写入excel表中的失败用例数单元格中
self.parseexcel.writeCellValue(self.sheetnames[0], 1, 6, len(faild_case))
# 循环是否执行列的中n的数量
n_case = []
for ni in list(filter(None, self.parseexcel.getColumnValue(self.sheetnames[0], testCase_Isimplement))):
if ni.lower() == 'n':
n_case.append(ni)
# 写入excel表中的未测试用例的单元格中
self.parseexcel.writeCellValue(self.sheetnames[0], 1, 8, len(n_case))
except Exception as e:
logger.info(e)
self.pageaction.quitBrowser()
finally:
self.parseexcel.wb.save(self.testdata_path)
logger.info('用例测试结束')
print('用例测试结束')
except Exception as e:
print(e)
# 异常结束时，关闭文件流
self.parseexcel.wb.close()
def RunReport(self):
report_path = ParseYaml().ReadParameter('ReportAddress') # 报告存放位置
timestr = time.strftime('%Y%m%d%H%M%S', time.localtime(time.time()))
filename = report_path+'\\'+timestr+'.html'
fp = open(filename, 'wb')
# suites = unittest.defaultTestLoader.discover(TESTCASE_PATH, pattern='test*.py', top_level_dir=TESTCASE_PATH)
suites = unittest.TestSuite()
suites.addTest(RunnerTestCase('test_Case'))
runner = HTMLTestRunner(
title='自动化测试报告',
description='',
stream=fp,
verbosity=2,
)
runner.run(suites)
fp.close()
def RunnerBeautifulReport(self):
suite = unittest.TestLoader().loadTestsFromTestCase(RunnerTestCase)
runner = bf(suite)
report_path = REPORT_PATH# 报告存放位置
timestr = time.strftime('%Y%m%d%H%M', time.localtime(time.time()))
filename = report_path+'\\'+timestr+'.html'
runner.report(filename = '\\'+'测试报告'+timestr+'.html', description=u"测试报告",report_dir=report_path,theme="theme_cyan")
# if __name__ == '__main__':
# RunnerTestCase().RunnerBeautifulReport()
def main(): parseE = ParseExcel() parseE.loadWorkBook(file_path) sheetObj = parseE.getSheetByName(u"API") activeList = parseE.getColumn(sheetObj, API_active) print activeList for idx, cell in enumerate(activeList[1:], 2): if cell.value == "y": # 需要执行的接口所在行的行对象 rowObj = parseE.getRow(sheetObj, idx) apiName = rowObj[API_apiName - 1].value requestUrl = rowObj[API_requestUrl - 1].value requestMethod = rowObj[API_requestMothod - 1].value paramsType = rowObj[API_paramsType - 1].value apiTestCaseFileName = rowObj[API_apiTestCaseFileName - 1].value # 下一步读用例sheet表，准备执行测试用例 caseSheetObj = parseE.getSheetByName(apiTestCaseFileName) caseActiveObj = parseE.getColumn(caseSheetObj, CASE_active) for c_idx, col in enumerate(caseActiveObj[1:], 2): if col.value == "y": # 说明此case行需要执行 caseRowObj = parseE.getRow(caseSheetObj, c_idx) requestData = caseRowObj[CASE_requestData - 1].value relyData = caseRowObj[CASE_relyData - 1].value dataStore = caseRowObj[CASE_dataStore - 1].value checkPoint = caseRowObj[CASE_checkPoint - 1].value headers = caseRowObj[CASE_headers - 1].value print headers if relyData: # 发送接口请求之前，先做依赖数据的处理 requestData = "%s" %GetKey.get(eval(requestData), eval(relyData)) # 拼接接口请求参数，发送接口请求 httpC = HttpClient() # print requestMethod, requestUrl, paramsType, requestData response = httpC.request(requestMethod = requestMethod, requestUrl = requestUrl, paramsType = paramsType, requestData = requestData, headers=headers ) print response.text if response.status_code == 200: responseData = response.json()#对比json responseText = response.text # 存储依赖数据 if dataStore: RelyDataStore.do(eval(dataStore),apiName, c_idx - 1, eval(requestData),responseData) # 比对结果 errorKey = CheckResult.check(responseData, eval(checkPoint)) print type(responseData) write_result(parseE, caseSheetObj,responseText, errorKey, c_idx)#讲返回的报文体填入结果 else: responseData = response.json() responseText = response.text print "错误json%s"%responseData errorKey = CheckResult.check(responseData, eval(checkPoint)) print responseData,type(eval(checkPoint)) write_result(parseE, caseSheetObj, responseText,errorKey,c_idx) print errorKey print responseData else: print "用例被忽略执行" else: print "接口被设置忽略执行"
def setup_class(self):
self.pe=ParseExcel()
self.wb=self.pe.loadWorkBook(r'../data/study.xlsx')
class TestExcel:
@classmethod
def setup_class(self):
self.pe=ParseExcel()
self.wb=self.pe.loadWorkBook(r'../data/study.xlsx')
def test_getSheetByName(self):
self.sheet=self.pe.getSheetByName("Sheet1")
print(type(self.sheet))
def test_getSheetByIndex(self):
self.sheet=self.pe.getSheetByIndex(0)
print(type(self.sheet))
def test_getRowsNumber(self):
self.sheet = self.pe.getSheetByIndex(0)
self.rows=self.pe.getRowsNumber(self.sheet)
print(self.rows)
def test_getColsNumber(self):
self.sheet = self.pe.getSheetByIndex(0)
self.cols=self.pe.getColsNumber(self.sheet)
print(self.cols)
def test_getStartRowNumber(self):
self.sheet = self.pe.getSheetByIndex(0)
self.startrow=self.pe.getStartRowNumber(self.sheet)
print(self.startrow)
def test_getStartColNumber(self):
self.sheet = self.pe.getSheetByIndex(0)
self.startcols=self.pe.getStartColNumber(self.sheet)
print(self.startcols)
def test_getRow(self):
self.sheet = self.pe.getSheetByIndex(0)
self.rowobj=self.pe.getRow(self.sheet,1)
print(self.rowobj)
def test_getColumn(self):
self.sheet = self.pe.getSheetByIndex(0)
self.colsobj=self.pe.getColumn(self.sheet,2)
print(self.colsobj)
def test_getCellOfValue(self):
self.sheet = self.pe.getSheetByIndex(0)
value=self.pe.getCellOfValue(self.sheet,rowNo = 1, colsNo = 2)
print(value)
def test_getCellOfObject(self):
self.sheet = self.pe.getSheetByIndex(0)
obj=self.pe.getCellOfObject(self.sheet,rowNo = 1, colsNo = 2)
print(obj)
def test_writeCell(self):
self.sheet = self.pe.getSheetByIndex(0)
self.pe.writeCell(self.sheet,"失败",rowNo=15,colsNo=15,fontColor="green",fillColor="red")
def test_writeCellCurrentTime(self):
self.sheet = self.pe.getSheetByIndex(0)
self.pe.writeCellCurrentTime(self.sheet,rowNo=15,colsNo=14)
def main():
# 实现从获取接口测试数据到发送情况，再到获取返回结果，并处理结果
# 创建ParseExcel类的实例对象
parseE = ParseExcel()
# 调用封装好的加载excel到内存的方法将需要解析的excel加载到内存
parseE.loadWorkBook(excelPath)
# 获取“API”表的表对象
sheetObj = parseE.getSheetByName(apiExcelName)
#print(sheetObj)
# 获取API表中是否需要执行api自动化case的列对象
activeList = parseE.getColumn(sheetObj, API_active)
#print(activeList)
# 遍历是否需要执行标记列，只执行标记为y的api自动化测试
for idx, cell in enumerate(activeList[1:], 2):
#print(idx, cell.value)
if cell.value == "y":
# 需要执行的api
rowObj = parseE.getRow(sheetObj, idx)
apiName = rowObj[API_apiName - 1].value
requestUrl = rowObj[API_requestUrl - 1].value
requestMethod = rowObj[API_requestMothod - 1].value
paramsType = rowObj[API_paramsType - 1].value
apiTestCaseSheetName = rowObj[API_apiTestCaseFileName - 1].value
#print(apiName, requestUrl, requestMethod, paramsType, apiTestCaseSheetName)
# 下一步就是获取api的测试case，然后准备执行用例
caseSheetObj = parseE.getSheetByName(apiTestCaseSheetName)
caseActiveObj = parseE.getColumn(caseSheetObj, CASE_active)
print(caseActiveObj)
for c_idx, col in enumerate(caseActiveObj[1:], 2):
if col.value == 'y':
# 说明当前case是需要被执行的
caseRowObj = parseE.getRow(caseSheetObj, c_idx)
requestsData = caseRowObj[CASE_requestData - 1].value
#print(requestsData)
relyData = caseRowObj[CASE_relyData - 1].value
dataStore = caseRowObj[CASE_dataStore - 1].value
checkPoint = caseRowObj[CASE_checkPoint - 1].value
# 下一步，在发送接口请求之前，需要处理数据依赖
if relyData:
# 需要进行数据依赖处理
RequestData = GetRelyValue.get(requests, relyData)
print(RequestData)
else:
print("第%s个API的第%s条不需要做数据依赖处理！" % ((idx - 1),
(c_idx - 1)))
if requestData[0] == "{" and requestData[-1] == "}":
# 说明请求参数是一个json串格式数据
requestData = eval(requestData)
# 处理完接口请求参数的依赖数据后，接下来就是发送请求并获取响应结果
response = HttpClient.request(requestUrl, requestMethod,
paramsType, requestData)
print(response.status_code)
print(response.json())
# 下一步，根据接口响应结果，做数据依赖存储以及结果检测
if response.status_code == "200":
# 获取接口响应body
responseBody = response.json()
# 接下来做数据依赖存储
if dataStore:
RelyDataStore.do(apiName, c_idx - 1, requestData,
responseBody, eval(dataStore))
# 接下来进行接口响应结果检测
if checkPoint:
errorKey = CheckResult.check(response.json(),
eval(checkPoint))
# 将测试结果写回excel
write_result(parseE, caseSheetObj, response.json(),
errorKey, c_idx)
else:
print("第%s个API的第%s条case被忽略执行！" % ((idx - 1), (c_idx - 1)))
else:
print("第%s个API不需要执行" % (idx - 1))