class Run_case():
def __init__(self, excelpath, file_path):
self.data = Getdata(excelpath=excelpath)
self.opexcel = oper_excel(filename=excelpath)
self.file_path = file_path
def banch_case(self):
rows_count = self.data.get_case_lines()
print("**********", rows_count)
# print(rows_count)
# 排除表头，从-1开始
for i in range(1, rows_count):
url = self.data.get_url(i)
functionname = self.data.get_functionname(i)
# method = self.data.get_method(i)
isrun = self.data.get_is_run(i)
# print("第", i, "条执行记录", isrun, functionname)
# print("+++++++",url,isrun,functionname)
# 查看运行状态是否运行
if isrun:
# print("第", i, "条执行记录", isrun, functionname)
res = requests_batch(file_path=self.file_path + "/" +
functionname + ".json",
params_name=functionname,
url=url)
try:
print(res.json)
except Exception as e:
print("未返回数据")
def __init__(self,excelpath= None,jsonpath=None):
if excelpath:
self.excelpath = excelpath
else:
self.excelpath = '../Test_Case/TestCase.xls'
if jsonpath:
self.jsonpath = jsonpath
else:
self.jsonpath='..//data//Tc_alldata.json'
self.get = Getdata(self.excelpath,self.jsonpath)
self.operation_excel = oper_excel(self.excelpath)
def __init__(self,division= None,classname = None):
# 若传参:属性标签以及数据排序方式
if division and classname:
self.division = division
self.classname = classname
#不传参，默认表格数据，div,x-scroll
else:
self.division = "div"
self.classname = "x-scroll"
self.data = Getdata()
self.cookies = Get_cookies.Get_cookies(2)
self.cookies= self.cookies.get_cookies_admin()
def __init__(self, filename=None, new_filename=None, report_path=None):
self.tablename = Case_Table().get_tablename()
if filename:
self.filename = filename
else:
self.filename = "../Test_Case/" + self.tablename + ".xls"
if new_filename:
self.new_filename = new_filename
else:
self.new_filename = "../Test_Case/" + self.tablename + "_bk.xls"
if report_path:
self.report_path = report_path
else:
self.report_path = "../Test_Case/TestCase.html"
self.data = Getdata()
def __init__(self,
txt_path=None,
report_path=None,
excelpath=None,
jsonpath=None):
if report_path:
self.report_path = report_path
else:
self.report_path = "../Test_Case/Report.html"
if txt_path:
self.txt_path = txt_path
else:
self.txt_path = "../Test_Case/Result.txt"
if jsonpath:
self.jsonpath = jsonpath
else:
self.jsonpath = "../Test_Case/TestCase.xls"
self.run_method = inter()
self.data = Getdata(excelpath=excelpath, jsonfilepath=jsonpath)
self.result = isequal()
self.opexcel = oper_excel(filename=excelpath)
self.sendmail = sendmail()
self.page_check = PageCheck()
self.de_data = dependata(excelpath=excelpath, jsonpath=jsonpath)
self.pass_total = []
self.fail_total = []
class PageCheck:
def __init__(self,division= None,classname = None):
# 若传参:属性标签以及数据排序方式
if division and classname:
self.division = division
self.classname = classname
#不传参，默认表格数据，div,x-scroll
else:
self.division = "div"
self.classname = "x-scroll"
self.data = Getdata()
self.cookies = Get_cookies.Get_cookies(2)
self.cookies= self.cookies.get_cookies_admin()
# 在界面中获取到所有指定属性的数据
def get_allparam(self,html,actualvalue):
soup = BeautifulSoup(html, "html.parser")
a = []
# 结合业务，界面数据均是在列表中
# 若是按照创建时间倒序排序，则获取当前第二列数据
try:
for tag in soup.find_all('div', class_='x-scroll'):
# print("*****", tag)
data = tag.find_all('tr')
# data = data.next_sibling
# print("======",data,type(data))
# 初始化返回至
flag = True
flag_b = True
for i in actualvalue:
if i in str(data):
flag_b = True
else:
flag_b = False
flag = flag_b and flag
return flag
except Exception as e:
print("找不到界面预期结果",e)
# 获取页面html信息
def getHtml(self,url,actualvalue):
# 获取登录cookies
page = requests.get(url, cookies=self.cookies,verify = False)
# 页面转换为html格式输出
html = page.text
# 返回比较结果
return self.get_allparam(html,actualvalue)
def case_param(self,i,url):
#testcase表格界面期望数据整理
#获取表格数据
actualvalue =self.data.get_web_expectvalu(i)
#分号分隔字符串放置在辅助数组
arr = []
for k in range(len(actualvalue.split(";"))):
arr.append(actualvalue.split(";")[k])
return self.getHtml(url,arr)
# a = PageCheck()
# print(a.case_param(3,"http://testing.iqidao.com/admin001/activity/users?activityId=740"))
def __init__(self, excelpath=None, jsonpath=None):
self.tablename = Case_Table().get_tablename()
if excelpath:
self.excelpath = excelpath
else:
self.excelpath = "../Test_Case/" + self.tablename + ".xls"
if jsonpath:
self.jsonpath = jsonpath
else:
self.jsonpath = "../data/" + self.tablename + ".json"
self.run_method = inter()
self.data = Getdata(self.excelpath, self.jsonpath)
self.result = isequal()
self.sendmail = sendmail()
self.page_check = PageCheck()
self.de_data = dependata(self.excelpath, self.jsonpath)
self.caserun = Case(excelpath=self.excelpath, jsonpath=self.jsonpath)
self.opexcel = oper_excel(filename=excelpath)
self.DBCASE = Get_case()
class exceltodb:
def __init__(self):
self.excelpath= "../Test_Case/Paper_Process.xls"
self.db,self.connect = DB_CONNECT(2).connect_db()
self.excel = oper_excel(filename="../Test_Case/Paper_Process.xls")
self.data = Getdata(excelpath=self.excelpath)
def read_excel(self):
print("开始")
workbook =xlrd.open_workbook("../Test_Case/Paper_Process.xls")
table = workbook.sheet_by_index(0)
# 获取数据
rows_count = self.data.get_case_lines()
print("hangshu",rows_count)
for i in range(30,rows_count):
v1 = int(self.excel.get_cell_value(i,0))
v2=str(self.excel.get_cell_value(i,1))
v3 = int(self.excel.get_cell_value(i, 2))
v4 = str(self.excel.get_cell_value(i, 3))
v5= str(self.excel.get_cell_value(i, 4))
v6 = str(self.excel.get_cell_value(i, 5))
v7 = str(self.excel.get_cell_value(i,6))
v8 = str(self.excel.get_cell_value(i, 7))
v9 = self.excel.get_cell_value(i, 8)
v10 = str(self.excel.get_cell_value(i, 9))
v11 = self.excel.get_cell_value(i, 10)
v12 = str(self.excel.get_cell_value(i, 11))
v13 = str(self.excel.get_cell_value(i, 12))
v14 = self.excel.get_cell_value(i, 13)
v15 = self.excel.get_cell_value(i, 14)
v16 = self.excel.get_cell_value(i, 15)
v17 = self.excel.get_cell_value(i, 16)
sql = "INSERT INTO Paper_Process VALUES(%s,'%s',%s,'%s','%s','%s','%s','%s',%s,'%s','%s','%s','%s','%s','%s','%s','%s')"%(v1,v2,v3,v4,v5,v6,v7,v8,v9,pymysql.escape_string(v10),v11,v12,v13,v14,v15,v16,v17)
print(sql)
# print(sql)
# self.db.execute(sql)
# self.connect.commit()
# if __name__ == '__main__':
# a = exceltodb().read_excel()
def __init__(self):
self.excelpath= "../Test_Case/Paper_Process.xls"
self.db,self.connect = DB_CONNECT(2).connect_db()
self.excel = oper_excel(filename="../Test_Case/Paper_Process.xls")
self.data = Getdata(excelpath=self.excelpath)
def isequalstring(self, expect, result,i):
# expect是预期结果一栏中数据，若是None则不做比较
if expect == None:
print("无预期结果,接口不做比较")
return True
# expect是预期结果一栏中数据，若是Select,则执行sql语句
elif expect.startswith("select"):
print("select",expect)
db = DataBase()
# 执行sql获取返回结果sql_value
sql_value = db.selectvalue(expect)
# expect_compare:获取case表格中sql比对结果的值
ex = Getdata()
expect_compare = ex.get_db_value(i)
if sql_value == [None]:
# print("数据*****", sql_value, type(sql_value),expect_compare)
if expect_compare == None:
print("数据删除成功，数据库无数据")
return True
else:
print("数据库数据不存在，请检查")
return False
else:
try:
expect_compare_len = len(expect_compare)
sql_value_len = len(sql_value)
except Exception as e:
# print("whywhy",expect_compare,sql_value)
print("请检查是否数据库链接错误，没有查到数据",expect_compare,sql_value)
# sql执行结果与预期比较数据中查找共同字段，且比较值是否相等
# 借助辅助数组
compare_dict = {}
for i in range(len(sql_value)):
# print("数据*****",sql_value[i],type(sql_value[i]))
for k in sql_value[i]:
try:
if k in expect_compare:
#相同值添加至辅助数组
compare_dict[k] = expect_compare.get(k)
except Exception as e:
print("没有找到expect",expect_compare,"sql_value",sql_value,"coppare_dict",compare_dict)
# print("数据*****",compare_dict,type(compare_dict),compare_dict,sql_value,sql_value==compare_dict)
# 辅助数组与sql查询数组比较
if operator.eq(compare_dict,sql_value[i]):
#sql查询结果与预期比较结果一致
return True
else:
# print("sql查询数据与预期结果不相等",sql_value,"!=",expect_compare)
return False
else:
# expect是预期结果一栏中数据，若是指定字符，则与请求响应结果比较
if isinstance(result,dict):
result = str(result)
if expect in result:
return True
else:
return False
elif str(result)== expect:
return True
return False
# a = isequal()
# # expect = "select activityId,seasonId,uid from ActivitySeasonUser where seasonId =835 ORDER BY id DESC LIMIT 1"
# # result = {
# # "activityId": 740,
# # "seasonId": 835,
# # "uid": 18213
# # }
# expect = "<Response [302]>"
# result ="<Response [302]>"
# print(a.isequalstring(expect,expect,3))
class Run_case():
# class执行前执行，且一个class只执行一次
# @classmethod
# def setupClass(cls):
# print('class执行前执行')
# # 每个方法执行前执行一次
# def setUp(self):
# print('test前执行')
def __init__(self, excelpath=None, jsonpath=None):
self.tablename = Case_Table().get_tablename()
if excelpath:
self.excelpath = excelpath
else:
self.excelpath = "../Test_Case/" + self.tablename + ".xls"
if jsonpath:
self.jsonpath = jsonpath
else:
self.jsonpath = "../data/" + self.tablename + ".json"
self.run_method = inter()
self.data = Getdata(self.excelpath, self.jsonpath)
self.result = isequal()
self.sendmail = sendmail()
self.page_check = PageCheck()
self.de_data = dependata(self.excelpath, self.jsonpath)
self.caserun = Case(excelpath=self.excelpath, jsonpath=self.jsonpath)
self.opexcel = oper_excel(filename=excelpath)
self.DBCASE = Get_case()
# 学生登陆cookies
def student_cookies(self):
cookies = Get_cookies(2)
return cookies.getcookies_web()
#admin登陆cookies
def admin_cookies(self):
cookies = Get_cookies(1)
return cookies.get_cookies_admin()
# 教师登陆cookies
def teacher_cookies(self):
cookies = Get_cookies(3)
return cookies.getcookies_web()
def go_run(self):
# pass_count = []
# fail_count = []
res = None
# 数据库中获取案例，写至表格中
self.DBCASE.create_excel()
# 获取需执行案例总数量
rows_count = self.data.get_case_lines()
# book = xlrd.open_workbook(filename=self.excelpath, formatting_info=True).sheet_by_index(0)
# # 将上次的执行结果清除
# for i in range(1, rows_count):
# self.opexcel.write_value(i, 13, "")
# self.opexcel.write_value(i, 16, "")
# del book
# print("清除原测试结果数据")
# 排除表头，从-1开始
for i in range(1, rows_count):
caseid = self.data.get_case_id(i)
url = self.data.get_url(i)
functionname = self.data.get_functionname(i)
web_admin = self.data.get_web_admin(i)
method = self.data.get_method(i)
isrun = self.data.get_is_run(i)
data = self.data.get_data_json(i)
file = self.data.get_files(i)
return_value = self.data.get_return_value(i)
# 若url为拼接方式
if "+" in url:
# 则获取到拼接参数
url_data = self.de_data.depend_data(i)
# print("表格里数据",url,url_data)
url = url.split("+")[0] + str(url_data)
# print("拼接后",url,type(url_data),type(str(url_data)))
if file:
files = {"logo1": ("aa.jpg", open(file, "rb"), "image/jpeg")}
else:
files = None
# cookies = self.data.get_cookies(i)
expect = self.data.get_expect(i)
webexpect = self.data.get_web_expectvalu(i)
depen_case = self.data.get_case_depen(i)
depend_data = self.de_data.depend_data(i)
# print("*****第",i,"个",data,depen_data,depen_filed)
# 查看运行状态是否运行
# 判断是否是后台案例
if isrun and web_admin == 0:
# 首先获取cookie,请求代入cookies
cookies = self.admin_cookies()
# 执行案例
self.caserun.case_run(caseid, rows_count, depen_case,
depend_data, url, method, data, cookies,
expect, i, webexpect, functionname,
files, return_value)
# 判断是否是学生端案例
elif isrun and web_admin == 1:
cookies = self.student_cookies()
self.caserun.case_run(caseid, rows_count, depen_case,
depend_data, url, method, data, cookies,
expect, i, webexpect, functionname,
files, return_value)
# 判断是否是教师端案例
elif isrun and web_admin == 2:
cookies = self.teacher_cookies()
self.caserun.case_run(caseid, rows_count, depen_case,
depend_data, url, method, data, cookies,
expect, i, webexpect, functionname,
files, return_value)
class dependata:
def __init__(self,excelpath= None,jsonpath=None):
if excelpath:
self.excelpath = excelpath
else:
self.excelpath = '../Test_Case/TestCase.xls'
if jsonpath:
self.jsonpath = jsonpath
else:
self.jsonpath='..//data//Tc_alldata.json'
self.get = Getdata(self.excelpath,self.jsonpath)
self.operation_excel = oper_excel(self.excelpath)
def depend_data(self,i):
# 判断依赖数据值的获取方式：select?depend[]?请求数据？
col = int(unittesttool.Data_config.get_data_depend())
data = self.operation_excel.get_cell_value(i, col)
# 获取依赖数据的值以及其所属字段
depen_data = self.get.get_data_depen(i)
depen_filed = self.get.get_filed_depen(i)
# 获取请求参数
request_data = self.get.get_data_json(i)
# 若是查询数据库，则直接取出列表中的字典与请求参数合并
if data.startswith("select") or data.startswith("SELECT") or data.startswith("Select"):
# 若是所属字段为No,则不需要合并，反之为None或有值则需要合并
try:
if depen_filed == "No":
return list(depen_data[0].values())[0]
# 若存在数请求数据，则将请求数据与依赖数据合并作为请求数据发送
for i in range(len(depen_data)):
# 若请求参数不是空，则合并
if request_data is not None:
# return dict(data.items()+depen_data[0].items())
# return dict(request_data, **depen_data[0])
data_combin = request_data.copy()
data_combin.update(depen_data[i])
return data_combin
# 反之，则直接返回依赖数据作为请求参数
return depen_data[0]
except Exception as e:
print("请确认数据库查询依赖值是否存在,depen_data=",depen_data,"request_data=",request_data)
elif data.startswith("depend["):
#若是从depend表格中获取，则将依赖数据的值与所属字段相合并组成字典，且与请求参数合并
#拆分依赖数据值，与所属字段相结合
depend_split =list(depen_data.values())[0]
depend_dict = {}
depend_dict[depen_filed] = depend_split
if depen_filed == "No":
return list(depend_dict.values())[0]
# print("组合后所属字段",depend_dict)
# 若请求参数不是空，则合并
if request_data is not None:
data_combin = request_data.copy()
data_combin.update(depend_dict)
return data_combin
# 反之，则直接返回依赖数据作为请求参数
return depend_dict
else:
# 若是从直接给出值，则直接进行两次合并即可
depend_dict = {}
depend_dict[depen_filed] = depen_data
# print("oper+depen**",request_data)
if request_data is not None:
data_combin = request_data.copy()
data_combin.update(depend_dict)
return data_combin
# 反之，则直接返回依赖数据作为请求参数
return depend_dict
class report():
def __init__(self, filename=None, new_filename=None, report_path=None):
self.tablename = Case_Table().get_tablename()
if filename:
self.filename = filename
else:
self.filename = "../Test_Case/" + self.tablename + ".xls"
if new_filename:
self.new_filename = new_filename
else:
self.new_filename = "../Test_Case/" + self.tablename + "_bk.xls"
if report_path:
self.report_path = report_path
else:
self.report_path = "../Test_Case/TestCase.html"
self.data = Getdata()
def order_excel(self):
read_excel = xlrd.open_workbook(self.filename)
# 借助辅助数组，rows_mark
rows_mark = []
#遍历是否执行列数据
rows_count = self.data.get_case_lines()
for i in range(1, rows_count):
isrun = self.data.get_is_run(i)
#如果不执行，则将该行数记录
if isrun == False:
rows_mark.append(i)
return rows_mark
#生成报告
def creat_report(self):
# 获取不执行案例所在行数
rows = self.order_excel()
#读取测试案例表格文件，跳过不执行的行数
xd = pd.read_excel(self.filename,
skiprows=rows,
usecols=[0, 1, 4, 12, 13, 15, 16]) # 指定读取列
# print(xd)
#摘取所需要内容生成新的xls文件
xd.to_excel(self.new_filename, index=False)
#将excel转换为html
to_html = pd.ExcelFile(self.new_filename)
pd.set_option('display.max_colwidth', 1000) # 设置列的宽度，以防止出现省略号
df = to_html.parse()
with codecs.open(self.report_path, 'w') as html_file:
html_file.write(
df.to_html(header=True, index=False, table_id="Test_Report"))
# 将多余文件删除
os.remove(self.new_filename)
#自动打开报告文件
# webbrowser.open(self.report_path)
# 案例表格、数据文件路径
# case_excelpath = "..//Test_Case//Paper_Process.xls"
# data_jsonpath="../data/Paper_Process.json"
# # # 定义报告文件
# excel_copy = "..//Test_Case//Paper_Process_bk.xls"
# html_path = "../Test_Case/Paper_Process.html"
# a = report(case_excelpath,excel_copy,html_path)
# a.creat_report()
def __init__(self, excelpath, file_path):
self.data = Getdata(excelpath=excelpath)
self.opexcel = oper_excel(filename=excelpath)
self.file_path = file_path