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()))