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