def __getRealQuery(self, columns, outerjoins=[], filters=None, orders=None): """ 私有方法,通用查询方法 :param columns: :param outerjoins: :param filters: :param orders: :return: """ if columns is not None: if isinstance(columns, (tuple, list)): query = db_session.query(*columns) else: query = db_session.query(columns) if outerjoins is not None: if isinstance(outerjoins, (tuple, list)): query = query.outerjoin(*outerjoins) else: query = query.outerjoin(outerjoins) if filters is not None: if isinstance(filters, (tuple, list)): query = query.filter(*filters) else: query = query.filter(filters) if orders is not None: if isinstance(orders, (tuple, list)): query = query.order_by(*orders) else: query = query.order_by(orders) return query
def updRolePermission(): myRes = MyResponse() RESOURCE_KEYS = MyRequest.get_verify_list("RESOURCE_KEYS") ROLE_KEY = MyRequest.get("ROLE_KEY", type=int) RESOURCE_TYPE = MyRequest.get("RESOURCE_TYPE", type=int) try: db_session.begin_nested() db_session.query(RolePermission).filter( RolePermission.ROLE_KEY == ROLE_KEY).filter( RolePermission.RESOURCE_TYPE == RESOURCE_TYPE).delete( synchronize_session=False) for key in RESOURCE_KEYS: rolePermission = RolePermission() rolePermission.ROLE_KEY = ROLE_KEY rolePermission.RESOURCE_KEY = key rolePermission.RESOURCE_TYPE = RESOURCE_TYPE db_session.add(rolePermission) db_session.commit() myRes.code = ResState.HTTP_SUCCESS myRes.msg = "操作成功" except SQLAlchemyError as ex: db_session.rollback() mylog.error(ex) myRes.msg = str(ex) finally: db_session.close() return myRes.to_json()
def add(): myRes = MyResponse() OPER_NAME = MyRequest.get_verify_empty("OPER_NAME", errmsg="操作名称不能为空") OPER_PKEY = MyRequest.get_verify_empty("OPER_PKEY", errmsg="OPER_PKEY不能为空") OPER_URL = MyRequest.get("OPER_URL", type=str) OPER_TYPE = MyRequest.get("OPER_TYPE", type=int) OPER_SOFT = MyRequest.get("OPER_SOFT", type=int) try: mybrother = db_session.query( OperInfo.OPER_KEY).filter(OperInfo.OPER_PKEY == OPER_PKEY).all() if mybrother: array_id = [int(x.OPER_KEY) for x in mybrother] ###取最大的ID OPER_KEY = str(max(array_id) + 1) elif OPER_PKEY == "0": OPER_KEY = "101" else: OPER_KEY = OPER_PKEY + "001" operInfo = OperInfo() operInfo.OPER_KEY = OPER_KEY operInfo.OPER_NAME = OPER_NAME operInfo.OPER_URL = OPER_URL operInfo.OPER_TYPE = OPER_TYPE operInfo.OPER_PKEY = OPER_PKEY operInfo.OPER_SOFT = OPER_SOFT MySqlalchemy.comAdd(operInfo) myRes.code = ResState.HTTP_SUCCESS myRes.msg = "操作成功" except SQLAlchemyError as ex: mylog.error(ex) myRes.msg = str(ex) return myRes.to_json()
def identify(cls, request): """ 用户鉴权 :return: list """ auth_header = request.headers.get('Authorization') if (auth_header): auth_tokenArr = auth_header.split(" ") if (not auth_tokenArr or auth_tokenArr[0] != 'Bearer' or len(auth_tokenArr) != 2): abort(401, 'Token错误或已过期,请重新登录') else: auth_token = auth_tokenArr[1] payload = cls.__decode_auth_token(cls, auth_token) if not isinstance(payload, str): userInfo = db_session.query(UserInfo).filter( UserInfo.USER_KEY == payload['data']['USER_KEY']) if (userInfo is None): abort(401, '找不到该用户信息') else: # if (user.login_time == payload['data']['login_time']): if True: return payload['data']['USER_KEY'] else: abort(401, 'Token已更改,请重新登录获取') else: abort(401, 'Token错误或已过期,请重新登录') else: abort(401, '没有提供认证token')
def getDptAndUserList(): myRes = MyResponse() try: query_data_dpt = db_session.query(DptInfo.DPT_KEY, DptInfo.DPT_PKEY, DptInfo.DPT_NAME) query_data_user = db_session.query( UserInfo.USER_KEY, UserInfo.DPT_KEY, UserInfo.USER_NAME).filter(UserInfo.USER_KEY != 1) query_data = query_data_dpt.union_all(query_data_user) myRes.data = MySqlalchemy.convertToList(query_data) myRes.code = ResState.HTTP_SUCCESS myRes.msg = "操作成功" except SQLAlchemyError as ex: mylog.error(ex) myRes.msg = str(ex) return myRes.to_json()
def exportLogOper(): start_time = time.time() searchName = MyRequest.get("searchName", type=str).strip() startDate = MyRequest.get_verify_date("startDate") endDate = MyRequest.get_verify_date("endDate") downLoadFile = DownLoadFile() xlsxwriterWriter = XlsxwriterWriter() try: query_data = db_session.query(LogOper).all() from app.apis.utils.thread_helper import convertToListThread dataArray = convertToListThread(query_data) end_time = time.time() print(end_time - start_time) print(len(dataArray)) # df = pandas.DataFrame(dataArray) # xlsxwriterWriter.open() # xlsxwriterWriter.writeSheetbyList(df, titles=None, sheetname="ttt") # downLoadFile.filepath = xlsxwriterWriter.filepath # downLoadFile.filename=xlsxwriterWriter.filename # downLoadFile.newfilename = "操作日志"+getSysDateTimeStr()+".xlsx" except ExcelError as ex: mylog.error(ex) abort(404, "写入excel错误") except SQLAlchemyError as ex: mylog.error(ex) abort(404, sys.exc_info()[1]) except Exception as ex: mylog.error(ex) abort(404, sys.exc_info()[1]) finally: xlsxwriterWriter.close() myRes = MyResponse() print("ok") return myRes.to_json()
def add(): myRes = MyResponse() MENU_NAME = MyRequest.get_verify_empty("MENU_NAME", errmsg="菜单名称不能为空") MENU_PKEY = MyRequest.get_verify_empty("MENU_PKEY", errmsg="MENU_PKEY") MENU_URL = MyRequest.get("MENU_URL", type=str) MENU_ICON = MyRequest.get("MENU_ICON", type=str) MENU_SOFT = MyRequest.get("MENU_SOFT", type=str) try: mybrother = db_session.query( MenuInfo.MENU_KEY).filter(MenuInfo.MENU_PKEY == MENU_PKEY).all() if mybrother: array_id = [int(x.MENU_KEY) for x in mybrother] ###取最大的ID MENU_KEY = str(max(array_id) + 1) elif MENU_PKEY == "0": MENU_KEY = "101" else: MENU_KEY = MENU_PKEY + "001" menu = MenuInfo() menu.MENU_KEY = MENU_KEY menu.MENU_NAME = MENU_NAME menu.MENU_PKEY = MENU_PKEY menu.MENU_URL = MENU_URL menu.MENU_ICON = MENU_ICON menu.MENU_SOFT = MENU_SOFT MySqlalchemy.comAdd(menu) myRes.code = ResState.HTTP_SUCCESS myRes.msg = "操作成功" except SQLAlchemyError as ex: mylog.error(ex) myRes.msg = str(ex) return myRes.to_json()
def add(): myRes = MyResponse() DPT_PKEY = MyRequest.get_verify_empty("DPT_PKEY", errmsg="请选择一个父节点") DPT_NAME = MyRequest.get_verify_empty("DPT_NAME", errmsg="部门名称不能为空") DPT_SOFT = MyRequest.get("DPT_SOFT", type=int) try: mybrother = db_session.query(DptInfo.DPT_KEY).filter(DptInfo.DPT_PKEY == DPT_PKEY).all() if mybrother: array_id = [int(x.DPT_KEY) for x in mybrother] ###取最大的ID DPT_KEY = str(max(array_id) + 1) elif DPT_PKEY=="0": DPT_KEY = "101" else: DPT_KEY=DPT_PKEY+"001" dpt = DptInfo() dpt.DPT_KEY=DPT_KEY dpt.DPT_PKEY = DPT_PKEY dpt.DPT_NAME = DPT_NAME dpt.DPT_SOFT = DPT_SOFT MySqlalchemy.comAdd(dpt) myRes.code = ResState.HTTP_SUCCESS myRes.msg = "操作成功" except SQLAlchemyError as ex: mylog.error(ex) myRes.msg=ResState.ERROR_DB return myRes.to_json()
def getList(): myRes = MyResponse() try: query_data = db_session.query(UserInfo).all() myRes.data = MySqlalchemy.convertToList(query_data) myRes.code = ResState.HTTP_SUCCESS myRes.msg = "操作成功" except SQLAlchemyError as ex: mylog.error(ex) myRes.msg = str(ex) return myRes.to_json()
def getDetail(): myRes = MyResponse() DPT_KEY = MyRequest.get_verify_empty("DPT_KEY", errmsg="部门ID不能为空") try: query_data=db_session.query(DptInfo).filter(DptInfo.DPT_KEY==DPT_KEY).one() dict_menu=MySqlalchemy.convertToDict(query_data) myRes.data = dict_menu myRes.code = ResState.HTTP_SUCCESS except SQLAlchemyError as ex: mylog.error(ex) myRes.msg=str(ex) return myRes.to_json()
def getList(): myRes = MyResponse() try: query_data = db_session.query(RoleInfo).order_by( RoleInfo.TIME_MODIFY.desc()) myRes.data = MySqlalchemy.convertToList(query_data) myRes.code = ResState.HTTP_SUCCESS myRes.msg = "操作成功" except SQLAlchemyError as ex: mylog.error(ex) myRes.msg = str(ex) return myRes.to_json()
def getDetail(): myRes = MyResponse() ROLE_KEY = MyRequest.get("ROLE_KEY", type=int) try: query_data = db_session.query(RoleInfo).filter( RoleInfo.ROLE_KEY == ROLE_KEY).one() dict_data = MySqlalchemy.convertToDict(query_data) myRes.code = ResState.HTTP_SUCCESS myRes.data = dict_data except SQLAlchemyError as ex: mylog.error(ex) myRes.msg = str(ex) return myRes.to_json()
def getList(): myRes = MyResponse() try: query_menuButton = db_session.query(OperInfo).order_by( OperInfo.OPER_SOFT.asc()).all() myRes.data = MySqlalchemy.convertToList(query_menuButton) print(myRes.data) myRes.code = ResState.HTTP_SUCCESS myRes.msg = "操作成功" except SQLAlchemyError as ex: mylog.error(ex) myRes.msg = str(ex) return myRes.to_json()
def getDetail(): myRes = MyResponse() Fid = MyRequest.get_verify_empty("Fid", errmsg="Fid不能为空") try: query_menu = db_session.query(MenuInfo).filter( MenuInfo.Fid == Fid).one() dict_menu = MySqlalchemy.convertToDict(query_menu) myRes.data = dict_menu myRes.code = ResState.HTTP_SUCCESS except SQLAlchemyError as ex: mylog.error(ex) myRes.msg = str(ex) return myRes.to_json()
def updUserRole(): myRes = MyResponse() ROLE_KEYS = MyRequest.getList("ROLE_KEYS[]") USER_KEY = MyRequest.get("USER_KEY", type=int) try: db_session.begin_nested() db_session.query(UserRole).filter( UserRole.USER_KEY == USER_KEY).delete() for key in ROLE_KEYS: userRole = UserRole() userRole.USER_KEY = USER_KEY userRole.ROLE_KEY = key db_session.add(userRole) db_session.commit() myRes.code = ResState.HTTP_SUCCESS myRes.msg = "操作成功" except SQLAlchemyError as ex: db_session.rollback() mylog.error(ex) myRes.msg = str(ex) finally: db_session.close() return myRes.to_json()
def getList(): myRes = MyResponse() try: query_menu = db_session.query( MenuInfo.MENU_KEY, MenuInfo.MENU_PKEY, MenuInfo.MENU_NAME, MenuInfo.MENU_URL, MenuInfo.MENU_ICON, MenuInfo.MENU_SOFT).order_by(MenuInfo.MENU_SOFT.asc()).all() myRes.data = MySqlalchemy.convertToList(query_menu) myRes.code = ResState.HTTP_SUCCESS myRes.msg = "操作成功" except SQLAlchemyError as ex: mylog.error(ex) myRes.msg = str(ex) return myRes.to_json()
def getRoleMenuAndOperList(): myRes = MyResponse() ROLE_KEY = MyRequest.get("ROLE_KEY", type=int) try: query_data = db_session.query(RolePermission.ROLE_KEY, RolePermission.RESOURCE_KEY, RolePermission.RESOURCE_TYPE).filter( RolePermission.ROLE_KEY == ROLE_KEY) myRes.data = MySqlalchemy.convertToList(query_data) myRes.code = ResState.HTTP_SUCCESS myRes.msg = "操作成功" except SQLAlchemyError as ex: mylog.error(ex) myRes.msg = str(ex) return myRes.to_json()
def getRoleListByUserKey(): myRes = MyResponse() USER_KEY = MyRequest.get("USER_KEY", type=int) try: clomns = [UserRole.USER_KEY, RoleInfo.ROLE_NAME, UserRole.ROLE_KEY] query_data = db_session.query(*clomns).outerjoin( RoleInfo, RoleInfo.ROLE_KEY == UserRole.ROLE_KEY).filter( UserRole.USER_KEY == USER_KEY) myRes.data = MySqlalchemy.convertToList(query_data) myRes.code = ResState.HTTP_SUCCESS myRes.msg = "操作成功" except SQLAlchemyError as ex: mylog.error(ex) myRes.msg = str(ex) return myRes.to_json()
def delete(): myRes = MyResponse() OPER_KEY = MyRequest.get_verify_empty("OPER_KEY", errmsg="OPER_KEY不能为空") try: db_session.begin_nested() ###删除按钮相关的角色权限信息 db_session.query(RolePermission).filter( RolePermission.RESOURCE_TYPE == 1).filter( RolePermission.RESOURCE_KEY.like(OPER_KEY + "%")).delete( synchronize_session=False) ###删除按钮信息 db_session.query(OperInfo).filter( OperInfo.OPER_KEY.like(OPER_KEY + "%")).delete(synchronize_session=False) db_session.commit() myRes.code = ResState.HTTP_SUCCESS myRes.msg = "操作成功" except SQLAlchemyError as ex: db_session.rollback() mylog.error(ex) myRes.msg = str(ex) finally: db_session.close() return myRes.to_json()
def getPageList(): myRes = MyResponse() currentPage = MyRequest.get("currentPage", type=int) pageSize = MyRequest.get("pageSize", type=int) searchDptKey = MyRequest.get("searchDptKey", type=str) searchName = MyRequest.get("searchName", type=str).strip() columns = [ UserInfo.USER_KEY, UserInfo.USER_NAME, UserInfo.LOGIN_NAME, UserInfo.USER_SEX, UserInfo.USER_POSITION, UserInfo.DPT_KEY, DptInfo.DPT_NAME, UserInfo.TIME_MODIFY, UserInfo.PHONE ] filters = [] outerjoins = [(DptInfo, DptInfo.DPT_KEY == UserInfo.DPT_KEY)] try: USER_KEY = g.USER_KEY if USER_KEY != 1: filters.append(UserInfo.USER_KEY != 1) if searchDptKey != 0: filters.append(UserInfo.DPT_KEY.like(searchDptKey + "%")) if searchName is not None: filters.append(UserInfo.USER_NAME.like("%" + searchName + "%")) user_page_info = MySqlalchemy.get_page_list( currentPage, pageSize, UserInfo.USER_KEY, columns, outerjoins=outerjoins, filters=filters, orders=[UserInfo.TIME_MODIFY.desc()]) ###拼接用户角色 query_data_role = db_session.query( UserRole.USER_KEY, RoleInfo.ROLE_NAME, UserRole.ROLE_KEY).outerjoin( RoleInfo, RoleInfo.ROLE_KEY == UserRole.ROLE_KEY) list_role = MySqlalchemy.convertToList(query_data_role) for xuser in user_page_info["data"]: list_roleName = [] for xrole in list_role: if xuser["USER_KEY"] == xrole["USER_KEY"]: list_roleName.append(xrole["ROLE_NAME"]) xuser["ROLE_NAMES"] = ",".join(list_roleName) myRes.data = user_page_info myRes.code = ResState.HTTP_SUCCESS myRes.msg = "操作成功" except SQLAlchemyError as ex: mylog.error(ex) myRes.msg = str(ex) return myRes.to_json()
def resetPwdByUserKey(): myRes = MyResponse() USER_KEY = MyRequest.get("USER_KEY", type=int) try: q_model = db_session.query(UserInfo).filter( UserInfo.USER_KEY == USER_KEY).one() q_model.set_password("123456") db_session.commit() myRes.code = ResState.HTTP_SUCCESS myRes.msg = "操作成功" except SQLAlchemyError as ex: mylog.error(ex) myRes.msg = str(ex) finally: db_session.close() return myRes.to_json()
def delete(): myRes = MyResponse() DPT_KEY = MyRequest.get_verify_empty("DPT_KEY", errmsg="ID不能为空") try: rows = db_session.query(DptInfo).filter(DptInfo.DPT_KEY.like(DPT_KEY+"%")).delete(synchronize_session=False) db_session.commit() if rows < 1: raise SQLAlchemyError("操作失败") myRes.code = ResState.HTTP_SUCCESS myRes.msg = "操作成功" except SQLAlchemyError as ex: mylog.error(ex) myRes.msg = ResState.ERROR_DB finally: db_session.close() return myRes.to_json()
def comDel(cls, model, filters): """ 删除方法 :param model: :param filters: :return: """ try: rows = db_session.query(model).filter(*filters).delete( synchronize_session=False) db_session.commit() if rows < 1: raise SQLAlchemyError("操作失败") except SQLAlchemyError as ex: raise ex finally: db_session.close()
def comUpdate(cls, model, filters, attrs): """ 修改方法 :param model: :param filters: :param attrs: :return: """ try: rows = db_session.query(model).filter(*filters).update(attrs) db_session.commit() if rows < 1: raise SQLAlchemyError("操作失败") except SQLAlchemyError as ex: raise ex finally: db_session.close()
def authenticate(cls, username, password): """ 用户登录,登录成功返回token,写将登录时间写入数据库;登录失败返回失败原因 :param password: :return: json """ userInfo = db_session.query(UserInfo).filter( UserInfo.LOGIN_NAME == username).first() if (userInfo is None): return abort(401, "用户名或密码错误") else: if (userInfo.check_password(userInfo.USER_PWD, password)): login_time = int(time.time()) ###这里后期需要将登录时间写入缓存,提高性能 token = cls.__encode_auth_token(cls, userInfo.USER_KEY, login_time) dict_user = MySqlalchemy.convertToDict(userInfo) return dict_user, token.decode() else: return abort(401, "用户名或密码错误")
def comUpdates(cls, list_model, id, attrs): """ 批量修改 :param list_model: :param id: :param attrs: :return: """ try: db_session.begin_nested() for model in list_model: rows = db_session.query(model).filter( model.Fid == id).update(attrs) if rows < 1: raise SQLAlchemyError("操作失败") db_session.commit() except SQLAlchemyError as ex: db_session.rollback() raise ex finally: db_session.close()
def exportLogLogin(): myRes = MyResponse() searchName = MyRequest.get("searchName", type=str).strip() startDate = MyRequest.get_verify_date("startDate") endDate = MyRequest.get_verify_date("endDate") downLoadFile = DownLoadFile() xlsxwriterWriter = XlsxwriterWriter() try: db_query = db_session.query(UserInfo.Fid, UserInfo.FloginName, UserInfo.Fname, LogLogin.FcreateTime, LogLogin.Fremark, LogLogin.FipAddress) db_query = db_query.outerjoin(UserInfo, UserInfo.Fid == LogLogin.FuserId) db_query = db_query.filter( LogLogin.FcreateTime.between(startDate, endDate)) if searchName != '': db_query = db_query.filter( UserInfo.FloginName.like("%" + searchName + "%")) query_data = db_query.order_by(LogLogin.FcreateTime.desc()) dataArray = MySqlalchemy.convertToDict(query_data) df = pandas.DataFrame(dataArray) xlsxwriterWriter.open() xlsxwriterWriter.writeSheetbyList(df, titles=None, sheetname="ttt") downLoadFile.filepath = xlsxwriterWriter.filepath downLoadFile.filename = xlsxwriterWriter.filename downLoadFile.newfilename = "操作日志" + getSysDateTimeStr() + ".xlsx" myRes.data = [] myRes.code = ResState.HTTP_SUCCESS myRes.msg = "操作成功" except ExcelError as ex: mylog.error(ex) abort(404, "写入excel错误") except SQLAlchemyError as ex: mylog.error(ex) abort(404, sys.exc_info()[1]) finally: xlsxwriterWriter.close() return downLoadFile.download()
def resetMyPwd(): myRes = MyResponse() FuserId = g.USER_KEY FoldPwd = MyRequest.get_verify_empty("FoldPwd", errmsg="旧密码不能为空") FnewPwd = MyRequest.get_verify_empty("FnewPwd", errmsg="新密码不能为空") FnewPwdOk = MyRequest.get("FnewPwdOk", type=str) try: if FnewPwd != FnewPwdOk: return myRes.to_json_msg("新旧密码不一致") userInfo = db_session.query(UserInfo).filter( UserInfo.USER_KEY == FuserId).one() if not userInfo.check_password(userInfo.USER_PWD, FoldPwd): return myRes.to_json_msg("旧密码错误,请重新输入") userInfo.set_password(FnewPwd) db_session.commit() myRes.code = ResState.HTTP_SUCCESS myRes.msg = "操作成功" except SQLAlchemyError as ex: mylog.error(ex) myRes.msg = str(ex) finally: db_session.close() return myRes.to_json()
def comDels(cls, models, ids): """ 批量删除 :param models: :param ids: :return: """ try: if isinstance(models, (list, tuple)) and isinstance(ids, (list, tuple)): db_session.begin_nested() for model in models: rows = db_session.query(models).filter( model.Fid == id).delete(synchronize_session=False) if rows < 1: raise SQLAlchemyError("操作失败") db_session.commit() else: raise MyError("Models and ids mast be list or tuple.") except SQLAlchemyError as ex: db_session.rollback() raise ex finally: db_session.close()