def set_index(desc, dashboard_id):
"""
设置首页
---
tags:
- BI
parameters:
- name: dashboard_id
type: int
required: true
description: 要设置为首页的看板ID
responses:
0:
description: ok
"""
auth_token = request.headers.get('Authorization')
current_user_id = Users.decode_auth_token(auth_token)
if Dashboard.exist_item_by_id(dashboard_id):
UserDashboard.query.filter_by(dmp_user_id=current_user_id).delete()
index_page = UserDashboard(dmp_user_id=current_user_id,
dmp_dashboard_id=dashboard_id)
index_page.save()
return resp_hanlder(code=0, result="OK")
def get_queries_by_id(desc, query_id):
"""
根据ID获取保存的SQL
---
tags:
- SQL
parameters:
- name: query_id
in:
type: int
required: ture
description: 查询的ID
responses:
0:
description: ok
"""
auth_token = request.headers.get('Authorization')
current_user_id = Users.decode_auth_token(auth_token)
if SavedQuery.exist_item_by_id(query_id):
query_obj = SavedQuery.get(query_id)
if query_obj.created_dmp_user_id == current_user_id:
query_info = query_obj.__json__()
result = {"data": query_info}
return resp_hanlder(code=0, result=result)
else:
return resp_hanlder(code=999, msg="非改查询的所有者，无权查看")
else:
return resp_hanlder(code=999, msg="查询不存在或已被删除")
def del_queries_by_id(desc, query_id):
"""
删除保存的SQL
---
tags:
- SQL
parameters:
- name: query_id
in:
type: int
required: true
description: 要删除的查询的ID
responses:
o:
description: ok
"""
auth_token = request.headers.get('Authorization')
current_user_id = Users.decode_auth_token(auth_token)
if SavedQuery.exist_item_by_id(query_id):
current_queries = SavedQuery.get(query_id)
if current_queries.created_dmp_user_id == current_user_id:
current_queries.delete()
return resp_hanlder(code=0, result="OK")
else:
return resp_hanlder(code=999, msg="您不是该查询的所有者,无权删除改查询")
else:
return resp_hanlder(code=999, msg="该查询不存在或已被删除")
def del_archive_star(desc, archive_id):
"""
文件夹取消置顶
---
tags:
- BI
parameters:
- name: archive_id
type: int
required: true
description: 要取消置顶的文件夹ID
responses:
0:
description: ok
"""
auth_token = request.headers.get('Authorization')
current_user_id = Users.decode_auth_token(auth_token)
if DashboardArchive.exist_item_by_id(archive_id):
is_exists = db.session.query(exists().where(
and_(ArchiveStar.dmp_user_id == current_user_id,
ArchiveStar.dmp_archive_id == archive_id))).scalar()
if is_exists:
ArchiveStar.query.filter_by(dmp_user_id=current_user_id,
dmp_archive_id=archive_id).delete()
return resp_hanlder(code=0, msg="OK")
else:
return resp_hanlder(code=999, msg="文件夹未置顶")
else:
return resp_hanlder(code=999, msg="文件夹不存在或已被删除")
def migration(desc):
if request.method == "POST":
try:
auth_token = request.headers.get('Authorization')
submit_dmp_user_id = Users.decode_auth_token(auth_token)
form_info = request.json
origin_dmp_table_id = form_info.get("origin_dmp_table_id")
rule = form_info.get("rule")
destination_dmp_database_id = form_info.get(
"destination_dmp_database_id")
new_table_name = form_info.get("new_table_name")
method = form_info.get("method")
description = form_info.get("description")
new_form_info = FormMigrate(
origin_dmp_table_id=origin_dmp_table_id,
destination_dmp_database_id=destination_dmp_database_id,
new_table_name=new_table_name,
)
new_form_info.save()
new_form = Forms(submit_dmp_user_id=submit_dmp_user_id,
description=description,
form_info_id=new_form_info.fid,
form_type=3)
new_form.save()
current_app.logger.info(new_form.id)
return resp_hanlder(result="OK")
except Exception as err:
return resp_hanlder(code=999, err=err)
def from_db(desc):
if request.method == "POST":
try:
auth_token = request.headers.get('Authorization')
submit_dmp_user_id = Users.decode_auth_token(auth_token)
form_info = request.json
data_tablename = form_info.get("data_tablename")
db_tablename = form_info.get("db_tablename")
database_id = form_info.get("database_id")
dmp_data_case_id = form_info.get("dmp_data_case_id")
description = form_info.get("description")
new_form_info = FormAddDataTable(
dmp_data_table_name=data_tablename,
db_table_name=db_tablename,
dmp_database_id=database_id,
dmp_case_id=dmp_data_case_id,
)
new_form_info.save()
new_form = Forms(submit_dmp_user_id=submit_dmp_user_id,
description=description,
form_info_id=new_form_info.fid,
form_type=1)
new_form.save()
return resp_hanlder(result="OK")
except Exception as err:
return resp_hanlder(code=999, err=err)
def dbtdel(desc):
if request.method == "DELETE":
try:
auth_token = request.headers.get('Authorization')
current_user_id = Users.decode_auth_token(auth_token)
del_data_table_id = request.json.get("dmp_data_table_id")
if del_data_table_id:
del_data_table = DataTable.get(del_data_table_id)
if del_data_table:
is_user = Users.get(
DataTable.get(del_data_table_id).dmp_user_id
).id == current_user_id
is_user_leader = Users.get(
DataTable.get(del_data_table_id).dmp_user_id
).leader_dmp_user_id == current_user_id
is_admin = Users.get(current_user_id).dmp_group_id == 1
if is_user or is_user_leader or is_admin:
del_data_table.delete()
current_app.logger.info("del db table complete!")
return resp_hanlder(result="OK")
else:
return resp_hanlder(code=302)
else:
return resp_hanlder(code=404)
else:
return resp_hanlder(code=101)
except Exception as err:
return resp_hanlder(err=err)
def del_dashboard_star(desc, dashboard_id):
"""
看板取消置顶
---
tags:
- BI
parameters:
- name: dashboard_id
type: int
required: true
description: 要取消置顶的看板ID
responses:
0:
description: ok
"""
auth_token = request.headers.get('Authorization')
current_user_id = Users.decode_auth_token(auth_token)
if Dashboard.exist_item_by_id(dashboard_id):
is_exists = db.session.query(exists().where(
and_(DashboardStar.dmp_user_id == current_user_id,
DashboardStar.dmp_dashboard_id == dashboard_id))).scalar()
if is_exists:
DashboardStar.query.filter_by(
dmp_user_id=current_user_id,
dmp_dashboard_id=dashboard_id).delete()
return resp_hanlder(code=0, msg="OK")
else:
return resp_hanlder(code=999, msg="看板已删除置顶")
else:
return resp_hanlder(code=999, msg="看板不存在或已被删除")
def add_queries(desc):
"""
保存SQL
---
tags:
- SQL
parameters:
- name: query_name
in:
type: string
required: true
description: 查询的名称，最长64字符
- name: query_sql
in:
type: string
required: true
description: 查询语句，最长65535字符
- name: description
in:
type: string
required: false
description: 查询的简介，最长512字符
- name: dmp_case_id
in:
type: int
required: true
description: 原数据案例ID
- name: dmp_data_table_id
in:
type: int
required: true
description: 原数据表ID
responses:
0:
description: OK
"""
auth_token = request.headers.get('Authorization')
current_user_id = Users.decode_auth_token(auth_token)
request_json = request.json if request.json else {}
valid = Add_queries_validator(request_json)
if not valid.is_valid():
return resp_hanlder(code=201, msg=valid.str_errors)
new_queries = SavedQuery(
query_name=request_json.get("query_name"),
query_sql=request_json.get("query_sql"),
description=request_json.get("description"),
dmp_data_table_id=request_json.get("dmp_data_table_id"),
dmp_case_id=request_json.get("dmp_case_id"),
created_dmp_user_id=current_user_id,
changed_dmp_user_id=current_user_id,
)
new_queries.save()
return resp_hanlder(code=0, result=new_queries.__json__())
def post(desc):
db_info = request.json
auth_token = request.headers.get('Authorization')
current_user_id = Users.decode_auth_token(auth_token)
if request.method == "POST":
try:
new_db = Database(
dmp_database_name=db_info.get("dmp_database_name"),
db_type=db_info.get("db_type"),
db_host=db_info.get("db_host"),
db_port=db_info.get("db_port"),
db_username=db_info.get("db_username"),
db_passwd=db_info.get("db_passwd"),
db_name=db_info.get("db_name"),
ispublic=db_info.get("ispublic"),
description=db_info.get("description"),
dmp_user_id=current_user_id,
)
new_db.save()
current_app.logger.info("add new database")
return resp_hanlder(result={"add_database": "complete!"})
except Exception as err:
return resp_hanlder(code=200, err=err)
elif request.method == "PUT":
try:
if db_info.get("dmp_database_id"):
modify_db = Database.get(db_info.get("dmp_database_id"))
if "dmp_database_name" in db_info.keys():
modify_db.dmp_database_name = db_info.get("dmp_database_name")
if "db_type" in db_info.keys():
modify_db.db_type = db_info.get("db_type")
if "db_host" in db_info.keys():
modify_db.db_host = db_info.get("db_host")
if "db_port" in db_info.keys():
modify_db.db_port = db_info.get("db_port")
if "db_username" in db_info.keys():
modify_db.db_username = db_info.get("db_username")
if "db_passwd" in db_info.keys():
modify_db.db_passwd = db_info.get("db_passwd")
if "db_name" in db_info.keys():
modify_db.db_name = db_info.get("db_name")
if "ispublic" in db_info.keys():
modify_db.ispublic = True if db_info.get("ispublic") else False
if "description" in db_info.keys():
modify_db.description = db_info.get("description")
modify_db.put()
current_app.logger.info("database info modify complete!")
return resp_hanlder(result={"modify": "ok!"})
else:
return resp_hanlder(code=101)
except Exception as err:
return resp_hanlder(code=999, err=err)
def get_index_dashboards(desc):
"""
获取首页看板
---
tags:
- BI
parameters:
- name: 无
type: int
required: false
description: 无需参数
responses:
0:
description: OK!
"""
auth_token = request.headers.get('Authorization')
current_user_id = Users.decode_auth_token(auth_token)
index_dashboards = None
ud = UserDashboard.query.filter_by(dmp_user_id=current_user_id).first()
index_dashboards_id = ud.dmp_dashboard_id if ud else None
if index_dashboards_id:
index_dashboards_obj = db.session.query(
Dashboard.id.label("id"),
literal("dashboard").label("type"),
Dashboard.dmp_dashboard_name.label("name"),
Dashboard.charts_position.label("charts_position"),
Dashboard.description.label("description"),
Dashboard.release.label("release"),
db.session.query(DashboardStar.id).filter(
and_(DashboardStar.dmp_dashboard_id == Dashboard.id,
DashboardStar.dmp_user_id ==
current_user_id)).exists().label("is_star"),
exists().where(
and_(UserDashboard.dmp_dashboard_id == Dashboard.id,
UserDashboard.dmp_user_id == current_user_id)).label(
"is_index"),
Dashboard.upper_dmp_dashboard_archive_id.label(
"upper_dmp_dashboard_archive_id"),
db.session.query(Users.dmp_username).filter(
Users.id == Dashboard.created_dmp_user_id).subquery().c.
dmp_username.label("created_dmp_user_name"),
Dashboard.created_dmp_user_id.label("created_dmp_user_id"),
Dashboard.created_on.label("created_on"),
Dashboard.changed_on.label("changed_on")).filter(
Dashboard.id == index_dashboards_id).first()
index_dashboards = index_dashboards_obj._asdict(
) if index_dashboards_obj else None
return resp_hanlder(code=0, result={"index": index_dashboards})
def get_dashboard_by_id(desc, dashboard_id):
"""
获取单一看板信息
---
tags:
- BI
parameters:
- name: dashboard_id
type: int
required: true
description: 要获取的看板ID
responses:
0:
description: OK
"""
auth_token = request.headers.get('Authorization')
current_user_id = Users.decode_auth_token(auth_token)
if Dashboard.exist_item_by_id(dashboard_id):
dashboards_obj = db.session.query(
Dashboard.id.label("id"),
literal("dashboard").label("type"),
Dashboard.dmp_dashboard_name.label("name"),
Dashboard.description.label("description"),
Dashboard.charts_position.label("charts_position"),
Dashboard.release.label("release"),
db.session.query(DashboardStar.id).filter(
and_(DashboardStar.dmp_dashboard_id == Dashboard.id,
DashboardStar.dmp_user_id ==
current_user_id)).exists().label("is_star"),
exists().where(
and_(UserDashboard.dmp_dashboard_id == Dashboard.id,
UserDashboard.dmp_user_id == current_user_id)).label(
"is_index"),
Dashboard.upper_dmp_dashboard_archive_id.label(
"upper_dmp_dashboard_archive_id"),
db.session.query(Users.dmp_username).filter(
Users.id == Dashboard.created_dmp_user_id).subquery().c.
dmp_username.label("created_dmp_user_name"),
Dashboard.created_dmp_user_id.label("created_dmp_user_id"),
Dashboard.created_on.label("created_on"),
Dashboard.changed_on.label("changed_on")).filter(
Dashboard.id == dashboard_id).first()
dashboards = dashboards_obj._asdict() if dashboards_obj else None
return resp_hanlder(code=0, result={"data": dashboards})
else:
return resp_hanlder(code=999, msg="看板不存在或已被删除")
def from_file(desc):
if request.method == "POST":
try:
auth_token = request.headers.get('Authorization')
submit_dmp_user_id = Users.decode_auth_token(auth_token)
form_info = request.json
filetype = form_info.get("filetype")
filepath = form_info.get("filepath")
column_line = form_info.get("column_line")
column = form_info.get("column")
json_dimension_reduction = form_info.get(
"json_dimension_reduction")
destination_dmp_database_id = form_info.get(
"destination_dmp_database_id")
destination_db_table_name = form_info.get("tablename")
dmp_data_table_name = form_info.get("dmp_data_table_name")
method = form_info.get("method")
dmp_data_case_id = form_info.get("dmp_data_case_id")
description = form_info.get("description")
new_form_info = FormUpload(
filetype=filetype,
filepath=filepath,
column_line=column_line,
column=column,
destination_dmp_database_id=destination_dmp_database_id,
destination_db_table_name=destination_db_table_name,
dmp_data_table_name=dmp_data_table_name,
method=method,
dmp_case_id=dmp_data_case_id,
)
new_form_info.save()
new_form = Forms(
submit_dmp_user_id=submit_dmp_user_id,
description=description,
form_info_id=new_form_info.fid,
form_type=2,
)
new_form.save()
return resp_hanlder(result="OK")
except Exception as err:
return resp_hanlder(code=999, err=err)
def get_data_service_by_id(id, desc):
"""
获取单一数据服务
---
tags:
- DataService
parameters:
- name: id
in: path
type: int
required: true
description: URL参数要获取的数据服务内容的ID
responses:
500:
description: Error The language is not awesome!
0:
description: 查询单一数据服务信息
schema:
$ref: '#/definitions/DataServiceParameterItems'
"""
if request.method == 'GET':
try:
auth_token = request.headers.get("Authorization")
user_id = Users.decode_auth_token(auth_token)
data_services = DataService.get(id)
if data_services:
create_user_id = data_services.created_dmp_user_id
'''判断数据服务是否属于当前用户'''
if create_user_id != user_id:
return resp_hanlder(code=301)
parameters = [
d.__json__() for d in DataServiceParameter.query.filter_by(
dmp_data_service_id=id)
]
result = data_services.__json__()
result['parameters'] = parameters
return resp_hanlder(result=result)
else:
return resp_hanlder(code=7401, msg="数据服务不存在或在操作期间被删除")
except Exception as err:
return resp_hanlder(code=999, error=err)
def download(desc):
if request.method == "POST":
try:
auth_token = request.headers.get('Authorization')
submit_dmp_user_id = Users.decode_auth_token(auth_token)
form_info = request.json
dmp_data_table_id = form_info.get("dmp_data_table_id")
rule = form_info.get("rule")
description = form_info.get("description")
new_form_info = FormDownload(dmp_data_table_id=dmp_data_table_id, )
new_form_info.save()
new_form = Forms(submit_dmp_user_id=submit_dmp_user_id,
description=description,
form_info_id=new_form_info.fid,
form_type=4)
new_form.save()
current_app.logger.info(new_form.id)
return resp_hanlder(result="OK")
except Exception as err:
return resp_hanlder(code=999, err=err)
def info(desc):
if request.method == "GET":
auth_token = request.headers.get('Authorization')
current_user_id = Users.decode_auth_token(auth_token)
try:
database_id = request.json.get("dmp_database_id") if request.json else None
if database_id:
data = Database.query.get(database_id).__json__()
else:
data = []
if Users.get(current_user_id).dmp_group_id == 1:
data = [d.__json__() for d in Database.query.all()]
else:
user_ids = [u.id for u in Users.query.filter_by(leader_dmp_user_id=current_user_id).all()]
user_ids.append(current_user_id)
# current_app.logger.info(user_ids)
data = [d.__json__() for d in
Database.query.filter(Database.dmp_user_id.in_(user_ids) | Database.ispublic == True).all()]
# current_app.logger.info(data)
return resp_hanlder(result=data)
except Exception as err:
return resp_hanlder(code=999, err=err)
def del_index(desc):
"""
取消设置首页
---
tags:
- BI
parameters:
- name: 无
in: <++>
type: string
required: false
description: 不需要参数
responses:
0:
description: ok
"""
auth_token = request.headers.get('Authorization')
current_user_id = Users.decode_auth_token(auth_token)
if current_user_id:
UserDashboard.query.filter_by(dmp_user_id=current_user_id).delete()
return resp_hanlder(code=0, result="OK")
def put(desc):
if request.method == "PUT":
auth_token = request.headers.get('Authorization')
current_user_id = Users.decode_auth_token(auth_token)
dbt_info = request.json
dbt_id = dbt_info.get("dmp_data_table_id")
dbt = DataTable.get(dbt_id)
if dbt:
dmp_user_id = dbt.dmp_user_id
if current_user_id == 1 or current_user_id == dmp_user_id or Users.get(
dmp_user_id).leader_dmp_user_id == current_user_id:
if "dmp_data_table_name" in dbt_info.keys():
dbt.dmp_data_table_name = dbt_info.get(
"dmp_data_table_name")
if "description" in dbt_info.keys():
dbt.description = dbt_info.get("description")
dbt.put()
return resp_hanlder(result="OK!")
else:
return resp_hanlder(code=301)
else:
return resp_hanlder(code=404)
def delete_data_service_by_id(id, desc):
"""
删除数据服务
---
tags:
- DataService
parameters:
- name: id
in: path
type: integer
required: true
description: URL参数，要删除的数据服务的ID
responses:
500:
description: Error The language is not awesome!
0:
msg: 'ok'
"""
if request.method == 'DELETE':
try:
if id:
if DataService.exist_item_by_id(id):
item = DataService.get(id)
auth_token = request.headers.get("Authorization")
user_id = Users.decode_auth_token(auth_token)
if user_id == item.created_dmp_user_id:
item.delete()
return resp_hanlder(
result={"delete_data_service": "success"})
else:
return resp_hanlder(code=301)
else:
return resp_hanlder(code=7401, msg="数据服务不存在或在操作期间被删除")
else:
return resp_hanlder(code=101)
except Exception as err:
return resp_hanlder(code=999, error=err)
def update_queries_by_id(desc, query_id):
"""
更新保存的SQL
---
tags:
- SQL
parameters:
- name: query_id
in:
type: int
required: false
description: 要进行修改的查询ID
- name: query_name
in:
type: string
required: false
description: 查询的名称，最长64字符
- name: query_sql
in:
type: string
required: true
description: 查询语句，最长65535字符
- name: description
in:
type: string
required: false
description: 查询的简介，最长512字符
- name: dmp_case_id
in:
type: int
required: false
description: 原数据案例ID
- name: dmp_data_table_id
in:
type: int
required: false
description: 原数据表ID
responses:
0:
description: OK
"""
auth_token = request.headers.get('Authorization')
current_user_id = Users.decode_auth_token(auth_token)
request_json = request.json if request.json else {}
print(request_json)
valid = Update_queries_validator(request_json)
if not valid.is_valid():
return resp_hanlder(code=201, msg=valid.str_errors)
if SavedQuery.exist_item_by_id(query_id):
current_queries = SavedQuery.get(query_id)
if current_queries.created_dmp_user_id == current_user_id:
if request_json.get("query_name"):
current_queries.query_name = request_json.get("query_name")
if request_json.get("query_sql"):
current_queries.query_sql = request_json.get("query_sql")
if request_json.get("description"):
current_queries.description = request_json.get("description")
if request_json.get("dmp_data_table_id"):
current_queries.dmp_data_table_id = request_json.get(
"dmp_data_table_id")
if request_json.get("dmp_case_id"):
current_queries.dmp_case_id = request_json.get("dmp_case_id")
current_queries.changed_dmp_user_id = current_user_id
current_queries.put()
return resp_hanlder(code=0, result="OK")
else:
return resp_hanlder(code=999, msg="您不是该查询的所有者,无权修改本查询")
else:
return resp_hanlder(code=999, msg="该查询不存在或已被删除")
def add_data_services(desc):
"""
添加数据服务
---
tags:
- DataService
parameters:
- name: data_service_name
in: json
type: string
required: true
description: 数据服务名称
- name: api_path
in: json
type: string
required: true
description: 数据服务接口
- name: request_method
in: json
type: integer
required: true
description: 请求方法1get2post
- name: description
in: json
type: string
required: false
description: 简介
responses:
500:
description: Error The language is not awesome!
0:
msg: 'ok'
"""
if request.method == 'POST':
try:
auth_token = request.headers.get("Authorization")
user_id = Users.decode_auth_token(auth_token)
data = request.json
valid = Add_dataservice_validator(data)
if not valid.is_valid():
return resp_hanlder(code=201, msg=valid.str_errors)
data_service_name = data.get("data_service_name")
api_path = data.get("api_path")
if DataService.exsit_data_service_by_apipath(apipath=api_path):
return resp_hanlder(code=101, msg="API路径已存在")
request_method = data.get("request_method")
description = data.get("description")
source_dmp_data_table_id = data.get("source_dmp_data_table_id")
data_service = DataService(
data_service_name=data_service_name,
api_path=api_path,
source_dmp_data_table_id=source_dmp_data_table_id,
request_method=request_method,
created_dmp_user_id=user_id,
changed_dmp_user_id=user_id,
description=description)
data_service.save()
return resp_hanlder(result={
"add_data_service": "complete!",
"id": data_service.id
})
except Exception as err:
return resp_hanlder(code=999, msg=str(err))
def get_data_services(desc):
"""
查询多个数据服务
---
tags:
- DataService
parameters:
- name: data_service_name
in: json
type: string
required: false
description: 要进行检索的服务名
- name: page_num
in: json
type: string
required: int
description: 页码
default: '1'
- name: pagesize
in: json
type: string
required: int
description: 单页数量
default: '10'
definitions:
DataService:
type: object
properties:
id:
type: integer
description: 数据服务ID
data_service_name:
type: string
description: 数据服务名称
request_method:
type: integer
description: 请求方法1get2post
source_dmp_data_table_id:
type: integer
description: 源数据表ID
projection:
type: string
description: 预选字段
state:
type: integer
description: 是否启用
description:
type: string
description: 数据服务简介
created_on:
type: string
description: 创建时间
changed_on:
type: string
description: 最后修改时间
created_dmp_user_id:
type: integer
description: 创建人ID
changed_dmp_user_id:
type: integer
description: 最后修改人ID
created_dmp_user_name:
type: string
description: 创建人名称
changed_dmp_user_name:
type: string
description: 最后修改人名称
DataServiceParameter:
type: object
properties:
id:
type: integer
description: 数据服务参数ID
parameter_name:
type: string
description: 数据服务参数名称
type:
type: integer
description: 数据类型
source_dmp_data_table_id:
type: integer
description: 源数据表ID
required_parameter:
type: integer
description: 是否必填
description:
type: string
description: 数据服务简介
DataServiceItems:
properties:
results:
type: array
items:
$ref: '#/definitions/DataService'
DataServiceParameterItem:
properties:
id:
type: integer
description: 数据服务ID
data_service_name:
type: string
description: 数据服务名称
request_method:
type: integer
description: 请求方法1get2post
source_dmp_data_table_id:
type: integer
description: 源数据表ID
projection:
type: string
description: 预选字段
state:
type: integer
description: 是否启用
description:
type: string
description: 数据服务简介
created_on:
type: string
description: 创建时间
changed_on:
type: string
description: 最后修改时间
created_dmp_user_id:
type: integer
description: 创建人ID
changed_dmp_user_id:
type: integer
description: 最后修改人ID
created_dmp_user_name:
type: string
description: 创建人名称
changed_dmp_user_name:
type: string
description: 最后修改人名称
parameters:
type: array
$ref: '#/definitions/DataServiceParameter'
DataServiceParameterItems:
properties:
results:
type: array
items:
$ref: '#/definitions/DataServiceParameterItem'
responses:
500:
description: Error The language is not awesome!
0:
description: 查询数据服务信息
schema:
$ref: '#/definitions/DataServiceItems'
"""
if request.method == 'GET':
try:
auth_token = request.headers.get("Authorization")
current_user_id = Users.decode_auth_token(auth_token)
request_json = request.json if request.json else {}
valid = Get_dataservice_validator(request_json)
if not valid.is_valid():
return resp_hanlder(code=201, msg=valid.str_errors)
data_service_name = request.json.get("data_service_name", None)
page_num = request.json.get("page_num", 1)
pagesize = request.json.get("pagesize", 10)
filters = {
DataService.created_dmp_user_id == current_user_id,
}
if data_service_name != None:
filters.add(
DataService.data_service_name.like("%" +
data_service_name +
"%"))
data_services_query = DataService.query.filter(*filters)
data_count = data_services_query.count()
data_services = data_services_query.limit(pagesize).offset(
(int(page_num) - 1) * int(pagesize))
data = [d.__json__() for d in data_services]
results = {
'data': data,
'page_num': page_num,
'pagesize': pagesize,
'data_count': data_count
}
return resp_hanlder(result=results)
except Exception as err:
raise err
return resp_hanlder(code=999, error=err)
def approve(desc):
if request.method == "PUT":
try:
# 获取请求参数
approve_form_info = request.json
auth_token = request.headers.get('Authorization')
approve_user_id = Users.decode_auth_token(auth_token)
form_type = approve_form_info.get("dmp_form_type", None)
form_id = approve_form_info.get("dmp_form_id", None)
approve_result = approve_form_info.get("approve_result", None)
answer = approve_form_info.get("answer", None)
# 保存表单审批信息
approve_form = Forms.get(form_id)
approve_form.approve_dmp_user_id = approve_user_id
approve_form.approve_result = approve_result
approve_form.approve_on = datetime.datetime.now()
approve_form.answer = answer
approve_form.put()
# 执行审批动作
if form_type == 1:
# 从数据库添加数据表单
try:
if approve_result == 1:
post(
dmp_data_table_name=approve_form.info_form.
dmp_data_table_name,
db_table_name=approve_form.info_form.db_table_name,
dmp_user_id=approve_form.submit_dmp_user_id,
dmp_database_id=approve_form.info_form.
dmp_database_id,
dmp_case_id=approve_form.info_form.dmp_case_id,
description=approve_form.description)
approve_form.result = "SUCCEED！"
except Exception as err:
approve_form.result = "ASSOCIATION FAILED，ERROR_MSG:%s" % str(
err)
approve_form.finish = True
elif form_type == 2:
# 文件上传添加数据表单
upload_path = current_app.config.get("UPLOADED_PATH")
file_path = os.path.join(upload_path,
approve_form.info_form.filepath)
file_type = approve_form.info_form.filetype
column_line = approve_form.info_form.column_line
column = approve_form.info_form.column.split(",") if type(
approve_form.info_form.column) == str else []
destination_dmp_database_id = approve_form.info_form.destination_dmp_database_id
destination_db_table_name = approve_form.info_form.destination_db_table_name
dmp_data_table_name = approve_form.info_form.dmp_data_table_name
method = approve_form.info_form.method
description = approve_form.description
submit_dmp_user_id = approve_form.submit_dmp_user_id
dmp_case_id = approve_form.info_form.dmp_case_id
destination_database = Database.get(
destination_dmp_database_id)
destination_database_type = destination_database.db_type
destination_db_host = destination_database.db_host
destination_db_port = destination_database.db_port
destination_db_username = destination_database.db_username
destination_db_passwd = destination_database.db_passwd
destination_db_name = destination_database.db_name
try:
reader = []
text_column = []
csv_filepath = os.path.join(
current_app.config.get("UPLOADED_PATH"), file_path)
if file_type == 1 or file_type == 3:
# csv、excel
dt = pd.read_csv(csv_filepath, header=column_line)
csv_column = [
"_".join(lazy_pinyin(d))
if is_contains_chinese(d) else d
for d in list(dt.columns)
]
text_column = column if column and len(column) == len(
csv_column) else csv_column
csv_column_d = [{
"index": i,
"type": "string"
} for i, cc in enumerate(text_column)]
reader = textfile_reader(filepath=csv_filepath,
column=csv_column_d)
elif file_type == 2:
# json
dt = pd.read_csv(csv_filepath, header=0)
csv_column = [
"_".join(lazy_pinyin(d))
if is_contains_chinese(d) else d
for d in list(dt.columns)
]
text_column = column if column and len(column) == len(
csv_column) else csv_column
csv_column_d = [{
"index": i,
"type": "string"
} for i, cc in enumerate(text_column)]
reader = textfile_reader(filepath=csv_filepath,
column=csv_column_d)
pass
writer = []
if destination_database_type == 1:
# hive_writer
hive_columns = [{
"name": col,
"type": "string"
} for col in text_column]
hive_path = "/user/hive/warehouse/%s.db/%s" % (
destination_db_name, destination_db_table_name)
hive_conn = auto_connect(
db_id=destination_dmp_database_id)
create_table_sql = create_table_query_handler(
table_name=destination_db_table_name,
fields=text_column,
uniform_type="string",
id_primary_key=False,
semicolon=False,
fieldDelimiter=",")
print(create_table_sql)
if method == 1:
hive_conn.execsql(create_table_sql)
elif method == 3:
del_table_sql = "drop table {table_name}"
hive_conn.execsql(
del_table_sql.format(
table_name=destination_db_table_name))
hive_conn.execsql(create_table_sql)
else:
pass
writer = hive_writer(
host=destination_db_host,
port=8020,
path=hive_path,
filename=destination_db_table_name,
column=hive_columns,
fieldDelimiter=",",
)
elif destination_database_type == 2:
# mysql_writer
create_table_sql = create_table_query_handler(
table_name=destination_db_table_name,
fields=text_column,
uniform_type="text",
id_primary_key=True,
semicolon=True,
fieldDelimiter=None)
current_app.logger.info(create_table_sql)
mysql_conn = auto_connect(
db_id=destination_dmp_database_id)
del_table_sql = "drop table {table_name};"
if method == 1:
mysql_conn.execsql(sql=create_table_sql)
elif method == 2:
pass
elif method == 3:
mysql_conn.execsql(
del_table_sql.format(
table_name=destination_db_table_name))
mysql_conn.execsql(create_table_sql)
column = text_column
writer = mysql_writer(
model=1,
username=destination_db_username,
password=destination_db_passwd,
column=column,
host=destination_db_host,
port=destination_db_port,
db=destination_db_name,
table=destination_db_table_name,
)
elif destination_database_type == 3:
# mongo_writer
mongo_conn = auto_connect(
db_id=destination_dmp_database_id)
if method == 3:
mongo_conn.del_table(
table_name=destination_db_table_name)
column = [{
"name": col,
"type": "string"
} for col in text_column]
writer = mongodb_writer(
host=destination_db_host,
port=destination_db_port,
username=destination_db_username,
password=destination_db_passwd,
db_name=destination_db_name,
collection_name=destination_db_table_name,
column=column,
)
meta = {
"form_id": approve_form.id,
"csv_file_path": csv_filepath,
"dmp_data_table_name": dmp_data_table_name,
"db_table_name": destination_db_table_name,
"dmp_user_id": submit_dmp_user_id,
"dmp_database_id": destination_dmp_database_id,
"dmp_case_id": dmp_case_id,
"description": description,
}
job_hanlder.delay(reader=reader,
writer=writer,
channel=3,
func=postfunc,
meta=meta)
approve_form.result = "SUCCEED!"
except Exception as err:
approve_form.result = "CREATE UPLOAD JOB FAILED，ERROR MESSAGE：%s" % str(
err)
approve_form.finish = True
elif form_type == 3:
# 数据迁移表单
if approve_result == 1:
origin_data_table = DataTable.get(
approve_form.info_form.origin_dmp_table_id)
origin_database = Database.get(
origin_data_table.dmp_database_id)
origin_database_type = origin_database.db_type
origin_db_host = origin_database.db_host
origin_db_port = origin_database.db_port
origin_db_username = origin_database.db_username
origin_db_passwd = origin_database.db_passwd
origin_db_name = origin_database.db_name
origin_db_table_name = origin_data_table.db_table_name
destination_database = Database.get(
approve_form.info_form.destination_dmp_database_id)
destination_database_type = destination_database.db_type
destination_db_host = destination_database.db_host
destination_db_port = destination_database.db_port
destination_db_username = destination_database.db_username
destination_db_passwd = destination_database.db_passwd
destination_db_name = destination_database.db_name
destination_db_table_name = approve_form.info_form.new_table_name
try:
base_column = auto_connect(
db_id=origin_data_table.dmp_database_id).columns(
origin_data_table.db_table_name)
# current_app.logger.info(base_column)
reader = []
if origin_database_type == 1:
# hive_reader
reader = hive_reader(
host=origin_db_host,
port=8020,
path="/user/hive/warehouse/%s.db/%s" %
(origin_db_name, origin_db_table_name),
fileType="text",
column=["*"],
fieldDelimiter=',',
encoding="utf-8")
elif origin_database_type == 2:
# mysql_reader
column = [
col.get("dmp_data_table_column_name")
for col in base_column
]
reader = mysql_reader(
username=origin_db_username,
password=origin_db_passwd,
column=column,
host=origin_db_host,
port=origin_db_port,
db=origin_db_name,
table=origin_db_table_name,
where=None,
)
elif origin_database_type == 3:
# mongodb
column = [{
"index":
i + 1,
"name":
col.get("dmp_data_table_column_name"),
"type":
col.get("dmp_data_table_column_type")
} for i, col in enumerate(base_column)]
reader = mongodb_reader(
host=origin_db_host,
port=origin_db_port,
db_name=origin_db_name,
collection_name=origin_db_table_name,
column=column,
username=origin_db_username,
password=origin_db_passwd)
pass
writer = []
if destination_database_type == 1:
# hive_writer
hive_col = [
col.get("dmp_data_table_column_name")
for col in base_column
]
hive_columns = [{
"name": col,
"type": "string"
} for col in hive_col]
hive_path = "/user/hive/warehouse/%s.db/%s" % (
destination_db_name, destination_db_table_name)
hive_conn = auto_connect(
db_id=approve_form.info_form.
destination_dmp_database_id)
create_table_sql = create_table_query_handler(
table_name=destination_db_table_name,
fields=hive_col,
uniform_type="string",
id_primary_key=False,
semicolon=False,
fieldDelimiter=",")
hive_conn.execsql(create_table_sql)
writer = hive_writer(
host=destination_db_host,
port=8020,
path=hive_path,
filename=destination_db_table_name,
column=hive_columns,
fieldDelimiter=",",
)
elif destination_database_type == 2:
# mysql_writer
column = [
col.get("dmp_data_table_column_name")
for col in base_column
]
create_table_sql = create_table_query_handler(
table_name=destination_db_table_name,
fields=column,
uniform_type="text",
id_primary_key=True,
semicolon=True,
fieldDelimiter=None)
mysql_conn = auto_connect(
db_id=approve_form.info_form.
destination_dmp_database_id)
mysql_conn.execsql(sql=create_table_sql)
# preSQL = []
writer = mysql_writer(
model=1,
username=destination_db_username,
password=destination_db_passwd,
column=column,
host=destination_db_host,
port=destination_db_port,
db=destination_db_name,
table=destination_db_table_name,
)
elif destination_database_type == 3:
# mongo_writer
column = [{
"name":
col.get("dmp_data_table_column_name"),
"type":
"string"
} for col in base_column]
writer = mongodb_writer(
host=destination_db_host,
port=destination_db_port,
username=destination_db_username
if destination_db_username else None,
password=destination_db_passwd
if destination_db_passwd else None,
db_name=destination_db_name,
collection_name=destination_db_table_name,
column=column,
)
meta = {"form_id": approve_form.id}
job_hanlder.delay(reader=reader,
writer=writer,
channel=3,
func=job_finish,
meta=meta)
except Exception as err:
approve_form.result = "CREATE MIGRATE JOB FAILED，ERROR MESSAGE：%s" % str(
err)
approve_form.finish = True
raise err
elif form_type == 4:
# 数据导出表单
if approve_result == 1:
origin_data_table = DataTable.get(
approve_form.info_form.dmp_data_table_id)
if origin_data_table:
origin_database = Database.get(
origin_data_table.dmp_database_id)
origin_database_type = origin_database.db_type
origin_db_host = origin_database.db_host
origin_db_port = origin_database.db_port
origin_db_username = origin_database.db_username
origin_db_passwd = origin_database.db_passwd
origin_db_name = origin_database.db_name
origin_db_table_name = origin_data_table.db_table_name
try:
base_column = auto_connect(
db_id=origin_data_table.dmp_database_id
).columns(origin_data_table.db_table_name)
reader = []
if origin_database_type == 1:
# hive_reader
reader = hive_reader(
host=origin_db_host,
port=8020,
path="/user/hive/warehouse/%s.db/%s" %
(origin_db_name, origin_db_table_name),
fileType="text",
column=["*"],
fieldDelimiter=',',
encoding="utf-8")
elif origin_database_type == 2:
# mysql_reader
column = [
col.get("dmp_data_table_column_name")
for col in base_column
]
reader = mysql_reader(
username=origin_db_username,
password=origin_db_passwd,
column=column,
host=origin_db_host,
port=origin_db_port,
db=origin_db_name,
table=origin_db_table_name,
where=None,
)
elif origin_database_type == 3:
# mongodb
column = [{
"index":
i + 1,
"name":
col.get("dmp_data_table_column_name"),
"type":
col.get("dmp_data_table_column_type")
} for i, col in enumerate(base_column)]
reader = mongodb_reader(
host=origin_db_host,
port=origin_db_port,
db_name=origin_db_name,
collection_name=origin_db_table_name,
column=column,
username=origin_db_username,
password=origin_db_passwd)
pass
writer = []
download_path = os.path.join(
current_app.config.get("DOWNLOAD_PATH"),
approve_form.submit_dmp_username)
file_name = origin_db_table_name + uuid_str(
) + ".csv"
finally_name = origin_db_table_name + "-" + uuid_str(
) + ".csv"
headers = [
col.get("dmp_data_table_column_name")
for col in base_column
]
writer = textfile_writer(filepath=download_path,
filename=file_name,
header=headers)
ip = socket.gethostbyname(socket.gethostname())
ftp_url = "ftp://%s:21/%s" % (
str(ip),
str(
os.path.join(
approve_form.submit_dmp_username,
finally_name)))
meta = {
"form_id": approve_form.id,
"file_name": file_name,
"finally_name": finally_name,
"download_path": download_path,
"ftp_url": ftp_url,
}
job_hanlder.delay(reader=reader,
writer=writer,
channel=1,
func=dlfunc,
meta=meta)
except Exception as err:
approve_form.result = "CREATE DOWNLOAD JOB FAILED，ERROR MESSAGE：%s" % str(
err)
approve_form.finish = True
else:
approve_form.result = "The original data sheet information is not obtained"
approve_form.finish = True
approve_form.put()
return resp_hanlder(result="OK!")
except Exception as err:
return resp_hanlder(code=999, err=err, msg=str(err))
def update_data_service_by_id(id, desc):
"""
修改数据服务
---
tags:
- DataService
parameters:
- name: id
in: path
type: integer
required: true
description: URL参数，要修改的数据服务的ID
- name: data_service_name
in: json
type: string
required: false
description: 数据服务名称
- name: api_path
in: json
type: string
required: false
description: 数据服务接口
- name: request_method
in: json
type: integer
required: false
description: 请求方法1get2post
- name: description
in: json
type: string
required: false
description: 简介
- name: source_dmp_data_table_id
in: json
type: integer
required: false
description: 源数据表ID
- name: query_sql
in: json
type: string
required: false
description: 查询语句（mysql）
- name: query_params
in: json
type: integer
required: false
description: 查询参数（mongodb）
- name: state
in: json
type: integer
required: false
description: 是否启用
responses:
500:
description: Error The language is not awesome!
0:
msg: 'ok'
"""
if request.method == 'PUT':
try:
auth_token = request.headers.get("Authorization")
current_user_id = Users.decode_auth_token(auth_token)
if not current_user_id or not id:
return resp_hanlder(code=201)
if DataService.exist_item_by_id(id):
data_service = DataService.get(id)
if current_user_id == data_service.created_dmp_user_id:
data = request.json
valid = Update_dataservice_validator(data)
if not valid.is_valid():
return resp_hanlder(code=201, msg=valid.str_errors)
if "data_service_name" in data.keys():
data_service.data_service_name = data.get(
"data_service_name")
if "api_path" in data.keys():
api_path = data.get("api_path")
if DataService.exsit_data_service_by_apipath(
apipath=api_path):
item_api_path = data_service.api_path
if item_api_path == api_path:
data_service.api_path = api_path
else:
return resp_hanlder(code=101, msg="API路径已存在")
else:
data_service.api_path = api_path
if "request_method" in data.keys():
data_service.request_method = data.get(
"request_method")
if "description" in data.keys():
data_service.description = data.get("description")
if "source_dmp_data_table_id" in data.keys():
data_service.source_dmp_data_table_id = data.get(
"source_dmp_data_table_id")
if not DataTable.exist_item_by_id(
data.get("source_dmp_data_table_id")):
return resp_hanlder(code=101, msg="源数据表不存在")
if "query_sql" in data.keys():
data_service.query_sql = data.get("query_sql")
if "query_params" in data.keys():
data_service.query_params = data.get("query_params")
if "state" in data.keys():
data_service.state = data.get("state")
data_service.changed_dmp_user_id = current_user_id
data_service.put()
return resp_hanlder(
result={"update_data_service": "complete!"})
else:
return resp_hanlder(code=301)
else:
return resp_hanlder(code=7401, msg="数据服务不存在或在操作期间被删除")
except Exception as err:
return resp_hanlder(code=999, error=err)
def info(desc):
if request.method == "GET":
auth_token = request.headers.get('Authorization')
user_id = Users.decode_auth_token(auth_token)
form_status = request.json.get("form_status", 0)
page_limit = request.json.get("page_limit", 10)
page_num = request.json.get("page_num", 1)
try:
committed, pending, complete = {
"form_status_code": 1
}, {
"form_status_code": 2
}, {
"form_status_code": 3
}
if form_permission(user_id) == 1:
if form_status == 1 or form_status == 0:
committed_query = Forms.query.filter_by(
submit_dmp_user_id=user_id, approve_result=0)
committed["form_count"] = committed_query.count()
committed["page_num"] = page_num
committed["forms"] = [
f.__json__() for f in committed_query.limit(
page_limit).offset((page_num - 1) * page_limit)
]
if form_status == 3 or form_status == 0:
complete_query = Forms.query.filter(
Forms.submit_dmp_user_id == user_id,
Forms.approve_result != 0)
complete["form_count"] = complete_query.count()
complete["page_num"] = page_num
complete["forms"] = [
f.__json__() for f in complete_query.limit(
page_limit).offset((page_num - 1) * page_limit)
]
return resp_hanlder(result={
"committed": committed,
"complete": complete
})
elif form_permission(user_id) == 2:
if form_status == 1 or form_status == 0:
committed_query = Forms.query.filter_by(
submit_dmp_user_id=user_id, approve_result=0)
committed["form_count"] = committed_query.count()
committed["page_num"] = page_num
committed["forms"] = [
f.__json__() for f in committed_query.limit(
page_limit).offset((page_num - 1) * page_limit)
]
if form_status == 2 or form_status == 0:
pending_query = Forms.query.filter(
and_(
tuple_(Forms.submit_dmp_user_id, ).in_(
Users.query.with_entities(Users.id).filter_by(
leader_dmp_user_id=user_id).all()),
Forms.approve_result == 0))
pending["form_count"] = pending_query.count()
pending["page_num"] = page_num
pending["forms"] = [
f.__json__() for f in pending_query.limit(
page_limit).offset((page_num - 1) * page_limit)
]
if form_status == 3 or form_status == 0:
complete_query = Forms.query.filter(
and_(
or_(Forms.submit_dmp_user_id == user_id,
Forms.approve_dmp_user_id == user_id),
Forms.approve_result != 0))
complete["form_count"] = complete_query.count()
complete["page_num"] = page_num
complete["forms"] = [
f.__json__() for f in complete_query.limit(
page_limit).offset((page_num - 1) * page_limit)
]
return resp_hanlder(
result={
"committed": committed,
"pending": pending,
"complete": complete
})
elif form_permission(user_id) == 3:
if form_status == 2 or form_status == 0:
pending_query = Forms.query.filter_by(approve_result=0)
pending["form_count"] = pending_query.count()
pending["page_num"] = page_num
pending["forms"] = [
f.__json__() for f in pending_query.limit(
page_limit).offset((page_num - 1) * page_limit)
]
if form_status == 3 or form_status == 0:
complete_query = Forms.query.filter(
Forms.approve_result != 0)
complete["form_count"] = complete_query.count()
complete["page_num"] = page_num
complete["forms"] = [
f.__json__() for f in complete_query.limit(
page_limit).offset((page_num - 1) * page_limit)
]
return resp_hanlder(result={
"pending": pending,
"complete": complete
})
except Exception as err:
return resp_hanlder(code=999, err=err)
def add_ds_parameters(desc):
"""
添加数据服务参数
---
tags:
- DataService
parameters:
- name: dmp_data_service_id
in: json
type: integer
required: true
description: 数据服务ID
- name: parameter_name
in: json
type: string
required: true
description: 参数名
- name: type
in: json
type: string
required: true
description: 字段类型
- name: required_parameter
in: json
type: integer
required: false
description: 是否是必填项
- name: description
in: json
type: string
required: false
description: 简介
responses:
500:
description: Error The language is not awesome!
0:
msg: 'ok'
"""
if request.method == 'POST':
try:
auth_token = request.headers.get("Authorization")
user_id = Users.decode_auth_token(auth_token)
if user_id:
data = request.json
data_service_id = data.get("dmp_data_service_id")
parameters = data.get("parameters", [])
if DataService.exist_item_by_id(data_service_id):
data_service = DataService.get(data_service_id)
create_user_id = data_service.created_dmp_user_id
if user_id != create_user_id:
return resp_hanlder(code=301)
else:
if parameters is None or len(parameters) == 0:
return resp_hanlder(
result={
"add_data_service_parameter": "success"
})
data_service_parameters = []
for item in parameters:
# form = DataServiceParameterForm(csrf_enabled=False, data=item)
# if not form.validate_on_submit():
# return resp_hanlder(code=201, err=form.errors)
parameter_name = item.get("parameter_name")
parameter_type = item.get("type")
required_parameter = item.get("required_parameter")
description = item.get("description")
data_service_parameter = DataServiceParameter(
dmp_data_service_id=data_service_id,
parameter_name=parameter_name,
type=parameter_type,
required_parameter=required_parameter,
description=description)
data_service_parameters.append(
data_service_parameter)
data_service_parameter.save_bulk(
data_service_parameters)
return resp_hanlder(
result={"add_data_service_parameter": "success"})
else:
return resp_hanlder(code=7401, msg="数据服务不存在或在操作期间被删除")
else:
return resp_hanlder(code=301, msg="用户ID获取异常")
except Exception as err:
return resp_hanlder(code=999, msg=str(err))
def update_ds_parameters(id, desc):
"""
修改数据服务参数
---
tags:
- DataService
parameters:
- name: id
in: path
type: integer
required: true
description: 数据服务参数ID
- name: dmp_data_service_id
in: json
type: integer
required: true
description: 数据服务ID
- name: parameter_name
in: json
type: string
required: false
description: 参数名
- name: type
in: json
type: string
required: false
description: 字段类型
- name: required_parameter
in: json
type: integer
required: false
description: 是否是必填项
- name: description
in: json
type: string
required: false
description: 简介
responses:
500:
description: Error The language is not awesome!
0:
msg: 'ok'
"""
if request.method == 'PUT':
try:
auth_token = request.headers.get("Authorization")
user_id = Users.decode_auth_token(auth_token)
if user_id:
data = request.json
data_service_id = data.get("dmp_data_service_id")
if DataService.exist_item_by_id(data_service_id):
data_service = DataService.get(data_service_id)
create_user_id = data_service.created_dmp_user_id
if user_id != create_user_id:
return resp_hanlder(code=301)
else:
if not DataServiceParameter.exist_item_by_id(id):
return resp_hanlder(code=7501,
msg="数据服务参数不存在或在操作期间被删除")
data_service_parameter = DataServiceParameter.get(id)
if "parameter_name" in data.keys():
data_service_parameter.parameter_name = data.get(
"parameter_name")
if "type" in data.keys():
data_service_parameter.type = data.get("type")
if "required_parameter" in data.keys():
data_service_parameter.required_parameter = data.get(
"required_parameter")
if "description" in data.keys():
data_service_parameter.description = data.get(
"description")
form = DataServiceParameterForm(csrf_enabled=False)
if not form.validate_on_submit():
return resp_hanlder(code=101, err=form.errors)
data_service_parameter.put()
return resp_hanlder(
result={"put_data_service_parameter": "success"})
else:
return resp_hanlder(code=7401, msg="数据服务不存在或在操作期间被删除")
else:
return resp_hanlder(code=301)
except Exception as err:
return resp_hanlder(code=999, msg=str(err))
def get_dashboards_and_archives(desc):
"""
查询多个文件夹和看板服务
---
tags:
- BI
parameters:
- name: upper_dmp_dashboard_archive_id
type: int
required: false
description: 父文件夹
- name: is_owner
type: bool
required: true
description: 是否是我的
- name: state
type: int
required: false
description: 发布状态
- name: name
type: string
required: true
description: 要检索的看板名或用户名
- name: pagenum
type: int
required: true
description: 页码
- name: pagesize
type: int
required: true
description: 每页内容数量
responses:
0:
description: OK!
"""
auth_token = request.headers.get('Authorization')
current_user_id = Users.decode_auth_token(auth_token)
# current_user_id = 1
request_json = request.json if request.json else {}
valid = Get_dashboards_and_archives_validator(request_json)
if not valid.is_valid():
return resp_hanlder(code=201, msg=valid.str_errors)
upper_dmp_dashboard_archive_id = request_json.get(
"upper_dmp_dashboard_archive_id", None)
is_owner = request_json.get("is_owner", False)
state = request_json.get("state", 3)
name = request_json.get("name", None)
pagenum = request_json.get("pagenum", 1)
pagesize = request_json.get("pagesize", 10)
dashboards_filters = {
Dashboard.upper_dmp_dashboard_archive_id ==
upper_dmp_dashboard_archive_id,
}
archives_filters = {
DashboardArchive.upper_dmp_dashboard_archive_id ==
upper_dmp_dashboard_archive_id,
}
if name != None:
dashboards_filters.clear()
archives_filters.clear()
dashboards_filters.add(
Dashboard.dmp_dashboard_name.like("%" + name + "%"))
archives_filters.add(
DashboardArchive.dashboard_archive_name.like("%" + name + "%"))
if is_owner == True:
dashboards_filters.add(
Dashboard.created_dmp_user_id == current_user_id)
archives_filters.add(
DashboardArchive.created_dmp_user_id == current_user_id)
if state != 3:
dashboards_filters.add(Dashboard.release == state)
name_subquery = db.session.query(
Users.id, Users.dmp_username.label("username")).subquery()
dashboards = db.session.query(
Dashboard.id.label("id"),
literal("dashboard").label("type"),
Dashboard.dmp_dashboard_name.label("name"),
Dashboard.description.label("description"),
Dashboard.release.label("release"),
db.session.query(DashboardStar.id).filter(
and_(DashboardStar.dmp_dashboard_id == Dashboard.id,
DashboardStar.dmp_user_id == current_user_id)).exists().label(
"is_star"),
db.session.query(DashboardStar.id).filter(
and_(UserDashboard.dmp_dashboard_id == Dashboard.id,
UserDashboard.dmp_user_id == current_user_id)).exists().label(
"is_index"),
# exists().where(and_(UserDashboard.dmp_dashboard_id ==Dashboard.id,UserDashboard.dmp_user_id==current_user_id)).label("is_index"),
Dashboard.upper_dmp_dashboard_archive_id.label(
"upper_dmp_dashboard_archive_id"),
Dashboard.created_dmp_user_id.label("created_dmp_user_id"),
name_subquery.c.username.label("created_dmp_user_name"),
# db.session.query(Users.dmp_username).filter(Users.id == Dashboard.created_dmp_user_id).subquery().c.dmp_username.label("created_dmp_user_name"),
Dashboard.changed_dmp_user_id.label("changed_dmp_user_id"),
name_subquery.c.username.label("changed_dmp_user_name"),
# db.session.query(Users.dmp_username.label("changed_dmp_user_name")).join(Dashboard,Users.id == Dashboard.changed_dmp_user_id).subquery(),
Dashboard.created_on.label("created_on"),
Dashboard.changed_on.label("changed_on")).outerjoin(
name_subquery,
Dashboard.changed_dmp_user_id == name_subquery.c.id).filter(
*dashboards_filters)
archives = db.session.query(
DashboardArchive.id.label("id"),
literal("archive").label("type"),
DashboardArchive.dashboard_archive_name.label("name"),
literal("-").label("description"),
literal(0).label("release"),
exists().where(
and_(ArchiveStar.dmp_archive_id == DashboardArchive.id,
ArchiveStar.dmp_user_id == current_user_id)).label("is_star"),
literal(0).label("is_index"),
DashboardArchive.upper_dmp_dashboard_archive_id.label(
"upper_dmp_dashboard_archive_id"),
DashboardArchive.created_dmp_user_id.label("created_dmp_user_id"),
name_subquery.c.username.label("created_dmp_user_name"),
# db.session.query(Users.dmp_username).filter(Users.id == Dashboard.created_dmp_user_id).subquery().c.dmp_username.label("created_dmp_user_name"),
DashboardArchive.changed_dmp_user_id.label("changed_dmp_user_id"),
name_subquery.c.username.label("changed_dmp_user_name"),
# db.session.query(Users.dmp_username.label("changed_dmp_user_name")).join(DashboardArchive,Users.id == DashboardArchive.changed_dmp_user_id).subquery(),
DashboardArchive.created_on.label("created_on"),
DashboardArchive.changed_on.label("changed_on")).outerjoin(
name_subquery,
DashboardArchive.changed_dmp_user_id == name_subquery.c.id).filter(
*archives_filters)
dashboards_and_archives = dashboards.union(archives)
# dashboards_and_archives = union(dashboards.alias("dashboards"), archives.alias("archives"))
# dashboards_and_archives = db.session.query([dashboards, archives]).select_entity_from(union(dashboards.select(), archives.select()))
# current_app.logger.info(dashboards_and_archives)
count = dashboards_and_archives.count()
data = [
d._asdict() for d in dashboards_and_archives.order_by(
desc_("is_index"), desc_("is_star"), desc_("changed_on"),
"type").offset((pagenum - 1) * pagesize).limit(pagesize)
]
res = {
"data_count": count,
"pagenum": pagenum,
"pagesize": pagesize,
"data": data
}
return resp_hanlder(result=res)
def get_data_by_data_service(api):
"""
数据服务调用
---
tags:
- DataService
parameters:
- name: api
type: string
required: true
description: 数据接口路径
- name: secret_key
type: string
required: true
description: 密钥
- name: page_num
type: int
required: true
description: 数据页码
- name: 其他参数
type: string
required: false
description: 数据服务的筛选参数
responses:
0:
description: 正常响应
"""
try:
api = str(api)
print(api)
request_params = request.json
code = 100
msg = None
result = None
secret_key = request_params.get("secret_key", None)
if secret_key:
# user_id = Users.encode_auth_token(secret_key)
user_id = Users.decode_auth_token(secret_key)
if isinstance(user_id, str):
return resp_hanlder(code=999, msg='缺少秘钥或秘钥已失效')
else:
code = 8101
msg = "缺少秘钥或秘钥已失效"
return resp_hanlder(code=code, msg=msg, result=result)
if DataService.exsit_data_service_by_apipath(api):
current_data_sevice = DataService.query.filter_by(
api_path=api).first()
else:
code = 8104
msg = "未匹配到改数据服务"
return resp_hanlder(code=code, msg=msg, result=result)
if current_data_sevice.state:
# dataservice_log = UserDataService(
# dmp_user_id=user_id,
# dmp_data_service_id=current_data_sevice.id,
# )
# dataservice_log.save()
db_type = current_data_sevice.database_type
missing, query_params = parse_query_params(request_params,
current_data_sevice)
else:
code = 8108
msg = "当前数据服务未开启"
return resp_hanlder(code=code, msg=msg, result=result)
if missing == None:
source_dmp_data_table_id = current_data_sevice.source_dmp_data_table_id
else:
code = 8105
msg = "缺少%d个必要参数：%s" % (len(missing), ",".join(missing))
return resp_hanlder(code=code, msg=msg, result=result)
if source_dmp_data_table_id:
page_num = request_params.get("page_num", 1)
page_size = request_params.get("page_size", 100)
conn = auto_connect(table_id=source_dmp_data_table_id)
qp = {}
if db_type in [1, 2]:
query_sql_tmp = current_data_sevice.query_sql
query_sql = format_sql(
query_sql_tmp,
query_params) if query_params else query_sql_tmp
qp["sql"] = query_sql
print(qp)
data = conn.exec_query(**qp)
result = data
return resp_hanlder(code=code, msg=msg, result=result)
elif db_type == 3:
query_sql_tmp = current_data_sevice.query_params
query_sql = eval(query_sql_tmp)
query_sql[filter] = query_params
qp = query_sql
print(qp)
data = conn.exec_query(**qp)
result = data
return resp_hanlder(code=code, msg=msg, result=result)
else:
code = 8107
msg = "源数据库异常"
return resp_hanlder(code=code, msg=msg, result=result)
else:
msg = "源数据库异常"
code = 8106
return resp_hanlder(code=code, msg=msg, result=result)
except Exception as err:
# raise err
return resp_hanlder(code=999, msg=str(err))
def get_queries(desc):
"""
获取多个保存的SQL
名称查询均支持模糊查询
---
tags:
- SQL
parameters:
- name: query_name
in:
type: string
required: false
description: 查询的名称，最长64字符
- name: case_name
in:
type: string
required: false
description: 案例名称，最长64字符
- name: table_name
in:
type: string
required: false
description: 数据表名称,最长64字符
- name: start_time
in:
type: string
required: false
description: 开始时间,最长64字符,示例：2020-10-14 17:58:00
- name: end_time
in:
type: string
required: false
description: 结束时间,最长64字符,格式同上
- name: pagesize
in:
type: int
required: false
description: 一页数量，最小值为1
- name: pagenum
in:
type: int
required: false
description: 页码，最小值为1
responses:
0:
description: ok
"""
auth_token = request.headers.get('Authorization')
current_user_id = Users.decode_auth_token(auth_token)
request_json = request.json if request.json else {}
valid = Get_queries_validator(request_json)
if not valid.is_valid():
return resp_hanlder(code=201, msg=valid.str_errors)
query_name = request_json.get("query_name")
case_name = request_json.get("case_name")
table_name = request_json.get("table_name")
start_time = request_json.get("start_time")
end_time = request_json.get("end_time", str(datetime.datetime.now()))
pagesize = request_json.get("pagesize", 10)
pagenum = request_json.get("pagenum", 1)
queries_filters = {SavedQuery.created_dmp_user_id == current_user_id}
if query_name:
queries_filters.add(SavedQuery.query_name.like("%" + query_name + "%"))
if case_name:
queries_filters.add(
tuple_(SavedQuery.dmp_data_table_id).in_(
db.session.query(DataTable.id).filter(
tuple_(DataTable.dmp_case_id).in_(
db.session.query(Case.id).filter(
Case.dmp_case_name.like("%" + case_name +
"%")).all())).all()))
if table_name:
queries_filters.add(
tuple_(SavedQuery.dmp_data_table_id).in_(
db.session.query(DataTable.id).filter(
DataTable.dmp_data_table_name.like("%" + table_name +
"%")).all()))
if start_time and end_time:
queries_filters.add(SavedQuery.changed_on.between(
start_time, end_time))
queries_query = SavedQuery.query.filter(*queries_filters)
count = queries_query.count()
queries_objs = queries_query.offset(
(pagenum - 1) * pagesize).limit(pagesize)
queries_data = [q.__json__() for q in queries_objs]
result = {
"data": queries_data,
"pagenum": pagenum,
"pagesize": pagesize,
"count": count
}
return resp_hanlder(code=0, result=result)