def apply_order_by( self, query: Query, order_column: str, order_direction: str, aliases_mapping: Dict[str, AliasedClass] = None, ) -> Query: if order_column != "": # if Model has custom decorator **renders('<COL_NAME>')** # this decorator will add a property to the method named *_col_name* if hasattr(self.obj, order_column): if hasattr(getattr(self.obj, order_column), "_col_name"): order_column = getattr(self._get_attr(order_column), "_col_name") _order_column = self._get_attr(order_column) or order_column if is_column_dotted(order_column): root_relation = get_column_root_relation(order_column) # On MVC we still allow for joins to happen here if not self.is_model_already_joined( query, self.get_related_model(root_relation)): query = self._query_join_relation( query, root_relation, aliases_mapping=aliases_mapping) column_leaf = get_column_leaf(order_column) _alias = self.get_alias_mapping(root_relation, aliases_mapping) _order_column = getattr(_alias, column_leaf) if order_direction == "asc": query = query.order_by(asc(_order_column)) else: query = query.order_by(desc(_order_column)) return query
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 findByFieldsValues(cls,fields,values,session=None,onlyOne=True,notNoneFields=None,orderByFields=None,ascending=True,operators=None,groupByFields=None,selectFieldsAndFunctions=None,nestedOperators=None,returnDataframe=False,distinct=False,printQuery=False): if operators is None: operators = [SQLOperator.equalOperator] * 1000 close = False if session is None: session = cls.database.Session() close = True o = None if selectFieldsAndFunctions is None: o = session.query(cls) else: o = Query(selectFieldsAndFunctions, session=session) for field,value,operator in zip(fields,values,operators): operator.field = field operator.value = value o = operator.filter(o) if nestedOperators is not None: for operator in nestedOperators: o = operator.filter(o) if notNoneFields is not None: for f in notNoneFields: o = o.filter(f != None) if groupByFields is not None: for f in groupByFields: o = o.group_by(f) if orderByFields is not None: for f in orderByFields: if ascending: o = o.order_by(f) else: o = o.order_by(f.desc()) if printQuery: print(o) if onlyOne: o = o.first() else: if distinct: o = o.distinct() if returnDataframe: o = cls.queryToDataframe(o) else: o = o.all() if close: session.close() return o
def _order_by_offer_name_containing_keyword_string(keywords_string: str, query: Query) -> Query: offer_alias = aliased(Offer) return query.order_by( desc( Offer.query .filter(Offer.id == offer_alias.id) .filter(Offer.name.op('@@')(func.plainto_tsquery(keywords_string))) .order_by(offer_alias.name) .exists() ) )
def sort(self, query: Query): params = self.params if 'sort_by' in params: sort_by = params['sort_by'] sort_column = getattr(self.model, sort_by, None) else: sort_column = self.get_default_sort_column() if sort_column is not None: return query.order_by(*sort_column) else: return query
def apply_engine_specific_hack( self, query: Query, page: Optional[int], page_size: Optional[int], order_column: Optional[str], ) -> Query: # MSSQL exception page/limit must have an order by if (page and page_size and not order_column and self.session.bind.dialect.name == "mssql"): pk_name = self.get_pk_name() return query.order_by(pk_name) return query
def paginator(self, query: Query, options: PaginatorOptions): if options is None: return query if options.sort_column: column = getattr(self.model, options.sort_column) if \ options.sort_column in self.model.__dict__ \ else options.sort_column criterion = column if not options.sort_descending else desc(column) query = query.order_by(criterion) if options.page_size: query = query. \ offset(options.page_size * options.page_number). \ limit(options.page_size) return query
def apply_sorting( query: Query, order_by: str, to_replace: Optional[Dict[str, str]] = None, allowed_attrs: Optional[Container[str]] = None, ) -> Query: """Apply sorting to query""" lstriped_orderby = order_by.lstrip('-') if allowed_attrs and lstriped_orderby not in allowed_attrs: raise BadRequest( detail=f"Ordering with '{lstriped_orderby}' is disallowed or " f"the attribute does not exist on the model") if to_replace: lstriped_orderby = to_replace.get(lstriped_orderby, lstriped_orderby) if order_by[0] == "-": order_by = f"{lstriped_orderby} desc" else: order_by = f"{lstriped_orderby} asc" return query.order_by(text(order_by))
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 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 order(self, order_expression): return SaQuery.order_by(self, order_expression)
def __desc(query: Query, column: Column) -> Query: new_query = query.order_by(desc(column)) return new_query