def get_request_data(self,row):
'''获取请求数据
'''
operate_json = OperateJson()
col = self.global_val.request_data
request_key = self.excel.get_cell(row,col)
request_data = None
if request_key != '':
request_data = operate_json.get_data(request_key)
for key in request_data:
if not isinstance(request_data[key],str): # 如果value不是str类型
request_data[key] = json.dumps(request_data[key]) # 将value转换成str类型
return request_data
def get_case_data(self, row):
data4json = self.get_data_key(row)
if data4json == '':
request_data = None
else:
request_data = OperateJson().get_data(data4json)
return request_data
def get_case_header(self, row, url, request_data):
"""
:param row:
:param url:
:param request_data:
:return: 若header为write，则写入cookie，返回None，若为yes则获取cookie，若为no则返回none
"""
header_col = get_header()
header = self.excel.get_cell_value(row, header_col)
if header == "write":
op_header = OperateHeader()
op_header.write_cookies(url=url, data=request_data)
return None
elif header == "yes":
op_json = OperateJson(r"../dataconfigure/cookie.json")
value = op_json.get_data("sessionid")
cookie = {"sessionid": value}
return cookie
else:
return None
class RunExcelTestcases(object):
def __init__(self):
self.gtc = GetExcelTestcases()
self.ir = IntergrateRequest()
self.ec = EmailConfig()
self.oj = OperateJson()
self.cs = CompareStr()
# 执行测试用例
def run_testcases(self):
# 定义空列表，存放执行成功和失败的测试用例
pass_lists = []
fail_lists = []
no_execute_lists = []
# no_execute_case_name = []
# 获取总的用例条数
cases_num = self.gtc.get_cases_num()
# 遍历执行每一条测试用例
for case in range(1, cases_num):
# 用例是否执行
is_run = self.gtc.get_is_run(case)
# print("is_run: ", is_run)
# 接口的请求方式
method = self.gtc.get_method(case)
# 请求测试接口
url = self.gtc.get_url(case)
# 要请求的数据
data = self.gtc.get_payload(case)
# 取出 header
if case == 1:
header = None
else:
header = self.oj.get_json()
# 获取预期结果值 expected_result
expected_result = self.gtc.get_expected_result(case)
if is_run is True:
res = self.ir.main_req(method, url, data, header)
if self.cs.is_contain(expected_result, res):
self.gtc.write_actual_result(case, 'pass')
pass_lists.append(case)
else:
self.gtc.write_actual_result(case, res)
fail_lists.append(case)
else:
no_execute_lists.append(case)
print("没有执行的测试用例有, 按序号有：", no_execute_lists)
self.ec.send_mail(pass_lists, fail_lists, no_execute_lists)
print("....邮件已发送成功...")
def read_cookies(self):
'''从json文件中读取cookies
'''
ope_json = OperateJson(self.cookies_file)
cookies = ope_json.read_json()
return cookies
def __init__(self):
"""yaml文件中读取相关配置"""
self.oy = OperateYaml()
self.oj = OperateJson()
class OperateMySQL(object):
def __init__(self):
"""yaml文件中读取相关配置"""
self.oy = OperateYaml()
self.oj = OperateJson()
def conn_db(self, goal_db):
"""
通过传入的字符串判定连接的数据库配置
:param goal_db:
:return:
"""
if goal_db.startswith("ysy_test") or goal_db.startswith(
"ysy_t_property"):
self.dbhost = self.oy.read_yaml()['db']['ysy_test']['db_host']
self.dbport = self.oy.read_yaml()['db']['ysy_test']['db_port']
self.dbname = self.oy.read_yaml()['db']['ysy_test']['db_name']
self.user = self.oy.read_yaml()['db']['ysy_test']['user']
self.pwd = self.oy.read_yaml()['db']['ysy_test']['pwd']
elif goal_db.startswith("ysy_official"):
self.dbhost = self.oy.read_yaml()['db']['ysy_official']['db_host']
self.dbport = self.oy.read_yaml()['db']['ysy_official']['db_port']
self.dbname = self.oy.read_yaml()['db']['ysy_official']['db_name']
self.user = self.oy.read_yaml()['db']['ysy_official']['user']
self.pwd = self.oy.read_yaml()['db']['ysy_official']['pwd']
elif goal_db.startswith("property_bg_test"):
self.dbhost = self.oy.read_yaml(
)['db']['property_bg_test']['db_host']
self.dbport = self.oy.read_yaml(
)['db']['property_bg_test']['db_port']
self.dbname = self.oy.read_yaml(
)['db']['property_bg_test']['db_name']
self.user = self.oy.read_yaml()['db']['property_bg_test']['user']
self.pwd = self.oy.read_yaml()['db']['property_bg_test']['pwd']
def formate_sql(self, sql_str):
'''
该方法暂时不使用，被re_sql代替
:param sql_str:
:return:
'''
if '%%%(' in sql_str:
sql_list = sql_str.split("%%%(")
sql_vars = sql_list[1].split(",")
temp_str = ''
sql_var_list = []
for i in sql_vars:
value = self.oj.get_json_value(i)
temp_str = temp_str + "," + value
temp_str = str(temp_str.strip(",")) # 去掉左右两边的逗号
return sql_list[0].format(temp_str)
else:
return sql_str
def execute_sql(self, conn_str, sql_str):
'''
连接并创建游标,执行sql,返回结果
:return:
'''
self.conn_db(conn_str)
db = pymysql.connect(host=self.dbhost,
port=self.dbport,
user=self.user,
passwd=self.pwd,
db=self.dbname,
charset='utf8')
cursor = db.cursor() # 创建一个游标
exe_sql = self.re_sql(sql_str)
cursor.execute(exe_sql)
data = cursor.fetchone()
return data[0]
db.close() # 关闭数据库
def deal_sql2(self, conn, sql_str):
"""
去掉该方法
:param conn: 指定连接的数据库
:param sql_str: 执行的语句
:return:
"""
sql_result = self.execute_sql(conn, sql_str)
return sql_result
def sql_main(self, dbcon, preset):
"""
sql处理入口：将sql进行有序分割；且执行对应sql得到返回值后，调用写入json方法
:param dbcon:连接指定db
:param preset:sql语句
:return: 写入json，无返回值
"""
preset_l = preset.split(";")
for i in preset_l:
preset_list = i.split("$$")
#這裏dbcon值需要轉化
preset_value = self.execute_sql(dbcon, preset_list[1])
self.oj.write_json_value(preset_list[0], preset_value)
def re_sql(self, var_str):
"""
处理str中包含了变量的sql
:param str:可能包含了formate的字符串
:return:不包含了formate的字符串
"""
if 'formate' in var_str:
p1 = re.compile(r"[(](.*?)[')]", re.S) # 非贪心匹配
split_str = var_str.split('formate')
var_1 = re.findall(p1, split_str[1])
#这里会对list中每个值进行判断
var_1 = self.list_value_y_j(var_1)
# 注意这里只传递了第一个格式化值进来
sql_resutl = split_str[0].format(*var_1)
return sql_resutl
else:
return var_str
def list_value_y_j(self, list_var):
"""
将传入的list，遍历每个值，处理后，以list形式再度返回
:param list_var:
:return:
"""
temp_list = []
for i in list_var:
if i.startswith("j::"): #如果全局变量中值和key有差异，使用这个特殊处理
temp = i.split("::")[1]
i = self.oper_json.get_json_value(temp)
elif i.startswith("y::"):
temp = i.split("::")[1]
i = self.oy.read_main(temp)
temp_list.append(i)
return temp_list
def write_cookies(self, url, data):
cookie_jar = self.get_cookies(url, data)
# 转换成字典
cookies = requests.utils.dict_from_cookiejar(cookie_jar)
op_json = OperateJson()
op_json.write_json(cookies)
def get_case_expect(self, row):
expect4json = self.get_expect_key(row)
expect = OperateJson(r"../dataconfigure/expect.json").get_data(
expect4json)
return expect
def __init__(self):
self.gtc = GetExcelTestcases()
self.ir = IntergrateRequest()
self.ec = EmailConfig()
self.oj = OperateJson()
self.cs = CompareStr()