def test_wrong_page_size(self, session, page_number, page_size): query = session.query(Bar) with pytest.raises(InvalidPage) as err: apply_pagination(query, page_number, page_size) expected_error = 'Page size should not be negative: {}'.format( page_size) assert error_value(err) == expected_error
def test_wrong_page_number_with_no_results(self, session, page_number, page_size): query = session.query(Bar) with pytest.raises(InvalidPage) as err: apply_pagination(query, page_number, page_size) expected_error = 'Page number should be positive: {}'.format( page_number) assert error_value(err) == expected_error
def search_filter_sort_paginate( db_session, model, query_str: str = None, page: int = 1, items_per_page: int = 5, sort_by: List[str] = None, descending: List[bool] = None, fields: List[str] = None, ops: List[str] = None, values: List[str] = None, ): """Common functionality for searching, filtering and sorting""" if query_str: query = search(db_session=db_session, query_str=query_str, model=model) else: query = get_all(db_session=db_session, model=model) filter_spec = create_filter_spec(model, fields, ops, values) query = apply_filters(query, filter_spec) sort_spec = create_sort_spec(model, sort_by, descending) query = apply_sort(query, sort_spec) if items_per_page == -1: items_per_page = None query, pagination = apply_pagination(query, page_number=page, page_size=items_per_page) return { "items": query.all(), "itemsPerPage": pagination.page_size, "page": pagination.page_number, "total": pagination.total_results, }
def get_sections(self, db: Session, *, filter_spec: list, paginate: dict, sort_spec: list) -> List[Optional[Section]]: """ 查询所有板块信息 :param filter_spec: 过滤查询的条件 :param sort_spec: 排序(field: 字段名,direction: 排序方式) :param paginate: page: 当前页数,limit: 每页显示的条数 :return: 板块信息列表, 总记录数, 总页数 """ query = db.query(Section).filter(Section.status == 0) # 查询出所有板块信息 # --------------- 过滤 ---------------- # filters = [] for filter in filter_spec: if filter["value"]: data = {"model": "Section", "field": filter["field"], "option": filter["option"], "value": filter["value"]} filters.append(data) filters_query = apply_filters(query, filters) # ------------- 排序 -------------- # sort_specs = [] for sort in sort_spec: sort_specs.append({"model": "Section", "field": sort["field"], "direction": sort["direction"]}) sorted_query = apply_sort(filters_query, sort_specs) # --------------- 分页 ---------------- # page_number = paginate["page_number"] page_size = paginate["page_size"] pagination_query, pagination = apply_pagination( sorted_query, page_number=page_number, page_size=page_size) db_obj = pagination_query.all() total = pagination.total_results # 总记录数 number_page = pagination.num_pages # 总页数 return db_obj, total, number_page
def paginate_query(self): page_number = 1 elem_per_page = 10 if self.__data['page'] and self.__data['elem_per_page']: return apply_pagination( self.__query, page_number=int(self.__data['page']), page_size=int(self.__data['elem_per_page']) ) else: return apply_pagination( self.__query, page_number=page_number, page_size=elem_per_page )
def full_search(self, table, query, page=False, page_size=False): filters = [] for item in self.spec[table]: if item['searchable']: filters.append({ 'field': item['name'], 'op': 'ilike', 'value': '%{0}%'.format(query) }) filter_spec = [ { "or": filters } ] res = sqlalchemy_filters.apply_filters( self.session.query(self.tables[table]), filter_spec) res_len = len(res.all()) if page: res, pagination = sqlalchemy_filters.apply_pagination( res, page_number=int(page), page_size=int(page_size) ) return { "count": res_len, "results": self.query_to_list(res.all()) }
def search_filter_sort_paginate( db_session, model, query_str: str = None, filter_spec: List[dict] = None, page: int = 1, items_per_page: int = 5, sort_by: List[str] = None, descending: List[bool] = None, current_user: DispatchUser = None, ): """Common functionality for searching, filtering, sorting, and pagination.""" model_cls = get_class_by_tablename(model) sort_spec = create_sort_spec(model, sort_by, descending) query = db_session.query(model_cls) if query_str: sort = False if sort_spec else True query = search(db_session=db_session, query_str=query_str, model=model, sort=sort) query = apply_model_specific_filters(model, query, current_user) query = apply_filters(query, filter_spec) query = apply_sort(query, sort_spec) if items_per_page == -1: items_per_page = None query, pagination = apply_pagination(query, page_number=page, page_size=items_per_page) return { "items": query.all(), "itemsPerPage": pagination.page_size, "page": pagination.page_number, "total": pagination.total_results, }
def get_users(): """ Query users with Filtering and Paging capabilities e.g.: http://127.0.0.1:5000/auth/users/?q={"filters":[{"field":"role_id","op":"eq","value":"3"}]} &page_number=1&page_size=1 :return: JSON list of User instances """ if g.user.role_id == Role.Admin or g.user.role_id == Role.Manager: query = User.query if request.query_string: q_param = request.args.get('q') if q_param: q = json.loads(q_param) for f in q['filters']: query = apply_filters(query, f) page_number = request.args.get('page_number') page_size = int(request.args.get('page_size', default=10)) if page_number: page_number = int(page_number) query, pagination = apply_pagination(query, page_number=page_number, page_size=page_size) return jsonify({'users': [user.export_data() for user in query.all()]}) else: return jsonify({}), 403
def _apply_filters_and_pagination(self, page_number=PAGE_DEFAULT, page_size=PER_PAGE_DEFAULT, sort_field=None, sort_dir=None, status=None, noticeofworktype=None, mine_region=None, trackingnumber=None, mine_search=None): filters = [] base_query = Application.query if noticeofworktype is not None: filters.append( func.lower(Application.noticeofworktype).contains( func.lower(noticeofworktype))) if trackingnumber is not None: filters.append(Application.trackingnumber == trackingnumber) if mine_region is not None or mine_search is not None: base_query = base_query.join(Mine) if mine_region is not None: region_filter_values = mine_region.split(',') filters.append(Mine.mine_region.in_(region_filter_values)) if mine_search is not None: filters.append( or_( func.lower(Application.minenumber).contains( func.lower(mine_search)), func.lower(Mine.mine_name).contains( func.lower(mine_search)), func.lower(Mine.mine_no).contains( func.lower(mine_search)))) status_filter_values = [] if status is not None: status_filter_values = status.split(',') if len(status_filter_values) > 0: status_filters = [] for status in status_filter_values: status_filters.append( func.lower(Application.status).contains( func.lower(status))) filters.append(or_(*status_filters)) base_query = base_query.filter(*filters) if sort_field and sort_dir: sort_criteria = [{ 'model': 'Application', 'field': sort_field, 'direction': sort_dir }] base_query = apply_sort(base_query, sort_criteria) return apply_pagination(base_query, page_number, page_size)
def fetch_all_with_pagination(self, db: Session, page: int, per_page: int = 20, name: str = '') -> ProductsResponse: """ Retrieve all products records listed by page argument and pagination metadata. Args: db (Session): The database session. page (int): Page to fetch. per_page (int): Amount of products per page. name (str): Product name to filter. Raises: InvalidPage: If the page informed is invalid. ItensNotFound: If no item was found. InvalidPageItemsNumber: Numbers of items per page must be greater than 0. Returns: ProductsResponse: A dict with products records and pagination metadata. """ if page <= 0: raise InvalidPage( f"Page number should be positive and greater than zero: {page}" ) if per_page <= 0: raise InvalidPageItemsNumber( f"Numbers of items per page must be greater than zero") query = db.query(Product).filter( Product.is_deleted == False, func.lower(Product.name).contains( name.lower(), autoescape=True)).order_by(Product.id) query, pagination = apply_pagination(query, page_number=page, page_size=per_page) products = parse_obj_as(List[ProductResponse], query.all()) if page > pagination.num_pages and pagination.num_pages > 0: raise InvalidPage( f"Page number invalid, the total of pages is {pagination.num_pages}: {page}" ) if len(products) == 0: raise ItensNotFound("No products found") pagination_metadata = make_pagination_metadata( current_page=page, total_pages=pagination.num_pages, per_page=per_page, total_items=pagination.total_results, url_args={'name': name}) response = ProductsResponse(pagination_metadata=pagination_metadata, records=products) return response
def object_list(request, page, filters=None, includes=None, *args, **kwargs) -> Tuple[List, List, int, int]: permitted_query = permitted_objects( request, self._base_query() if self._base_query is not None else self._model.objects.query()) # # Apply all the filters from the URL # if filters: filtered_query = permitted_query copied_filters = filters[:] for filter in filters: field = filter['field'] if field in self._computed_filters: copied_filter = dict(filter) copied_filter.pop('field') filtered_query = self._computed_filters[ field].filter_func(filtered_query, **copied_filter) copied_filters.remove(filter) filtered_query = apply_filters(filtered_query, copied_filters) else: filtered_query = permitted_query # # Paginate the result # query, pagination = apply_pagination(filtered_query, page_number=int(page), page_size=self._page_size) if self._before_list_callback: query = self._before_list_callback(request, query) # Fetch the values from DB objects = query.all() rendered_includes = render_includes(includes, objects) if self._after_list_callback: try: objects = self._after_list_callback(request, objects) except Exception as e: traceback.print_exc() return [{ 'errors': [str(e)] }], [], 0, getattr(e, 'http_status', HTTP_500_INTERNAL_SERVER_ERROR) result = schema_many.json_api_dump(objects, self._resource_name) return result, rendered_includes, pagination.total_results, HTTP_200_OK
def _build_response_list(self, model, builder, data, error=None): """ :param model: :param builder: :param data: :param error: :return: """ qsqla = Qs2Sqla(model, self.syntax, self.arguments) invalid = error or [] page, limit, error = qsqla.get_pagination( request.args, cap.config['AUTOCRUD_MAX_QUERY_LIMIT']) invalid += error query, error = qsqla.dict2sqla(data) invalid += error if len(invalid) > 0: flask.abort(status.BAD_REQUEST, response=dict(invalid=invalid)) query, pagination = sqlaf.apply_pagination(query, page, limit) result = query.all() response = [] links_enabled = cap.config['AUTOCRUD_EXPORT_ENABLED'] is False \ or qsqla.arguments.scalar.export not in request.args for r in result: if qsqla.arguments.scalar.as_table in request.args: response += to_flatten(r, to_dict=model.to_dict) else: response.append(r.to_dict(links=links_enabled)) if cap.config['AUTOCRUD_EXPORT_ENABLED'] is True: if qsqla.arguments.scalar.export in request.args: filename = request.args.get( qsqla.arguments.scalar.export) or "{}{}{}".format( self._model.__name__, "_{}".format(page) if page else "", "_{}".format(limit) if limit else "") return self._response.csv(response, filename=filename) response = { model.__name__ + model.collection_suffix: response, '_meta': self._pagination_meta(pagination) } etag = self._compute_etag(response) self._check_etag(etag) return self._response_with_etag( builder, (response, *self._pagination_headers(pagination)), etag)
def get_whole_table(self, table, page=False, page_size=False): res = self.session.query(self.tables[table]) res_len = len(res.all()) if page: res, pagination = sqlalchemy_filters.apply_pagination( res, page_number=int(page), page_size=int(page_size) ) return { "count": res_len, "results": self.query_to_list(res.all()) }
def apply_filter_and_search(self, args): # Handle ListView request items_per_page = args['per_page'] page = args['page'] # parse the filter terms first_name_filter_term = args['first_name'] last_name_filter_term = args['last_name'] party_name_filter_term = args['party_name'] type_filter_term = args['type'] role_filter_term = args['role'] email_filter_term = args['email'] phone_filter_term = args['phone_no'] conditions = [] if first_name_filter_term: conditions.append( Party.first_name.ilike('%{}%'.format(first_name_filter_term))) if last_name_filter_term: conditions.append( Party.party_name.ilike('%{}%'.format(last_name_filter_term))) if party_name_filter_term: conditions.append( Party.party_name.ilike('%{}%'.format(party_name_filter_term))) if email_filter_term: conditions.append( Party.email.ilike('%{}%'.format(email_filter_term))) if type_filter_term: conditions.append(Party.party_type_code.like(type_filter_term)) if phone_filter_term: conditions.append( Party.phone_no.ilike('%{}%'.format(phone_filter_term))) if role_filter_term == "NONE": conditions.append(Party.mine_party_appt == None) contact_query = Party.query.filter(and_(*conditions)) if role_filter_term and not role_filter_term == "NONE": role_filter = MinePartyAppointment.mine_party_appt_type_code.like( role_filter_term) role_query = Party.query.join(MinePartyAppointment).filter( role_filter) contact_query = contact_query.intersect( role_query) if contact_query else role_query sort_criteria = [{ 'model': 'Party', 'field': 'party_name', 'direction': 'asc' }] contact_query = apply_sort(contact_query, sort_criteria) return apply_pagination(contact_query, page, items_per_page)
def get(self): paginated_variances, pagination_details = apply_pagination( Variance.query, request.args.get('page', PAGE_DEFAULT, type=int), request.args.get('per_page', PER_PAGE_DEFAULT, type=int)) if not paginated_variances: raise BadRequest('Unable to fetch variances.') return { 'records': paginated_variances.all(), 'current_page': pagination_details.page_number, 'total_pages': pagination_details.num_pages, 'items_per_page': pagination_details.page_size, 'total': pagination_details.total_results, }
def get(self, party_guid=None): if party_guid: try: party = Party.find_by_party_guid(party_guid) except DBAPIError: return self.create_error_payload(422, 'Invalid Party guid'), 422 if party: return party.json() else: return self.create_error_payload(404, 'Party not found'), 404 else: search_term = request.args.get('search') search_type = request.args.get('type').upper() if request.args.get( 'type') else None items_per_page = request.args.get('per_page', 25, type=int) page = request.args.get('page', 1, type=int) parties = Party.query if search_term: if search_type in ['PER', 'ORG']: parties = Party.search_by_name( search_term, search_type, self.PARTY_LIST_RESULT_LIMIT) else: parties = Party.search_by_name( search_term, query_limit=self.PARTY_LIST_RESULT_LIMIT) paginated_parties, pagination_details = apply_pagination( parties, page, items_per_page) if not paginated_parties: self.raise_error(404, 'No parties found'), 404 parties = paginated_parties.all() relationships = request.args.get('relationships') relationships = relationships.split(',') if relationships else [] return { 'parties': list( map(lambda x: x.json(relationships=relationships), parties)), 'current_page': pagination_details.page_number, 'total_pages': pagination_details.num_pages, 'items_per_page': pagination_details.page_size, 'total': pagination_details.total_results, }
def search_filter_sort_paginate( db_session, model, query_str: str = None, filter_spec: List[dict] = None, page: int = 1, items_per_page: int = 5, sort_by: List[str] = None, descending: List[bool] = None, fields: List[str] = None, ops: List[str] = None, values: List[str] = None, join_attrs: List[str] = None, user_role: UserRoles = UserRoles.user, ): """Common functionality for searching, filtering and sorting""" model_cls = get_class_by_tablename(model) sort_spec = create_sort_spec(model, sort_by, descending) if query_str: sort = False if sort_spec else True query = search(db_session=db_session, query_str=query_str, model=model, sort=sort) else: query = db_session.query(model_cls) query = join_required_attrs(query, model_cls, join_attrs, fields, sort_by) if not filter_spec: filter_spec = create_filter_spec(model, fields, ops, values, user_role) query = apply_filters(query, filter_spec) query = apply_sort(query, sort_spec) if items_per_page == -1: items_per_page = None query, pagination = apply_pagination(query, page_number=page, page_size=items_per_page) return { "items": query.all(), "itemsPerPage": pagination.page_size, "page": pagination.page_number, "total": pagination.total_results, }
def test_page_number_greater_than_one(self, session, page_number): query = session.query(Bar) page_size = None paginated_query, pagination = apply_pagination(query, page_number, page_size) assert query != paginated_query assert Pagination(page_number=page_number, page_size=8, num_pages=1, total_results=8) == pagination result = paginated_query.all() assert len(result) == 0
def test_page_size_zero(self, session, page_number): query = session.query(Bar) page_size = 0 paginated_query, pagination = apply_pagination(query, page_number, page_size) assert query != paginated_query assert Pagination(page_number=page_number, page_size=0, num_pages=0, total_results=8) == pagination result = paginated_query.all() assert len(result) == 0
def get_topics(self, db: Session, *, filter_spec: list, paginate: dict, sort_spec: list) -> List[Optional[Topic]]: """ 查询所有主贴信息 :param filter_spec: 过滤查询的条件 :param sort_spec: 排序(field: 字段名,direction: 排序方式) :param paginate: page: 当前页数,limit: 每页显示的条数 :return: 主贴信息列表, 总记录数, 总页数 """ query = db.query(Topic, Section, User).filter(Topic.userid == User.id, Topic.sectionid == Section.id, Topic.status == 0) # --------------- 过滤 ---------------- # filters = [] for filter in filter_spec: # 过滤 if filter["value"]: data = { "model": "Topic", "field": filter["field"], "option": filter["option"], "value": filter["value"] } filters.append(data) filters_query = apply_filters(query, filters) # 根据查询条件查询 # ------------- 排序 -------------- # sort_specs = [] for sort in sort_spec: sort_specs.append({ "model": "Topic", "field": sort["field"], "direction": sort["direction"] }) sorted_query = apply_sort(filters_query, sort_specs) # --------------- 分页 ---------------- # page_number = paginate["page_number"] page_size = paginate["page_size"] pagination_query, pagination = apply_pagination( sorted_query, page_number=page_number, page_size=page_size) # 分页 db_result_obj = pagination_query.all() total = pagination.total_results # 总记录数 number_page = pagination.num_pages # 总页数 return db_result_obj, total, number_page
def __new__(cls, query, page_number=1, page_size=10, serializer=None): query, pagination = apply_pagination(query, page_number=page_number, page_size=page_size) page_size, page_number, num_pages, total_results = pagination _next = cls.is_next_page(page_number, page_size, total_results) result = serializer.dump(query) __paginate_serializer = PaginateSchema() __dict_paginate = { "count": page_number, "next": _next, "total_results": total_results, "total_page": num_pages, "result": result } __serialized_result = __paginate_serializer.dump(__dict_paginate) return __serialized_result
def test_page_size_and_page_number_provided(self, session): query = session.query(Bar) page_size = 2 page_number = 1 paginated_query, pagination = apply_pagination(query, page_number, page_size) assert query != paginated_query assert Pagination(page_number=1, page_size=2, num_pages=0, total_results=0) == pagination result = paginated_query.all() assert len(result) == 0
def _apply_filters_and_pagination(self, page_number=DEFAULT_PAGE_NUMBER, page_size=DEFAULT_PAGE_SIZE, sort_field=None, sort_dir=None, id=None, guid=None, company_name=None, application_status_code=[], application_phase_code=[]): base_query = Application.query filters = [] if id: filters.append(Application.id == id) if guid: filters.append(Application.guid == guid) if application_status_code: base_query = base_query.filter( Application.application_status_code.in_( application_status_code)) if application_phase_code: base_query = base_query.filter( Application.application_phase_code.in_(application_phase_code)) if company_name: filters.append(Application.json['company_details']['company_name'] ['label'].astext.contains(company_name.upper())) base_query = base_query.filter(*filters) if sort_field and sort_dir: sort_criteria = [{ 'model': 'Application', 'field': sort_field, 'direction': sort_dir, }] base_query = apply_sort(base_query, sort_criteria) return apply_pagination(base_query, page_number, page_size)
def get_signin_logs(self, db: Session, *, filter_spec: list, paginate: dict, sort_spec: list) -> List[User]: """ 查询所有用户登录日志信息 :param filter_spec: 过滤查询的条件 :param sort_spec: 排序(field: 字段名,direction: 排序方式) :param paginate: page: 当前页数,limit: 每页显示的条数 :return: 用户登录日志信息关联列表, 总记录数, 总页数 """ query = db.query(SigninLog, User).filter(SigninLog.userid == User.id, User.is_logoff == 0) # 查询关联表之间的所有信息 # --------------- 过滤 ---------------- # filters = [] for filter in filter_spec: if filter["value"]: filters.append({ "model": "SigninLog", "field": filter["field"], "option": filter["option"], "value": filter["value"] }) filters_query = apply_filters(query, filters) # --------------- 排序 ---------------- # sort_specs = [] for sort in sort_spec: sort_specs.append({ "model": "SigninLog", "field": sort["field"], "direction": sort["direction"] }) sorted_query = apply_sort(filters_query, sort_specs) # --------------- 分页 ---------------- # page_number = paginate["page_number"] page_size = paginate["page_size"] pagination_query, pagination = apply_pagination( sorted_query, page_number=page_number, page_size=page_size) db_result_obj = pagination_query.all() total = pagination.total_results # 总记录数 number_page = pagination.num_pages # 总页数 return db_result_obj, total, number_page
def test_get_individual_record(self, session): query = session.query(Bar) page_size = 1 page_number = 5 paginated_query, pagination = apply_pagination(query, page_number, page_size) assert query != paginated_query assert Pagination(page_number=5, page_size=1, num_pages=8, total_results=8) == pagination result = paginated_query.all() assert len(result) == 1 assert result[0].id == 5
def test_page_number_and_page_size_provided(self, session): query = session.query(Bar) page_size = 2 page_number = 3 paginated_query, pagination = apply_pagination(query, page_number, page_size) assert query != paginated_query assert Pagination(page_number=3, page_size=2, num_pages=4, total_results=8) == pagination result = paginated_query.all() assert len(result) == 2 assert result[0].id == 5 assert result[1].id == 6
def get_users(self, db: Session, *, filter_spec: list, paginate: dict, sort_spec: list) -> List[User]: """ 查询所有用户信息 :param filter_spec: 过滤查询的条件 :param sort_spec: 排序(field: 字段名,direction: 排序方式) :param paginate: page: 当前页数,limit: 每页显示的条数 :return: 用户信息列表, 总记录数, 总页数 """ query = db.query(User).filter(User.is_logoff == 0) # 查询出所有未注销的用户 # --------------- 过滤 ---------------- # filters = [] for filter in filter_spec: if filter["value"]: data = { "model": "User", "field": filter["field"], "option": filter["option"], "value": filter["value"] } filters.append(data) filters_query = apply_filters(query, filters) # 根据查询条件查询 # ------------- 排序 -------------- # sort_specs = [] for sort in sort_spec: sort_specs.append({ "model": "User", "field": sort["field"], "direction": sort["direction"] }) sorted_query = apply_sort(filters_query, sort_specs) # --------------- 分页 ---------------- # page_number = paginate["page_number"] page_size = paginate["page_size"] pagination_query, pagination = apply_pagination( sorted_query, page_number=page_number, page_size=page_size) # 分页 db_result_obj = pagination_query.all() # 符合条件记录 total = pagination.total_results # 总记录数 number_page = pagination.num_pages # 总页数 return db_result_obj, total, number_page
def test_get_first_page(self, session): query = session.query(Bar) page_size = 2 page_number = 1 paginated_query, pagination = apply_pagination(query, page_number, page_size) assert query != paginated_query assert Pagination(page_number=1, page_size=2, num_pages=4, total_results=8) == pagination result = paginated_query.all() assert len(result) == 2 assert result[0].id == 1 assert result[1].id == 2
def __new__(cls, query, serializer=None): page_number = bottle.request.GET.get('page') or 1 page_size = bottle.request.GET.get('count') or 10 query, pagination = apply_pagination(query, page_number=int(page_number), page_size=int(page_size)) page_size, page_number, num_pages, total_results = pagination _next = cls.is_next_page(page_number, page_size, total_results) result = serializer.dump(query) __paginate_serializer = PaginateSchema() __dict_paginate = { "count": page_number, "next": _next, "total_results": total_results, "total_page": num_pages, "result": result } __serialized_result = __paginate_serializer.dump(__dict_paginate) return __serialized_result
def test_first_page(self, session): query = session.query(Bar) page_size = None page_number = 1 paginated_query, pagination = apply_pagination(query, page_number, page_size) assert query != paginated_query assert Pagination(page_number=1, page_size=8, num_pages=1, total_results=8) == pagination result = paginated_query.all() assert len(result) == 8 for i in range(8): assert result[i].id == i + 1