class PaserExc(object):
"""
sheetIndex:所解析exel表的索引
"""
def __init__(self, path, sheetIndex):
self.log = Log()
if os.path.isfile(path):
if os.path.exists(path):
self.workbook = xlrd.open_workbook(path)
self.sheet = self.workbook.sheet_by_index(sheetIndex)
self.log.info("用例路径：{}".format(path))
else:
self.log.error("{}文件不存在".format(path))
else:
self.log.error("请检查{}路径是否正确".format(path))
# 获得总行数
def get_nrows(self):
return self.sheet.nrows
# 获得总列数
def get_ncols(self):
return self.sheet.ncols
# 按行获得所有数据
def get_row(self):
rowValue = []
for row in range(self.get_nrows()):
rowValue.append(self.sheet.row_values(row))
return rowValue
class ReadFile():
def __init__(self):
self.yaml_path = os.path.join(
os.path.dirname(os.path.dirname(__file__)), "config/config.yaml")
self.mysql_yaml_path = os.path.join(
os.path.dirname(os.path.dirname(__file__)), "config/mysql.yaml")
self.excel_path = os.path.join(
os.path.dirname(os.path.dirname(__file__)), "config/testcase.xls")
self.log = Log()
def read_yaml(self, path_type):
try:
if path_type == "yaml_path":
file_path = self.yaml_path
elif path_type == "mysql_path":
file_path = self.mysql_yaml_path
else:
print("文件类型输入错误")
"""
open与with open区别在于 open不关闭 需要手动f.close()
with open会自动关闭
"""
# f = open("file","r",encoding="utf-8")
# print(yaml.load(f.read()))
with open(file_path, "r", encoding="utf-8") as f:
return yaml.load(f.read(), Loader=yaml.FullLoader)
except Exception as e:
print("读取文件报错{}".format(e))
self.log.error("读取文件报错{}".format(e))
def read_excel(self, sheet_name, function, casename=None, excel_path=None):
if excel_path:
excel_path = excel_path
else:
excel_path = self.excel_path
"""
读取excel
:param sheet_name:
:param function:
:param casename:
:return:
"""
try:
book = xlrd.open_workbook(excel_path)
data = book.sheet_by_name(sheet_name)
param = []
# print("列数:%s" % data.nrows)
for i in range(0, data.nrows):
get_func_name = data.row_values(i)[0]
get_run = data.row_values(i)[3]
get_cas_name = data.row_values(i)[1]
if casename is None:
if get_func_name == function and get_run == 1:
param.append(data.row_values(i))
else:
if get_func_name == function and get_cas_name == casename and get_run == 1:
param.append(data.row_values(i))
return param
except Exception as e:
print("读取excel报错{}".format(e))
def base_get_gwasgen_cal_result(self, para_id, data_id, cookies, flag):
"""历史记录-查询GWAS-基因型计算结果公共方法"""
# 获取请求url
url_get_gwasgen_cal_result = self.domain + Base.dh.get_path(para_id)
Log.info('get_gwasgen_maf_result request url : {}'.format(
url_get_gwasgen_cal_result))
# 获取请求数据
data_source = self.dh.get_data(data_id)
req_para = Base.get_req_para(para_id=para_id, data_id=data_id)
# 查询数据库taskId，随机选择查询
req_para['taskId'] = self.get_gwasgen_done_task_id()
req_para['pageNo'] = eval(req_para['pageNo'])
req_para['pageSize'] = eval(req_para['pageSize'])
data_source[0][5] = req_para['taskId']
# 数据库查询taskId为空处理
if req_para['taskId'] is None:
actual = not flag
Log.error('数据库查询taskId为空，该用例未执行，失败')
else:
Log.info(
'get_gwasgen_cal_result request data is {}'.format(req_para))
# 请求
res = requests.post(url=url_get_gwasgen_cal_result,
headers=Base.headers,
cookies=cookies,
data=Base.sign(req_para)).json()
Log.info('get_gwasgen_cal_result response data is {}'.format(res))
# 结果检查
actual = self.get_gwasgen_result_check(req_para['taskId'], res,
flag)
# 结果写入
DataHandle.set_data(data_source[0], actual)
self.dh.write_data(data_source)
# 结果检查
return self.dh.check_result(data_source)
def test_cases(self, case):
global test_result
url = case['Url']
method = case['Method']
param = case['Param']
case_id = case['CaseId']
module = case['Module']
expected_result = case['ExpectedResult']
Log.info('用例参数为{}'.format(case))
actual_result = HttpRequest().http_request(
url, method, param, getattr(GetData, 'cookie')) # 返回 resp
if actual_result.cookies:
setattr(GetData, 'cookie', actual_result.cookies)
Log.info('第{}条{}模块用例，返回结果{}'.format(case_id, module,
actual_result.text))
try:
self.assertEqual(expected_result, actual_result.json())
test_result = 'Pass'
except Exception as e:
test_result = 'Fail'
Log.error(e)
raise e
finally:
Log.info(
'-----------------------------------------开始写回测试结果-----------------------------------------'
)
DoExcel(project_path.test_cases_path).write_excel(
'recharge', case_id + 1, 8, actual_result.text)
DoExcel(project_path.test_cases_path).write_excel(
'recharge', case_id + 1, 9, test_result)
class ReadExcel(object):
#参数excel_path是文件的绝对路径，sheet_name 是表名
def __init__(self, excel_path, sheet_name="Sheet1"):
self.data = xlrd.open_workbook(excel_path) #根据路径打开一个文件
self.table = self.data.sheet_by_name(sheet_name) #根据表名打开表
self.keys = self.table.row_values(0) #获取第一行作为字典的key值
self.rowNum = self.table.nrows #获取总行数
self.colNum = self.table.ncols #获取总列数
self.log = Log('读取Excel').get_logger()
#获取整张表的数据（数据装在列表中，列表的每个子元素是字典类型数据）
def get_dict_data(self):
if self.rowNum <= 1:
self.log.error('xlsx表的总行数小于1')
else:
r = [] #定义列表变量，把读取的每行数据拼接到此列表中
for row in range(1, self.rowNum): #对行进行循环读取数据，从第二行开始
s = {} #定义字典变量
s['rowNum'] = row + 1 #存储行数，从第二行开始读，行数等于下标加1
values = self.table.row_values(row) #获取行的数据
for col in range(0, self.colNum):
cell_value = values[col]
if isinstance(cell_value, (int, float)): #判断读取数据是否是整型或浮点型
cell_value = int(cell_value) #是，数据转换为整数
s[self.keys[col]] = str(
cell_value).strip() #获取到单元数据（去掉头尾空格）和key组成键对值
r.append(s) #把获取到行的数据装入r列表中
return r #返回整个表的数据
def put(self):
# param check
parser = reqparse.RequestParser()
parser.add_argument('DataIds', required=True)
args = parser.parse_args()
data_ids = args.get('DataIds').strip()
data_id_list = data_ids.split(',')
if len(data_id_list) == 0:
raise Exception('Invalid DataIds')
for data_id in data_id_list:
if not data_id.isdigit():
raise Exception('Invalid DataIds')
# update the flag to 1, which means important
db_conf = conf['DATABASE']
mysql = Mysql(db_conf['HOST'], db_conf['PORT'], db_conf['USER'],
db_conf['PASSWORD'], db_conf['DATABASE'])
result = mysql.connect()
if result['code'] != 0:
Log.error(result['info'])
raise Exception(result['info'])
data_ids = args.get('DataIds')
sql = "update table_1 set flag = 1 where id in ({}) and flag = 0".format(
data_ids)
result = mysql.write(sql)
if result['code'] != 0:
Log.error(result['info'])
raise Exception(result['info'])
return {'DataIds': data_ids}
def get(self):
db_conf = conf['DATABASE']
mysql = Mysql(db_conf['HOST'], db_conf['PORT'], db_conf['USER'],
db_conf['PASSWORD'], db_conf['DATABASE'])
result = mysql.connect()
if result['code'] != 0:
Log.error(result['info'])
raise Exception(result['info'])
sql = "select id,data1,data2,data3,data4,created_time from table_1 where created_time + interval 10 second > now()"
result = mysql.read(sql)
if result['code'] != 0:
Log.error(result['info'])
raise Exception(result['info'])
if len(result['data']) == 0:
raise Exception('No data in recent 10 seconds.')
data_list = []
for item in result['data']:
tmp = dict()
tmp['Id'] = item['id']
tmp['Data1'] = item['data1']
tmp['Data2'] = item['data2']
tmp['Data3'] = item['data3']
tmp['Data4'] = item['data4']
tmp['CreatedTime'] = item['created_time']
data_list.append(tmp)
return {'DataList': data_list}
def gen_status_check(self, res, status):
code = '00000'
if res['code'] == code:
taskId = res['data']
Log.debug('taskId is {}'.format(taskId))
# 等待双端状态同步
time.sleep(5)
# 待接收对端task存于task表，其它状态在task_history
if status == 1:
[data_n, data_o] = self.get_data(taskId)
else:
[data_n, data_o] = self.get_data_history(taskId)
Log.info('本端状态为： {}'.format(data_n['status']))
Log.info('对端状态为： {}'.format(data_o['status']))
Log.info('期望状态为：{}'.format(status))
if data_n['status'] == data_o['status'] and data_n[
'status'] == status:
Log.info('双端状态与期望状态一致，用例执行成功 !')
return status
else:
Log.error('双端状态与期望状态不一致，用例执行失败 !')
return 0
Log.error('请求返回有误！{}'.format(res))
return 0
def after_update_check(self, res, status):
""" 修改用户信息结果验证，检查请求返回值和数据库
1 成功 0 失败"""
code = '00000'
msg = "成功"
data = self.s.select_dic_single(self.sql_select_update)
Log.info("修改用户信息后用户状态:name={}，status={}".format(
data['name'], data['status']))
# 检查数据库group
if data['group'] != self.node:
Log.error('group error ! only {} allowed,it\'s {}'.format(
self.node, data['group']))
return 0
if status in self.user_status:
if res["code"] == code and res["message"] == msg \
and status == data['status']:
Log.debug('status valid,actual res check is 1')
return 1
else:
Log.debug('status valid,actual res check is 0')
return 0
elif res["code"] == code and res["message"] == msg:
Log.debug('status invalid,actual res check is 1')
return 1
else:
Log.debug('status invalid,actual res check is 0')
return 0
def find_brand_by_identify(word, dao, tbl_brands):
"""stock_identifyにつぶやきwordを当てて検索"""
brands = dao.table('stock_identify').find_query({"nm": word})
if not brands:
Log.error('wordがstock_identifyに無い。mecab辞書とidentifyが不一致')
return False
if len(brands) > 1:
brands_y = [b for b in brands if b['main'] == 'y']
if len(brands_y) > 1:
Log.error('mainで同じnmが複数登録されている: brands_y={}'.format(brands_y))
return False
elif len(brands_y) == 1:
return brands[0]
else:
# ログだけ出して、複数nの１件目を使うことにする
Log.warn('main=y がいない。nが複数。 brands={}'.format(brands))
elif len(brands) == 1:
b = brands[0]
if b['nm'] == b['ccode'] and b['main'] == 'n':
brand = tbl_brands.find_by_key(b['ccode'])
b['nm'] = brand['name']
return brands[0]
def get_list_by_accept_check(res):
code = '00000'
message = '成功'
if res['code'] == code and res['message'] == message:
Log.info('actual res check is 1 ,success !')
return 1
Log.error('请求返回有误！{}'.format(res))
return 0
def get_detail_by_accept_check(self, taskId, res):
"""1 成功 0 失败"""
code = '00000'
if res["code"] != code:
Log.error('返回错误：{}'.format(res))
return 0
sql_task = 'select * from t_task where id = %d' % taskId
data_database = self.con_n.select_dic_single(sql_task)
data_res = res['data']
if self.compare(data_database, data_res):
Log.info('data equal, compare success')
return 1
Log.error('data different, compare fail')
return 0
def get_error_result_check(self, taskId, res, flag):
"""1 成功 0 失败"""
code = '00000'
if flag and res['code'] != code:
Log.error('请求返回应该为成功，实际失败 {}'.format(res))
return 0
if not flag and res['code'] == code:
Log.error('异常用例请求返回应该为失败，实际成功 {}'.format(res))
return 1
if res["code"] != code:
Log.error('返回错误：{}'.format(res))
return 0
sql_res = 'select * from t_task_history where id = %d' % taskId
Log.debug('====查询sql：{}'.format(sql_res))
data_database = self.con_n.select_dic_single(sql_res)
Log.debug('=数据库查询结果={}={}='.format(data_database['errType'],
data_database['errRole']))
data_res = res['data']
Log.debug('*接口返回*{}*{}*'.format(data_res['errType'],
data_res['errRole']))
if data_database['errType'] == data_res['errType'] and data_database[
'errRole'] == data_res['errRole']:
Log.info('data equal, compare success')
return 1
Log.error('data different, compare fail')
return 0
def get_logic_check(self, res, flag):
code = '00000'
if flag and res['code'] != code:
Log.error('请求返回应该为成功，实际失败 {}'.format(res))
return 0
if not flag and res['code'] == code:
Log.error('异常用例请求返回应该为失败，实际成功 {}'.format(res))
return 1
if res["code"] != code:
Log.error('请求返回有误 {}'.format(res))
return 0
# 查询数据库服务器列表
sql_get_logic = 'select * from t_logic order by id'
data_database = self.con_n.select_dic(sql_get_logic)
# 列表中的字典元素按key排序
for i in range(len(data_database)):
data_database[i] = Base.order_dic_by_keys(data_database[i])
Log.info('数据库t_logic列表排序后：{}'.format(data_database))
# 列表元素按id升序排列
data_res = sorted(res['data'], key=lambda k: k['id'])
# 列表中的字典元素按key排序
for i in range(len(data_res)):
data_res[i] = Base.order_dic_by_keys(data_res[i])
Log.info('接口查询logic列表排序后：{}'.format(data_res))
if data_database == data_res:
Log.info('check success,result is 1')
return 1
Log.error('接口返回数据与数据库查询不一致!')
return 0
def find_brand_by_identify(self, word):
"""stock_brandsのidentifyにつぶやきwordを当てて検索"""
brands = list(
self.dao.table('stock_brands').find({"identify": {
"$in": [word]
}}, {'_id': 0}))
if not brands:
Log.error('wordがstock_brandsに無い。mecab辞書とidentifyが不一致')
return False
if len(brands) > 1:
Log.warn(
"confrict word!! stock_brandsのidentifyフィールドに同じwordをもつ銘柄がある。 word={}",
word)
return False
return AttrDict(brands[0])
def get_server_check(self, res):
code = '00000'
if res["code"] != code:
Log.error('请求返回有误 {}'.format(res))
return 0
# 查询数据库服务器列表
sql_get_server = 'select id serverId,serverName name from t_server order by id'
data_database = self.con_n.select_dic(sql_get_server)
Log.info('数据库服务器列表：{}'.format(data_database))
# 按id升序排列
data_res = sorted(res['data'], key=lambda k: k['serverId'])
Log.info('接口查询服务器列表：{}'.format(data_res))
if len(data_database) == len(data_res):
Log.info('check success,result is 1')
return 1
Log.error('接口返回数据与数据库查询不一致!')
return 0
class ConfigHttp:
def __init__(self):
self.host = localReadConfig.get_http("baseurl")
self.port = localReadConfig.get_http("port")
self.timeout = localReadConfig.get_http("timeout")
self.logger = Log().get_logger()
self.headers = {}
self.params = {}
self.data = {}
self.url = ""
self.files = {}
def set_url(self, url, host=""):
if host == "":
self.url= self.host + url
else:
self.url = host + url
def set_headers(self, header):
self.headers = header
def set_params(self, param):
self.params = param
def set_data(self, data):
self.data = data
def set_files(self, file):
self.files = file
# define http method
def get(self):
try:
response = requests.get(self.url, params=self.params, headers=self.headers, timeout=float(self.timeout))
return response
except TimeoutError:
self.logger.error("Time out!")
return None
def post(self):
try:
response = requests.post(self.url, data=self.data, headers=self.headers, timeout=float(self.timeout))
return response
except TimeoutError:
self.logger.error("Time out!")
return None
def updata(self, sql):
conn = pymysql.connect(
host=self.conf.get('host'),
port=int(self.conf.get('port')),
user=self.conf.get('user'),
password=self.conf.get('password'),
database=self.conf.get('database'),
)
cur = conn.cursor()
try:
cur.execute(sql)
conn.commit()
except:
# 发生错误回滚
conn.rollback()
Log.error("updata fail ! {}".format(sql))
cur.close()
conn.close()
def gwas_check(self, res):
code = '00000'
if res['code'] == code:
Log.info('result check success ')
return 1
# taskId = res['data']
# Log.debug('taskId is {}'.format(taskId))
# [data_n, data_o] = self.get_data(taskId)
# Log.info('本端数据为： {}'.format(data_n))
# Log.info('对端数据为： {}'.format(data_o))
# if self.compare(data_n, data_o):
# Log.info('compare result is True ,success !')
# return 1
# else:
# Log.error('compare result is False ,fail !')
# return 0
Log.error('请求返回有误！{}'.format(res))
return 0
def select_dic(self, sql):
"""返回字典"""
conn = pymysql.connect(host=self.conf.get('host'),
port=int(self.conf.get('port')),
user=self.conf.get('user'),
password=self.conf.get('password'),
database=self.conf.get('database'),
cursorclass=pymysql.cursors.DictCursor)
cur = conn.cursor()
try:
cur.execute(sql)
result = cur.fetchall()
return result
except:
Log.error('select_dic error !')
finally:
cur.close()
conn.close()
def find_brand_by_identify(self, word):
"""stock_identifyにつぶやきwordを当てて検索"""
brands = self.dao.table('stock_identify').find_query({"nm": word})
if not brands:
Log.error('wordがstock_identifyに無い。mecab辞書とidentifyが不一致')
return False
if len(brands) > 1:
brands_y = [b for b in brands if b['main'] == 'y']
if len(brands_y) > 1:
Log.error('mainで同じnmが複数登録されている: brands_y={}'.format(brands_y))
return False
elif len(brands_y) == 1:
return brands[0]
else:
# ログだけ出して、複数nの１件目を使うことにする
Log.warn('main=y がいない。nが複数。 brands={}'.format(brands))
return brands[0]
def http_request(self, url, method, param, cookie):
global resp
if method.upper() == 'GET':
try:
resp = requests.get(url, params=param, cookies=cookie)
# print(resp)
# print(resp.text)
# print(resp.json())
except Exception as e:
Log.error('GET请求出错啦{}'.format(e))
elif method.upper() == 'POST':
try:
resp = requests.post(url, data=param, cookies=cookie)
# result = resp.text()
except Exception as e:
Log().error('POST请求出错啦{}'.format(e))
else:
print('不支持该方法')
return resp
def download_check(res, flag):
# code = 13333
Log.debug('test1:res是否有.json:{}'.format(res.json()))
if not flag:
try:
json_res = res.json()
Log.error('请求返回有误：{}'.format(json_res))
return 0
except Exception:
Log.error("返回错误")
return 1
# if not flag and eval(res.json()["code"]) == code:
# Log.info('请求返回错误:{}'.format(res.json()))
# return 0
elif res.content is not None and res.status_code == 200:
Log.info('文件下载接口ok')
return 1
return not flag
def vcf_check(self, res, flag):
code = '00000'
if flag and res['code'] != code:
raise AssertionError('请求返回应该为成功，实际失败 {}'.format(res))
if not flag and res['code'] == code:
raise AssertionError('异常用例请求返回应该为失败，实际成功 {}'.format(res))
if res['code'] == code:
Log.info('result check success ')
return 1
# taskId = res['data']
# Log.debug('taskId is {}'.format(taskId))
# [data_n, data_o] = self.get_data(taskId)
# Log.info('本端数据为： {}'.format(data_n))
# Log.info('对端数据为： {}'.format(data_o))
# if self.compare(data_n, data_o):
# Log.info('compare result is True ,success !')
# return 1
# else:
# Log.error('compare result is False ,fail !')
# return 0
Log.error('请求返回有误！{}'.format(res))
return 0
def set_data(data_ori, act):
"""
设置单条数据测试结果
:param data_ori:
:param act:
:return:
"""
# 数据源列索引
actual = 2
expect = 3
result = 4
try:
data_ori[actual] = act
if act == eval(data_ori[expect]):
data_ori[result] = 'success'
Log.info('test result is success')
else:
data_ori[result] = 'fail'
Log.error('test result is fail')
except IndexError as e:
print('error %s' % e)
def cancel_check(self, res, temp, flag):
code = '00000'
if flag and res['code'] != code:
raise AssertionError('请求返回应该为成功，实际失败 {}'.format(res))
if not flag and res['code'] == code:
raise AssertionError('异常用例请求返回应该为失败，实际成功 {}'.format(res))
if not res['code'] == code:
Log.error('请求返回有误！{}'.format(res))
return 0
else:
if temp['startTaskId'] is None:
status = 4
id = temp['id']
sql_n = 'select * from t_task_history where id = %d ' % id
sql_o = 'select * from t_task_history where startTaskId = %d ' % id
else:
status = 5
id = temp['startTaskId']
sql_n = 'select * from t_task_history where startTaskId = %d ' % id
sql_o = 'select * from t_task_history where id = %d ' % id
Log.debug('本端查询sql：{}'.format(sql_n))
Log.debug('对端查询sql：{}'.format(sql_o))
data_n = self.con_n.select_dic_single(sql=sql_n)
Log.info('本端数据为： {}'.format(data_n))
time.sleep(10)
try:
data_o = self.con_o.select_dic_single(sql=sql_o)
except IndexError as e:
raise AssertionError('数据库查询为空，exception is:{}'.format(e))
Log.info('对端数据为： {}'.format(data_o))
if self.compare(data_n, data_o, status):
Log.info('compare result is True ,success !')
return 1
else:
Log.error('compare result is False ,fail !')
return 0
def get_new_data():
# get data
try:
rsp = requests.get('http://localhost:5000/producer/data')
except Exception as e:
Log.error("ask api for new data error[{}]".format(str(e)))
return
if rsp.status_code != 200:
Log.error("get data error[{}]".format(rsp.text))
return
result = rsp.json()
if result['Code'] != 200:
Log.error("get data error[{}]".format(result['Message']))
return
# insert data
value_list = []
for item in result['Data']['DataList']:
value = "({Data1},{Data2},{Data3},{Data4},now())".format(
Data1=item['Data1'],
Data2=item['Data2'],
Data3=item['Data3'],
Data4=item['Data4'])
value_list.append(value)
db_conf = conf['DATABASE']
mysql = Mysql(db_conf['HOST'], db_conf['PORT'], db_conf['USER'],
db_conf['PASSWORD'], db_conf['DATABASE'])
result = mysql.connect()
if result['code'] != 0:
Log.error(result['info'])
return
# if the data is large, segmentation is required
sql = "insert into table_1(data1,data2,data3,data4,created_time) values" + ','.join(
value_list)
result = mysql.write(sql)
if result['code'] != 0:
Log.error(result['info'])
def upload_and_return_id(self, filename, para_id, fileType, cookies):
""" 上传文件，返回fileid"""
# 获取请求url
url_upload = self.domain + Base.dh.get_path(para_id)
Log.info('upload request url : {}'.format(url_upload))
# 获取请求数据格式
req_para = Base.get_req_para(para_id=para_id, data_id=10001)
# 拼接文件绝对路径
req_para['fileName'] = filename
Log.debug('文件绝对路径：{}'.format(self.gene_file(req_para['fileName'])))
Log.debug('********：{}'.format(
type(self.gene_file(req_para['fileName']))))
try:
f = open(self.gene_file(req_para['fileName']), 'rb')
Log.debug('*********{}****'.format(type(f)))
files = {'file': f}
Log.info('要上传的文件是 :{}'.format(f.name))
except Exception as e:
Log.error('上传文件为空')
raise AssertionError('文件不存在 ! {}'.format(e))
# 接口数据设置
req_para['fileType'] = fileType
req_para['fileSize'] = 7000
Log.info('upload request data is {}'.format(json.dumps(req_para)))
# 请求接口
try:
res = requests.post(url=url_upload,
cookies=cookies,
data=req_para,
files=files).json()
Log.info('upload response data is {}'.format(res))
except Exception as e:
raise AssertionError("文件上传失败！{}".format(e))
return res['data']
def run(self):
try:
suit = self.set_case_suite()
if suit is not None:
logging.info("********TEST START********")
fp = open(resultPath, 'wb')
runner = HTMLTestRunnerNew.HTMLTestRunner(
stream=fp,
title='Test Report',
description='Test Description')
runner.run(suit)
else:
logging.info("Have no case to test.")
except Exception as ex:
logging.error(str(ex))
finally:
logging.info("*********TEST END*********")
# send test report by email
if int(on_off) == 0:
self.email.send_email()
elif int(on_off) == 1:
logging.info("Doesn't send report email to developer.")
else:
logging.info("Unknow state.")
class ConMysql(object):
def __init__(self, sqlInfo=mysqlInfo):
self.conn = pymysql.connect(**sqlInfo)
self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
self.log = Log()
# 删除表中所有数据
def truncate_data(self, table):
self.execute_sql("TRUNCATE TABLE {}".format(table))
def execute_sql(self, sql):
self.cursor.execute(sql)
def query_one(self, sql):
try:
self.cursor.execute(sql)
datas = self.cursor.fetchone()
except Exception as e:
self.log.error("sql语句错误---->{}".format(sql))
return None
return datas
def query_all(self, sql):
try:
self.cursor.execute(sql)
datas = self.cursor.fetchall()
except Exception as e:
self.log.error("sql语句错误---->{}".format(sql))
return None
return datas
def insert_data(self, table, **kwargs):
sql = "INSERT INTO {} SET ".format(table)
for key in kwargs.keys():
if not key.__eq__("caseId"):
if isinstance(kwargs[key], dict):
kwargs[key] = json.dumps(kwargs[key], ensure_ascii=False)
elif isinstance(kwargs[key], str):
kwargs[key] = kwargs[key].replace("\'", "\"")
elif isinstance(kwargs[key], int):
kwargs[key] = kwargs[key]
elif kwargs[key].__eq__(""):
continue
sql += "{}='{}',".format(key, kwargs[key])
sql = sql[:-1]
try:
self.cursor.execute(sql)
self.conn.commit()
except Exception as e:
self.log.error("sql语句错误---->{}".format(sql))
def close(self):
self.cursor.close()
self.conn.close()
