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