def xlutils_excel(self, row_value, col_value, value, info=None):
rb = xlrd.open_workbook(self.file_path,
formatting_info=True) # formatting_info 带格式导入
wb = copy(rb) # 利用xlutils.copy下的copy函数复制
ws = wb.get_sheet(0) # 获取表单0
rs = rb.sheet_by_index(0)
# ws.write(0, 0, 'changed!') # 改变（0,0）的值
value_row_col = rs.cell(row_value, col_value).value # 表格原有值
if not value:
ws.write(row_value, col_value, value)
else:
if not value_row_col:
ws.write(row_value, col_value, value) # 增加指定列的值
else:
ws.write(row_value, col_value,
"{}；{}".format(value_row_col, value)) # 增加指定列的值
if info:
logger.debug("{}:({}, {}, {})".format(info, row_value, col_value,
value))
else:
logger.debug((row_value, col_value, value))
try:
wb.save(self.file_path) # 保存文件
except Exception as msg:
logger.error("保存excel文件失败:{}".format(msg))
def request_get(self, url, data):
timestampt = int(time.time() * 1000)
# is_true = self.is_json(data)
# print(is_true)
# if isinstance(is_true, dict):
# data = is_true
# else:
# url = "{}/{}".format(url, data)
# data = None
data_dict = {
"accountNo": "T9-TServer",
"secretCode": "fbw77t42dlb3cw6z",
"timestamp": timestampt
}
data_dict["data"] = data
authentication = sign.get_authentication(None, data_dict)
print(authentication)
header_dict = {
"requestId": "1",
"timestamp": str(timestampt),
"authentication": authentication
}
logger.debug("请求url:{};请求头:{};请求数据:{}".format(url, header_dict, data))
res = requests.get(url=url, headers=header_dict, params=data)
return res
def select_all(self, sql):
try:
self._cursor.execute(sql)
res = self._cursor.fetchall()
self._conn.commit()
logger.debug("SQL语句【{}】，执行结果【{}】".format(sql, res))
except pymysql.Error as e:
res = False
logger.warn("Select database exception:{}".format(e))
return res
def update(self, sql):
try:
self._cursor.execute(sql)
self._conn.commit()
logger.debug("SQL语句【{}】，执行结果【{}】".format(sql, "True"))
# logger.debug("Update database successfully")
except pymysql.Error as e:
logger.warn("Update database exception.{}".format(e))
self._conn.rollback()
return False
return True
def select_one(self, sql):
try:
self._cursor.execute(sql)
res = self._cursor.fetchone() # 获取第一行数据
# res = self._cursor.fetchmany(3) # 获取前n行数据
self._conn.commit()
logger.debug("SQL语句【{}】，执行结果【{}】".format(sql, res))
except pymysql.Error as e:
res = False
logger.warn("Select database exception.{}".format(e))
return res
def read_excel(self, file_name):
if isinstance(file_name, str): # 判断是否是字符串类型
if file_name.endswith('.xls'): # 判断字符串以什么结尾
self.file_path = os.path.dirname(os.path.dirname(
__file__)) + '/dataFile/{}'.format(file_name)
workbook = xlrd.open_workbook(self.file_path) # 打开一个workbook
# 抓取所有sheet页的名称
worksheets = workbook.sheet_names()
for worksheet_name in worksheets:
worksheet = workbook.sheet_by_name(worksheet_name)
# 遍历sheet1中所有行row
num_rows = worksheet.nrows # 获取sheet行数
row_demo = [
'用例集编号', '用例标识', '模块名称', '接口名称', '用例描述', '请求url',
'请求方式', '请求头部', '用例说明', '前置处理', '请求数据', '匹配规则', '预期结果',
'后置处理', '优先级', '是否执行', '被依赖value', '响应数据', '状态码',
'执行结果', '执行时间', '备注'
]
# 判断模板第二行格式
# print(worksheet.row_values(1))
if worksheet.row_values(
1)[0:22] == row_demo: # 判断sheet1表头是否与预期模板一致
all_row_value = []
for curr_row in range(num_rows -
2): # 获取表格三行以后数据，包括第三行
row_value = worksheet.row_values(curr_row + 2)
all_row_value.append(
row_value[0:17]) # 将表格0-16列数据转化为列表
logger.debug("{}".format(
worksheet.row_values(1))) # 表头
logger.debug("{}".format(all_row_value)) # 表格0-16列数据
return all_row_value # 返回第三列后的所有数据
else:
logger.error("用例模板表头格式有误")
exit(0)
# return "用例模板格式有误"
else:
logger.error("读取的不是.xls文件")
exit(0)
else:
logger.error("用例文件格式有误")
exit(0)
def request_post(self, url, data):
timestampt = int(time.time() * 1000)
data_dict = {
"accountNo": "T9-TServer",
"secretCode": "fbw77t42dlb3cw6z",
"timestamp": timestampt
}
data_dict["data"] = data
authentication = sign.get_authentication(None, data_dict)
header_dict = {
"Content-Type": "application/json",
"requestId": "1",
"timestamp": str(timestampt),
"authentication": authentication
}
# print(header_dict)
logger.debug("请求url:{};请求头:{};请求数据:{}".format(url, header_dict, data))
res = requests.post(url=url, headers=header_dict, json=data) # 发送请求
# data_eval = json.loads(r.text) # 字符串转成字典类型
# print(data_eval)
return res
def del_redis(self, key_data):
list_keys = self.r.keys(key_data)
logger.debug("Redis to delete data【{}】".format(list_keys))
for key in list_keys:
res = self.r.delete(key)
logger.debug("Redis delete data【{}】 Delete status【{}】".format(key, res))