def _filter_multi_query( self, query: Query, filtration: schemas.FilterData) -> tuple[int, list[ModelType]]: for col_name, (filter_type, filter_value) in filtration.data.items(): column = getattr(self.model, col_name) if filter_type == schemas.FilterType.VALUE: query = query.filter(column == filter_value) elif filter_type == schemas.FilterType.DATETIME: start_date, end_date = filter_value if start_date is not None: query = query.filter(column >= filter_value) if end_date is not None: query = query.filter(column <= filter_value) elif filter_type == schemas.FilterType.ENUM: query = query.filter(column in filter_value) query = query.order_by( self.model.created if filtration.sort is None else getattr(self.model, filtration.sort.column ) if filtration.sort.direction != schemas.SortType. ASC else getattr(self.model, filtration.sort.column).desc()) return ( query.count(), query.offset(filtration.offset).limit(filtration.limit).all(), )
def _apply_range_filter(query: Query, key: ClauseElement, value_range: Tuple[T, T]) -> Query: gte_value, lte_value = value_range if gte_value is not None: query = query.filter(key >= gte_value) if lte_value is not None: query = query.filter(key <= lte_value) return query
def base_query(self, query: Query): query = query.join(ValueSet).options( joinedload(Value.valueset).joinedload( ValueSet.references).joinedload(ValueSetReference.source)) if self.language: query = query.join(ValueSet.parameter) if self.language.level == models.LanguoidLevel.group: children = self.language.children children_pks = [child.pk for child in children] filtered = query.filter(ValueSet.language_pk.in_(children_pks)) filtered = filtered.join(ValueSet.language) return filtered return query.filter(ValueSet.language_pk == self.language.pk) if self.parameter: query = query.join(ValueSet.language) query = query.outerjoin(DomainElement).options( joinedload(Value.domainelement)) return query.filter(ValueSet.parameter_pk == self.parameter.pk) if self.contribution: query = query.join(ValueSet.parameter) return query.filter( ValueSet.contribution_pk == self.contribution.pk) query = query.join(ValueSet.language).join(ValueSet.parameter) return query
def generate_query(model, path, filters=None): """ Generate a SELECT query to fetch `model` ids along the path with given `filters` on the last model in `path`. :param model: A :ref:`declarative <sqla:declarative_toplevel>` class. :param path: :param [sqlalchemy.sql.expression.BinaryExpression] filters: :rtype: A :ref:`sqlalchemy.orm.query.Query` object """ query = Query(model.id) if path: # The below is a fix in case the same table is joined # multiple times. In that case, we alias everything except # the last path and then filter on the last path. path_list = path.split(".") last_path = path_list[-1] path_list = path_list[:-1] if path_list: query = query.join(*path_list, aliased=True) query = query.join(last_path, from_joinpoint=True) if filters is not None: if isinstance(filters, list): query = query.filter(*filters) else: query = query.filter(filters) return query
def generate_query(model, path, filters=None): """ Generate a SELECT query to fetch `model` ids along the path with given `filters` on the last model in `path`. :param model: A :ref:`declarative <sqla:declarative_toplevel>` class. :param path: :param [sqlalchemy.sql.expression.BinaryExpression] filters: :rtype: A :ref:`sqlalchemy.orm.query.Query` object """ # We start with the query selecting the ids of the models we want to return. query = Query(model.id) if path: # In case path is not blank, we need to alias the model id while joining # to prevent referencing the same table again. query = (Query(aliased(model).id)) # The below is a fix in case the same table is joined # multiple times. In that case, we alias everything except # the last path and then filter on the last path. path_list = path.split(".") last_path = path_list[-1] path_list = path_list[:-1] if path_list: query = query.join(*path_list, aliased=True) # The last path is purposfully left out from being aliased to make it easier # to contrunct filter conditions. query = query.join(last_path, from_joinpoint=True) if filters is not None: if isinstance(filters, list): query = query.filter(*filters) else: query = query.filter(filters) return query
def apply_filter(query: Query, comparator: ComparisonFunc[common.T], arg: common.Filterable[common.T]) -> Query: if arg is None: return query elif isinstance(arg, Iterable) and not isinstance(arg, str): return query.filter(comparator.__self__.in_(arg)) else: return query.filter(comparator(arg))
def apply(self, query: Query, value: Any) -> Query: user_roles = [role.name.lower() for role in list(get_user_roles())] if "admin" in user_roles: return query datasource_perms = security_manager.user_view_menu_names("datasource_access") schema_perms = security_manager.user_view_menu_names("schema_access") published_dash_query = ( db.session.query(Dashboard.id) .join(Dashboard.slices) .filter( and_( Dashboard.published == True, # pylint: disable=singleton-comparison or_( Slice.perm.in_(datasource_perms), Slice.schema_perm.in_(schema_perms), security_manager.can_access_all_datasources(), ), ) ) ) users_favorite_dash_query = db.session.query(FavStar.obj_id).filter( and_( FavStar.user_id == security_manager.user_model.get_user_id(), FavStar.class_name == "Dashboard", ) ) owner_ids_query = ( db.session.query(Dashboard.id) .join(Dashboard.owners) .filter( security_manager.user_model.id == security_manager.user_model.get_user_id() ) ) if "custom" in user_roles: query = query.filter( and_( Dashboard.id.in_(owner_ids_query), ) ) return query query = query.filter( or_( Dashboard.id.in_(owner_ids_query), Dashboard.id.in_(published_dash_query), Dashboard.id.in_(users_favorite_dash_query), ) ) return query
def apply(self, query: Query, value: Any) -> Query: # If anonymous user filter nothing if security_manager.current_user is None: return query users_favorite_query = db.session.query(FavStar.obj_id).filter( and_(FavStar.user_id == g.user.id, FavStar.class_name == self.class_name) ) if value: return query.filter(and_(self.model.id.in_(users_favorite_query))) return query.filter(and_(~self.model.id.in_(users_favorite_query)))
def element_name(self, zeq, language='en', reference=None): z = self._get_z(zeq) q = Query(ElementNameProperty.name) q = q.filter(ElementNameProperty.language_code == language) q = q.join(Element) q = q.filter(Element.z == z) exception = ValueError('Unknown name for z="{0}", ' 'language="{1}" and ' 'reference="{2}"' .format(z, language, reference)) return self._query_with_references(q, exception, reference)
def apply(self, query: Query, value: bool) -> Query: check_value = '%"certification":%' if value is True: return query.filter(SqlaTable.extra.ilike(check_value)) if value is False: return query.filter( or_( SqlaTable.extra.notlike(check_value), SqlaTable.extra.is_(None), )) return query
def apply(self, query: Query, value: Any) -> Query: if value is True: return query.filter( and_( Dashboard.certified_by.isnot(None), Dashboard.certified_by != "", )) if value is False: return query.filter( or_( Dashboard.certified_by.is_(None), Dashboard.certified_by == "", )) return query
def default_filters(self, query: Query) -> Query: """Apply default filters for Comments resource.. :param query: Base query. :return: Query with additional filters appliet to it. """ request = self.request entity_id = request.matchdict.get('entity_id', '') user = request.user groups = user.groups query = query.filter(self.model.entity_id == entity_id) # External users should not access internal notes if 'g:briefy' not in groups: query = query.filter(self.model.internal.is_(False)) return query
def authorize_query(query: Query, get_oso, get_user, get_action) -> Query: """Authorize an existing query with an oso instance, user and action.""" oso = get_oso() action = get_action() actor = get_user() # TODO (dhatch): This is necessary to allow ``authorize_query`` to work # on queries that have already been made. If a query has a LIMIT or OFFSET # applied, SQLAlchemy will by default throw an error if filters are applied. # This prevents these errors from occuring, but could result in some # incorrect queries. We should remove this if possible. query = query.enable_assertions(False) entities = {column["entity"] for column in query.column_descriptions} for entity in entities: # Only apply authorization to columns that represent a mapper entity. if entity is None: continue authorized_filter = authorize_model_filter(oso, actor, action, query.session, entity) if authorized_filter is not None: query = query.filter(authorized_filter) return query
def _filter_or( query: Query, condition_tuple_list: List[Tuple[str, Column, Any]] ) -> Query: def build_condition(filter_type: str, column: Column, value): if filter_type == "like": if not isinstance(value, list): value = [value] response = [column.like(f"%{single_value}%") for single_value in value] else: if not isinstance(value, list): value = [value] response = [column.in_(value)] return response condition_list = [] for condition_tuple in condition_tuple_list: condition_list.extend( build_condition( filter_type=condition_tuple[0], column=condition_tuple[1], value=condition_tuple[2], ) ) new_query = query.filter(or_(*condition_list)) return new_query
def apply(self, query: Query, value: bool) -> Query: filter_clause = or_(SqlaTable.sql.is_(None), SqlaTable.sql == "") if not value: filter_clause = not_(filter_clause) return query.filter(filter_clause)
def _authorize_query(query: Query) -> Optional[Query]: """Authorize an existing query with an oso instance, user and action.""" # Get the query session. session = query.session # Check whether this is an oso session. if not isinstance(session, AuthorizedSessionBase): # Not an authorized session. return None oso = session.oso_context["oso"] user = session.oso_context["user"] action = session.oso_context["action"] # TODO (dhatch): This is necessary to allow ``authorize_query`` to work # on queries that have already been made. If a query has a LIMIT or OFFSET # applied, SQLAlchemy will by default throw an error if filters are applied. # This prevents these errors from occuring, but could result in some # incorrect queries. We should remove this if possible. query = query.enable_assertions(False) entities = {column["entity"] for column in query.column_descriptions} for entity in entities: # Only apply authorization to columns that represent a mapper entity. if entity is None: continue authorized_filter = authorize_model(oso, user, action, query.session, entity) if authorized_filter is not None: query = query.filter(authorized_filter) return query
def apply(self, query: Query, value: Optional[Any]) -> Query: role_model = security_manager.role_model if value: return query.filter( role_model.name.ilike(f"%{value}%"), ) return query
def filter(cls, info: ResolveInfo, query: Query, filters: 'FilterType') -> Query: """ Return a new query instance with the args ANDed to the existing set. Args: info: GraphQL execution info. query: SQLAlchemy query. filters: Filters dictionary. Returns: Filtered query instance. """ context = info.context if isinstance(context, dict): context[cls._filter_aliases] = {} elif '__dict__' in context.__dir__(): setattr(context, cls._filter_aliases, {}) else: msg = ('Graphene-SQLAlchemy-Filter: ' 'info.context has an unsupported type {}. ' 'Now cls.aliased(info, ...) is not supported. ' 'Allowed types: dict and object with __dict__ attribute.' ).format(type(context)) warnings.warn(msg, RuntimeWarning) query, sqla_filters = cls._translate_many_filter(info, query, filters) if sqla_filters is not None: query = query.filter(*sqla_filters) return query
def _ignore_domains_filter(query: Query) -> Query: """Add a filter to ignore domains we do not fetch history for.""" return query.filter( and_(*[ ~States.entity_id.like(entity_domain) for entity_domain in IGNORE_DOMAINS_ENTITY_ID_LIKE ]))
def get_cboe_sym_ts_from_db(query_start_date, query_end_date, symbol_list=['SPX'], px_types=['ALL']): """ Get timeseries data from mysql :param symbol_list: :param query_start_date: :param query_end_date: :param px_types: Open, High, Low, Close, LastSale :return: """ list_poc_db_col_nm = px_types session = get_db_session() if px_types[0] == 'ALL': list_poc_db_col_nm = ['Id', 'Symbol', 'Open', 'High', 'Low', 'Close', 'LastSale', 'LastTime'] the_columns = [getattr(VixTermStructure, poc_db_col_nm) for poc_db_col_nm in list_poc_db_col_nm] q = Query(the_columns, session=session) else: list_poc_db_col_nm = ['Id', 'Symbol'] + px_types + ['LastSale', 'LastTime'] the_columns = [getattr(VixTermStructure, poc_db_col_nm) for poc_db_col_nm in list_poc_db_col_nm] q = Query(the_columns, session=session) from_db = q.filter(VixTermStructure.Id >= str(query_start_date), VixTermStructure.Id <= str(query_end_date), VixTermStructure.Symbol.in_(symbol_list)).all() df = pd.DataFrame().from_records(from_db) # we are going to need to create a MULTI-INDEX for this returned Dataframe # first level is the DATE (Id) and second level is the Symbol (symbol) # TODO: LEFT OFF HERE ON JUNE 13th, before heading out to pick up Bane with Nada. df.columns = list_poc_db_col_nm df.set_index(['Id', 'Symbol'], inplace=True) return df
def addIfNotThere(self, table, params): ''' Using the PrimaryKeys of the table, it looks for the record in the database. If it is not there, it is inserted as a new entry. :param table: table where to add or modify :type table: str :param params: dictionary of what to add or modify :type params: dict :return: S_OK() || S_ERROR() ''' session = self.sessionMaker_o() table_c = getattr(__import__(__name__, globals(), locals(), [table]), table) primaryKeys = [key.name for key in class_mapper(table_c).primary_key] try: select = Query(table_c, session=session) for columnName, columnValue in params.iteritems(): if not columnValue or columnName not in primaryKeys: continue column_a = getattr(table_c, columnName.lower()) if isinstance(columnValue, (list, tuple)): select = select.filter(column_a.in_(list(columnValue))) elif isinstance(columnValue, basestring): select = select.filter(column_a == columnValue) else: self.log.error("type(columnValue) == %s" % type(columnValue)) res = select.first() # the selection is done via primaryKeys only if not res: # if not there, let's insert it return self.insert(table, params) session.commit() return S_OK() except exc.SQLAlchemyError as e: session.rollback() self.log.exception("addIfNotThere: unexpected exception", lException=e) return S_ERROR("addIfNotThere: unexpected exception %s" % e) finally: session.close()
def element_atomic_weight(self, zeq, reference=None): z = self._get_z(zeq) q = Query(ElementAtomicWeightProperty.value) q = q.join(Element) q = q.filter(Element.z == z) exception = ValueError('Unknown atomic weight for z="{0}" and ' 'reference="{1}"'.format(z, reference)) return self._query_with_references(q, exception, reference)
def apply(self, query: Query, value: Any) -> Query: if security_manager.can_access_all_datasources(): return query perms = security_manager.user_view_menu_names("datasource_access") schema_perms = security_manager.user_view_menu_names("schema_access") return query.filter( or_(self.model.perm.in_(perms), self.model.schema_perm.in_(schema_perms)) )
def element_mass_density_kg_per_m3(self, zeq, reference=None): z = self._get_z(zeq) q = Query(ElementMassDensityProperty.value_kg_per_m3) q = q.join(Element) q = q.filter(Element.z == z) exception = ValueError('Unknown mass density for z="{0}" and ' 'reference="{1}"'.format(z, reference)) return self._query_with_references(q, exception, reference)
def apply(self, query: Query, value: Any) -> Query: return query.filter( or_( Dashboard.created_by_fk # pylint: disable=comparison-with-callable == get_user_id(), Dashboard.changed_by_fk # pylint: disable=comparison-with-callable == get_user_id(), ))
def _windowed_lru_digests(self, q: Query, column: Column) -> Iterable[IndexEntry]: """ Generate a query for each window produced by _column_windows and yield the results one by one. """ for whereclause in self._column_windows(q.session, column): window = q.filter(whereclause).order_by(column.asc()) yield from window
def _apply_event_entity_id_matchers(events_query: Query, entity_ids: Iterable[str]) -> Query: ors = [] for entity_id in entity_ids: like = ENTITY_ID_JSON_TEMPLATE.format(entity_id) ors.append(Events.event_data.like(like)) ors.append(EventData.shared_data.like(like)) return events_query.filter(sqlalchemy.or_(*ors))
def unique_filter(cls, query: Query, name: str) -> Query: """ Filters by unique attribute :param query: query object :param name: unique attribute :return: """ return query.filter(User.name == name)
def apply(self, query: Query, value: Any) -> Query: if not value: return query ilike_value = f"%{value}%" return query.filter( or_( AnnotationLayer.name.ilike(ilike_value), AnnotationLayer.descr.ilike(ilike_value), ))
def apply(self, query: Query, value: Any) -> Query: if not value: return query ilike_value = f"%{value}%" return query.filter( or_( Dashboard.dashboard_title.ilike(ilike_value), Dashboard.slug.ilike(ilike_value), ))
def addIfNotThere( self, table, params ): ''' Using the PrimaryKeys of the table, it looks for the record in the database. If it is not there, it is inserted as a new entry. :param table: table where to add or modify :type table: str :param params: dictionary of what to add or modify :type params: dict :return: S_OK() || S_ERROR() ''' session = self.sessionMaker_o() table_c = getattr(__import__(__name__, globals(), locals(), [table]), table) primaryKeys = [key.name for key in class_mapper(table_c).primary_key] try: select = Query(table_c, session = session) for columnName, columnValue in params.iteritems(): if not columnValue or columnName not in primaryKeys: continue column_a = getattr(table_c, columnName.lower()) if isinstance(columnValue, (list, tuple)): select = select.filter(column_a.in_(list(columnValue))) elif isinstance(columnValue, basestring): select = select.filter(column_a == columnValue) else: self.log.error("type(columnValue) == %s" %type(columnValue)) res = select.first() # the selection is done via primaryKeys only if not res: # if not there, let's insert it return self.insert(table, params) session.commit() return S_OK() except exc.SQLAlchemyError as e: session.rollback() self.log.exception( "addIfNotThere: unexpected exception", lException = e ) return S_ERROR( "addIfNotThere: unexpected exception %s" % e ) finally: session.close()
def element_atomic_number(self, symbol): q = Query(Element.z) q = q.filter(Element.symbol == symbol) exception = \ ValueError('Unknown atomic number for symbol="{0}"'.format(symbol)) return self._query_one(q, exception)
def addOrModify( self, table, params ): """ Using the PrimaryKeys of the table, it looks for the record in the database. If it is there, it is updated, if not, it is inserted as a new entry. :param table: table where to add or modify :type table: str :param params: dictionary of what to add or modify :type params: dict :return: S_OK() || S_ERROR() """ session = self.sessionMaker_o() found = False for ext in self.extensions: try: table_c = getattr(__import__(ext + __name__, globals(), locals(), [table]), table) found = True break except (ImportError, AttributeError): continue # If not found in extensions, import it from DIRAC base (this same module). if not found: table_c = getattr(__import__(__name__, globals(), locals(), [table]), table) columns = [key.name for key in class_mapper(table_c).columns] primaryKeys = [key.name for key in class_mapper(table_c).primary_key] try: select = Query(table_c, session = session) for columnName, columnValue in params.iteritems(): if not columnValue or columnName not in primaryKeys: continue column_a = getattr(table_c, columnName.lower()) if isinstance(columnValue, (list, tuple)): select = select.filter(column_a.in_(list(columnValue))) elif isinstance(columnValue, basestring): select = select.filter(column_a == columnValue) else: self.log.error("type(columnValue) == %s" %type(columnValue)) res = select.first() # the selection is done via primaryKeys only if not res: # if not there, let's insert it return self.insert(table, params) # Treating case of time value updates if 'LastCheckTime' in columns and not params.get('LastCheckTime'): params['LastCheckTime'] = None if 'DateEffective' in columns and not params.get('DateEffective'): params['DateEffective'] = None # now we assume we need to modify for columnName, columnValue in params.iteritems(): if columnName == 'LastCheckTime' and not columnValue: # we always update lastCheckTime columnValue = datetime.datetime.utcnow().replace(microsecond = 0) if columnName == 'DateEffective' and not columnValue: # we always update DateEffective, if there columnValue = datetime.datetime.utcnow().replace(microsecond = 0) if columnValue: setattr(res, columnName.lower(), columnValue) session.commit() return S_OK() except exc.SQLAlchemyError as e: session.rollback() self.log.exception( "addOrModify: unexpected exception", lException = e ) return S_ERROR( "addOrModify: unexpected exception %s" % e ) finally: session.close()
def delete( self, table, params ): """ :param table: table from where to delete :type table: str :param params: dictionary of which line(s) to delete :type params: dict :return: S_OK() || S_ERROR() """ session = self.sessionMaker_o() found = False for ext in self.extensions: try: table_c = getattr(__import__(ext + __name__, globals(), locals(), [table]), table) found = True break except (ImportError, AttributeError): continue # If not found in extensions, import it from DIRAC base (this same module). if not found: table_c = getattr(__import__(__name__, globals(), locals(), [table]), table) # handling query conditions found in 'Meta' older = params.get('Meta', {}).get('older', None) newer = params.get('Meta', {}).get('newer', None) order = params.get('Meta', {}).get('order', None) limit = params.get('Meta', {}).get('limit', None) params.pop('Meta', None) try: deleteQuery = Query(table_c, session = session) for columnName, columnValue in params.iteritems(): if not columnValue: continue column_a = getattr(table_c, columnName.lower()) if isinstance(columnValue, (list, tuple)): deleteQuery = deleteQuery.filter(column_a.in_(list(columnValue))) elif isinstance(columnValue, (basestring, datetime.datetime, bool) ): deleteQuery = deleteQuery.filter(column_a == columnValue) else: self.log.error("type(columnValue) == %s" %type(columnValue)) if older: column_a = getattr(table_c, older[0].lower()) deleteQuery = deleteQuery.filter(column_a < older[1]) if newer: column_a = getattr(table_c, newer[0].lower()) deleteQuery = deleteQuery.filter(column_a > newer[1]) if order: order = [order] if isinstance(order, basestring) else list(order) column_a = getattr(table_c, order[0].lower()) if len(order) == 2 and order[1].lower() == 'desc': deleteQuery = deleteQuery.order_by(desc(column_a)) else: deleteQuery = deleteQuery.order_by(column_a) if limit: deleteQuery = deleteQuery.limit(int(limit)) res = deleteQuery.delete(synchronize_session=False) #FIXME: unsure about it session.commit() return S_OK(res) except exc.SQLAlchemyError as e: session.rollback() self.log.exception( "delete: unexpected exception", lException = e ) return S_ERROR( "delete: unexpected exception %s" % e ) finally: session.close()
def select( self, table, params ): """ Uses params to build conditional SQL statement ( WHERE ... ). :Parameters: **params** - `dict` arguments for the mysql query ( must match table columns ! ). :return: S_OK() || S_ERROR() """ session = self.sessionMaker_o() # finding the table found = False for ext in self.extensions: try: table_c = getattr(__import__(ext + __name__, globals(), locals(), [table]), table) found = True break except (ImportError, AttributeError): continue # If not found in extensions, import it from DIRAC base (this same module). if not found: table_c = getattr(__import__(__name__, globals(), locals(), [table]), table) # handling query conditions found in 'Meta' columnNames = [column.lower() for column in params.get('Meta', {}).get('columns', [])] older = params.get('Meta', {}).get('older', None) newer = params.get('Meta', {}).get('newer', None) order = params.get('Meta', {}).get('order', None) limit = params.get('Meta', {}).get('limit', None) params.pop('Meta', None) try: # setting up the select query if not columnNames: # query on the whole table wholeTable = True columns = table_c.__table__.columns # retrieve the column names columnNames = [str(column).split('.')[1] for column in columns] select = Query(table_c, session = session) else: # query only the selected columns wholeTable = False columns = [getattr(table_c, column) for column in columnNames] select = Query(columns, session = session) # query conditions for columnName, columnValue in params.iteritems(): if not columnValue: continue column_a = getattr(table_c, columnName.lower()) if isinstance(columnValue, (list, tuple)): select = select.filter(column_a.in_(list(columnValue))) elif isinstance(columnValue, (basestring, datetime.datetime, bool) ): select = select.filter(column_a == columnValue) else: self.log.error("type(columnValue) == %s" %type(columnValue)) if older: column_a = getattr(table_c, older[0].lower()) select = select.filter(column_a < older[1]) if newer: column_a = getattr(table_c, newer[0].lower()) select = select.filter(column_a > newer[1]) if order: order = [order] if isinstance(order, basestring) else list(order) column_a = getattr(table_c, order[0].lower()) if len(order) == 2 and order[1].lower() == 'desc': select = select.order_by(desc(column_a)) else: select = select.order_by(column_a) if limit: select = select.limit(int(limit)) # querying selectionRes = select.all() # handling the results if wholeTable: selectionResToList = [res.toList() for res in selectionRes] else: selectionResToList = [[getattr(res, col) for col in columnNames] for res in selectionRes] finalResult = S_OK(selectionResToList) finalResult['Columns'] = columnNames return finalResult except exc.SQLAlchemyError as e: session.rollback() self.log.exception( "select: unexpected exception", lException = e ) return S_ERROR( "select: unexpected exception %s" % e ) finally: session.close()
def _filterX(me, *a, **k): return _saQuery.filter(me, *a, **k)
def element_symbol(self, z): q = Query(Element.symbol) q = q.filter(Element.z == z) exception = ValueError('Unknown symbol for z={0}'.format(z)) return self._query_one(q, exception)