def __init__(self, fileName, SheetName="Sheet1"):
self.filename = fileName
if not os.path.exists(self.filename):
# 文件不存在，则拷贝文件至指定目录下
shutil.copyfile(FILE_PATH, REPORT_FILE_PATH)
self.wb = load_workbook(self.filename)
self.ws = self.wb[SheetName]
# --------- 读取config.ini配置文件 ---------------
cf = cparser.ConfigParser()
cf.read("../config/config.ini", encoding='UTF-8')
self.name = cf.get("tester", "name")
self.logger = my_log.Logger('../log/log/all.log', level='debug').logger
self.error_log = my_log.Logger('../log/log/error.log', level='error').logger
class MyTestCase(unittest.TestCase):
ip = "http://172.18.255.8:8011"
logger = my_log.Logger('../log/log/all.log', level='debug').logger
litigation = read_excel.ReadExcel(FILE_PATH).read_data()
@data(litigation)
def test_litigation(self, test_data):
self.logger.info("测试用例数据长度： ", len(test_data))
if test_data is None or test_data == []:
self.logger.warning('{}{}'.format(self.get_now_datetime('%Y-%m-%d %H:%M:%S'), '------暂未读取到有效测试用例-----'))
else:
for datai in test_data:
params = {"token": TOKEN}
original = req_params.ReqParams[datai["CASE"]].value # 初始所有参数
body = eval(datai["body"]) # 需要填写的参数
result = dict_merge.DictMerge(body, original)
res = requests.post(url=self.ip+datai["URL"], data=json.dumps(result), params=params, headers=HEADERS).json()
if res["code"] == 200:
write_excel.WriteExcel(REPORT_FILE_PATH).write_somewhere(datai['row'], "PASS", str(res))
self.logger.info("流程 --------- 用例{0}测试通过".format(datai["casenum"]))
else:
self.logger.info("实际返回code： ", res)
write_excel.WriteExcel(REPORT_FILE_PATH).write_somewhere(datai['row'], "FAIL", str(res))
self.logger.error("流程 --------- 用例{0}测试不通过，流程类接口终止测试~~~~~".format(datai["casenum"]))
raise _ShouldStop
class OracleUtil(object):
logger = my_log.Logger('../log/log/error.log', level='error').logger
def __init__(self):
# --------- 读取config.ini配置文件 ---------------
cf = cparser.ConfigParser()
cf.read("../config/config.ini", encoding='UTF-8')
username = cf.get("oracle", "username")
password = cf.get("oracle", "password")
service = cf.get("oracle", "ip_host_service")
try:
self.conn = connect(username, password, service)
except OperationalError as e:
self.logger.error("Oracle Error %d: %s" % (e.args[0], e.args[1]))
# 查询数据
def select(self, sql):
with self.conn.cursor() as cursor:
self.conn.ping()
cursor.execute(sql)
data = cursor.fetchall()
cols = [i[0] for i in cursor.description]
result = []
for row in data:
result.append(dict(zip(cols, row)))
self.conn.close()
return result
# 清除数据
def clear(self, sql):
with self.conn.cursor() as cursor:
# 取消表的外键约束
cursor.execute("SET FOREIGN_KEY_CHECKS=0;")
cursor.execute(sql)
self.conn.commit()
def __init__(self, fileName, SheetName="Sheet1"):
self.data = xlrd.open_workbook(fileName)
self.table = self.data.sheet_by_name(SheetName)
# 获取总行数、总列数
self.nrows = self.table.nrows
self.ncols = self.table.ncols
self.logger = my_log.Logger('../log/log/all.log', level='debug').logger
class MySQLUtil(object):
logger = my_log.Logger('../log/log/error.log', level='error').logger
def __init__(self):
# --------- 读取config.ini配置文件 ---------------
cf = cparser.ConfigParser()
cf.read("../config/config.ini", encoding='UTF-8')
db_config = eval(cf.get("mysql", "db_config"))
db_config['cursorclass'] = cursors.DictCursor
try:
# 连接数据库
self.conn = connect(**db_config)
self.cursor = self.conn.cursor()
except OperationalError as e:
self.logger.error("Mysql Error %d: %s" % (e.args[0], e.args[1]))
# 查询数据
def my_select(self, select_sql):
self.conn.ping()
self.cursor.execute(select_sql)
data = self.cursor.fetchall()
return data
# 新增、更新、删除数据
def add_update_delete(self, my_sql):
# with self.conn.cursor() as cursor:
# # 取消表的外键约束
# cursor.execute("SET FOREIGN_KEY_CHECKS=0;")
self.conn.ping()
self.cursor.execute(my_sql)
self.conn.commit()
def __enter__(self):
return self
def __exit__(self, exc_type, exc_val, exc_tb):
if exc_type:
self.conn.rollback()
# else:
# self.conn.commit()
def __del__(self):
self.conn.close()
self.cursor.close()
class Email:
logger = my_log.Logger('../log/log/all.log', level='debug').logger
def sendemail(self):
# 测试文件
file = '../report/测试报告.html'
if os.path.exists(file):
# 内容
email_text = "测试完成！测试报告已生成，请查看附件 ~ "
# 构造邮件体
msg = MIMEMultipart()
msg['subject'] = subject # 主题
msg['from'] = send_user # 发送邮箱
msg['to'] = receive # 接收邮箱
# 构建正文
part_text = MIMEText(email_text)
msg.attach(part_text) # 正文添加到邮件体中
# 构造附件1
att1 = MIMEText(open(file, 'rb').read(), 'base64', 'utf-8')
att1["Content-Type"] = 'application/octet-stream'
att1["Content-Disposition"] = 'attachment; filename="TestReport.html"'
msg.attach(att1)
# #构建附件2
# part_attach1 = MIMEApplication(open(file,'rb').read())
# part_attach1.add_header('Conten-Disposition','attachment',filename="testReport.html")
# msg.attach(part_attach1) #附件添加到邮件体中
try:
# 发送邮件
smtp = smtplib.SMTP_SSL(server_address, 465) # 启用SSL发信, 端口一般是465
smtp.login(send_user, password) # 登录验证
smtp.sendmail(send_user, receive, msg.as_string()) # 发送
self.logger.info("测试结果邮件发送成功~")
except smtplib.SMTPException as e:
self.logger.error("发送测试结果邮件失败！", e)
else:
self.logger.warning("没有找到[../report/测试报告.html]这个文件～")
class RunTest(unittest.TestCase):
logger = my_log.Logger('../log/log/all.log', level='debug').logger
select_data = read_excel.ReadExcel(FILE_PATH, u"Sheet2").read_data()
# add_data = read_excel.ReadExcel(FILE_PATH, u"新增").read_data()
# update_data = read_excel.ReadExcel(FILE_PATH, u"修改").read_data()
# delete_data = read_excel.ReadExcel(FILE_PATH, u"删除").read_data()
def __init__(self, *args, **kwargs):
unittest.TestCase.__init__(self, *args, **kwargs)
def setUp(self) -> None:
self.logger.info(
"==========================----测试case开始----==========================\n"
)
def tearDown(self) -> None:
self.logger.info(
"==========================----测试case结束----==========================\n"
)
@classmethod
def setUpClass(cls) -> None:
cls.logger.info(
"-=============================---必须使用@classmethod装饰器, 所有case运行之前只运行一次--======================--"
)
cf = cparser.ConfigParser()
cf.read("../config/config.ini", encoding='UTF-8')
cls.ip = cf.get("ip", "auction_ip")
if DATATYPE == "ORACLE":
cls.data_type = oracle_util.OracleUtil()
else:
cls.data_type = mysql_util.MySQLUtil()
@classmethod
def tearDownClass(cls) -> None:
cls.logger.info(
"-===========================---必须使用@classmethod装饰器, 所有case运行完之后只运行一次--===========================--"
)
@ddt.data(*select_data)
def test_case_01(self, data):
# data中没数据则提示
if data is None or data == []:
self.logger.warning("================没有查询到数据================")
else:
# 参数params设置，没有其他参数则只放入token
if data["type"].lower() != "cookie":
if data["params"] == "":
data["params"] = str("token=" + TOKEN)
else:
data["params"] = str(data["params"] + "&token=" + TOKEN)
res = send_request.HttpClient().my_request(self.ip, data)
self.logger.info("=========响应结果中code为：" + str(res["code"]))
self.logger.info("=========预期结果中code为：" + str(int(data["pre"])))
if res["code"] == int(data["pre"]):
result = "PASS"
# 用例中sql、可断言字段取出
sql = data["sql"]
field = data['field']
code_type = data['code_type']
pre_value = data['pre_value']
# 有sql语句则断言sql语句执行结果与接口返回结果
if sql != '':
sql_result = self.data_type.my_select(sql)
# 如果需要数据库某个字段与预期值对比，可以在pre_value中添加预期值
if pre_value != '':
self.logger.info("=========数据库查询结果中为：" +
str(sql_result[0][0]))
self.logger.info("=========预期数据库查询结果为：" +
str(pre_value))
write_excel.WriteExcel(REPORT_FILE_PATH,
"Sheet2").write_data(
data['row'], result,
str(res))
self.assertEqual(
str(sql_result[0][0]), str(pre_value),
"查询 --------- 用例{0}测试不通过，与预期返回结果总量不一致".format(
data["casenum"]))
# 如果field字段有值，进行响应结果字段校验
if field != '':
# 如果code_type是all，则比对接口返回的list中某一条记录
if code_type == 'all':
actual_res = eval(field)
# 接口返回的为键值对，处理成只有值方便与数据查询结果做比对
# 数据库查询结果为tuple，处理成tuple方便比对,调用写的方法deal处理数据
tuple1 = code_deal.CodeDeal().deal(actual_res)
# 预期结果与实际结果比较
if str(sql_result[0]) != str(tuple1):
result = "FAIL"
self.logger.info("响应结果中为：" + str(tuple1))
self.logger.info("预期结果中为：" + str(sql_result[0]))
write_excel.WriteExcel(REPORT_FILE_PATH,
"Sheet2").write_data(
data['row'], result,
str(res))
self.assertEqual(
str(sql_result[0]), str(tuple1),
"查询 --------- 用例{0}测试不通过，与预期返回结果总量不一致".format(
data["casenum"]))
else:
# eval执行field得到实际结果
actual_res = str(eval(field))
# 预期结果与实际结果比较
if str(sql_result[0][0]) != actual_res:
result = "FAIL"
self.logger.info("=======响应结果中为：" + actual_res)
write_excel.WriteExcel(REPORT_FILE_PATH,
"Sheet2").write_data(
data['row'], result,
str(res))
self.logger.info("=======预期结果中为：" +
str(sql_result[0][0]))
self.assertEqual(
str(sql_result[0][0]), actual_res,
"查询 --------- 用例{0}测试不通过，与预期返回结果总量不一致".format(
data["casenum"]))
else:
result = "FAIL"
# 无sql语句则直接断言code，结束
write_excel.WriteExcel(REPORT_FILE_PATH, "Sheet2").write_data(
data['row'], result, str(res))
self.logger.info("用例" + str(data["casenum"]) + "执行结果为：" +
str(res["code"] == int(data["pre"])))
self.assertEqual(
res["code"], int(data["pre"]),
"用例{0}测试不通过,与预期返回code不一致".format(data["casenum"]))
class HttpClient(object):
logger = my_log.Logger('../log/log/all.log', level='debug').logger
error_log = my_log.Logger('../log/log/error.log', level='error').logger
def my_request(self, ip, apiData, **kwargs):
try:
# Excel中获取参数传递
# 请求方法 post/get/update...
method = apiData["method"]
# 请求链接
url = ip + apiData["url"]
# 请求参数
if apiData["params"] == "":
params = None
else:
params = apiData["params"]
# 请求头
if apiData["headers"] == "":
header = HEADERS
else:
header = eval(apiData["headers"])
if apiData["cookies"] == "":
cookies = None
else:
cookies = {
x.split('=')[0]: x.split('=')[1]
for x in apiData["cookies"].split('; ')
}
# 请求体
if apiData["body"] == "":
body_data = None
else:
body_data = eval(apiData["body"])
if method == "post":
# 请求体类型 data/json/url
type1 = apiData["type"]
if type1.lower() == "data":
response = self.__post(url=url,
data=json.dumps(body_data),
headers=header,
**kwargs)
return response
elif type1.lower() == "json":
response = self.__post(url=url,
data=json.dumps(body_data),
params=params,
headers=header,
**kwargs)
return response
elif type1.lower() == "cookie":
response = self.__post(url=url,
data=json.dumps(body_data),
headers=header,
cookies=cookies,
**kwargs)
return response
else:
self.logger.warning(
"---------------------------------请求格式{0}不支持----------------------------------"
.format(type1))
elif method.lower() == "get":
# print(params)
# params = "token=eyJhbGciOiJIUzI1NiJ9.eyJqdGkiOiIyODcxIiwic3ViIjoiMTU1MjMzIiwiaWF0IjoxNjIwMjg2MTEwLCJyb2xlcyI6WyLnrqHnkIblkZjnlKjmiLciXSwiYXV0aG9yaXRpZXMiOltdLCJob3N0Ijoid3d3LnljemNqay5jb206ODAiLCJ1dWlkIjoiNmYwOTMwZDYtYWI5Yi00NjU3LWFmNmUtZjJkN2MwMDIzZTZlIiwiZXhwaXJlIjoxNjI4Njk3NTk5LCJleHAiOjE2MjAzNzI1MTB9.454MKbW3dpPXQrOneLiU4YxOH8m1zWnlsWmbFnQzsk0"
# print(params)
response = self.__get(url=url,
params=params,
headers=header,
**kwargs)
return response
else:
self.logger.warning(
"---------------------------------暂不支持{0}请求方法----------------------------------"
.format(method))
except Exception as e:
self.error_log.error("请求报错了---", e)
def __post(self,
url,
data=None,
params=None,
headers=None,
cookies=None,
**kwargs):
self.logger.info(
"--------------------------->{0}<---------------------------".
format("post请求"))
response = requests.post(url,
data=data,
params=params,
headers=headers,
cookies=cookies).json()
return response
def __get(self, url, params=None, **kwargs):
self.logger.info(
"--------------------------->{0}<---------------------------".
format("get请求"))
response = requests.get(url, params=params, **kwargs).json()
return response