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