def get_scenario_nodes(self, nodes_filter=None, pagination=None): """Search for scenario nodes by filter. :param nodes_filter: instance of :class:`ScenarioNodeFilter <autostorage.core.scenario.param_spec.ScenarioNodeFilter>`. :param pagination: instance of `Pagination <autostorage.core.param_spec.Pagination>`. :returns: generator with instances of :class:`ScenarioNode <autostorage.core.scenario.node.ScenarioNode>`. """ ids_query = Query(ScenarioNodeRecord.node_id) if nodes_filter: ids = nodes_filter.node_ids if ids: ids_query = ids_query.filter(ScenarioNodeRecord.node_id.in_(ids)) else: return [] if pagination: offset = pagination.page_index * pagination.items_per_page ids_query = ids_query.offset(offset).limit(pagination.items_per_page) with self.base.get_session() as session: bound_query = ids_query.with_session(session) return [ScenarioNodeEntity(self.base, record[0]) for record in bound_query]
def get_scenarios(self, scenarios_filter=None, pagination=None): """Search for scenarios by filter. :param scenarios_filter: instance of :class:`ScenarioFilter <autostorage.core.scenario.param_spec.ScenarioFilter>`. :param pagination: instance of `Pagination <autostorage.core.param_spec.Pagination>`. :returns: list of instances of :class:`Scenario <autostorage.core.scenario.scenario.Scenario>`. """ ids_query = Query(ScenarioRecord.scenario_id) if scenarios_filter: ids = scenarios_filter.scenario_ids if ids: ids_query = ids_query.filter(ScenarioRecord.scenario_id.in_(ids)) else: return [] if pagination: offset = pagination.page_index * pagination.items_per_page ids_query = ids_query.offset(offset).limit(pagination.items_per_page) with self.base.get_session() as session: bound_query = ids_query.with_session(session) return [ScenarioEntity(self.base, record[0]) for record in bound_query]
def sample_n_rows_with_repeat(query: Query, num_of_rows: int) -> Iterator[VocabularyTerm]: out = [] count = query.count() for _ in range(num_of_rows): row = query.offset(math.floor(random.random() * count)).first() out.append(row) return out
def paginate_mods(mods: Query, page_size: int = 30) -> Tuple[List[Mod], int, int]: total_pages = math.ceil(mods.count() / page_size) page = get_page() if page > total_pages: page = total_pages if page < 1: page = 1 return mods.offset(page_size * (page - 1)).limit(page_size), page, total_pages
def get_paginated_result(query: Query, skip: int, limit: int): return { 'pagination': { 'skip': skip, 'limit': limit, 'all_records_count': query.order_by(None).count(), }, 'result': query.offset(skip).limit(limit).all(), }
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 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 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 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_scenario_view_states(self, states_filter=None, pagination=None): """Search for scenario view_states by filter. :param states_filter: instance of :class:`ScenarioStateFilter <autostorage.core.scenario.param_spec.ScenarioStateFilter>`. :param pagination: instance of `Pagination <autostorage.core.param_spec.Pagination>`. :returns: list with instances of :class:`ScenarioState <autostorage.core.scenario.scenario.ScenarioState>`. """ ids_query = Query(ScenarioViewStateRecord) subquery = Query([ ScenarioViewStateRecord.scenario_id, func.max(ScenarioViewStateRecord.changed).label('newest_change_date') ]) if states_filter and states_filter.date: subquery = subquery.filter(ScenarioViewStateRecord.changed <= states_filter.date) subquery = subquery.group_by(ScenarioViewStateRecord.scenario_id).subquery() ids_query = ids_query.join( subquery, and_( ScenarioViewStateRecord.scenario_id == subquery.columns.scenario_id, ScenarioViewStateRecord.changed == subquery.columns.newest_change_date ) ) if pagination: offset = pagination.page_index * pagination.items_per_page ids_query = ids_query.offset(offset).limit(pagination.items_per_page) with self.base.get_session() as session: bound_query = ids_query.with_session(session) states = [] for state_record in bound_query: scenario = ScenarioEntity(self.base, state_record.scenario_id) state = ScenarioViewStateEntity( scenario=scenario, name=state_record.name, description=state_record.description, date=state_record.changed ) states.append(state) return states
def _paginate_legacy( query: LegacyQuery, session: Session, total_items: int, offset: int, limit: int, ) -> Page[T]: total_pages = math.ceil(total_items / limit) page_number = offset / limit + 1 return Page[T]( data=query.offset(offset).limit(limit).all(), meta={ "offset": offset, "total_items": total_items, "total_pages": total_pages, "page_number": page_number, }, )
def _fetch_dag_runs( query: Query, *, end_date_gte: Optional[str], end_date_lte: Optional[str], execution_date_gte: Optional[str], execution_date_lte: Optional[str], start_date_gte: Optional[str], start_date_lte: Optional[str], limit: Optional[int], offset: Optional[int], order_by: str, ) -> Tuple[List[DagRun], int]: if start_date_gte: query = query.filter(DagRun.start_date >= start_date_gte) if start_date_lte: query = query.filter(DagRun.start_date <= start_date_lte) # filter execution date if execution_date_gte: query = query.filter(DagRun.execution_date >= execution_date_gte) if execution_date_lte: query = query.filter(DagRun.execution_date <= execution_date_lte) # filter end date if end_date_gte: query = query.filter(DagRun.end_date >= end_date_gte) if end_date_lte: query = query.filter(DagRun.end_date <= end_date_lte) total_entries = query.count() to_replace = {"dag_run_id": "run_id"} allowed_filter_attrs = [ "id", "state", "dag_id", "execution_date", "dag_run_id", "start_date", "end_date", "external_trigger", "conf", ] query = apply_sorting(query, order_by, to_replace, allowed_filter_attrs) return query.offset(offset).limit(limit).all(), total_entries
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 search_query(quotes: Query, search: QuoteSearch, skip=0, limit=100): anywhere = search.anywhere title = search.title text = search.text quote_type = search.quote_type description = search.description author = search.author # public = search.public color = search.color tags = search.tags if title: quotes = quotes.filter(Quote.title.ilike(title)) if text: quotes = quotes.filter(Quote.text.ilike(text)) if quote_type: quotes = quotes.filter(Quote.type == quote_type) if description: quotes = quotes.filter(Quote.description.ilike(description)) if author: quotes = quotes.filter(Quote.author.ilike(author)) if color: quotes = quotes.filter(Quote.color == color.as_hex()) if anywhere: logger.info("ANYWHERE called") quotes = quotes.filter( or_(Quote.title.ilike(anywhere), Quote.text.ilike(anywhere), Quote.description.ilike(anywhere), Quote.author.ilike(author)) ) quotes = quotes.offset(skip).limit(limit).all() if tags: return get_multi_by_tag(quotes, tags) else: return quotes
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 paginate(query: Query, page: int, size: int) -> Page: content: List = query.offset(size * (page - 1)).limit(size).all() # https://github.com/pallets/flask-sqlalchemy/pull/281#issuecomment-95957173 total_elements: int = query.order_by(None).value(func.count()) return Page(content, page, size, total_elements)
# 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 # batch operator #########################################
def as_page(self, query: Query): if query is None: return None return query.offset(self.offset()).limit(self.size)
def search(self, query: Query) -> list: self.__data = query.offset((self.page_index - 1) * self.page_size) \ .limit(self.page_size).all() return self.__data
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