def getAll(self, params, page, limit): whereStr = " 1=1 " limitStr = "" if ("jobName" in params.keys()): whereStr = whereStr + " and jobName=\'" + params["jobName"] + "\'" if ("jobParams" in params.keys()): whereStr = whereStr + " and jobParams=\'" + params[ "jobParams"] + "\'" if ("jobDesc" in params.keys()): whereStr = whereStr + " and jobDesc=\'" + params["jobDesc"] + "\'" if ((page != None) and (limit != None)): limitStr = " order by id desc limit " + str( (int(page) - 1) * int(limit)) + " , " + str(limit) sqlStr = whereStr + limitStr try: jobDescs = dbSession.query(JobDesc).filter(text(sqlStr)).all() jobsDescTotal = dbSession.query(func.count(JobDesc.id)).filter( text(whereStr)).all() results = self.getResult(jobDescs, totalTmps=jobsDescTotal) dbSession.commit() except Exception as e: dbSession.rollback() app.logger.info("ERROR:ServiceJobDesc.getAll():" + e.args.__str__()) return results
def get_list(self, filters, order, field=(), offset=0, limit=10): """ 列表 :param cls_: :param filters: :param order: :param field: :param offset: :param limit: :return: """ result = {} result['page'] = {} result['page']['count'] = dbSession.query(Comment).filter( *filters).count() result['list'] = [] result['page']['total_page'] = self.get_page_number( result['page']['count'], limit) result['page']['current_page'] = offset if offset != 0: offset = (offset - 1) * limit if result['page']['count'] > 0: result['list'] = dbSession.query(Comment).filter(*filters) result['list'] = result['list'].order_by(order).offset( offset).limit(limit).all() if not field: result['list'] = [res.to_dict() for res in result['list']] else: result['list'] = [ res.to_dict(only=field) for res in result['list'] ] return result
def get_list(self, cls_: object, filters=(), order='id desc', field=(), offset=0, limit=10): """ 列表 :param cls_: :param filters: :param order: :param field: :param offset: :param limit: :return: """ result = {} result['page'] = {} result['page']['count'] = dbSession.query(cls_).filter(*filters).count() result['list'] = [] result['page']['total_page'] = self.get_page_number(result['page']['count'], limit) result['page']['current_page'] = offset if offset != 0: offset = (offset - 1) * limit if result['page']['count'] > 0: result['list'] = dbSession.query(cls_).filter(*filters) order_list = order.split(' ') if order_list[1] == 'desc': result['list'] = result['list'].order_by(desc(order_list[0])).offset(offset).limit(limit).all() else: result['list'] = result['list'].order_by(asc(order_list[0])).offset(offset).limit(limit).all() if not field: result['list'] = [res.to_dict() for res in result['list']] else: result['list'] = [res.to_dict(only=field) for res in result['list']] return result
def getByIpByDate(self, params, page=None, limit=None): results = {} whereStr = " 1=1 " if ("ip" in params.keys()): whereStr = whereStr + " and ip=\'" + params["ip"] + "\'" if ("reportDate" in params.keys()): whereStr = whereStr + " and reportDate=\'" + params[ "reportDate"] + "\'" if ((page != None) and (limit != None)): limitStr = " order by ip limit " + str( (int(page) - 1) * int(limit)) + " , " + str(limit) sqlStr = whereStr + limitStr try: backups = dbSession.query(Report).filter(text(sqlStr)).all() backupsTotal = dbSession.query(func.count(Report.id)).filter( text(whereStr)).all() results = self.getResult(backups, totalTmps=backupsTotal) dbSession.rollback() except Exception as e: dbSession.rollback() app.logger.info(e.args.__str__()) return results
def deleteJob(self, jobId): try: dbSession.query(CronJob).filter(CronJob.jobId == jobId).delete() dbSession.commit() except Exception as e: dbSession.rollback() app.logger.info("ERROR:ServiceJob.deleteJob():" + e.args.__str__() + jobId)
def delete(self, id): """ 根据id删除用户 :param id: :return: """ dbSession.query(User).filter_by(id=id).delete() return dbSession.commit()
def update(email, updated_time): """ 更新更新时间 :param email: :param updated_time: :return: """ dbSession.query(User).filter_by(email=email).update({'updated_time': updated_time}) return dbSession.commit()
def get_count(self, cls_: object, filters: set, field=None) -> int: """ 统计数据 :param cls_: 数据模型实体类 :param filters: 查询条件 :param field: 查询字段 :return: int统计记录数量 """ if field == None: return dbSession.query(cls_).filter(*filters).count() else: return dbSession.query(cls_).filter(*filters).count(field)
def get_one(self, filters, order='id desc', field=()): """ 获取一条 :param filters: :param order: :param field: :return: """ result = dbSession.query(User).filter(*filters) # 查询结果为空 if result == None: return None order_list = order.split(' ') # 查询结果降序排列 if order_list[1] == 'desc': result = result.order_by(desc(order_list[0])).first() # 查询结果升序排列 else: result = result.order_by(asc(order_list[0])).first() # 查询字段为空 if not field: result = result.to_dict() else: result = result.to_dict(only=field) return result
def get(id): """ 获取用户信息 :param id: :return: """ return dbSession.query(User).filter_by(id=id).first()
def left_join(): """ 一对多left join 方式 :return: """ data = dbSession.query(Suggest).outerjoin(User, User.id == Suggest.user_id).all() data_msg = Utils.list_to_dict(data) return data_msg
def getMainMenu(): datas=[] id=request.args.get("node"); if id=='root': menus = dbSession.query(MainMenu).filter(MainMenu.parentId==0).all() else: menus=dbSession.query(MainMenu).filter(MainMenu.parentId==id).all() for item in menus: datas.append(item.to_json()) try: dbSession.rollback() except Exception as e: app.logger.info(e.args.__str__()) dbSession.rollback() return jsonify(datas)
def lock(self): """ 行级锁 :return: """ query = dbSession.query(User).filter( User.id == 34).with_for_update().first() dbSession.execute('select sleep(10)')
def one_to_many(): """ 一对多普通方式 :return: """ data = dbSession.query(Suggest).filter(User.id == Suggest.user_id).all() data_msg = Utils.list_to_dict(data) return data_msg
def delete(self, cls_: object, filters: set) -> int: """ 删除 :param cls_: 数据库模型实体类 :param filters: 查询条件 :return: int(删除的数据id) """ return dbSession.query(cls_).filter(*filters).delete(synchronize_session=False)
def getAll(self, page, limit): whereStr = " 1=1 " limitStr = "" if ((page != None) and (limit != None)): limitStr = " order by taskBeginTime desc limit " + str( (int(page) - 1) * int(limit)) + " , " + str(limit) sqlStr = whereStr + limitStr try: tasks = dbSession.query(Task).filter(text(sqlStr)).all() tasksTotal = dbSession.query(func.count(Task.id)).filter().all() results = self.getResult(tasks, totalTmps=tasksTotal) dbSession.rollback() except Exception as e: dbSession.rollback() app.logger.info(e.args.__str__()) return results
def edit(self, cls_: object, data: dict, filters: set) -> bool: """ 修改 :param cls_: 数据库模型实体类 :param data: 数据 :param filters: 筛选条件 :return: bool """ return dbSession.query(cls_).filter(*filters).update(data, synchronize_session=False)
def get_data(uuid): """ 根据uuid获取分片数据 :param uuid: :return: """ obj = dbSession.query(ImgShard).filter_by( uuid=uuid).order_by('index').all() data = Utils.db_to_d(obj) return data
def getByJobId(self, jobId): job = None try: job = dbSession.query(CronJob).filter( CronJob.jobId == jobId).first() dbSession.commit() except Exception as e: dbSession.rollback() app.logger.info("ERROR:ServiceJob.getByJobId():" + e.args.__str__() + ":" + jobId) return job
def get_all(self, cls_: object, filters: set, order: str = 'id desc', field: tuple = (), limit: int = 0) -> list: """ 查询全部 :param cls_: 数据库模型实体类 :param filters: 查询条件 :param order: 排序 :param field: 字段 :param limit: 取多少条 :return: dict """ if not filters: result = dbSession.query(cls_) else: result = dbSession.query(cls_).filter(*filters) order_list = order.split(' ') if order_list[1] == 'desc': result = result.order_by(desc(order_list[0])).all() else: result = result.order_by(asc(order_list[0])).all() if not field: result = [res.to_dict() for res in result] else: result = [res.to_dict(only=field) for res in result] return result
def get_one(self, filters, order='id desc', field=()): """ :param filters: :param order: :param field: :return: """ result = dbSession.query(Log).filter(*filters) order_list = order.split(' ') if result == None: return None if order_list[1] == 'desc': result = result.order_by(desc(order[0])).first() else: result = result.order_by(asc(order_list[0])).first() if not field: result = result.to_dict() else: result = result.to_dict(only=field) return result
def get_one(self, cls_: object, filters: set, order: str = 'id desc', field: tuple = ()): """ 获取一条 :param cls_: 数据库模型实体类 :param filters: 查询条件 :param order: 排序方式 :param field: 查询字段 :return: dict """ result = dbSession.query(cls_).filter(*filters) order_list = order.split(' ') if result == None: return None # 排序方式为降序 if order_list[1] == 'desc': result = result.order_by(desc(order_list[0])).first() else: result = result.order_by(asc(order_list[0])).first() if not field: result = result.to_dict() else: result = result.to_dict(only=field) return result