def list(self, image_id, region_id, zone_id, limit: int,
marker: uuid.UUID):
# TODO: allow filtering by tags
project = cherrypy.request.project
starting_query = Query(Instance).filter(
Instance.project_id == project.id)
if image_id is not None:
starting_query = starting_query.filter(
Instance.image_id == image_id)
if region_id is not None:
with cherrypy.request.db_session() as session:
region = session.query(Region).filter(
Region.id == region_id).first()
if region is None:
raise cherrypy.HTTPError(
404, "A region with the requested id does not exist.")
starting_query = starting_query.filter(
Instance.region_id == region.id)
if zone_id is not None:
with cherrypy.request.db_session() as session:
zone = session.query(Zone).filter(Zone.id == zone_id).first()
if zone is None:
raise cherrypy.HTTPError(
404, "A zone with the requested id does not exist.")
starting_query = starting_query.filter(Instance.zone_id == zone_id)
return self.paginate(Instance,
ResponseInstance,
limit,
marker,
starting_query=starting_query)
def paginated_query(*, page: int, query: Query) -> dict:
page = max(1, page)
per_page = 20
entity = query.column_descriptions[0]["entity"]
q = {
"before": request.args.get("before"),
"after": request.args.get("after")
}
if q["after"]:
query = query.filter(entity.created_at >= q["after"])
if q["before"]:
query = query.filter(entity.created_at <= q["before"])
paginated_items = query.order_by(entity.id.desc()).paginate(
page=page, per_page=per_page, error_out=False)
items_list = paginated_items.items or []
return success_response({
"page":
paginated_items.page,
"perPage":
per_page,
"total":
paginated_items.pages,
"next":
paginated_items.has_next and paginated_items.next_num,
"prev":
paginated_items.has_prev and paginated_items.prev_num,
"items": [item.to_dict() for item in items_list],
})
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 _apply_filters_to_query(self, query: Query, filters: list,
kwarg_filters: dict) -> Query:
"""
Applies filter criteria to a query. Filter expressions can be sql expressions
or keyword arguments.
Arguments:
query {Query} -- Existing sql query
filters {list} -- List of sql expressions to filter query (ex. [self.model.column==True])
kwarg_filters {dict} -- Dictionary of expressions to filter query (ex. {column_name: value})
Raises:
AttributeError -- If kwarg_filters contains keys that aren't column names of self.model
Returns:
Query -- Modified sql query
"""
if kwarg_filters:
for k, v in kwarg_filters.items():
try:
query = query.filter(getattr(self.model, k) == v)
except AttributeError as e:
raise AttributeError(
f'Cannot filter query with invalid model attribute \'{k}\''
) from e
if filters:
for condition in filters:
query = query.filter(condition)
return query
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 _filter_by_creation_mode(query: Query, creation_mode: str) -> Query:
if creation_mode == MANUAL_CREATION_MODE:
query = query.filter(Offer.lastProviderId.is_(None))
if creation_mode == IMPORTED_CREATION_MODE:
query = query.filter(~Offer.lastProviderId.is_(None))
return query
def _parent_id_filter(self, query: Query,
parent_ids: typing.List[int]) -> Query:
""" Filtering result by parent ids"""
if parent_ids == 0:
return query.filter(Workspace.parent_id == None) # noqa: E711
if parent_ids is None or parent_ids == []:
return query
if parent_ids:
allowed_parent_ids = []
allow_root = False
for parent_id in parent_ids:
if parent_id == 0:
allow_root = True
else:
allowed_parent_ids.append(parent_id)
if allow_root:
query = query.filter(
or_(Workspace.parent_id.in_(allowed_parent_ids),
Workspace.parent_id == None) # noqa: E711
)
else:
query = query.filter(
Workspace.parent_id.in_(allowed_parent_ids))
return query
def apply(self, query: Query):
from models import Measurement
if self.start:
query = query.filter(Measurement.created_at >= self.start)
if self.end:
query = query.filter(Measurement.created_at <= self.end)
return query
def common_user_search(query: Query, params: CommonlyUsedUserSearch) -> Query:
if params.sex is not None:
query = query.filter(UserDB.sex == params.sex)
if params.keyword_name:
query = query.filter(UserDB.name.ilike(f'%{params.keyword_name}%'))
if params.user_identity is not None:
query = query.filter(
UserDB.user_identity.op('&')(params.user_identity))
return query
def resource_info(self, resource):
with session_scope() as session:
query = Query(Resource)
query = query.with_session(session)
if resource.isnumeric():
query = query.filter(Resource.id == resource)
else:
query = query.filter(Resource.name == resource)
resource = query.one()
print(json.dumps(resource.to_dict(), indent=4))
def list(self, name, region_id, limit: int, marker: uuid.UUID):
starting_query = Query(Network)
if region_id is not None:
with cherrypy.request.db_session() as session:
region = session.query(Region).filter(Region.id == region_id).first()
if region is None:
raise cherrypy.HTTPError(404, "A region with the requested id does not exist.")
starting_query = starting_query.filter(Network.region_id == region.id)
if name is not None:
starting_query = starting_query.filter(Network.name == name)
return self.paginate(Network, ResponseNetwork, limit, marker, starting_query=starting_query)
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 handleFilters(query: Query, request_params: ImmutableDict)-> Query:
fromYear = request_params.get("fromYear", None)
toYear = request_params.get("toYear", None)
shape = request_params.get("shape", None)
if fromYear:
query = query.filter(Report.date >= year_to_date(fromYear))
if toYear:
query = query.filter(Report.date <= year_to_date(toYear))
if shape:
shapelist = shape.split(",")
shapelist = [shape.strip().upper() for shape in shapelist]
query = query.filter(func.upper(Report.shape).in_(shapelist))
return query
def _build_filters_by(self,
query: sqlalchemy_orm.Query) -> sqlalchemy_orm.Query:
for key, value in self._filters_by.items():
path = key.split(".")
if len(path) == 1:
query = query.filter(getattr(self._table, key) == value)
elif len(path) == 2:
query = query.filter(
getattr(self._table, path[0]).has(**{path[1]: value}))
else:
raise InternalError(
f"internal.query.wrong_entity_key_path:{key}")
return query
def restricted_worker_filter(query: orm.Query, current_user: DispatchUser,
role: UserRoles):
"""Adds additional incident type filters to query (usually for permissions)."""
if current_user:
if role == UserRoles.PLANNER:
team_list = [i.id for i in current_user.managed_teams]
team_list.append(current_user.default_team_id)
query = query.filter(Worker.team_id.in_(set(team_list)))
elif role in (UserRoles.WORKER, UserRoles.CUSTOMER):
query = (query.filter(Worker.id == -999999))
query.distinct()
return query
def to_python(conv, value):
model_ = model or conv.field.form.model
if value not in ('', None):
field = getattr(model_, conv.field.name)
# XXX Using db.query() won't work properly with custom
# auto-filtering query classes. But silently replacing the class is
# not good too.
query = Query(model_, session=conv.env.db)
if issubclass(model_, WithState):
states = WithState.PRIVATE, WithState.PUBLIC
query = query.filter(model_.state.in_(states))
item = query.filter(field==value).scalar()
if item is not None and item != conv.field.form.item:
return False
return True
def to_python(conv, value):
model_ = model or conv.field.form.model
if value not in ('', None):
field = getattr(model_, conv.field.name)
# XXX Using db.query() won't work properly with custom
# auto-filtering query classes. But silently replacing the class is
# not good too.
query = Query(model_, session=conv.env.db)
if issubclass(model_, WithState):
states = WithState.PRIVATE, WithState.PUBLIC
query = query.filter(model_.state.in_(states))
item = query.filter(field == value).scalar()
if item is not None and item != conv.field.form.item:
return False
return True
def filter_func(query: Query):
if search_string.count(' '):
str1, str2 = search_string.split(' ', 1)
query = query.filter(or_(
and_(cls.firstname.like("%{}%".format(str1)), cls.lastname.like("%{}%".format(str2))),
and_(cls.lastname.like("%{}%".format(str1)), cls.firstname.like("%{}%".format(str2))),
))
else:
query = query.filter(or_(
cls.email.like("%{}%".format(search_string)),
cls.username.like("%{}%".format(search_string)),
cls.firstname.like("%{}%".format(search_string)),
cls.lastname.like("%{}%".format(search_string)),
))
return query
def filter_in(
query: Query,
column: Column,
is_null: bool = False,
negate: bool = False,
values: Optional[List[Any]] = None,
) -> Query:
""" Update the query to filter based on a set of values for a column """
if is_null:
# pylint: disable=singleton-comparison
return query.filter(column == None)
if not values:
return query
if negate:
return query.filter(column.notin_(tuple(values)))
return query.filter(column.in_(tuple(values)))
def _filter_event_types(self, query: Query, event_types: Optional[
List[EventTypeDatabaseParameters]], exclude: bool) -> Query:
if event_types:
event_type_filters = []
for event_type in event_types:
if event_type.subtype:
event_type_filter = and_(
Event.entity_type == event_type.entity,
Event.operation == event_type.operation,
Event.entity_subtype == event_type.subtype,
)
else:
event_type_filter = and_(
Event.entity_type == event_type.entity,
Event.operation == event_type.operation,
)
event_type_filters.append(event_type_filter)
if len(event_type_filters) > 1:
f = or_(*event_type_filters)
else:
f = event_type_filters[0]
if exclude:
f = not_(f)
return query.filter(f)
return query
def books_query(args, _, context):
query = Query([]).select_from(Book)
if "genre" in args:
query = query.filter(Book.genre == args["genre"])
return query
def get_search_results(cls, qs: orm.Query, term: str) -> orm.Query:
return qs.filter(
sa.or_(
DemoModel.name.ilike(f"%{term}%"),
DemoModel.description.ilike(f"%{term}%")
)
)
def global_settings(self) -> GlobalSetting:
if self._global_settings is None:
qr = Query(GlobalSetting).with_session(self.session)
if self._global_settings_id is not None:
qr = qr.filter(GlobalSetting.id == self._global_settings_id)
self._global_settings = qr.first()
return self._global_settings
def _extract_simulation_template(
self, session: Session, global_settings_id: Optional[int] = None
) -> SimulationTemplate:
"""
Extract a SimulationTemplate instance using the given database session
"""
qr = Query(GlobalSetting).with_session(session)
if global_settings_id is not None:
qr = qr.filter(GlobalSetting.id == global_settings_id)
global_settings: GlobalSetting = qr.first()
if global_settings is None:
raise SchematisationError(
f"Global settings with id: {global_settings_id} not found."
)
dwf_laterals = DWFCalculator(session, global_settings.use_0d_inflow).laterals
initial_waterlevels = InitialWaterlevelExtractor(session, global_settings_id)
settings = SettingsExtractor(session, global_settings.id)
return SimulationTemplate(
events=Events(
structure_controls=StructureControlExtractor(
session, control_group_id=global_settings.control_group_id
).all_controls(),
laterals=LateralsExtractor(session).as_list(),
dwf_laterals=dwf_laterals,
boundaries=BoundariesExtractor(session).as_list(),
),
settings=settings.all_settings(),
initial_waterlevels=initial_waterlevels.all_initial_waterlevels(),
)
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 add_integer_filter(query: Query, ints: List[str], body_tables: List[AliasedClass]) -> Query:
any_int = '*'
for integer, table in zip(ints, body_tables):
if integer != any_int:
query = query.filter(eval("table.longitude_coeff %s" % integer))
return 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 filter_comp(
query: Query,
column: Column,
negate: bool = False,
oper: str = "eq",
upper: Optional[Any] = None,
value: Optional[Any] = None,
) -> Query:
""" Update the query to filter based on number-like comparisons for a column """
if value is None:
return query
if oper == "eq":
expr = column == value
elif oper == "lt":
expr = column < value
elif oper == "lte":
expr = column <= value
elif oper == "gt":
expr = column > value
elif oper == "gte":
expr = column >= value
elif oper == "between" and upper is not None:
expr = column.between(value, upper)
else:
return query # unknown operator
if negate:
expr = not_(expr)
return query.filter(expr)
def __apply_filters_to_query(self, query: Query,
filters: Union[Dict[str, List[str]]]):
query = query.join(MetaORM)
for key, values in filters.items():
query = query.filter(MetaORM.name == key,
MetaORM.value.in_(values))
return query
def task_query_restricted_to_permitted_users(
req: "CamcopsRequest",
q: Query,
cls: Union[Type[Task], Type[TaskIndexEntry]],
as_dump: bool,
) -> Optional[Query]:
"""
Restricts an SQLAlchemy ORM query to permitted users, for a given
task class. THIS IS A KEY SECURITY FUNCTION.
Args:
req:
the :class:`camcops_server.cc_modules.cc_request.CamcopsRequest`
q:
the SQLAlchemy ORM query
cls:
the class of the task type, or the
:class:`camcops_server.cc_modules.cc_taskindex.TaskIndexEntry`
class
as_dump:
use the "dump" permissions rather than the "view" permissions?
Returns:
a filtered query (or the original query, if no filtering was required)
"""
user = req.user
if user.superuser:
return q # anything goes
# Implement group security. Simple:
if as_dump:
group_ids = user.ids_of_groups_user_may_dump
else:
group_ids = user.ids_of_groups_user_may_see
if not group_ids:
return None
if cls is TaskIndexEntry:
# noinspection PyUnresolvedReferences
q = q.filter(cls.group_id.in_(group_ids))
else: # a kind of Task
q = q.filter(cls._group_id.in_(group_ids))
return q
def get_structure(self, structure_filter=None):
"""Get structure of scenario by filter.
:param structure_filter: instance of :class:`ScenarioStructureFilter
<autostorage.core.scenario.param_spec.ScenarioStructureFilter>`.
:returns: dictionary with tree_path as keys and node_ids as values.
"""
subquery = Query([
ScenarioStructureStateRecord.scenario_id,
ScenarioStructureStateRecord.tree_path,
func.max(ScenarioStructureStateRecord.changed).label('newest_change_date')
]).filter_by(
scenario_id=self.__id
)
if structure_filter:
if structure_filter.date:
subquery = subquery.filter(
ScenarioStructureStateRecord.changed <= structure_filter.date)
if structure_filter.tree_path:
subquery = subquery.filter(ScenarioStructureStateRecord.tree_path.like(
"{0}-%".format(structure_filter.tree_path)
))
subquery = subquery.group_by(
ScenarioStructureStateRecord.scenario_id,
ScenarioStructureStateRecord.tree_path
).subquery()
states_query = Query([
ScenarioStructureStateRecord
]).join(
subquery,
and_(
ScenarioStructureStateRecord.scenario_id == subquery.columns.scenario_id,
ScenarioStructureStateRecord.tree_path == subquery.columns.tree_path,
ScenarioStructureStateRecord.changed == subquery.columns.newest_change_date
)
).filter(
ScenarioStructureStateRecord.enabled == True # pylint: disable=singleton-comparison
)
with self.base.get_session() as session:
bound_query = states_query.with_session(session)
return {record.tree_path: record.node_id for record in bound_query}
def filter_by_planets(query: Query, planets) -> Query:
"""Add filter state for query that gets resonances."""
body_count = BodyNumberEnum(len(planets) + 1)
for i, key in enumerate(FOREIGNS):
if i >= (body_count.value - 1):
break
query = query.filter(PLANET_TABLES[key].name == planets[i])
return query
async def get_content_by_localcontentid(
localcontentid: int, query: Query = Depends(_get_my_content_query)):
try:
content = query.filter(
ReportContent.localcontentid == localcontentid).one_or_none()
return Contentout(**content.__dict__)
except:
raise HTTPException(status_code=400)
def add_integer_filter(query: Query, ints: List[str],
body_tables: List[AliasedClass]) -> Query:
any_int = '*'
for integer, table in zip(ints, body_tables):
if integer != any_int:
query = query.filter(eval('table.longitude_coeff %s' % integer))
return query
def base_append_only_query(model: Base, query: Query) -> Query:
"""
This query is equal to
SELECT tbl1.* FROM :tablename AS tbl1
LEFT JOIN :tablename AS tbl2 ON
tbl1.uid = tbl2.uid AND tbl2.id > tbl1.id
WHERE
tbl2.id IS NULL AND tbl1.is_deleted = false
:return: SQLAlchemy Query
"""
alias = aliased(model)
query = query.outerjoin(alias,
and_(alias.uid == model.uid, alias.id > model.id))
query = query.filter(alias.id.is_(None))
query = query.filter(model.is_deleted.is_(False))
return query
def filter_query_for_content_label_as_path(
self,
query: Query,
content_label_as_file: str,
is_case_sensitive: bool = False,
) -> Query:
"""
Apply normalised filters to found Content corresponding as given label.
:param query: query to modify
:param content_label_as_file: label in this
FILE version, use Content.get_label_as_file().
:param is_case_sensitive: Take care about case or not
:return: modified query
"""
file_name, file_extension = os.path.splitext(content_label_as_file)
label_filter = Content.label == content_label_as_file
file_name_filter = Content.label == file_name
file_extension_filter = Content.file_extension == file_extension
if not is_case_sensitive:
label_filter = func.lower(Content.label) == \
func.lower(content_label_as_file)
file_name_filter = func.lower(Content.label) == \
func.lower(file_name)
file_extension_filter = func.lower(Content.file_extension) == \
func.lower(file_extension)
return query.filter(or_(
and_(
Content.type == ContentType.File,
file_name_filter,
file_extension_filter,
),
and_(
Content.type == ContentType.Thread,
file_name_filter,
file_extension_filter,
),
and_(
Content.type == ContentType.Page,
file_name_filter,
file_extension_filter,
),
and_(
Content.type == ContentType.Folder,
label_filter,
),
))
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 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 _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
# explict join using relationship
Query(User).join(User.addresses)
Query(User).join('addresses')
Query(User).join(Address, User.addresses)
# In join query, table on the left side of the join is decided
# by the leftmost entity in the Query object's list of
# entities by default. Use select_from() to specify a table.
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
