def write_all_back(self, result_data, test_result_data, file_name=None):
if file_name is None:
wb = load_workbook(test_case_path)
else:
wb = load_workbook(base_path + 'test_data' + file_name)
excel_row = eval(ReadConfig().get_config(case_config_path, 'Excel_row',
'excel_row'))
mode = eval(ReadConfig.get_config(case_config_path, 'Mode', 'mode'))
for key in mode:
sheet = wb[key]
if mode[key] == 'all':
list_range = range(1, sheet.max_row)
else:
list_range = mode[key]
k = 0
for i in list_range:
i = i + 1
sheet.cell(i, excel_row['result']).value = result_data[k]
sheet.cell(
i, excel_row['test_result']).value = test_result_data[k]
k = k + 1
if file_name is None:
wb.save(test_case_path)
else:
wb.save(base_path + 'test_data' + file_name)
def get_data(self, file_name=None):
global new_data
excel_row = eval(ReadConfig().get_config(case_config_path, 'Excel_row',
'excel_row'))
if file_name is None:
wb = load_workbook(test_case_path)
else:
wb = load_workbook(base_path + 'test_data' + file_name)
mode = eval(ReadConfig.get_config(case_config_path, 'Mode', 'mode'))
test_data = []
for key in mode:
# print(key)
sheet = wb[key]
if mode[key] == 'all':
list_range = range(1, sheet.max_row)
else:
list_range = mode[key]
for i in list_range:
i = i + 1
new_data = sheet.cell(i, excel_row['data']).value
if sheet.cell(i,
excel_row['data']).value.find('${admin}') != -1:
new_admin = getattr(GetAdmin, 'admin') + str_time_mdhms
setattr(GetAdmin, 'admin', new_admin)
new_data = DoRegx().do_regx(
sheet.cell(i, excel_row['data']).value, GetAdmin)
if sheet.cell(
i, excel_row['data']).value.find('${student}') != -1:
new_student = getattr(GetStudent,
'student') + str_time_mdhms
setattr(GetStudent, 'student', new_student)
new_data = DoRegx().do_regx(
sheet.cell(i, excel_row['data']).value, GetStudent)
if sheet.cell(
i, excel_row['data']).value.find('${teacher}') != -1:
new_student = getattr(GetTeacher,
'teacher') + str_time_mdhms
setattr(GetTeacher, 'teacher', new_student)
new_data = DoRegx().do_regx(
sheet.cell(i, excel_row['data']).value, GetTeacher)
row_data = {
'caseid': sheet.cell(i, excel_row['caseid']).value,
'url': sheet.cell(i, excel_row['url']).value,
'data': eval(new_data),
'title': sheet.cell(i, excel_row['title']).value,
'method': sheet.cell(i, excel_row['method']).value,
'data_type': sheet.cell(i, excel_row['data_type']).value,
'expect': sheet.cell(i, excel_row['expect']).value,
'test_result': sheet.cell(i,
excel_row['test_result']).value,
'test_sql': sheet.cell(i, excel_row['test_sql']).value,
'sheet_name': key
}
test_data.append(row_data)
return test_data
def get_data(cls, file_name):
wb = load_workbook(file_name)
mode = eval(ReadConfig().read_config(case_config_path, 'MODE',
'mode')) # 配置文件的内容 字典
test_data = []
for key in mode: # 遍历这个存在配置文件里面的字典 也就是表单名
sheet = wb[key] # 打开Excel里的这个表单
# 跑全部用例
if mode[key]["焕商分佣"] == 'all': # 判断value
for i in range(2, sheet.max_row + 1):
row_data = {}
row_data['case_id'] = sheet.cell(i, 1).value
row_data['title'] = sheet.cell(i, 2).value
row_data['member_level'] = sheet.cell(i, 3).value
row_data['buyer_identity'] = sheet.cell(i, 4).value
row_data['seller_identity'] = sheet.cell(i, 5).value
row_data['payment_method'] = sheet.cell(i, 6).value
row_data['goodsname'] = sheet.cell(i + 1, 7).value
row_data['data'] = sheet.cell(i, 8).value
row_data['operational_setting'] = sheet.cell(i, 9).value
row_data['proportion'] = sheet.cell(i, 10).value
row_data['superior'] = sheet.cell(i, 11).value
row_data['order'] = sheet.cell(i, 12).value
row_data['reserve_fund'] = sheet.cell(i, 13).value
row_data['bind_relationship_data'] = sheet.cell(i,
14).value
row_data['second_payagent_ratio'] = sheet.cell(i, 15).value
row_data['sheet_name'] = key
row_data['surroundings'] = mode[key]["环境"]
row_data['payPassword'] = mode[key]["支付密码"]
test_data.append(row_data)
# 跑指定用例
else:
for i in mode[key]["焕商分佣"]: # case_id
row_data = {}
row_data['case_id'] = sheet.cell(i + 1, 1).value
row_data['title'] = sheet.cell(i + 1, 2).value
row_data['member_level'] = sheet.cell(i + 1, 3).value
row_data['buyer_identity'] = sheet.cell(i + 1, 4).value
row_data['seller_identity'] = sheet.cell(i + 1, 5).value
row_data['payment_method'] = sheet.cell(i + 1, 6).value
row_data['goodsname'] = sheet.cell(i + 1, 7).value
row_data['data'] = sheet.cell(i + 1, 8).value
row_data['operational_setting'] = sheet.cell(i + 1,
9).value
row_data['proportion'] = sheet.cell(i + 1, 10).value
row_data['superior'] = sheet.cell(i + 1, 11).value
row_data['order'] = sheet.cell(i + 1, 12).value
row_data['reserve_fund'] = sheet.cell(i + 1, 13).value
row_data['bind_relationship_data'] = sheet.cell(i + 1,
14).value
row_data['second_payagent_ratio'] = sheet.cell(i + 1,
15).value
row_data['sheet_name'] = key
row_data['surroundings'] = mode[key]["环境"]
row_data['payPassword'] = mode[key]["支付密码"]
test_data.append(row_data)
return test_data
def get_data(file_name):
wb = load_workbook(file_name)
mode = eval(ReadConfig.read_config(test_config_path, 'MODE', 'mode'))
test_data = []
for key in mode: #遍历配置文件中的字典（键）
sheet = wb[key]
if mode[key] == 'all':
for i in range(2, sheet.max_row + 1):
row_data = {}
row_data['case_id'] = sheet.cell(i, 1).value
row_data['method'] = sheet.cell(i, 2).value
row_data['url'] = sheet.cell(i, 3).value
row_data['data'] = sheet.cell(i, 4).value
row_data['expected'] = sheet.cell(i, 5).value
row_data['sheet_name'] = sheet.cell(i, 6).value
test_data.append(row_data)
else:
for case_id in mode[key]:
row_data = {}
row_data['case_id'] = sheet.cell(case_id + 1, 1).value
row_data['method'] = sheet.cell(case_id + 1, 2).value
row_data['url'] = sheet.cell(case_id + 1, 3).value
row_data['data'] = sheet.cell(case_id + 1, 4).value
row_data['expected'] = sheet.cell(case_id + 1, 5).value
row_data['sheet_name'] = sheet.cell(case_id + 1, 6).value
test_data.append(row_data)
return test_data
def get_data(file_name):
wb = load_workbook(file_name)
mode = eval(ReadConfig().read_config(case_config_path, 'MODE',
'mode')) # 配置文件的内容 字典
test_data = []
for key in mode: # 遍历这个存在配置文件里面的字典 也就是表单名
sheet = wb[key] # 打开Excel里的这个表单
if mode[key] == 'all': # 判断value
for i in range(2, sheet.max_row + 1):
row_data = {}
row_data['case_id'] = sheet.cell(i, 1).value
row_data['title'] = sheet.cell(i, 2).value
row_data['url'] = sheet.cell(i, 3).value
row_data['data'] = sheet.cell(i, 4).value
row_data['http_method'] = sheet.cell(i, 5).value
row_data['expected_code'] = str(sheet.cell(i, 6).value)
row_data['sheet_name'] = key
test_data.append(row_data)
else:
for case_id in mode[key]:
row_data = {}
row_data['case_id'] = sheet.cell(case_id + 1, 1).value
row_data['title'] = sheet.cell(case_id + 1, 2).value
row_data['url'] = sheet.cell(case_id + 1, 3).value
row_data['data'] = sheet.cell(case_id + 1, 4).value
row_data['http_method'] = sheet.cell(case_id + 1, 5).value
row_data['expected_code'] = str(
sheet.cell(case_id + 1, 6).value)
row_data['sheet_name'] = key
test_data.append(row_data)
return test_data
def do_mysql(self, query_sql, state='all'):
# 数据库连接信息
db_config = eval(ReadConfig().get_config(test_config_path, 'DB', 'db_config'))
# 创建数据库连接
cnn = mysql.connector.connect(**db_config)
# 创建游标
cursor = cnn.cursor()
# sql语句(做成配置)
# query_sql = "select * from member where MobilePhone=13700000356"
# 执行语句
cursor.execute(query_sql)
# 获取结果，打印结果
if state == 1:
res = cursor.fetchone()
else:
res = cursor.fetchall()
# print(res)
# 关闭游标
cursor.close()
# 关闭连接
cnn.close()
return res
def read_excel(self):
mode = eval(ReadConfig().read_config(config_path, 'MODE', 'mode'))
wb = openpyxl.load_workbook(test_data_path)
test_data=[]
for key in mode:
sheet = wb[key]
if mode[key] == 'all':
for row in range(2, sheet.max_row + 1):
each_data = {}
for col in range(1, sheet.max_column + 1):
each_data[sheet.cell(1, col).value] = sheet.cell(row, col).value
if each_data['data'].find("${name}") != -1:
name = str(wb['init'].cell(2, 1).value)
each_data['data'] = each_data['data'].replace('${name}', name)
wb['init'].cell(2, 1).value += 1
wb.save(test_data_path)
else:
each_data['data'] = each_data['data']
each_data['sheet_name']=key
test_data.append(each_data)
else:
for case_id in mode[key]:
each_data = {}
for col in range(1, sheet.max_column + 1):
each_data[sheet.cell(1, col).value] = sheet.cell(case_id+1, col).value
each_data['sheet_name'] = key
test_data.append(each_data)
return test_data
def __init__(self, file_name):
self.file_name = file_name
# self.sheet_name = sheet_name
self.workbook = openpyxl.load_workbook(file_name)
# self.sheet = self.workbook[sheet_name]
# self.max_row = self.sheet.max_row
# self.max_column = self.sheet.max_column
self.mode = eval(ReadConfig().read_config(
self.case_path, "MODE", "mode")) #测试用例的匹配模式，运行哪些用例的哪些条
def test_dele_menu(self, login):
# LoginPage(AccessBrowser).login("yang", "123456", "abcd")
#删除菜单并没有真正的删除，知识将parentid改为999
MenuManagement(login).delete_menu()
menu_name = ReadConfig().read_config_as_section(
test_config_path, "menu")['menu_name']
sql = f"select parent_id from menu where menu_name = '{menu_name}'"
parent_id = DB().do_sql(sql, 1)
#不知道为什么返回不是999，而是原来的值，再执行一次才会改变
assert parent_id[0] == 999
def dele_menu_but(self):
menu_name = ReadConfig().read_config_as_section(
test_config_path, "menu")['menu_name']
sql = f"select menu_id from menu where menu_name = '{menu_name}'"
menu_id = DB().do_sql(sql, 1)
#这个引号嵌套不好写，写不顺
# del_but = "(By.XPATH,'//td[@class=\"\'menuid\'\"][text()=f\"\'{menu_id}\'\"]/../td/a[contains(@href,\"\'delete\'\")]')"
# MyLog().info(f"del_but是{del_but}")
return menu_id
def common():
global driver
url = ReadConfig().read_config(test_config_path, "project", "url")
# 启动浏览器前置
MyLog().info("###########################")
MyLog().info("启动浏览器")
driver = webdriver.Chrome()
MyLog().info(f"driver.get({url})")
MyLog().info(type(url))
driver.get(url)
driver.maximize_window()
def do_mysql(query_sql,
state='all'): # query_sql——>查询语句 state——>all 多条 ; 1 一条
db_config = eval(
ReadConfig().get_config(project_path.test_config_path, 'DB',
'db_config')) # 利用这个类从配置文件里面读取db info
cnn = mysql.connector.connect(**db_config) # 创建一个数据库连接
cursor = cnn.cursor() # 游标cursor
cursor.execute(query_sql) # 执行语句
if state == 1:
res = cursor.fetchone() # 元组 针对一条数据
else:
res = cursor.fetchall() # 列表 针对多行数据 列表嵌套元组
cursor.close() # 关闭游标
cnn.close() # 关闭连接
return res
class GetData:
Cookie = None
loan_id = None
check_list = eval(ReadConfig().get_config(project_path.test_config_path, 'CHECKLEAVEAMOUNT', 'check_list'))
# NoRegTel = pd.read_excel(test_case_path, sheet_name='init').ix[0,0] # 利用pandas取值
# normal_tel = pd.read_excel(test_case_path, sheet_name='init').ix[1,0] # 利用pandas取值
# admin_tel = pd.read_excel(test_case_path, sheet_name='init').ix[2,0] # 利用pandas取值
# loan_member_id = pd.read_excel(test_case_path, sheet_name='init').ix[3,0] # 利用pandas取值
# memberID = pd.read_excel(test_case_path, sheet_name='init').ix[4,0] # 利用pandas取值
NoRegTel = Gettel.get_tel(project_path.test_case_path, 'init', 2, 1) # 获取excel中的值
normal_tel = Gettel.get_tel(project_path.test_case_path, 'init', 3, 1)
admin_tel = Gettel.get_tel(project_path.test_case_path, 'init', 4, 1)
loan_member_id = Gettel.get_tel(project_path.test_case_path, 'init', 5, 1)
memberID = Gettel.get_tel(project_path.test_case_path, 'init', 6, 1)
def get_data(cls, file_name):
wb = load_workbook(file_name) # linux命令
mode = eval(
ReadConfig.get_config(project_path.case_config_path, 'MODE',
'mode'))
tel = getattr(GetData, "NoRegTel") #从GetData拿到的手机号
test_data = []
for key in mode: #遍历这个存在配置文件里面的字典
sheet = wb[key] #表单名
if mode[key] == 'all':
for i in range(2, sheet.max_row + 1):
row_data = {} #字典
row_data["case_id"] = sheet.cell(i, 1).value
row_data["url"] = sheet.cell(i, 2).value
row_data["data"] = sheet.cell(i, 3).value
# if sheet.cell(i,3).value.find("${tel_1}")!=-1:
# row_data["data"] = sheet.cell(i, 3).value.replace("${tel_1}",tel)
# elif sheet.cell(i,3).value.find("${tel}")!=-1:
# row_data["data"] = sheet.cell(i, 3).value.replace("${tel_1}",tel)
# else:
# row_data["data"]=sheet.cell(i,3).value
row_data["title"] = sheet.cell(i, 4).value
row_data["http_method"] = sheet.cell(i, 5).value
row_data["excepted"] = sheet.cell(i, 6).value
row_data["sheet_name"] = key
test_data.append(row_data)
# cls.updata_tel(tel+2,file_name,"init")#更新手机号
# cls.updata_tel(tel,file_name,"init")#更新手机号
else:
for case_id in mode[key]:
row_data = {} # 字典
row_data["case_id"] = sheet.cell(case_id + 1, 1).value #行号
row_data["url"] = sheet.cell(case_id + 1, 2).value
# row_data["data"] = sheet.cell(case_id+1, 3).value
if sheet.cell(i, 3).value.find("${tel_1}") != -1:
row_data["data"] = sheet.cell(i, 3).value.replace(
"${tel_1}", tel)
elif sheet.cell(i, 3).value.find("${tel}") != -1:
row_data["data"] = sheet.cell(i, 3).value.replace(
"${tel_1}", tel)
else:
row_data["data"] = sheet.cell(i, 3).value
row_data["title"] = sheet.cell(case_id + 1, 4).value
row_data["http_method"] = sheet.cell(case_id + 1, 5).value
row_data["excepted"] = sheet.cell(case_id + 1, 6).value
test_data.append(row_data)
cls.updata_tel(tel + 2, file_name, "init") #更新手机号
return test_data
def test_api(self,
item,
result_list=result_list,
test_result_list=test_result_list):
print("正在执行的用例是{0}".format(item['title']))
excel_row = eval(ReadConfig().get_config(case_config_path, 'Excel_row',
'excel_row'))
if item['title'] == '修改管理员':
newid = getattr(GetAdminId, 'Adminid')
item['data']['id'] = newid
res = HttpRequests(item['url'],
item['method'],
data=item['data'],
cookies=getattr(GetCookie,
'Cookie')).send_request('json')
if res.cookies:
setattr(GetCookie, 'Cookie', res.cookies)
if str(res.json()).find('id') != -1 and item['title'] == '添加管理员':
setattr(GetAdminId, 'Adminid', res.json()['id'])
if item['test_sql'] and item['test_sql'].find('${admin}') != -1:
item['test_sql'] = item['test_sql'].replace(
'${admin}', getattr(GetAdmin, 'admin'))
try:
if item['test_sql'] is not None:
sql_result = HandleSqlite().select_sql(
eval(item['test_sql'])['sql'])
if sql_result:
self.assertEqual(sql_result[0][0], res.json()['id'])
self.assertEqual(int(res.json()['ret']), item['expect'])
test_result = 'pass'
except AssertionError as e:
test_result = 'fail'
initLogging(log_path, e)
except Exception as e:
print('执行了Exception')
test_result = 'fail'
initLogging(log_path, e)
finally:
# DoExcel().write_back(item['caseid'] + 1, excel_row['result'], str(res.json()),
# sheet_name=item['sheet_name'])
# DoExcel().write_back(item['caseid'] + 1, excel_row['test_result'], test_result,
# sheet_name=item['sheet_name'])
str_json = str(res.json())
result_list.append(str_json)
test_result_list.append(test_result)
def getCaseDataFromExcel(cls, file_name, sheet_name):
wb = load_workbook(file_name)
mode = ReadConfig().read_config(case_config_path) # 配置文件的内容 字典
test_data = []
# for key in mode: # 遍历这个存在配置文件里面的字典 也就是表单名
sheet = wb[sheet_name] # 打开Excel里的这个表单
if mode[sheet_name] == ['all']: # 判断value
for i in range(2, sheet.max_row + 1):
row_data = cls.row_data(sheet, i, sheet_name)
test_data.append(row_data)
elif mode[sheet_name] == []:
pass
else:
for case_id in mode[sheet_name]:
row_data = cls.row_data(sheet, case_id + 1, sheet_name)
test_data.append(row_data)
return test_data
def do_mysql(self, query, state='all'): #query-->查询语句
config = eval(ReadConfig.read_config(test_config_path, 'DB', 'config'))
#创建一个数据库连接
cnn = mysql.connector.connect(**config)
#游标cursor
cursor = cnn.cursor()
#执行语句
cursor.execute(query)
#获取结果 打印结果
if state == 1:
res = cursor.fetchone() #针对一条数据 返回的是元组
else:
res = cursor.fetchall() #针对多条数据 返回的是列表
#关闭游标
cursor.close()
#关闭连接
cnn.close()
return res
def get_data_notice(cls, file_name):
wb = load_workbook(file_name)
# mode = eval(ReadConfig.get_config(project_path.case_config_path, "MODE", "mode"))
mode = eval(
ReadConfig.get_config(project_path.case_config_path, "MODE",
"mode"))
test_data = []
mode_key = set(mode)
for key in mode_key:
if key == "notice":
sheet = wb[key]
if mode[key] == "all":
for i in range(2, sheet.max_row + 1):
row_data = {} # 字典
row_data["case_id"] = sheet.cell(i, 1).value
row_data["model"] = sheet.cell(i, 2).value
row_data["title"] = sheet.cell(i, 3).value
row_data["url"] = sheet.cell(i, 4).value
row_data["params"] = sheet.cell(i, 5).value
row_data["header"] = sheet.cell(i, 6).value
row_data["http_method"] = sheet.cell(i, 7).value
row_data["data"] = sheet.cell(i, 8).value
row_data["excepted_code"] = sheet.cell(i, 9).value
row_data["sheet_name"] = key
test_data.append(row_data)
else:
for case_id in mode[key]:
row_data = {} # 字典
row_data["case_id"] = sheet.cell(case_id + 1,
1).value # 行号
row_data["model"] = sheet.cell(case_id + 1, 2).value
row_data["title"] = sheet.cell(case_id + 1, 3).value
row_data["url"] = sheet.cell(case_id + 1, 4).value
row_data["params"] = sheet.cell(case_id + 1, 5).value
row_data["header"] = sheet.cell(case_id + 1, 6).value
row_data["http_method"] = sheet.cell(case_id + 1,
7).value
row_data["data"] = sheet.cell(case_id + 1, 8).value
row_data["excepted_code"] = sheet.cell(case_id + 1,
9).value
test_data.append(row_data)
return test_data
def send():
# html报告地址
with open(test_report_path, 'r', encoding="utf-8") as f:
content_html = f.read()
mail_config = eval(ReadConfig.get_config(mail_config_path, 'Mail', 'mail'))
mail = {
'subject': mail_config['subject'], # Anything you want.
'content_text': mail_config['content_text'], # Anything you want.
# 'content_html': content_html, # Body html
'attachments': mail_config['attachments'], # Absolute path will be better.
}
server = zmail.server(mail_config['sender'],
mail_config['auth'],
smtp_host=mail_config['smtp_host'],
smtp_port=mail_config['smtp_port'])
server.send_mail(mail_config['receiver'], mail, cc=mail_config['cc'])
def get_data(file_name):
wb = load_workbook(file_name)
mode = eval(ReadConfig.get_config(test_config_path, 'MODE', 'mode'))
test_data = []
for key in mode:
sheet = wb[key]
if mode[key] == 'all':
for i in range(2, sheet.max_row + 1):
row_data = dict()
row_data['case_id'] = sheet.cell(i, 1).value
row_data['url'] = sheet.cell(i, 2).value
# if sheet.cell(i, 3).value.find('${tel}') != -1:
# row_data['data'] = sheet.cell(i, 3).value.replace('${tel}', str(tel))
# tel = tel + 1
# elif sheet.cell(i, 3).value.find('${admin_tel}') != -1:
# row_data['data'] = sheet.cell(i, 3).value.replace('${admin_tel}', str(getattr(GetData, 'admin_tel')))
# elif sheet.cell(i, 3).value.find('${loan_member_id}') != -1:
# row_data['data'] = sheet.cell(i, 3).value.replace('${loan_member_id}', str(getattr(GetData, 'loan_member_id')))
# elif sheet.cell(i, 3).value.find('${normal_tel}') != -1:
# row_data['data'] = sheet.cell(i, 3).value.replace('${normal_tel}', str(getattr(GetData, 'normal_tel')))
# elif sheet.cell(i, 3).value.find('${memberID}') != -1:
# row_data['data'] = sheet.cell(i, 3).value.replace('${memberID}', str(getattr(GetData, 'memberID')))
# else:
# row_data['data'] = sheet.cell(i, 3).value
row_data['data'] = sheet.cell(i, 3).value
row_data['title'] = sheet.cell(i, 4).value
row_data['http_method'] = sheet.cell(i, 5).value
row_data['header'] = sheet.cell(i, 6).value
row_data['sheet_name'] = key
test_data.append(row_data)
else:
for case_id in mode[key]:
row_data = dict()
row_data['case_id'] = sheet.cell(case_id + 1, 1).value
row_data['url'] = sheet.cell(case_id + 1, 2).value
row_data['data'] = sheet.cell(case_id + 1, 3).value
row_data['title'] = sheet.cell(case_id + 1, 4).value
row_data['http_method'] = sheet.cell(case_id + 1, 5).value
row_data['header'] = sheet.cell(case_id + 1, 6).value
row_data['sheet_name'] = key
test_data.append(row_data)
return test_data
def do_mysql(self,query_sql,state=1):
db_config = eval(ReadConfig().get_config(project_path.case_config_path,"DB","db_config"))
cnn = pymysql.connect(**db_config)
#游标cursor
cursor=cnn.cursor()
#写sql语句
#执行语句
cursor.execute(query_sql)
if state==1:
res = cursor.fetchone() # 元组 针对一条数据
# print(res)#获取结果，打印结果
else:
res=cursor.fetchall() #列表 针对多行数据 列表嵌套元组
# print(res)
#关闭游标
cursor.close()
#关闭连接
cnn.close()
return res
def get_data(self):
wb = load_workbook(self.file_name)
# sheet = wb[self.sheet_name]
#读取配置文件只
mode = eval(ReadConfig.get_config(case_config_path, 'MODE', 'mode'))
test_data = []
for key in mode: #遍历存在这个字典里面的字典
sheet = wb[key]
if mode[key] == 'all':
for i in range(2, sheet.max_row + 1):
sub_data = {}
sub_data["case_id"] = sheet.cell(i, 1).value
sub_data["module"] = sheet.cell(i, 2).value
sub_data["title"] = sheet.cell(i, 3).value
sub_data["method"] = sheet.cell(i, 4).value
sub_data["url"] = sheet.cell(i, 5).value
sub_data["data"] = sheet.cell(i, 6).value
sub_data["expected"] = sheet.cell(i, 7).value
sub_data["sheet_name"] = key
test_data.append(sub_data)
else:
for case_id in mode[key]:
i = case_id + 1 #行号
sub_data = {}
sub_data["case_id"] = sheet.cell(i, 1).value
sub_data["module"] = sheet.cell(i, 2).value
sub_data["title"] = sheet.cell(i, 3).value
sub_data["method"] = sheet.cell(i, 4).value
sub_data["type"] = sheet.cell(i, 5).value #5
sub_data["url"] = sheet.cell(i, 6).value
sub_data["data"] = sheet.cell(i, 7).value
sub_data["get_value"] = sheet.cell(i, 8).value #8
sub_data["expected"] = sheet.cell(i, 9).value
sub_data["sheet_name"] = key
test_data.append(sub_data)
return test_data
def get_data(cls,file_name):
wb = load_workbook(file_name)
mode = eval(ReadConfig.get_config(project_path.case_config_path,"MODE","mode"))
tel =getattr(GetData,"NoRegTel")
test_data= []
for key in mode:#遍历这个存在配置文件里面的字典
sheet = wb[key]#表单名
if mode[key]=="all":
for i in range(2,sheet.max_row+1):
row_data = {}#字典
row_data["case_id"] = sheet.cell(i, 1).value
row_data["model"] = sheet.cell(i, 2).value
row_data["title"] = sheet.cell(i, 3).value
row_data["url"] = sheet.cell(i, 4).value
row_data["params"] = sheet.cell(i, 5).value
row_data["header"] = sheet.cell(i, 6).value
row_data["http_method"] = sheet.cell(i, 7).value
row_data["data"] = sheet.cell(i, 8).value
row_data["excepted_code"] = sheet.cell(i, 9).value
row_data["sheet_name"] = key
test_data.append(row_data)
else:
for case_id in mode[key]:
row_data = {} # 字典
row_data["case_id"] = sheet.cell(case_id+1, 1).value#行号
row_data["model"] = sheet.cell(case_id+1, 2).value
row_data["title"] = sheet.cell(case_id+1, 3).value
row_data["url"] = sheet.cell(case_id+1, 4).value
row_data["params"] = sheet.cell(case_id+1, 5).value
row_data["header"] = sheet.cell(case_id+1, 6).value
row_data["http_method"] = sheet.cell(case_id+1, 7).value
row_data["data"] = sheet.cell(case_id+1, 8).value
row_data["excepted_code"] = sheet.cell(case_id+1, 9).value
test_data.append(row_data)
# cls.updata_tel(tel+2,file_name,"init")#更新手机号
return test_data
def get_data(file_name):
wb = load_workbook(file_name)
mode = eval(
ReadConfig.get_config(project_path.case_config_path, 'MODE',
'mode'))
# name="self:261993005056/123"
test_data = []
for key in mode: #遍历这个存在配置文件里面的字典
sheet = wb[key] #表单名
if mode[key] == 'all':
for i in range(2, sheet.max_row + 1):
row_data = {} #字典
row_data['case_id'] = sheet.cell(i, 1).value
row_data['url'] = sheet.cell(i, 2).value
row_data['data'] = sheet.cell(i, 3).value
# if sheet.cell(i,3).value.find('${name}')!=-1: #有找到这个${name}
# row_data['data']=sheet.cell(i,3).value.replace('${name}',str(name))
# else: #如果没有找到的话
# row_data['data']=sheet.cell(i,3).vlaue
row_data['title'] = sheet.cell(i, 4).value
row_data['http_method'] = sheet.cell(i, 5).value
row_data['expected'] = sheet.cell(i, 6).value
row_data['sheet_name'] = key
test_data.append(row_data)
else:
for case_id in mode[key]:
row_data = {} # 字典
row_data['case_id'] = sheet.cell(case_id + 1, 1).value
row_data['url'] = sheet.cell(case_id + 1, 2).value
row_data['data'] = sheet.cell(case_id + 1, 3).value
row_data['title'] = sheet.cell(case_id + 1, 4).value
row_data['http_method'] = sheet.cell(case_id + 1, 5).value
row_data['expected'] = sheet.cell(case_id + 1, 6).value
row_data['sheet_name'] = key
test_data.append(row_data)
return test_data
def setUpClass(cls) -> None:
"""
登录获取(后台)token
:return:
"""
# 获取主机地址
HOST = ReadConfig.get_config(case_config_path, 'ADDRESS', 'host')
url = f'{HOST}/api/auth/oauth/user/token'
param = {"username": "******", "password": "******"}
header = {
"Content-Type": "application/json;charset=UTF-8",
"Authorization": "Basic QmFzZVBsYXRmb3JtOjEyMzQ1Ng=="
}
res = HttpRequest().http_request(url,
param,
'post',
'params',
headers=header)
setattr(GetToken, "Token", res.json()['value']) # 反射设置token值
get_header = {
"Content-Type": "application/json;charset=UTF-8",
"Authorization": "Bearer " + getattr(GetToken, "Token")
}
setattr(GetToken, "header", get_header) # 反射设置header值
class DoExcel:
sheet = None
wb = None
mode = eval(ReadConfig.get_config(case_config_path, "MODE",
"mode")) # 连接到配置文件字典，eval是因为读取到的都是字符串
#读取excel数据
def get_data(self, file_name):
global wb
wb = load_workbook(file_name) # 打开excel
test_data = []
for key in DoExcel.mode: #遍历这个存在配置文件里面的字典
global sheet
sheet = wb[key] #key表单名 不需要传参
if DoExcel.mode[key] == "all":
for i in range(2, sheet.max_row + 1):
row_data = {}
row_data["case_id"] = sheet.cell(i, 1).value
row_data["module"] = sheet.cell(i, 2).value
row_data["title"] = sheet.cell(i, 3).value
row_data["headers"] = sheet.cell(i, 4).value
row_data["url"] = sheet.cell(i, 5).value
row_data["data"] = sheet.cell(i, 6).value
row_data["expected"] = sheet.cell(i, 7).value
row_data["method"] = sheet.cell(i, 8).value
row_data["sheet_name"] = key
test_data.append(row_data)
else:
for case_id in DoExcel.mode[key]:
row_data = {}
row_data["case_id"] = sheet.cell(case_id + 1, 1).value
row_data["module"] = sheet.cell(case_id + 1, 2).value
row_data["title"] = sheet.cell(case_id + 1, 3).value
row_data["headers"] = sheet.cell(case_id + 1, 4).value
row_data["url"] = sheet.cell(case_id + 1, 5).value
row_data["data"] = sheet.cell(case_id + 1, 6).value
row_data["expected"] = sheet.cell(case_id + 1, 7).value
row_data["method"] = sheet.cell(case_id + 1, 8).value
row_data["sheet_name"] = key
test_data.append(row_data)
return test_data
#将用例执行后的结果写回excel
def write_back(self, file_name, sheet_name, i, result,
testresult): #写回excel数据
sh = wb[sheet_name]
#####字体颜色#######
unexecuted_color = Font(u'宋体',
size=9,
bold=True,
italic=False,
strike=False,
color='3232CD')
pass_color = Font(u'宋体',
size=12,
bold=True,
italic=False,
strike=False,
color='228B22')
pass_color1 = Font(u'宋体',
size=6,
bold=True,
italic=False,
strike=False,
color='228B22')
fail_color = Font(u'宋体',
size=9,
bold=True,
italic=False,
strike=False,
color='FF0000')
sh.cell(i, 9).value = result
sh.cell(i, 11).value = time.strftime('%m-%d %H:%M',
time.localtime(time.time()))
#sheet.cell(i, 10).value = testresult
if testresult == "pass":
sh.cell(i, 10, value="pass").font = pass_color
elif testresult == "pass，但是未获取到下一个接口所用数据":
sh.cell(i, 10, value="pass，但是未获取到下一个接口所用数据").font = pass_color1
elif testresult == "Failed":
sh.cell(i, 10, value="Failed").font = fail_color
#区分mode读取的是all还是数字列表
if DoExcel.mode[sheet_name] != "all":
for case_id in range(2, sh.max_row):
if case_id not in DoExcel.mode[sheet_name]:
sh.cell(case_id + 1, 9).value = ""
sh.cell(case_id + 1, 10,
value="用例未执行").font = unexecuted_color
#sheet.cell(case_id + 1, 10).value = "用例未执行"
sh.cell(case_id + 1, 11).value = ""
#保存和关闭excel
def save_close_excel(self, file_name=test_case_path):
wb.save(file_name)
wb.close()
import pymysql
from tools.read_config import ReadConfig
from tools.get_path import *
db_config = eval(ReadConfig().read_config(config_path, 'DATABASE', 'database'))
class DoMysql():
def do_mysql(self, query_sql, status=1):
cn = pymysql.connect(**db_config)
cursor = cn.cursor()
query = query_sql
cursor.execute(query)
if status == 'all':
res = cursor.fetchall()[0]
else:
res = cursor.fetchone()[0]
return res
cursor.close()
cn.close()
if __name__ == '__main__':
mobile = 13163750276
mobile = str(mobile)
print(mobile[9:11])
query = 'select * from sms_db_{0}.t_mvcode_info_{1}'.format(
mobile[9:11], mobile[8])
print(query)
res = DoMysql().do_mysql(query, 'all')
print(res)
def __init__(self):
db_config = eval(ReadConfig.get_config(mysql_config_path, 'Config', 'db_config'))
db = pymysql.connect(db_config["host"], db_config["user"], db_config["password"],
db_config["database"], port=db_config["port"])
self.cursor = db.cursor()
import pymysql
from conf.project_path import test_config_path
from tools.read_config import ReadConfig
from tools.my_log import MyLog
#返回字典
db_data = ReadConfig().read_config_as_section(test_config_path, "DB")
class DB():
# @staticmethod
def connect_mysql(self):
#配置从配置文件中读
con = pymysql.connect(host=db_data['host'],
user=db_data['user'],
password=db_data['password'],
port=int(db_data['port']),
database=db_data['database'],
charset=db_data['charset'])
return con
#查询操作
def do_sql(self, sql, state='all', mode='query'):
'''
:param sql:
:param state: 返回多条还是单挑
:param mode: 查询还是其他操作，查询不需要commit，其他操作需要
:return:
'''
#创建游标
def test_api(self, item):
# 获取主机地址
HOST = ReadConfig.get_config(case_config_path, 'ADDRESS', 'host')
item['url'] = HOST + item['url'] # 获取url
#提交data为空，关联值get_value不为空
if item['data'] == None and item['get_value'] != None:
if type(eval(item['get_value'])) == dict:
res = HttpRequest().http_request(item['url'],
None,
item['method'],
item['type'],
headers=getattr(
GetToken, "header"))
if res.json()['msg'] == 'OK':
get_data = eval(item['get_value'])
for keys in get_data.keys():
key = keys
value = get_data[key]
add = 'res.json()' + value
add = eval(add)
OperaConfig.write_config(file_path, key, key, add)
try:
self.assertEqual(item['expected'], res.json()['msg'])
TestResult = 'PASS'
except AssertionError as e:
TestResult = 'FAIL'
print("执行用例出错：{0}".format(e))
raise e
finally:
#写回结果(str(res.json()),excel只能写字符串，数字，不能写字典
DoExcel(test_case_path, item['sheet_name']).write_back(
item['case_id'] + 1, str(res.json()), TestResult)
print("获取到的结果是：{0}".format(res.json()))
# 提交data不为空，关联值get_value不为空（字符串：使用值）
elif item['data'] != None and item['get_value'] != None and type(
item['get_value']) == str:
data1 = OperaConfig.read_config(file_path, item['get_value'],
item['get_value']) #获取配置文件值
if item['url'].find(item['get_value']) != -1:
url = item['url'].replace("{{" + item['get_value'] + "}}",
data1)
res = HttpRequest().http_request(url,
eval(item['data']),
item['method'],
item['type'],
headers=getattr(
GetToken, "header"))
elif item['data'] != None and item['data'].find(
item['get_value']) != -1:
data = item['data'].replace("{{" + item['get_value'] + "}}",
data1)
res = HttpRequest().http_request(item['url'],
eval(data),
item['method'],
item['type'],
headers=getattr(
GetToken, "header"))
try:
self.assertEqual(item['expected'], res.json()['msg'])
TestResult = 'PASS'
except AssertionError as e:
TestResult = 'FAIL'
print("执行用例出错：{0}".format(e))
raise e
finally:
#写回结果(str(res.json()),excel只能写字符串，数字，不能写字典
DoExcel(test_case_path,
item['sheet_name']).write_back(item['case_id'] + 1,
str(res.json()),
TestResult)
print("获取到的结果是：{0}".format(res.json()))
# 提交data为空，关联值get_value为空（普通get）
elif item['data'] == None and item['get_value'] == None:
res = HttpRequest().http_request(item['url'],
None,
item['method'],
item['type'],
headers=getattr(
GetToken, "header"))
try:
self.assertEqual(item['expected'], res.json()['msg'])
TestResult = 'PASS'
except AssertionError as e:
TestResult = 'FAIL'
print("执行用例出错：{0}".format(e))
raise e
finally:
# 写回结果(str(res.json()),excel只能写字符串，数字，不能写字典
DoExcel(test_case_path,
item['sheet_name']).write_back(item['case_id'] + 1,
str(res.json()),
TestResult)
print("获取到的结果是：{0}".format(res.json()))
# 其他
else:
res = HttpRequest().http_request(item['url'],
eval(item['data']),
item['method'],
item['type'],
headers=getattr(
GetToken, "header"))
try:
self.assertEqual(item['expected'], res.json()['msg'])
TestResult = 'PASS'
except AssertionError as e:
TestResult = 'FAIL'
print("执行用例出错：{0}".format(e))
raise e
finally:
#写回结果(str(res.json()),excel只能写字符串，数字，不能写字典
DoExcel(test_case_path,
item['sheet_name']).write_back(item['case_id'] + 1,
str(res.json()),
TestResult)
print("获取到的结果是：{0}".format(res.json()))
