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 _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
