def test_datasource_link(source_id, source_type, auth_type, source_host,
source_port, source_database, source_user,
source_password):
"""测试数据源连接"""
if source_id:
detail = DataSourceModel.get_datasource_detail(
db.etl_db, source_id)
if isinstance(detail['source_password'], bytes):
detail['source_password'] = detail['source_password'].decode(
'utf-8', 'ignore')
data = test_db_conn(detail['source_type'], detail['auth_type'],
detail['source_host'], detail['source_port'],
detail['source_database'],
detail['source_user'],
detail['source_password'])
if data['tag']:
DataSourceModel.update_datasource_status(
db.etl_db, source_id, 0)
return Response(status=200, msg=data['msg'])
else:
DataSourceModel.update_datasource_status(
db.etl_db, source_id, 1)
return Response(status=400, msg=data['msg'])
else:
data = test_db_conn(source_type, auth_type, source_host,
source_port, source_database, source_user,
source_password)
if data['tag']:
return Response(status=200, msg=data['msg'])
else:
return Response(status=400, msg=data['msg'])
def delete_datasource_detail(source_id):
"""删除数据源"""
prep_count = ParamsModel.get_param_by_source_id(db.etl_db, source_id)
if prep_count:
abort(
400,
**make_result(status=400,
msg='数据源在任务参数表中存在%s个依赖, 不可删除' % prep_count))
DataSourceModel.delete_datasource_detail(db.etl_db, source_id)
return Response(source_id=source_id)
def get_datasource_detail(source_id):
"""获取数据源详情"""
result = DataSourceModel.get_datasource_detail(db.etl_db, source_id)
if isinstance(result['source_password'], bytes):
result['source_password'] = result['source_password'].decode(
'utf-8', 'ignore')
return Response(result=result)
def add_datasource_detail(source_name, source_type, auth_type, source_host,
source_port, source_database, source_user,
source_password, source_desc, user_id):
"""新增数据源"""
source_id = DataSourceModel.add_datasource_detail(
db.etl_db, source_name, source_type, auth_type, source_host,
source_port, source_database, source_user, source_password,
source_desc, user_id)
return Response(source_id=source_id)
def update_datasource_detail(source_id, source_name, source_type,
auth_type, source_host, source_port,
source_database, source_user, source_password,
source_desc, is_deleted, user_id):
"""修改数据源"""
if is_deleted == 1:
prep_count = ParamsModel.get_param_by_source_id(
db.etl_db, source_id)
if prep_count:
abort(
400,
**make_result(status=400,
msg='数据源在任务参数表中存在%s个依赖, 不可设置失效' %
prep_count))
DataSourceModel.update_datasource_detail(
db.etl_db, source_id, source_name, source_type, auth_type,
source_host, source_port, source_database, source_user,
source_password, source_desc, user_id, is_deleted)
return Response(source_id=source_id)
def get_datasource_list(source_name, source_type, source_host, is_deleted,
page, limit):
"""获取数据源列表"""
condition = []
if source_name:
condition.append('source_name LIKE "%%%%%s%%%%"' % source_name)
if source_host:
condition.append('source_host LIKE "%%%%%s%%%%"' % source_host)
if source_type:
condition.append('source_type = %s' % source_type)
if is_deleted == 1:
condition.append('is_deleted = 0')
elif is_deleted == 2:
condition.append('is_deleted = 1')
condition = 'WHERE ' + ' AND '.join(condition) if condition else ''
result = DataSourceModel.get_datasource_list(db.etl_db, condition,
page, limit)
total = DataSourceModel.get_datasource_list_count(db.etl_db, condition)
return Response(result=result, total=total)
def test_params_detail(source_id, param_value):
"""测试SQL参数"""
item = DataSourceModel.get_datasource_by_id(db.etl_db, source_id)
if not item:
abort(400, **make_result(status=400, msg='该数据源id不存在'))
if isinstance(item['source_password'], bytes):
item['source_password'] = item['source_password'].decode(
'utf-8', 'ignore')
# 获取数据源数据
result_data = get_db_data_one(item['source_type'], item['source_host'],
item['source_port'], item['source_user'],
item['source_password'],
item['source_database'],
item['auth_type'], param_value)
return Response(result=result_data['result'],
msg=result_data['msg'],
flag=result_data['flag'])
def get_datasource_list_all():
"""获取所有数据源"""
result = DataSourceModel.get_datasource_list_all(db.etl_db)
return Response(result=result)
def ParamsUpload():
"""上传任务参数文件"""
# 文件路径
file_dir = './uploads'
# 文件类型
file_type = {'xlsx', 'xls', 'csv'}
# 异常类型
err_type = {
0: '第%s行[参数类型]不为整型',
1: '第%s行[参数名称]不为字符串类型',
2: '第%s行[数据源id]不为整型',
3: '第%s行[参数值]不为字符串类型',
4: '第%s行[描述]不为字符串类型',
5: '第%s行[参数目录]不为字符串类型'
}
file = request.files['file']
# 获取文件名
file_name = file.filename
# 获取文件后缀
file_suffix = '.' in file_name and file_name.rsplit('.', 1)[1]
if file and file_suffix in file_type:
# 保存文件到upload目录
file_path = os.path.join(file_dir, file_name)
file.save(file_path)
# excel文件
if file_suffix in {'xlsx', 'xls'}:
data = xlrd.open_workbook(file_path)
# 只取第一个sheet
sheet = data.sheet_by_name(data.sheet_names()[0])
# 从第2行开始读取
data = []
for i in range(1, sheet.nrows):
# 取前6列
data.append(sheet.row_values(i)[:6])
# csv文件
else:
data = []
with open(file_path, "r") as csv_file:
reader = csv.reader(csv_file)
for index, line in enumerate(reader):
if index > 0:
# 取前6列
data.append(line[:6])
# 异常原因
err_msg = []
# 数据源id列表
source_result = DataSourceModel.get_datasource_list_all(db.etl_db)
source_ids = [i['source_id'] for i in source_result]
# 文件为空
if not data:
err_msg.append('文件为空')
for index, row in enumerate(data):
# Excel行号
row_num = index + 2
# 校验列数
if len(row) < 5:
err_msg.append('第%s行参数个数小于5个' % row_num)
else:
# 校验并处理每列参数
for i, param in enumerate(row):
try:
# int类型参数
if i == 0:
row[i] = int(param)
# 数据源id
elif i == 2:
row[i] = int(row[i]) if int(row[0]) == 1 else 0
# 字符串类型参数
elif i in [1, 3, 4, 5]:
row[i] = str(param)
except:
err_msg.append(err_type[i] % row_num)
for i, param in enumerate(row):
# [参数类型]校验
if i == 0 and param not in [0, 1, 2]:
err_msg.append('第%s行[参数类型]不为0, 1或2' % row_num)
# [参数名称]校验
if i == 1 and param == '':
err_msg.append('第%s行[参数名称]不得为空' % row_num)
# [参数名称]数据库查重
if i == 1 and param != '':
if ParamsModel.get_params_detail_by_name(db.etl_db, param):
err_msg.append('第%s行[参数名称]已存在数据库中' % row_num)
# [数据源id]校验
if i == 2 and int(row[0]) == 1:
if param not in source_ids:
err_msg.append('第%s行[数据源id]不存在' % row_num)
# [参数值]校验
if i == 3 and param == '':
err_msg.append('第%s行[参数值]不得为空' % row_num)
# [参数目录]校验
if i == 5 and param == '':
err_msg.append('第%s行[参数目录]不得为空' % row_num)
# 返回异常信息
if err_msg:
# 删除文件
os.remove(file_path)
return jsonify({'status': 401, 'msg': '文件类型错误', 'data': {'err_msg': err_msg}})
# 写入数据库
else:
# 用户id
user_info = curr_session.get_info()
user_id = user_info['id']
params_data = []
for line in data:
params_data.append({
'param_type': line[0],
'param_name': line[1],
'param_index': line[5],
'source_id': line[2],
'param_value': line[3],
'param_desc': line[4],
'insert_time': int(time.time()),
'update_time': int(time.time()),
'creator_id': user_id,
'updater_id': user_id
})
if params_data:
ParamsModel.add_param_many(db.etl_db, params_data)
# 删除文件
os.remove(file_path)
return jsonify({'status': 200, 'msg': '成功', 'data': {}})
else:
return jsonify({'status': 400, 'msg': '文件类型错误', 'data': {}})