Python接口自动化测试从设计到开发，接口自动化测试,7、Excel数据读
Python接口自动化测试从设计到开发，接口自动化测试,7、Excel数据读
7、Excel数据读取
用例是放在Excel中的，用xlrd来读取数据，写数据需要用到xluntils，先安装：
pip install xlrdpip install xluntils7.1、读取配置文件
读取Excel数据，我们需要知道对应的行和列，列相对固定，在配置文件settings中定义，然后读取，行作为参数传入。conf/settings文件中的定义如下：
[excel]case_no=0case_name=1is_run=2case_level=3case_header=4case_cookies=5req_type=6case_url=7case_body=8expect_result=9operator=10actual_result=11test_result=12在unitls/load_conf.py中编写读取配置的方法，获取各项列值的方法。lood_conf()函数需要传入两个参数：配置项字符串标识符，配置项类型。比如要读取excel下整数case_url：lood_conf("excel.case_url","int")。class excel_config()下定义返回各项列值的方法。
完整代码如下：
import configparser‘‘‘read conf from setting.conf@:parameter:identstr,value_typevalue_type:"int" or "str"‘‘‘def lood_conf(identstr,value_type): cf = configparser.ConfigParser() cf.read("../config/settings.conf") idenlist = identstr.split(‘.‘) if value_type == "int": try: value = cf.getint(idenlist[0],idenlist[1]) return value except (configparser.NoSectionError ,configparser.NoOptionError) as e: print(e) if value_type == "str": try: value = cf.get(idenlist[0],idenlist[1]) return value except (configparser.NoSectionError ,configparser.NoOptionError) as e: print(e)‘‘‘获取url，request body等的列号‘‘‘class excel_config(): #获取用例编号的列 def caseno_col(self): return lood_conf("excel.case_no","int") def casename_col(self): return lood_conf("excel.case_name","int") def isrun_col(self): #print(lood_conf("excel.is_run","int")) return lood_conf("excel.is_run","int") def level_col(self): return lood_conf("excel.case_level","int") def header_col(self): return lood_conf("excel.case_header","int") def cookies_col(self): return lood_conf("excel.case_cookies","int") def reqtype_col(self): return lood_conf("excel.req_type","int") def caseurl_col(self): return lood_conf("excel.case_url","int") def casebody_col(self): return lood_conf("excel.case_body","int") def expectresult_col(self): return lood_conf("excel.expect_result","int") def actualresult_col(self): return lood_conf("excel.actual_result","int") def testresult_col(self): return lood_conf("excel.test_result","int") def test_operator_col(self): return lood_conf("excel.operator","int")7.1、编写Excel操作类
unitls/excel_tool.py中定义了获取用例编号，用例名称等方法，需要传入行。回写测试结果，回写实际结果方法需要传入两个参数：行，值。完整代码如下：
#coding:utf-8import xlrdfrom untils.log_trace import *from xlutils.copy import copyfrom untils.load_conf import excel_configclass excel_tool(): def __init__(self,excel_name): self.curr_excel = xlrd.open_workbook(excel_name) self.table = self.curr_excel.sheet_by_index(0) #print(self.table.cell(1,1).value) #实例化excel_config self.config = excel_config() self.rows = self.table.nrows self.excel_name = excel_name #获取用例编号 def get_caseno(self,row): caseno = self.table.cell(row,self.config.caseno_col()).value if caseno: return caseno else: logging.info("case no is null") return None #获取用例名称 def get_casename(self,row): casename = self.table.cell(row,self.config.casename_col()).value return casename #获取是否运行标志 def get_runflag(self,row): run_flag = self.table.cell(row,self.config.isrun_col()).value return run_flag #获取用例级别 def get_caselevel(self,row): caselevel = self.table.cell(row,self.config.level_col()).value return caselevel #获取请求url def get_caseurl(self,row): caseurl = self.table.cell(row,self.config.caseurl_col()).value return caseurl #获取请求body def get_casebody(self,row): case_body = self.table.cell(row,self.config.casebody_col()).value return case_body #获取header def get_headerflag(self,row): headerflag = self.table.cell(row,self.config.header_col()).value return headerflag #获取coocikes def get_cookiesflag(self,row): cookiesflag = self.table.cell(row,self.config.cookies_col()).value return cookiesflag #获取请求类型 def get_methodtype(self,row): method_type = self.table.cell(row,self.config.reqtype_col()).value return method_type #获取预期结果 def get_expectres(self,row): expect_res = self.table.cell(row,self.config.expectresult_col()).value return expect_res #获取测试结果 def get_testres(self,row): test_res= self.table.cell(row,self.config.testresult_col()).value return test_res #获取操作符 def get_operator(self,row): operator = self.table.cell(row,self.config.test_operator_col()).value return operator #回写测试结果到excel def write_testres(self,row,value): wbook = copy(xlrd.open_workbook(self.excel_name)) sheet = wbook.get_sheet(0) sheet.write(row, self.config.testresult_col(), value) wbook.save(self.excel_name) #回写实际结果 def write_actualres(self,row,value): wbook = copy(xlrd.open_workbook(self.excel_name)) sheet = wbook.get_sheet(0) sheet.write(row, self.config.actualresult_col(), value) wbook.save(self.excel_name)8、用例组装
有了Excel操作类，就可以方便读取数据和回填结果了。接下来，在unitls/run_main.py中来组装用例。组装之前，先获取是否运行的标志：
运行标志为N，不组装，将用例标记为skiiped，回填测试结果到Excel文件中。运行标志为Y，开始组装用例并执行，并对比预期结果和实际结果。用例执行通过，将用例标记为pass，回填测试结果和实际结果，实际结果为接口的返回。用例执行失败，将用例标记为failed，回填测试结果和实际结果。接口鉴权需要用到的headers，先在run_main.py 中写死，这个问题后面解决，在上面的过程中，增加必要的日志，方便定位问题和查看用例的运行日志。完整代码如下：
#coding:utf-8from untils.excel_tool import excel_toolfrom untils.send_request import send_requestfrom untils.log_trace import *from untils.check_result import CheckResultimport jsonheaders = { "X-Token":"0a6db4e59c7fff2b2b94a297e2e5632e"}class runner(): def __init__(self): self.excel = excel_tool("../testcase/test.xls") self.check = CheckResult() def join_case(self): global skip_list,sucess_list,failed_list,skip_list sucess_list = [] sucess_list = [] failed_list = [] skip_list = [] for row in range(1,self.excel.rows): no = self.excel.get_caseno(row) url = self.excel.get_caseurl(row) isrun = self.excel.get_runflag(row) name = self.excel.get_casename(row) level = self.excel.get_caselevel(row) data = self.excel.get_casebody(row) expect_res = self.excel.get_expectres(row) method = self.excel.get_methodtype(row) hasheader = self.excel.get_headerflag(row) operator = self.excel.get_operator(row) if isrun == "Y": logging.info("Begin to run test case : %s,case number :%s" %(name,no)) logging.info("Request method type is :%s" %method) logging.info("Request URL:%s" %url) logging.info("Request Body:%s" %json.dumps(json.loads(data),sort_keys=True,indent=2)) res = send_request(method,url,data=data,headers=headers) is_sucess = self.check.cmpdict(eval(expect_res),eval(res.text),operator) print(is_sucess) if is_sucess: sucess_list.append(name) #回写测试结果 self.excel.write_testres(row,"pass") #回写实际结果 self.excel.write_actualres(row,res.text) logging.info("Test case %s run sucess." %name) else: failed_list.append(name) print("fail",is_sucess) #回写测试结果 self.excel.write_testres(row,"failed") #回写实际结果 self.excel.write_actualres(row,res.text) logging.error("Test case %s run fail." %name) logging.info("Response is:%s" %json.dumps(res.json(),sort_keys=True,indent=2)) else: skip_list.append(name) self.excel.write_testres(row,"skipped") def sum(self): total = len(sucess_list)+len(failed_list) + len(skip_list) failed = len(failed_list) sucess = len(sucess_list) logging.info("-----------------------------------------------------------") logging.info("本次一共运行：%s 个用例" %total) logging.info("本次运行通过：%s 个用例" %sucess) logging.info("本次运行跳过：%s 个用例" %len(skip_list)) logging.info("跳过的用例：%s" %skip_list) logging.info("-----------------------------------------------------------")9、用例运行结果校验
在untils/run_main.py中方法cmpdict()是用来校验预期和结果实际结果是否匹配，需要传入三个参数：预期结果字典，实际结果字典，操作符。在check_result.py中编写校验用例结果的方法。目前只支持两种操作符，equal和notequal，预期结果为字典，其中不能嵌套字典。和完整代码如下：
from untils.log_trace import *class CheckResult(): def dict_value(self,key,actual): try: if key in actual: return actual[key] else: for keys in actual: return self.dict_value(key,actual[keys]) except Exception as e: logging.error(e) return None def cmpdict(self,expect,actual,equal): logging.info("Begin to check result of testcase.") is_dict = isinstance(expect,dict) and isinstance(actual,dict) if is_dict: if equal == "equal": for key in expect.keys(): if expect[key] == self.dict_value(key,actual): logging.info("%s is equal to %s" %(expect[key],self.dict_value(key,actual))) return True else: logging.error("%s is not equal to %s" %(expect[key],self.dict_value(key,actual))) return False if equal == "notequal": for key in expect.keys(): if key != self.dict_value(key,actual): logging.info("%s is not equal to %s" %(expect[key],self.dict_value(key,actual))) return True else: logging.error("%s is equal to %s" %(expect[key],self.dict_value(key,actual))) return False else: logging.error("Operator :%s is not support now,you can define it in file[check_result.py]" %equal) else: logging.error("Expect or actual result is not dict,check it in excel. ")10、运行用例
新建一个名称为test.xls的Excel，将其放到testcase路径下，并在Excel中编写测试用例。接口开发请参考：使用Django开发简单接口：文章增删改查，我准备的用例如下：
在untils/untils_test.py中导入run_mian模块来测试一下：
from untils.run_main import runnerif __name__ == "__main__": #test_send_request() runner = runner() runner.join_case() runner.sum()运行untils_test.py，然后去到Excel中查看运行结果：
report路径下查看测试用例运行日志，如下所示：
Sat, 11 May 2019 19:37:56 INFO check_result.py [line:16] Begin to check result of testcase.Sat, 11 May 2019 19:37:56 ERROR check_result.py [line:38] Operator :e1qual is not support now,you can define it in file[check_result.py]Sat, 11 May 2019 19:37:56 INFO run_main.py [line:37] Begin to run test case : 查询文章,case number :1.0Sat, 11 May 2019 19:37:56 INFO run_main.py [line:38] Request method type is :GETSat, 11 May 2019 19:37:56 INFO run_main.py [line:39] Request URL:http://127.0.0.1:9000/articlesSat, 11 May 2019 19:37:56 INFO run_main.py [line:40] Request Body:{}Sat, 11 May 2019 19:37:56 INFO send_request.py [line:25] {‘X-Token‘: ‘0a6db4e59c7fff2b2b94a297e2e5632e‘}Sat, 11 May 2019 19:37:56 INFO check_result.py [line:16] Begin to check result of testcase.Sat, 11 May 2019 19:37:56 INFO check_result.py [line:22] BS.200 is equal to BS.200Sat, 11 May 2019 19:37:56 INFO run_main.py [line:52] Test case 查询文章 run sucess.Sat, 11 May 2019 19:37:56 INFO run_main.py [line:62] Response is:{ "all_titles": { "Hello": "alive", "amy1": "alive", "modifytest": "alive", "useasge of ddt": "alive" }, "msg": "query articles sucess.", "status": "BS.200"}Sat, 11 May 2019 19:37:56 INFO run_main.py [line:37] Begin to run test case : 新增文章,case number :2.0Sat, 11 May 2019 19:37:56 INFO run_main.py [line:38] Request method type is :POSTSat, 11 May 2019 19:37:56 INFO run_main.py [line:39] Request URL:http://127.0.0.1:9000/articles/Sat, 11 May 2019 19:37:56 INFO run_main.py [line:40] Request Body:{ "content": "useasge of ddt", "title": "useasge of ddt"}Sat, 11 May 2019 19:37:56 INFO send_request.py [line:25] {‘X-Token‘: ‘0a6db4e59c7fff2b2b94a297e2e5632e‘}Sat, 11 May 2019 19:37:56 INFO check_result.py [line:16] Begin to check result of testcase.Sat, 11 May 2019 19:37:56 ERROR check_result.py [line:25] BS.200 is not equal to BS.400Sat, 11 May 2019 19:37:56 ERROR run_main.py [line:60] Test case 新增文章 run fail.Sat, 11 May 2019 19:37:56 INFO run_main.py [line:62] Response is:{ "msg": "title aleady exist,fail to publish.", "status": "BS.400"}Sat, 11 May 2019 19:37:56 INFO run_main.py [line:37] Begin to run test case : 修改文章,case number :3.0Sat, 11 May 2019 19:37:56 INFO run_main.py [line:38] Request method type is :POSTSat, 11 May 2019 19:37:56 INFO run_main.py [line:39] Request URL:http://127.0.0.1:9000/articles/7Sat, 11 May 2019 19:37:56 INFO run_main.py [line:40] Request Body:{ "content": "modify test", "title": "modify test"}Sat, 11 May 2019 19:37:56 INFO send_request.py [line:25] {‘X-Token‘: ‘0a6db4e59c7fff2b2b94a297e2e5632e‘}Sat, 11 May 2019 19:37:57 INFO check_result.py [line:16] Begin to check result of testcase.Sat, 11 May 2019 19:37:57 ERROR check_result.py [line:25] BS.200 is not equal to BS.300Sat, 11 May 2019 19:37:57 ERROR run_main.py [line:60] Test case 修改文章 run fail.Sat, 11 May 2019 19:37:57 INFO run_main.py [line:62] Response is:{ "msg": "article is not exists,fail to modify.", "status": "BS.300"}Sat, 11 May 2019 19:37:57 INFO run_main.py [line:37] Begin to run test case : 删除文章,case number :4.0Sat, 11 May 2019 19:37:57 INFO run_main.py [line:38] Request method type is :DELETESat, 11 May 2019 19:37:57 INFO run_main.py [line:39] Request URL:http://127.0.0.1:9000/articles/7Sat, 11 May 2019 19:37:57 INFO run_main.py [line:40] Request Body:{}Sat, 11 May 2019 19:37:57 INFO send_request.py [line:25] {‘X-Token‘: ‘0a6db4e59c7fff2b2b94a297e2e5632e‘}Sat, 11 May 2019 19:37:57 INFO check_result.py [line:16] Begin to check result of testcase.Sat, 11 May 2019 19:37:57 ERROR check_result.py [line:25] BS.200 is not equal to BS.300Sat, 11 May 2019 19:37:57 ERROR run_main.py [line:60] Test case 删除文章 run fail.Sat, 11 May 2019 19:37:57 INFO run_main.py [line:62] Response is:{ "msg": "article is not exists,fail to delete.", "status": "BS.300"}Sat, 11 May 2019 19:37:57 INFO run_main.py [line:74] -----------------------------------------------------------Sat, 11 May 2019 19:37:57 INFO run_main.py [line:75] 本次一共运行：5 个用例Sat, 11 May 2019 19:37:57 INFO run_main.py [line:76] 本次运行通过：1 个用例Sat, 11 May 2019 19:37:57 INFO run_main.py [line:77] 本次运行跳过：1 个用例Sat, 11 May 2019 19:37:57 INFO run_main.py [line:78] 跳过的用例：[‘新增文章缺少title‘]Sat, 11 May 2019 19:37:57 INFO run_main.py [line:79] -----------------------------------------------------------11 、小结
框架终于能跑起来了，但是遗留的问题还很多。
很多地方的代码不够健壮，这个后面慢慢优化。还有用例校验支持的运算符比较少。发送邮件模块待完成。Headers的问题如何解决？如果请求的body比较多，写在Excel是不是很不美观呀？这个可以从固定地方读取文件来完成。Excel中测试用例有没有必填项呀？这个可以在运行结果之前进行校验，必填项缺少，不运行。最关键的一点，如果第二个用例依赖于第一个用例的返回，用例依赖一直是个痛点，下一篇解决。Python接口自动化测试从设计到开发
评论关闭