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