def test_register(self, case): # 我写在了里面---为了能每次都读取数据号码+1 mysql = MysqlUtil() # 实例化对象(进行了一次数据库的连接) sql = "SELECT MAX(MobilePhone) FROM future.member WHERE MobilePhone LIKE '152%';" # 传一条sql进来 max = mysql.fetch_one(sql)[0] # 找到最大手机号码, 元组里取第1个值 print("开始执行第{0}行用例".format(case.case_id)) import json data_dict = json.loads(case.data) # Excel字符串转成字典 if data_dict['mobilephone'] == '${register_mobile}': # 判断是否等于标记 data_dict['mobilephone'] = int(max) + 1 # 将最大手机号码+1 赋值给mobilephone # 使用封装好的request来完成请求 resp = self.request.request(case.method, case.url, data_dict) # 实例对象已移到外面,这里要加self. # 将返回结果与期望结果对比 try: self.assertEqual(case.expected, resp.text, 'register error') # 一致就写入Excel的结果PASS,并且 self.do_excel.write_back(case.case_id + 1, resp.text, 'PASS') # 实例对象已放外面,类变量加self. print("第{0}行用例执行结果:PASS".format(case.case_id)) except AssertionError as e: self.do_excel.write_back(case.case_id + 1, resp.text, 'FAIL') # 实例对象已放外面,类变量加self.调用 print("第{0}行用例执行结果:FAIL".format(case.case_id)) raise e
class WithdrawTest(unittest.TestCase): do_excel = DoExcel(contants.excel_path) withdraw_cases = do_excel.read_data("withdraw") @classmethod def setUpClass(cls): cls.request = Request() def setUp(self): self.mysql = MysqlUtil(return_dict=True) # logger.info(type(max), max) @data(*withdraw_cases) def test_withdraw(self, case): logger.info('开始执行第{0}条用例'.format(case.id)) data_dict = json.loads(case.data) sql_1 = 'select LeaveAmount from future.member where mobilephone =18566668888' results_1 = self.mysql.fetch_one(sql_1) member_1 = results_1['LeaveAmount'] resp = self.request.request(case.method, case.url, data_dict) try: self.assertEqual(case.expected, resp.json()['code']) if resp.json()['msg'] == '取现成功': sql_2 = 'select * from future.member where mobilephone ={0}'. \ format(data_dict['mobilephone']) results_2 = self.mysql.fetch_all(sql_2) member_2 = results_2[0] self.assertEqual(member_1 - 500000, member_2['LeaveAmount'])#判断取现后余额是否正确 sql_3 = 'select paymembermoney from future.financelog where paymemberid = 1115526 ' \ 'ORDER BY createtime DESC LIMIT 1' results_3 = self.mysql.fetch_one(sql_3) member_3 = results_3['paymembermoney'] self.assertEqual(member_2['LeaveAmount'],member_3)#判断会员流水记录表余额是否与会员表余额一致 self.do_excel.write_data('withdraw',case.id + 1, resp.text, 'PASS') logger.info('第{0}条用例执行结果:pass'.format(case.id)) except AssertionError as e: self.do_excel.write_data('withdraw',case.id + 1, resp.text, 'FAIL') logger.error('第{0}条用例执行结果:fail'.format(case.id)) raise e def tearDown(self): self.mysql.close() @classmethod def tearDownClass(cls): cls.request.session.close()
class RegisterTest(unittest.TestCase): do_excel = DoExcel(contants.case_file) cases = do_excel.get_cases('register') @classmethod def setUpClass(cls): cls.request = Request() # 实例化对象 def setUp(self): self.mysql = MysqlUtil(return_dict=True) sql = "select max(mobilephone) as max_phone from future.member" self.max = self.mysql.fetch_one(sql)['max_phone'] @data(*cases) def test_register(self, case): logger.info("开始执行第{}条用例".format(case.id)) data_dict = json.loads(case.data) if data_dict['mobilephone'] == "${register_mobile}": data_dict['mobilephone'] = int(self.max) + 1 # 使用封装好的request 来完成请求 resp = self.request.request(case.method, case.url, data_dict) # 将返回结果和期望结果进行匹配 try: self.assertEqual(case.expected, resp.json()['code'], "login error") if resp.json()['msg'] == '注册成功': sql = 'select * from future.member where mobilephone = {}'.format( data_dict['mobilephone']) results = self.mysql.fetch_all(sql) self.assertEqual(1, len(results)) member = results[0] self.assertEqual(0, member['LeaveAmount']) # 一致就写入Excel的结果为pass,并且 self.do_excel.write_result('register', case.id + 1, resp.text, 'PASS') logger.info("第{}条用例执行结果:PASS".format(case.id)) except AssertionError as e: self.do_excel.write_result('register', case.id + 1, resp.text, 'FAIL') logger.error("第{}条用例执行结果:FAIL".format(case.id)) raise e def tearDown(self): pass @classmethod def tearDownClass(cls): cls.request.session.close()
class RegisterTest(unittest.TestCase): do_excel = DoExcel(contants.excel_path) register_cases = do_excel.read_data('register') @classmethod def setUpClass(cls): cls.request = Request() def setUp(self): self.mysql = MysqlUtil(return_dict=True) sql = 'select max(mobilephone) as max_phone from future.member' self.max = self.mysql.fetch_one(sql)['max_phone'] # logger.info(type(max), max) # @unittest.skip('不要执行') @data(*register_cases) def test_register(self, case): logger.info('开始执行第{0}条用例'.format(case.id)) data_dict = json.loads(case.data) #字典 if data_dict['mobilephone'] == '${phone}': data_dict['mobilephone'] = int(self.max) + 1 if data_dict['mobilephone'] == '${phone2}': data_dict['mobilephone'] = int(self.max) + 2 resp = self.request.request(case.method, case.url, data_dict) try: self.assertEqual(case.expected, resp.text) if resp.json()['msg'] == '注册成功': sql = 'select * from future.member where mobilephone ={0}'.\ format(data_dict['mobilephone']) results = self.mysql.fetch_all(sql) #列表里嵌套字典 self.assertEqual(1, len(results)) #首先判断是否有成功插入数据 member = results[0] #获取到这一条数据,是一个字典 self.assertEqual(0, member['LeaveAmount']) #判断注册成功余额是否为0 self.assertEqual(1, member['Type']) # 判断注册用户类型是否为1 self.assertNotEqual(data_dict['pwd'], member['Pwd']) # 判断密码是否加密 if 'regname' in data_dict.keys(): self.assertEqual(data_dict['regname'], member['RegName']) else: self.assertEqual('小蜜蜂', member['RegName']) #判断用户名是小蜜蜂 self.do_excel.write_data('register', case.id + 1, resp.text, 'PASS') logger.info('第{0}条用例执行结果:pass'.format(case.id)) except AssertionError as e: self.do_excel.write_data('register', case.id + 1, resp.text, 'FAIL') logger.error('第{0}条用例执行结果:fail'.format(case.id)) raise e def tearDown(self): self.mysql.close() @classmethod def tearDownClass(cls): cls.request.session.close()
class RegisterTest(unittest.TestCase): do_excel = DoExcel(contants.case_file) # 传入cases.xlsx cases = do_excel.get_cases('register') request = Request() # 实例化对象 def setUp(self): self.mysql = MysqlUtil(return_dict=True) # 创建数据连接 sql = "select max(mobilephone) as max_phone from future.member" # 查询最大手机号 self.max = self.mysql.fetch_one(sql)[ 'max_phone'] # 执行SQL,并且返回最近的一条数据,是元祖,使用下标取第一个值 @data(*cases) def test_register(self, case): print("开始执行第{0}用例".format(case.id)) # 替换手机号码 data = case.data # excel里面读取出来是字符串 # 字符串的查找并替换 # if data.find('${register_mobile}') > -1: # data.s.replace('${register_mobile}', self.max) # 字典,根据KEY取值,然后判断,是否需要替换 import json data = json.loads(data) if data['mobilephone'] == '${register_mobile}': data['mobilephone'] = int(self.max) + 1 # 使用封装好的request 来完成请求 resp = self.request.request(case.method, case.url, data) # 这里data传str或者dict都OK ,因为方法里面会做判断 # 将返回结果和期望结果进行匹配 try: self.assertEqual(case.expected, resp.text, "register error ") if resp.json()['msg'] == '注册成功': sql = 'select * from future.member where mobilephone = {0}' \ .format(data['mobilephone']) results = self.mysql.fetch_all(sql) # 首先判断是否有成功插入数据 self.assertEqual(1, len(results)) member = results[0] # 获取到这一条数据,是一个字典 self.assertEqual(0, member['LeaveAmount']) # 判断注册成功余额应该是0 # 一致就写入Excel的结果为PASS,并且 self.do_excel.write_result("register", case.id + 1, resp.text, 'PASS') print("第{0}用例执行结果:PASS".format(case.id)) except AssertionError as e: self.do_excel.write_result("register", case.id + 1, resp.text, 'FAIL') print("第{0}用例执行结果:FAIL".format(case.id)) raise e def tearDown(self): self.mysql.close()
class RegisterTest(unittest.TestCase): request = HttpRequest() doexcel = DoExcel(cases_dir) cases = doexcel.read("register") #获取注册的测试用例 # print(type(max_mobilephone))#数据库取出来的vachar类型,也就是str类型 def setUp(self): #每个用例执行开始前,都要运行,获取数据库里面最大的手机号 self.mysql = MysqlUtil() # 创建数据库连接,打开查询页面 sql = "select MAX(MobilePhone)from future.member" # 查询数据库里面最大的手机号 self.max_mobilephone = self.mysql.fetch_one(sql)[ 0] # 从数据库里面查询出来的数据类型是:元组,要用下标取值;数据库里面最大的手机号是:max_mobilephone @data(*cases) def test_register(self, case): logger.info("正在执行{}条用例,用例标题是:{}".format(case["case_id"], case["title"])) import json data_dict = json.loads( case["data"]) #excel表中的data数据是str类型,需要转成json字典格式的字符串 if data_dict[ "mobilephone"] == "$register_mobilephone": #转成json格式,可以根据key取值,判断value是否等于excel表中做的标记$register_mobilephone data_dict["mobilephone"] = int( self.max_mobilephone ) + 1 #如果value等于excel表中做的标记$register_mobilephone,那查出来的最大手机号再加上1,再去请求 # 调用请求接口,接口请求后,会有返回结果,也就是实际结果 res = self.request.request(case["method"], case["url"], data_dict) #把case[data]换成最新的data_dict字典去做请求 #预期结果和实际结果的断言 try: self.assertEqual(case["expectedresult"], res.text, "register error!!!") #断言成功,测试结果就写PASS self.doexcel.write_back("register", case["case_id"] + 1, res.text, "PASS") logger.error("第{}条用例执行的结果是:PASS".format(case["case_id"])) except AssertionError as e: # 断言失败,测试结果就写Fail self.doexcel.write_back("register", case["case_id"] + 1, res.text, "FAIL") logger.error("第{}条用例执行的结果是:FAIL".format(case["case_id"])) raise e def tearDown(self): self.mysql.close()
def test_invest(self, case): logger.info("开始执行第{0}行用例".format(case.case_id)) logger.info('Expected:{0}'.format(case.expected)) # 查看期望结果 # 查找参数化数据,动态替换 data_new = context.replace_new(case.data) # Str测试数据 # 使用封装好的request 来完成请求 resp = self.request.request(case.method, case.url, data_new) # 将返回结果和期望结果进行匹配 try: self.assertEqual( json.loads(case.expected)['code'], resp.json()['code'], 'invest error') # resp.text改成resp.json()['code'] 只通过code判断 # 一致就写入Excel结果为PASS,并且 self.do_excel.write_back(case.case_id + 1, resp.text, 'PASS') logger.info("第{0}行用例执行结果:PASS".format(case.case_id)) # 加标后的标id不能直接配置文件替换,需要到数据库查询,而且根据借款人的memberID查询加成功的标loan_id # setUpClass()里面创建实例对象 # 引入mysql模块中的MysqlUtil类 # 在tearDownClass()里添加关闭,数据库用完必须关 # 还要引入Context类 进行替换 # 判断是否加标成功,如果成功过就按照借款人ID去数据库拆线呢最新标的记录 if resp.json()['msg'] == '加标成功': mysql = MysqlUtil() loan_member_id = getattr(Context, 'loan_member_id') logger.debug('loan_member_id的取值:{0}{1}'.format( type(loan_member_id), loan_member_id)) sql = "SELECT Id FROM future.loan WHERE MemberID={0} ORDER BY CreateTime DESC LIMIT 1;".format( loan_member_id ) # 注意:表名loan前面必须加数据库名future.loan否则会报错(1046, 'No database selected') # 创建sql语句。注意:没有分号。 loan_id = mysql.fetch_one(sql)[ 0] # 传入sql查询得到一个元组,再取第[0]个值是int类型,再赋值 logger.debug('self.loan_id的取值:{0}{1}'.format( type(loan_id), loan_id)) setattr(Context, 'loan_id', str(loan_id)) # 函数:给类或实例对象动态的去添加属性或者方法。 # 注意:后面的loan_id是int类型需要转换成str类型,context.replace_new.value和key需要时str类型 否则后续正则替换会报错。使用魔术方法 动态添加load_id。 mysql.close() except AssertionError as e: self.do_excel.write_back(case.case_id + 1, resp.text, 'FAIL') logger.error("断言出错了:{}".format(e)) logger.error("第{0}行用例执行结果:FAIL".format(case.case_id)) raise e
class InvestTest(unittest.TestCase): do_excel = DoExcel(contants.case_file) cases = do_excel.read('invest') @classmethod def setUpClass(cls): # 每个测试 类 里面去运行的操作放到类方法里面,执行所有用例前只运行一次。 print("\n这是一个类方法") cls.request = Request() # 实例化对象 # cls.mysql = MysqlUtil() # 创建一个mysql实例且每个setUp都用这一个实例,所以放setUpClass()里面 def setUp(self): # 每个测试 方法 里面去运行的操作放到类方法里面,执行每一条用例前都运行一次。 print("这是一个setUp") self.mysql = MysqlUtil() @data(*cases) def test_invest(self, case): print("开始执行第{0}行用例".format(case.case_id)) print('Expected:', case.expected) # 查看期望结果 # 查找参数化的测试数据,动态替换。上面引入替换模块。正则引入替换数据 data_new = context.replace_new( case.data) # 传入从Excel里面读取出来的str类型的测试数据。要重新赋值data_new # 使用封装的request来完成请求 resp = self.request.request(case.method, case.url, data_new) # 将返回结果和期望结果进行匹配 try: self.assertEqual( json.loads(case.expected)['code'], resp.json()['code'], 'invest error') # resp.text改成resp.json()['code'] 只通过code判断 # 一致就写入Excel结果为PASS,并且 self.do_excel.write_back(case.case_id + 1, resp.text, 'PASS') print("第{0}行用例执行结果:PASS".format(case.case_id)) # 加标后的标id不能直接配置文件替换,需要到数据库查询,而且根据借款人的memberID查询加成功的标loan_id # setUpClass()里面创建实例对象 # 引入mysql模块中的MysqlUtil类 # 在tearDownClass()里添加关闭,数据库用完必须关 # 还要引入Context类 进行替换 # 判断是否加标成功,如果成功过就按照借款人ID去数据库拆线呢最新标的记录 if resp.json()['msg'] == '加标成功': loan_member_id = getattr(Context, 'loan_member_id') print('loan_member_id的取值:', type(loan_member_id), loan_member_id) sql = "SELECT Id FROM future.loan WHERE MemberID={0} ORDER BY CreateTime DESC LIMIT 1;".format( loan_member_id ) # 注意:表名loan前面必须加数据库名future.loan否则会报错(1046, 'No database selected') # 创建sql语句。注意:没有分号。 loan_id = self.mysql.fetch_one(sql)[ 0] # 传入sql查询得到一个元组,再取第[0]个值是int类型,再赋值 print('self.loan_id的取值:', type(loan_id), loan_id) setattr(Context, 'loan_id', str(loan_id)) # 函数:给类或实例对象动态的去添加属性或者方法。 # 注意:后面的loan_id是int类型需要转换成str类型,context.replace_new.value和key需要时str类型 否则后续正则替换会报错。使用魔术方法 动态添加load_id。 except AssertionError as e: self.do_excel.write_back(case.case_id + 1, resp.text, 'FAIL') print("第{0}行用例执行结果:FAIL".format(case.case_id)) raise e def tearDown(self): self.mysql.close() @classmethod def tearDownClass(cls): # cls.request.close() # 关闭session # 这个没有打开会报错,但老师视频里面有 # cls.mysql.close() # 关闭MySQL,用完就要去关闭,否则资源一直被占用 后面的连接人就会被拒绝再连 pass