def checkDatabase(databaseExpect, databaseResult, result, fact):
if databaseResult is None:
result[FACT] = fact.text
result[ISPASS] = BLOCK
result[TIME] = get_now().strftime(FORMORT)
result[REASON] = "用例sql语句书写错误"
return
if databaseExpect:
result[DATABASEEXPECT] = databaseExpect
else:
result[DATABASEEXPECT] = " "
if not databaseResult:
result[DATABASERESUTL] = " "
if databaseResult is not "" and databaseExpect is "":
result[ISPASS] = BLOCK
result[FACT] = fact.text
result[TIME] = get_now().strftime(FORMORT)
result[REASON] = "数据库检查点未设置"
return
if databaseExpect:
if int(databaseExpect) == len(databaseResult):
result[FACT] = fact.text
result[ISPASS] = PASS
result[TIME] = get_now().strftime(FORMORT)
else:
result[FACT] = fact.text
result[ISPASS] = FAIL
result[TIME] = get_now().strftime(FORMORT)
result[REASON] = "数据库检查失败，预期返回{}条数据，实际返回{}条数据".format(int(databaseExpect),
len(databaseResult))
def update_message(number):
"""
更新t_user_verify表信息
:param number: 用户手机号，从apiParams中获取
:return:
"""
result = con_server.query_one(
"select * from t_user_verify where number ={}".format(number))
# 十分钟前的时间
timedelta = datetime.datetime.now() - datetime.timedelta(minutes=10)
if not result:
con_server.insert_data("t_user_verify",
number=number,
call_sid=generate_random_str(32),
verify="4883",
date_created=get_now().strftime(FORMORT),
status=1)
return
if result["date_created"] < timedelta:
sql = "update t_user_verify set date_created='{}'where number={}". \
format(get_now().strftime(FORMORT), number)
con_server.update_data(sql)
if result["status"] == 0:
con_server.update_data(
"update t_user_verify set status=1 where number={}".format(number))
def checkDatabase(databaseExpect, databaseResult, result, fact):
"""
数据库验证
:param databaseExpect: 数据库期望
:param databaseResult: sql执行结果
:param result: 报告模板
:param fact: 接口返回实际结果
:return:
"""
if databaseExpect:
result[DATABASEEXPECT] = json.dumps(databaseExpect, ensure_ascii=False)
else:
result[DATABASEEXPECT] = " "
if not databaseResult:
result[DATABASERESUTL] = " "
if databaseExpect and databaseResult:
for key, values in databaseExpect.items():
value = values.split(",") or values.split("，")
for i in value:
k, v = i.split("=")
if k is None or v is None:
result[FACT] = fact.text
result[ISPASS] = BLOCK
result[TIME] = get_now().strftime(FORMORT)
result[REASON] = "用例sql语句书写错误"
return
if k == "len":
if str(len(databaseResult[key])) != v:
result[FACT] = fact.text
result[ISPASS] = FAIL
result[TIME] = get_now().strftime(FORMORT)
result[REASON] = "数据库{}检查点检查失败，预期返回{}条数据，实际返回{}条数据" \
.format(k, v, len(databaseResult[key]))
continue
if k not in databaseResult[key][0].keys():
result[FACT] = fact.text
result[ISPASS] = BLOCK
result[TIME] = get_now().strftime(FORMORT)
result[REASON] = "用例sql语句书写错误或数据库返回错误，{}不在数据库返回字段中" \
.format(k)
return
if str(databaseResult[key][0][k]) != v:
result[FACT] = fact.text
result[ISPASS] = FAIL
result[TIME] = get_now().strftime(FORMORT)
result[REASON] = "数据库{}.{}检查点检查失败，预期为:{}，实际为:{}" \
.format(key, k, v, databaseResult[key][0][k])
return
def updata_message(number):
result = con_server.query_one("select * from t_user_verify where number ={}".format(number))
timedelta = datetime.datetime.now() - datetime.timedelta(minutes=10) # 十分钟前的时间
if not result:
con_server.insert_data("t_user_verify", number=number, call_sid=generate_random_str(32), verify="4883",
date_created=get_now().strftime(FORMORT),
status=1)
return
if result["date_created"] < timedelta:
sql = f"update t_user_verify set date_created='{get_now().strftime(FORMORT)}'where number={number}"
con_server.update_data(sql)
if result["status"] == 0:
con_server.update_data(
"update t_user_verify set status=1 where number={}".format(number)
)
def check(fact, expect, result):
if "ERRRR" in fact:
result[ISPASS] = FAIL
result[REASON] = fact
result[TIME] = get_now().strftime(FORMORT)
return
try:
response = fact.json()
temp = ""
if not expect:
result[ISPASS] = BLOCK
result[FACT] = fact.text
result[TIME] = get_now().strftime(FORMORT)
result[REASON] = "检查点未设置"
return
for key in expect.keys():
if key not in response.keys():
result[FACT] = fact.text
result[ISPASS] = FAIL
result[TIME] = get_now().strftime(FORMORT)
result[REASON] = "实际结果中没有{}这个字段,检查用例是否错误或接口返回结果错误".format(key)
return
if not isinstance(expect[key], dict):
# 判断检查点中的字段是否在响应结果中
# 判断检查点中字段的值和返回结果字段的值是否一致
if not str(expect[key]).__eq__(str(response[key])):
result[FACT] = fact.text
result[ISPASS] = FAIL
result[TIME] = get_now().strftime(FORMORT)
temp += "{}的值预期为：{}，实际为：{}\n".format(key, expect[key], response[key])
result[REASON] = temp
else:
# 判断是否有检查点判断失败，如果有，ispass值仍然为fail
if result[ISPASS].__eq__(FAIL):
result[ISPASS] = FAIL
else:
result[FACT] = fact.text
result[ISPASS] = PASS
result[TIME] = get_now().strftime(FORMORT)
# 判断双重检查点，例如payload.message的形式
else:
for key1 in expect[key].keys():
if not key1 in response[key].keys():
result[FACT] = fact.text
result[ISPASS] = FAIL
result[TIME] = get_now().strftime(FORMORT)
result[REASON] = "实际结果中没有{}:{}这个字段,检查用例是否错误或接口返回结果错误".format(key, key1)
return
if str(response[key][key1]).__eq__(str(expect[key][key1])):
result[FACT] = fact.text
result[ISPASS] = PASS
result[TIME] = get_now().strftime(FORMORT)
else:
result[FACT] = fact.text
result[ISPASS] = FAIL
result[TIME] = get_now().strftime(FORMORT)
temp += "{}的值预期为：{}，实际为：{}\n".format(key, expect[key], response[key])
result[REASON] = temp
except Exception as e:
result[ISPASS] = FAIL
result[FACT] = ""
result[TIME] = get_now().strftime(FORMORT)
result[REASON] = "程序出错：{}".format(str(e))
log.error(e)
return
def check(fact, expect, result):
"""
弃用
检查点验证
:param fact:实际结果
:param expect: 预期结果
:param result: 报告模板
:return:
"""
if "ERRRR" in fact:
result[ISPASS] = FAIL
result[REASON] = fact
result[TIME] = get_now().strftime(FORMORT)
return
if isinstance(fact, tuple):
result[PARMAS] = json.dumps(fact[-1], ensure_ascii=False)
fact = fact[0]
else:
try:
response = fact.json()
except:
if "err_code" in expect.keys():
if expect["err_code"] != fact.status_code:
result[FACT] = fact.text
result[ISPASS] = FAIL
result[TIME] = get_now().strftime(FORMORT)
reason = "{}的值预期为：{}，实际为：{}" \
.format("err_code", expect["err-code"], fact.status_code)
result[REASON] = reason
else:
# 判断是否有检查点判断失败，如果有，ispass值仍然为fail
if result[ISPASS].__eq__(FAIL):
result[ISPASS] = FAIL
else:
result[FACT] = fact.text
result[ISPASS] = PASS
result[TIME] = get_now().strftime(FORMORT)
temps = ""
if not expect:
result[ISPASS] = BLOCK
result[FACT] = fact.text
result[EXPECT] = " "
result[TIME] = get_now().strftime(FORMORT)
result[REASON] = "检查点未设置或用例检查点格式错误"
return
# temp_except = expect
# temp_response = response
for keys in expect.keys():
index = 0
value = expect[keys]
if "," in value:
value = value.split(",")
if "，" in value:
value = value.split("，")
item = keys.split(".")
temp_response = response
r = re.compile(r"\[(.*?)]")
for key in item:
if key not in temp_response:
result[FACT] = fact.text
result[ISPASS] = FAIL
result[TIME] = get_now().strftime(FORMORT)
result[REASON] = "实际结果中没有{}这个字段," \
"检查用例是否错误或接口返回结果错误".format(key)
return
temp_response = temp_response[key]
if index == len(item) - 1:
if isinstance(temp_response, list):
if not temp_response and value:
result[FACT] = fact.text
result[ISPASS] = FAIL
result[TIME] = get_now().strftime(FORMORT)
result[REASON] = "{}实际返回为空，预期不为空".format(key)
return
if not value and temp_response:
result[FACT] = fact.text
result[ISPASS] = FAIL
result[TIME] = get_now().strftime(FORMORT)
result[REASON] = "{}实际返回不为空，预期为空".format(key)
return
if not value and not temp_response:
result[FACT] = fact.text
result[ISPASS] = PASS
result[TIME] = get_now().strftime(FORMORT)
continue
for rp in temp_response:
for vl in value:
k, v = vl.split("=")
if k not in rp:
result[FACT] = fact.text
result[ISPASS] = FAIL
result[TIME] = get_now().strftime(FORMORT)
result[REASON] = "实际结果中没有{}这个字段," \
"检查用例是否错误或接口返回结果错误".format(k)
return
if not str(rp[k]) == str(v):
result[FACT] = fact.text
result[ISPASS] = FAIL
result[TIME] = get_now().strftime(FORMORT)
temps += "{}的值预期为：{}，实际为：{}\n" \
.format(k, v, rp[k])
result[REASON] = temps
else:
# 判断是否有检查点判断失败，如果有，ispass值仍然为fail
if result[ISPASS].__eq__(FAIL):
result[ISPASS] = FAIL
else:
result[FACT] = fact.text
result[ISPASS] = PASS
result[TIME] = get_now().strftime(FORMORT)
if not str(temp_response) == str(value):
result[FACT] = fact.text
result[ISPASS] = FAIL
result[TIME] = get_now().strftime(FORMORT)
temps += "{}的值预期为：{}，实际为：{}\n".format(
key, value, temp_response)
result[REASON] = temps
else:
# 判断是否有检查点判断失败，如果有，ispass值仍然为fail
if result[ISPASS].__eq__(FAIL):
result[ISPASS] = FAIL
else:
result[FACT] = fact.text
result[ISPASS] = PASS
result[TIME] = get_now().strftime(FORMORT)
index += 1
def check(expect, fact, result, databaseResult="", databaseExpect=""):
# 默认结果为pass
result["ispass"] = "******"
# if fact.status_code == 200:
try:
response = fact.json()
# 循环检查点与响应结果是否匹配
temp = ""
if not expect:
result["ispass"] = "******"
result["time"] = get_now().strftime("%Y/%m/%d %H:%M:%S")
result["reason"] = "检查点未设置"
return
if databaseResult and not databaseExpect:
result["ispass"] = "******"
result["time"] = get_now().strftime("%Y/%m/%d %H:%M:%S")
result["reason"] = "数据库检查点未设置"
return
if databaseExpect:
if int(databaseExpect) == len(databaseResult):
result["ispass"] = "******"
result["time"] = get_now().strftime("%Y/%m/%d %H:%M:%S")
else:
result["ispass"] = "******"
result["time"] = get_now().strftime("%Y/%m/%d %H:%M:%S")
result["reason"] = "数据库检查失败，预期返回{}条数据，实际返回{}条数据".format(int(databaseExpect), len(databaseResult))
for key in expect.keys():
if not isinstance(expect[key], dict):
# 判断检查点中的字段是否在响应结果中
if key not in response.keys():
result["ispass"] = "******"
result["time"] = get_now().strftime("%Y/%m/%d %H:%M:%S")
result["reason"] = "实际结果中没有{}这个字段,检查用例是否错误或接口返回结果错误".format(key)
return
# 判断检查点中字段的值和返回结果字段的值是否一致
if not str(expect[key]).__eq__(str(response[key])):
result["ispass"] = "******"
result["time"] = get_now().strftime("%Y/%m/%d %H:%M:%S")
temp += "{}的值预期为：{}，实际为：{}\n".format(key, expect[key], response[key])
result["reason"] = temp
else:
# 判断是否有检查点判断失败，如果有，ispass值仍然为fail
if result["ispass"].__eq__("fail"):
result["ispass"] = "******"
else:
result["ispass"] = "******"
result["time"] = get_now().strftime("%Y/%m/%d %H:%M:%S")
# 判断双重检查点，例如payload.message的形式
else:
for key1 in expect[key].keys:
if str(response[key][key1]).__eq__(str(expect[key][key1])):
result["ispass"] = "******"
result["time"] = get_now().strftime("%Y/%m/%d %H:%M:%S")
temp += "{}的值预期为：{}，实际为：{}\n".format(key, expect[key], response[key])
result["reason"] = temp
else:
result["ispass"] = "******"
result["time"] = get_now().strftime("%Y/%m/%d %H:%M:%S")
except Exception as e:
result["ispass"] = "******"
result["time"] = get_now().strftime("%Y/%m/%d %H:%M:%S")
result["reason"] = "程序出错：{}".format(str(e))
return result
def runAll():
"""
运行所有用例
:return:
"""
init() # 初始化
resultSet = [] # 执行结果集
cases = get_all_case() # 测试用例集
start_time = time.time()
if not cases:
log.error("用例为空，无匹配格式的.xlsx文件或文件中暂无用例数据")
return
log.info("共获取{}条用例".format(len(cases)))
for case in cases:
# 将用例存入数据库临时保存,testData不入库保存
con.insert_data(TABLECASE, **case)
# 将接口数据插入数据库apiInfo表中暂时保存
apiInfo = {
APIID: int(case[APIID]),
APIHOST: case[APIHOST],
PARMAS: case[PARMAS],
METHOD: case[METHOD],
APIHEADERS: case[APIHEADERS],
RELATEDAPI: case[RELATEDAPI],
RELEATEDPARAMS: case[RELEATEDPARAMS]
}
# 如果数据库中不存在apiId的接口，则插入
if not con.query_all("select * from {} where apiId={}".format(
TABLEAPIINFO, apiInfo[APIID])):
con.insert_data(TABLEAPIINFO, **apiInfo)
for case in cases:
log.info("正在执行caseId={}的用例".format(case[CASEID]))
databaseExpect = case[DATABASEEXPECT]
sqlStatement = case[SQLSTATEMENT]
sqlResult = {}
# 执行用例
res = new_excute_case(case)
# sql语句参数化
if sqlStatement:
for key in sqlStatement.keys():
result = con_server.query_all(params_replace(
sqlStatement[key]))
if result is not None:
sqlResult[key] = result
else:
sqlResult[key] = None
else:
sqlResult = {}
# 报告模板
result = get_report_data(caseID=case[CASEID],
caseDesciribe=case[CASEDESCRIBE],
apiHost=case[APIHOST],
method=case[METHOD],
apiParams=case[PARMAS],
expect=json.dumps(case[EXPECT],
ensure_ascii=False),
fact=res,
databaseExpect=json.dumps(databaseExpect,
ensure_ascii=False,
cls=MyEncoder),
databaseResult=json.dumps(sqlResult,
ensure_ascii=False,
cls=MyEncoder))
# 检查点验证
test(fact=res, exp=case[EXPECT], result=result)
# 数据库验证
checkDatabase(databaseExpect=databaseExpect,
databaseResult=sqlResult,
result=result,
fact=res)
# 将执行结果写入数据库临时保存
con.insert_data(TABLERESULT, **result)
resultSet.append(result)
end_time = time.time()
time_consum = end_time - start_time # 测试耗时
case_count = con.query_all(
"SELECT caseId FROM {}".format(TABLERESULT)) # 执行用例
fail_case = con.query_all("SELECT caseId "
"FROM {} WHERE ispass='******'".format(
TABLERESULT, FAIL)) # 执行失败的用例
block_case = con.query_all(
"SELECT caseId FROM {} WHERE ispass='******'".format(TABLERESULT,
BLOCK)) # 执行阻塞的用例
success_case = con.query_all(
"SELECT caseId FROM {} WHERE ispass='******'".format(TABLERESULT,
PASS)) # 执行成功的用例
if case_count is None:
case_count = 0
else:
case_count = len(case_count)
if fail_case is None:
fail_case = 0
else:
fail_case = len(fail_case)
if block_case is None:
block_case = 0
else:
block_case = len(block_case)
if success_case is None:
success_case = 0
else:
success_case = len(success_case)
result_info = "本次测试执行完毕，本次测试环境为：{}，" \
"共耗时{}秒，共执行用例：{}条，" \
"成功：{}条，失败：{}条，阻塞：{}条" \
.format(test_env[7:11], float("%.2f" % time_consum),
case_count, success_case,
fail_case, block_case)
log.info(result_info)
# 将测试结果写入测试报告
report.set_result_info(result_info)
print(resultSet)
exc_path, part_name = report.get_report(resultSet)
#生成html报告
html_title = "%s接口自动化测试报告" % (get_now().strftime("%Y/%m/%d"))
html_path = html_body(total=case_count,
starttime=time.strftime(FORMORT,
time.localtime(start_time)),
endtime=time.strftime(FORMORT,
time.localtime(end_time)),
during=time_consum,
passd=success_case,
fail=fail_case,
block=block_case,
titles=html_title,
details=resultSet)
con.close()
con_server.close()
# 测试完成发送邮件
send_email_for_all(msg=result_info, part_path=[exc_path, html_path])
def test(exp, fact, result):
r = re.compile(r"\[(.*?)]")
if "ERRRR" in fact:
result[ISPASS] = FAIL
result[REASON] = fact
result[TIME] = get_now().strftime(FORMORT)
return
if isinstance(fact, tuple):
result[PARMAS] = json.dumps(fact[-1], ensure_ascii=False)
fact = fact[0]
else:
try:
res = fact.json()
except:
if "err_code" in exp.keys():
if str(exp["err_code"]) != str(fact.status_code):
result[FACT] = fact.text
result[ISPASS] = FAIL
result[TIME] = get_now().strftime(FORMORT)
reason = "{}的值预期为：{}，实际为：{}" \
.format("err_code", exp["err_code"], fact.status_code)
result[REASON] = reason
else:
# 判断是否有检查点判断失败，如果有，ispass值仍然为fail
if result[ISPASS].__eq__(FAIL):
result[ISPASS] = FAIL
else:
result[FACT] = fact.text
result[ISPASS] = PASS
result[TIME] = get_now().strftime(FORMORT)
return
if not exp:
result[ISPASS] = BLOCK
result[FACT] = fact.text
result[EXPECT] = " "
result[TIME] = get_now().strftime(FORMORT)
result[REASON] = "检查点未设置或用例检查点格式错误"
return
keys = exp.keys()
for key in keys:
values = exp[key].split(",")
items = key.split(".")
temp_res = res
for item in items:
if item not in temp_res.keys():
result[FACT] = fact.text
result[ISPASS] = FAIL
result[TIME] = get_now().strftime(FORMORT)
result[REASON] = "实际结果中没有{}这个字段," \
"检查用例是否错误或接口返回结果错误".format(item)
return
temp_res = temp_res[item]
for value in values:
t = []
if re.findall(r, value):
value = re.findall(r, value)
for i in value:
k, v = i.split("=")
for j in temp_res:
if k not in j.keys():
result[FACT] = fact.text
result[ISPASS] = FAIL
result[TIME] = get_now().strftime(FORMORT)
result[REASON] = "实际结果中没有{}这个字段," \
"检查用例是否错误或接口返回结果错误".format(k)
return
t.append(j[k])
try:
v = int(v)
except:
v = v
if v not in t:
result[FACT] = fact.text
result[ISPASS] = FAIL
result[TIME] = get_now().strftime(FORMORT)
reason = "{}字段预期的值不在返回结果集中，预期{}={}，实际{}=[{}]" \
.format(k, k, v, k, t)
result[REASON] = reason
else:
if str(temp_res) != str(value):
result[FACT] = fact.text
result[ISPASS] = FAIL
result[TIME] = get_now().strftime(FORMORT)
reason = "{}字段预期为{}，实际为{}]".format(
key, temp_res, value)
result[REASON] = reason
else:
# 判断是否有检查点判断失败，如果有，ispass值仍然为fail
if result[ISPASS].__eq__(FAIL):
result[ISPASS] = FAIL
else:
result[FACT] = fact.text
result[ISPASS] = PASS
result[TIME] = get_now().strftime(FORMORT)
