def product_query(req): """ Product Query method. Lazy load the product elaborating a Query object before hitting the database. """ session = Session() query = None response = None page = None try: query = Query([Products.name, Products.cost, Products.total_products, Products.batch, Products.expiration_date, Product_Types.name.label('type')]).join(Product_Types) if len(req.args) is 0: pass else: if 'type' in req.args: product_type = req.args['type'] query = query.filter(Product_Types.name == product_type) if 'lower_than' in req.args: cost = float(req.args['lower_than']) query = query.filter(Products.cost < cost) if 'higher_than' in req.args: cost = float(req.args['higher_than']) query = query.filter(Products.cost > cost) if 'name' in req.args: name = req.args['name'] query = query.filter(Products.name.like(f'%{name}%')) except exc.DBAPIError as e: session.rollback() response = error_response(e.code, e.orig) else: if 'page' in req.args: page = int(req.args['page']) query = query.limit(10).offset( (page - 1) * 10 ) else: page = 1 query = query.limit(10).offset(0) query.session = session response = product_response(query, 'products_query', page) finally: return response
def get_pagination_keyboard(query: Query, prefix: str, offset: int = 0) -> InlineKeyboardMarkup: """ Function selects object from model with offset AND PAGINATION_SIZE and then build list of object with pagination buttons, for ability to navigate between pages """ items = query.limit(PAGINATION_SIZE + 1).offset(offset).all() keyboards = [[ InlineKeyboardButton(item.name, callback_data=f'{prefix}.{item.id}') ] for item in items[:PAGINATION_SIZE]] has_next = (PAGINATION_SIZE + 1) == len(items) has_prev = offset > 0 control = [ # show previous button only if offset is more than 0 InlineKeyboardButton('⬅️️️', callback_data=f'{prefix}.prev.{offset}') if has_prev else InlineKeyboardButton( ' ', callback_data=f'{prefix}.prev.None'), # show next button only when there is not next city in list InlineKeyboardButton('➡️', callback_data=f'{prefix}.next.{offset}') if has_next else InlineKeyboardButton( ' ', callback_data=f'{prefix}.next.None'), ] keyboards.append(control) return InlineKeyboardMarkup(keyboards, resize_keyborad=True)
def paginate(query: Query, page: int, items_per_page: int): # Never pass a negative OFFSET value to SQL. offset_adj = 0 if page <= 0 else page - 1 items = query.limit(items_per_page).offset(offset_adj * items_per_page).all() total = query.order_by(None).count() return items, total
def common_filter(query: Query, data_schema, start_timestamp=None, end_timestamp=None, filters=None, order=None, limit=None, time_field='timestamp'): assert data_schema is not None time_col = eval('data_schema.{}'.format(time_field)) if start_timestamp: query = query.filter(time_col >= to_pd_timestamp(start_timestamp)) if end_timestamp: query = query.filter(time_col <= to_pd_timestamp(end_timestamp)) if filters: for filter in filters: query = query.filter(filter) if order is not None: query = query.order_by(order) else: query = query.order_by(time_col.asc()) if limit: query = query.limit(limit) return query
def common_filter(query: Query, data_schema, start_timestamp=None, end_timestamp=None, filters=None, order=None, limit=None): if start_timestamp: query = query.filter( data_schema.timestamp >= to_pd_timestamp(start_timestamp)) if end_timestamp: query = query.filter( data_schema.timestamp <= to_pd_timestamp(end_timestamp)) if filters: for filter in filters: query = query.filter(filter) if order is not None: query = query.order_by(order) else: query = query.order_by(data_schema.timestamp.asc()) if limit: query = query.limit(limit) return query
def paginate(model, query: Query, params: Optional[PaginationParams]) -> BasePage: code = '200' message = 'Success' try: total = query.count() if params.order: direction = desc if params.order == 'desc' else asc data = query.order_by(direction(getattr(model, params.sort_by))) \ .limit(params.page_size)\ .offset(params.page_size * params.page) \ .all() else: data = query.limit(params.page_size).offset(params.page_size * params.page).all() metadata = MetadataSchema(current_page=params.page, page_size=params.page_size, total_items=total) except Exception as e: raise CustomException(http_code=500, code='500', message=str(e)) return PageType.get().create(code, message, data, metadata)
def _apply_orm_limit_offset(self, query: Query, limit: Optional[int], offset: Optional[int]) -> Query: """ Return the query after applying the given limit and offset (if not None). """ if limit is not None: query = query.limit(limit) if offset is not None: query = query.offset(offset) return query
def get_paginated_result(query: Query, skip: int, limit: int): count = query.order_by(None).count() query = query.offset(skip) if limit >= 0: query = query.limit(limit) return { 'pagination': {'skip': skip, 'limit': limit, 'all_records_count': count}, 'result': query.all(), }
def get_collection_contents_qry(self, parent_id, limit=None, offset=None): """Find first level of collection contents by containing collection parent_id""" DCE = model.DatasetCollectionElement qry = Query(DCE).filter(DCE.dataset_collection_id == parent_id) qry = qry.order_by(DCE.element_index) qry = qry.options(joinedload('child_collection'), joinedload('hda')) if limit is not None: qry = qry.limit(int(limit)) if offset is not None: qry = qry.offset(int(offset)) return qry
def list_query(self, query: Query, **kwargs): _limit = kwargs.pop('limit', None) _offset = kwargs.pop('offset', None) if _limit: query = query.limit(_limit) if _offset: query = query.offset(_offset) logger.debug(f'list_query query: {render_query(query, self.session)}') return query.all()
def fetch_all(cls, session: Session, limit: int, offset: int, company_id: int = None) -> List['Match']: query = Query(cls, session=session) if company_id: query = query.filter( or_(Match.left_company_id == company_id, Match.right_company_id == company_id)) query = query.limit(limit).offset(offset) return query.all()
def get_eligible_facts(self, query: Query, skip: int = None, limit: int = None) -> List[models.Fact]: begin_overall_start = time.time() if skip: query = query.offset(skip) if limit: query = query.limit(limit) facts = query.all() overall_end_time = time.time() overall_total_time = overall_end_time - begin_overall_start logger.info("overall time facts: " + str(overall_total_time)) return facts
def get_all(instance: Query, offset: int=None, limit: int=None, filters: list=()) -> list: """ Gets all instances of the query instance :param instance: sqlalchemy queriable :param offset: Offset for request :param limit: Limit for request :param filters: Filters and OrderBy Clauses """ for expression in filters: if _is_ordering_expression(expression): instance = instance.order_by(expression) else: instance = instance.filter(expression) if offset is not None: instance = instance.offset(offset) if limit is not None: instance = instance.limit(limit) return instance.all()
def common_filter( query: Query, data_schema, start_timestamp=None, end_timestamp=None, filters=None, order=None, limit=None, time_field="timestamp", ): """ build filter by the arguments :param query: sql query :param data_schema: data schema :param start_timestamp: start timestamp :param end_timestamp: end timestamp :param filters: sql filters :param order: sql order :param limit: sql limit size :param time_field: time field in columns :return: result query """ assert data_schema is not None time_col = eval("data_schema.{}".format(time_field)) if start_timestamp: query = query.filter(time_col >= to_pd_timestamp(start_timestamp)) if end_timestamp: query = query.filter(time_col <= to_pd_timestamp(end_timestamp)) if filters: for filter in filters: query = query.filter(filter) if order is not None: query = query.order_by(order) else: query = query.order_by(time_col.asc()) if limit: query = query.limit(limit) return query
def common_filter(query: Query, data_schema, ids: List[str] = None, entity_ids: List[str] = None, entity_id: str = None, codes: List[str] = None, code: str = None, start_timestamp=None, end_timestamp=None, filters=None, order=None, limit: int = None, time_field='timestamp'): assert data_schema is not None if entity_id is not None: query = query.filter(data_schema.entity_id == entity_id) if entity_ids is not None: query = query.filter(data_schema.entity_id.in_(entity_ids)) if code is not None: query = query.filter(data_schema.code == code) if codes is not None: query = query.filter(data_schema.code.in_(codes)) if ids is not None: query = query.filter(data_schema.id.in_(ids)) time_col = eval(f'data_schema.{time_field}') if start_timestamp: query = query.filter(time_col >= to_pd_timestamp(start_timestamp)) if end_timestamp: query = query.filter(time_col <= to_pd_timestamp(end_timestamp)) if filters: for filter in filters: query = query.filter(filter) if order is not None: query = query.order_by(order) if limit: query = query.limit(limit) return query
def apply_filter_from_string(model, query: Query, filters: Sequence[str]): for f in filters: as_lower = f.lower() if f == "": pass elif as_lower.startswith("order by"): attribute_name = as_lower.replace("order by", "") descending = False if attribute_name.endswith("desc") or attribute_name.endswith( "descending"): descending = True if attribute_name.endswith("desc"): attribute_name = attribute_name.replace("desc", "") else: attribute_name = attribute_name.replace("descending", "") to_order_on = getattr(model, attribute_name.strip()) if descending: to_order_on = to_order_on.desc() query = query.order_by(to_order_on) elif as_lower == "first": return query.first() elif as_lower.startswith("limit"): to_limit = as_lower.replace("limit", "") query = query.limit(int(to_limit.strip())) else: raise NotImplementedError(f"Have not implemented filter: {f}") return query.all()
def _apply_kwargs(instance: Query, **kwargs) -> Query: for expression in kwargs.pop('filters', []): if _is_ordering_expression(expression): instance = instance.order_by(expression) else: instance = instance.filter(expression) if 'offset' in kwargs: offset = kwargs.pop('offset') foffset = lambda instance: instance.offset(offset) else: foffset = lambda instance: instance if 'limit' in kwargs: limit = kwargs.pop('limit') flimit = lambda instance: instance.limit(limit) else: flimit = lambda instance: instance instance = instance.filter_by(**kwargs) instance = foffset(instance) instance = flimit(instance) return instance
def __init__(self, query: Query, page_id=1, page_size=20, page_info=None): if page_info: page_id = page_info.page_id page_size = page_info.page_size if page_id < 1 or page_size < 1: logger.info( f'page_id: {page_id}, page_size: {page_size}, page_info: {page_info}' ) raise NotFound() total = fast_count(query) if total and total > 0: items = query.limit(page_size).offset( (page_id - 1) * page_size).all() else: total = 0 page_id = 1 items = [] self.items = items self.page_id = page_id self.page_size = page_size self.total = total
def _get_page(query: Query, page_number: int, page_size: int) -> List: offset = page_number * page_size return query.limit(page_size).offset(offset).all()
def paginate(query: Query, page: int, items_per_page: int): offset = 0 if page <= 0 else page - 1 items = query.limit(items_per_page).offset(offset * items_per_page).all() total = query.order_by(None).count() return items, total
def _query(cls, query: Query, start: int = None, count: int = None, order_by=None) -> Query: if start: query = query.offset(start) if count: query = query.limit(count) if order_by is not None: query = query.order_by(order_by) return query
def paginate(self, query: orm.Query, count: int) -> orm.Query: return query.limit(self.limit).offset(self.offset)
def limit(self, query: Query, limit: int) -> Query: return query.limit(limit)
def paginate(query: Query, page: int, limit: int = 50) -> Query: """Paginates a query, calculating the proper offset for the page.""" return query.limit(limit).offset(page * limit)
Query(User, Address).select_from(User).join(User.addresses) # left join Query(User).outerjoin(Address) # filter and filter_by, can be chained. query.filter_by(id=1) # uses keyword arguments query.filter(User.id == 1) # use SQL expression object query.filter(text("id<10")) # use textual SQL (query.filter(text("id<:value and name=:name")) # bind parameters .params(value=10, name='hello')) # other option query.order_by(User.id) query.distinct() query.limit() query.offset() # emit query and get result ################################# # the select Query instance will not emit a query until the # following method invoked query.all() # fetch all as list query.first() # fetch one or None query.one() # fetch one or NoResultFound or MultipleResultsFound query.one_or_none() # fetch one or None or MultipleResultsFound query.scalar() # invokes the one() method and return the first column result = query[1:3] # list with limit and offset