def read_data(self, option):
'''
将Excel中指定表单中每行数据存为一个列表row_list,再将其作为子列表存入all_row_list中
:param file: 文件名
:param sheet: 表单名
:return: all_row_list--一个嵌套列表
'''
case_id = ReadConfig(project_path.conf_path).read_str(option, 'case_id')
log.info('开始读取数据:')
all_row_list = [] # 把每行数据作为子列表，存放在该列表中
try:
wb = load_workbook(self.file) # 打开excel文件
sheet = wb[self.sheetname] # 定位表单
except Exception as e:
log.error('读取文件错误，请检查文件名或表单名是否正确!报错信息:{}'.format(e))
raise e
tel = self.read_tel() # 获取excel里面的电话号码
# 对应测试用例中用例编号
if case_id == 'all':
row_case = range(1, sheet.max_row)
else:
row_case = eval(case_id)
for item in row_case:
row_list = [] # 存放每行单元格内数据，每一行先将其置空
for i in (1, 2, 3, 5, 6, 7, 8): # id，模块名，接口名，请求方法，参数，(sql)，期望结果。[没有sql的表单，第7列对应期望结果]
row_list.append(sheet.cell(row=item + 1, column=i).value) # 用例id+1才是需读取数据的对应行数
if row_list[4].find('tel') != -1: # 列表中参数列，当参数中可以找到tel字段
row_list[4] = row_list[4].replace('tel', str(tel)) # 替换值 tel replace只能用于str替换
else:
self.update_tel(tel + 1)
all_row_list.append(row_list)
# 先全部读取数据，再返回需执行的对应case_id数据
# final_data = []
# if case_id == 'all':
# final_data = all_row_list
# else:
# for j in case_id:
# final_data.append(all_row_list[j-1])
log.info('读取到的数据为：{}'.format(all_row_list))
log.info('读取数据完毕')
wb.close()
return all_row_list
def do_mysql(self, query, flag=1):
'''
:param query: 查询语句
:param flag: 标志位 1-获取一条数据；2-获取多条数据
:return:
'''
db_config = ReadConfig(project_path.conf_path).read_itera(
'DB', 'db_config')
cnn = connector.connect(**db_config) #建立连接
cursor = cnn.cursor()
cursor.execute(query)
if flag == 1:
res = cursor.fetchone()
else:
res = cursor.fetchall()
return res
class TestCases(unittest.TestCase):
# 读取测试用例
a = ReadConfig(project_path.conf_path)
sheetname = 'login'
pre_url = a.read_str('reqUrl', 'pre_url')
t = PyExcel(project_path.case_path, sheetname)
test_data = t.read_data('loginCase')
def setUp(self):
pass
def tearDown(self):
pass
# 写用例
@data(*test_data)
# @unpack
def test_cases(self, case):
log.info('{}用例开始执行：'.format(self.sheetname + '_' + str(case[0])))
method = case[2]
# 获取已参数化的登录手机号及密码
case[4] = get_data.replace(case[4])
req = HttpRequest(self.pre_url, method)
actual_result = req.http_request(case[1], case[2], json.loads(case[4]))
log.info('实际结果是：{}'.format(actual_result.json()))
self.t.write_data(case[0] + 1, 8, json.dumps(actual_result.json(), ensure_ascii=False))
excepted_result = json.loads(case[5])
log.info('期望结果是：{}'.format(excepted_result))
try:
self.assertDictEqual(excepted_result, actual_result.json())
# self.assertEqual(excepted_result,actual_result)
res = 'Pass'
except AssertionError as e:
res = 'Failed'
print('请求出错，错误信息：{}'.format(e))
raise e
finally:
self.t.write_data(case[0] + 1, 9, res)
class TestCases(unittest.TestCase):
# 读取测试用例
a = ReadConfig(project_path.conf_path)
sheetname = 'recharge'
pre_url = a.read_str('reqUrl', 'pre_url')
t = PyExcel(project_path.case_path, sheetname)
test_data = t.read_data('rechargeCase')
def setUp(self):
pass
def tearDown(self):
pass
# 写用例
@data(*test_data)
# @unpack
def test_cases(self, case):
# global COOKIES #全局变量
log.info('{}用例开始执行：'.format(self.sheetname + '_' + str(case[0])))
method = case[2]
# 获取参数化的登录手机号码及密码
case[4] = get_data.replace(case[4])
# 请求之前，查询数据库账户剩余金额
if case[5] is not None:
sql = eval(case[5])['LeaveAmount']
before_amount = DoMySql().do_mysql(sql)[0]
# 发起请求
req = HttpRequest(self.pre_url, method)
# actual_result = req.http_request(case[1], case[2], json.loads(case[4]), cookies=COOKIES) #全局变量
actual_result = req.http_request(case[1],
case[2],
json.loads(case[4]),
cookies=getattr(GetData, 'COOKIE'))
# 写入实际结果
self.t.write_data(case[0] + 1, 9,
json.dumps(actual_result.json(), ensure_ascii=False))
log.info('实际结果是：{}'.format(actual_result.json()))
# 获取登录cookies
if actual_result.cookies:
# COOKIES = actual_result.cookies #全局变量更新
setattr(GetData, 'COOKIE', actual_result.cookies)
try:
# 请求之后，再次查询数据库账户剩余金额
if case[5] is not None:
sql = eval(case[5])['LeaveAmount']
recharge_amount = eval(case[4])['amount']
after_amount = DoMySql().do_mysql(sql)[0]
expect_amount = float(before_amount) + float(recharge_amount)
expect_amount = ("%.2f" % expect_amount) # 强制保留两位小数,四舍五入
# expect_amount = round(expect_amount, 2) # 保留两位小数，四舍五入，但只有一位小数时不会显示两位小数
self.assertEqual(float(expect_amount), float(after_amount))
# 判断是否需要替换期望值中的LeaveAmount
if case[6].find('expect_amount') != -1:
case[6] = case[6].replace('expect_amount', str(expect_amount))
excepted_result = json.loads(case[6])
log.info('期望结果是：{}'.format(excepted_result))
self.assertDictEqual(excepted_result, actual_result.json())
# self.assertEqual(excepted_result,actual_result)
res = 'Pass'
except AssertionError as e:
res = 'Failed'
print('请求出错，错误信息：{}'.format(e))
raise e
finally:
self.t.write_data(case[0] + 1, 10, res)
class TestCases(unittest.TestCase):
# 读取测试用例
a = ReadConfig(project_path.conf_path)
sheetname = 'add_loan'
pre_url = a.read_str('reqUrl', 'pre_url')
t = PyExcel(project_path.case_path, sheetname)
test_data = t.read_data('addloanCase')
def setUp(self):
pass
def tearDown(self):
pass
# 写用例
@data(*test_data)
# @unpack
def test_cases(self, case):
log.info('{}用例开始执行：'.format(self.sheetname + '_' + str(case[0])))
method = case[2]
# 获取参数化的手机号及密码和member_id
case[4] = get_data.replace(case[4])
# 替换参数中的loanid
if case[5] is not None:
sql = eval(case[5])['loanid']
loan_id = DoMySql().do_mysql(sql)[0]
setattr(GetData, 'loanid', loan_id)
try:
# 发起请求
req = HttpRequest(self.pre_url, method)
actual_result = req.http_request(case[1],
case[2],
json.loads(case[4]),
cookies=getattr(
GetData, 'COOKIE'))
# 写入实际结果
self.t.write_data(
case[0] + 1, 9,
json.dumps(actual_result.json(), ensure_ascii=False))
log.info('实际结果是：{}'.format(actual_result.json()))
# 获取登录cookies
if actual_result.cookies:
setattr(GetData, 'COOKIE', actual_result.cookies)
# # 加标请求之后，需查询数据库所加标的id
# if case[5] is not None:
# sql = eval(case[5])['loanid']
# loan_id = DoMySql().do_mysql(sql)[0]
# setattr(GetData, 'LOAN_ID', loan_id)
excepted_result = json.loads(case[6])
log.info('期望结果是：{}'.format(excepted_result))
self.assertDictEqual(excepted_result, actual_result.json())
res = 'Pass'
except Exception as e:
res = 'Failed'
print('请求出错，错误信息：{}'.format(e))
raise e
finally:
self.t.write_data(case[0] + 1, 10, res)
# _*_coding:utf-8_*_ # @Time :2019/4/10 13:53 # @Author :Tanxi # @Email :[email protected] # @File :get_data.py # @Software :PyCharm Community Edition import re from Combat_API.common import project_path from Combat_API.common.read_config import ReadConfig config = ReadConfig(project_path.conf_path) class GetData: '''可以用来动态的更改 删除 获取数据''' COOKIE = None # 请求时cookie初始值 LOAN_ID = None # 新添加项目时，标id的初始值 user_phone = config.read_str('data', 'user_phone') user_pwd = config.read_str('data', 'user_pwd') user_member_id = config.read_str('data', 'user_member_id') def replace(target): p = '#(.*?)#' while re.search(p, target): pre_value = re.search(p, target).group(1) value = getattr(GetData, pre_value) target = re.sub(p, value, target, count=1) return target