def apply_pagination(self, query: Query, page: Optional[int],
page_size: Optional[int]) -> Query:
if page and page_size:
query = query.offset(page * page_size)
if page_size:
query = query.limit(page_size)
return query
def __call__(self, scope: SAQuery) -> List[T]:
total = scope.count()
results = scope.limit(self.page_size).offset(self.offset).all()
meta = ListMeta(
total=total,
total_pages=self.total_pages(total),
page_size=self.page_size,
page=self.page,
next_page=self.next_page(total),
prev_page=self.prev_page,
)
return List(data=results, meta=meta)
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 paginate(self, query: Query):
params = self.params
per_page = params['per_page'] if 'per_page' in params else 25
page = params['page'] if 'page' in params else 1
return query.limit(per_page).offset((page - 1) * per_page), page
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 limit(self, limit):
return Query.limit(self.private(), limit)
def limit(self, limit):
return Query.limit(self.private(), limit)
def after_get(req: Request, resp: Response, item: Query, *args, **kwargs):
req.context['result']['error'] = False
req.context['result']['meta']['total'] = item.limit(None).offset(
None).count()
req.context['result']['meta'][
'offset'] = req.context['result']['meta'].get('offset') or 0
def get_paginated_list(
query: Query, page_number: int, page_size: int
) -> Tuple[List[BaseModel], int]:
total_count = BaseController.get_count(query)
result_list = query.limit(page_size).offset((page_number - 1) * page_size).all()
return result_list, total_count
def paginate(self, query: Query):
params = self.params
per_page = params['per_page'] if 'per_page' in params else 25
page = params['page'] if 'page' in params else 1
return query.limit(per_page).offset((page - 1) * per_page), page
