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 csu(dmp_username, real_name, passwd, email): """创建管理员用户""" from dmp.models import Users, Groups from dmp.utils.ep_data import EnvelopedData if not all([dmp_username, real_name, passwd, email]): return 'Insufficient parameter, please recreate superuser.' db_user_count = Users.query.count() if db_user_count == 0: user = Users(dmp_username=dmp_username, real_name=real_name, password=passwd, email=email) rootgroup = Groups.query.filter(Groups.id == 1).first() user.dmp_group_id = 1 user.leader_dmp_user_id = None user.confirmed = True db.session.add(user) db.session.commit() res = EnvelopedData.create_root(rootgroup) if isinstance(res, str): return {'msg': res} return {'msg': 'Super administrator has been created successfully.'} else: return { 'msg': 'The super administrator already exists in the database, please do not add it again.' }
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 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 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 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 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 form_permission(user_id): user = Users.get(user_id) usergroup_id = user.groups.id approve_id = Permissions.query.filter_by(route="/form/approve/").first().id p_ids = [p.id for p in user.groups.permissions] if usergroup_id == 1: return 3 elif approve_id in p_ids: return 2 elif approve_id not in p_ids: return 1
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 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 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 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 register(desc): ''' 说明:用户注册及超级管理员单一添加用户接口 参数:dmp_username,real_name,password,email;说明:客户端请求参数信息,数据类型:JSON 返回值:成功与失败返回对应的状态码及提示信息,数据类型:JSON,数据格式:{'msg':'pass','results':null,'status':xxx} ''' try: user_obj = Users.query.filter_by(id=1).first() # 判断初始状态有没有超级用管理员,没有则不能创建用户,必须要先创建一个超级管理员 ret = UserVerify.judge_superuser(user_obj) if ret: return resp_hanlder(code=999, msg=ret) data = request.json if data == None: return resp_hanlder(code=999) auth_token = data.get('Authorization') dmp_username = data.get('dmp_username') real_name = data.get('real_name') passwd = data.get('password') email = data.get('email') user = Users(dmp_username=dmp_username, real_name=real_name, password=passwd, email=email, leader_dmp_user_id=1) res_token = PuttingData.get_obj_data(Users, auth_token) if auth_token != None and isinstance(res_token, dict): res = PuttingData.root_add_user( data, res_token, user, dmp_username, real_name) # 返回字典-管理员单一添加成功 if isinstance(res, dict): return resp_hanlder(code=0, msg=res) # 返回元组-管理员/教师单一添加缺少参数 elif isinstance(res, tuple): return resp_hanlder(code=999, msg=res[1]) # 普通管理员和教师无法添加管理员角色,需要超级管理员添加 elif res == -1: return resp_hanlder(code=999, msg='无法添加管理员用户组用户,请联系管理员添加.') elif res == -2: return resp_hanlder(code=999, msg='无法添加管理员或教师用户组用户,请联系管理员添加.') # 返回token错误的字符串-注册成功(注册时无token) db.session.add(user) db.session.commit() ValidationEmail().activate_email(user, email) return resp_hanlder(code=1001, msg=RET.alert_code[1001]) except Exception as err: db.session.rollback() return resp_hanlder(code=999, msg=str(err))
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 changepwd(desc): ''' 说明:用户重设密码接口 参数:Authorization,newpassword,说明:重置密码生成的标识token及新密码,都通过json传入,数据类型:JSON 返回值:成功返回状态码及对应提示信息,数据类型:JSON,数据格式:{'msg':'pass','results':null,'status':xxx} ''' if request.method == 'PUT': try: data = request.json token = data.get('authorization') newpassword = data.get('newpassword') res = Users.reset_password(token, newpassword) if res == True: return resp_hanlder(code=1006, msg=RET.alert_code[1006]) else: return resp_hanlder(code=1007, msg=res) except Exception as err: return resp_hanlder(code=999, msg=str(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 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 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 activate(desc): ''' 说明:用户邮箱激活接口 参数:Authorization,说明:邮件生成的客户端标识,通过json传入,数据类型:JSON 返回值:成功返回状态码及对应提示信息,数据类型:JSON,数据格式:{'msg':'pass','results':null,'status':xxx} ''' try: # 激活邮箱--注册时激活、token失效或者忘记导致未激活 token = request.json.get('authorization') res = PuttingData.get_obj_data(Users, token) # 校验token的有效期及正确性 if isinstance(res, dict): if res.get('confirmed') == True: return resp_hanlder(code=999, msg=RET.alert_code[1014]) # 已激活,confirmed为True if Users.check_activate_token(res) == True: return resp_hanlder(code=1009, msg=RET.alert_code[1009]) else: return resp_hanlder(code=2002, msg=RET.alert_code[2002]) except Exception as err: return resp_hanlder(code=999, msg=str(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 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 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 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 submit_dmp_username(self): from dmp.models import Users u = Users.get(self.submit_dmp_user_id) s_u_name = u.dmp_username if u else "-" return s_u_name
def approve_dmp_username(self): from dmp.models import Users u = Users.get(self.approve_dmp_user_id) a_u_name = u.dmp_username if u else "-" return a_u_name
def dmp_user_name(self): from dmp.models import Users u = Users.get(self.dmp_user_id) user_name = u.dmp_username if u else "-" return user_name
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)