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)