def query(self, entity, limit=None, offset=None, limit_fields=None, order_by=None, desc=False, field_names=[], **kw): query = self.session.query(entity) count = query.count() if order_by is not None: if self.is_relation(entity, order_by): mapper = class_mapper(entity) class_ = None for prop in mapper.iterate_properties: try: class_ = prop.mapper.class_ except (AttributeError, KeyError): pass query = self.session.query(entity).join(order_by) f = self.get_view_field_name(class_, field_names) field = self.get_field(class_, f) else: field = self.get_field(entity, order_by) if desc: field = _desc(field) query = query.order_by(field) if offset is not None: query = query.offset(offset) if limit is not None: query = query.limit(limit) objs = query.all() return count, objs
def gen_datatable(request, db_session, Model, filter_=None, search_columns=None, ret_columns=None, order_by=None, desc=True): draw = int(request.args['draw'][0]) start = int(request.args['start'][0]) length = int(request.args['length'][0]) search_value = request.args.get('search[value]') query = db_session.query(Model) columns = [column.name for column in Model.__table__.columns._all_columns] if filter_: query = filter_(query) records_total = query.count() if records_total == 0: return response.json({ 'data': [], 'draw': draw, 'recordsTotal': records_total, 'recordsFiltered': records_total }) if search_value: if search_columns: search_condition = [ getattr(Model, attribute).like("%{}%".format(search_value)) for attribute in search_columns ] else: search_condition = [ getattr(Model, attribute).like("%{}%".format(search_value)) for attribute in columns ] records_total = query.filter(or_(*search_condition)).count() query = query.filter(or_(*search_condition)) if order_by: query = query.order_by(_desc(getattr( Model, order_by))) if desc else query.order_by( getattr(Model, order_by)) records_filtered = records_total results = query.offset(start).limit(length).all() if ret_columns: ret = [[getattr(result, attribute) for attribute in ret_columns] for result in results] else: ret = [[getattr(result, attribute) for attribute in columns] for result in results] return response.json({ 'data': ret, 'draw': draw, 'recordsTotal': records_total, 'recordsFiltered': records_filtered })
def get_all(cls, order_by='id', start=0, limit=10, desc=False): ''' :param order_by: One of ``'id'``, ``'live_count'`` or ``'updated_time'`` ''' query = session.query(User) order_by = getattr(User, order_by) if desc: order_by = _desc(order_by) users = query.order_by(order_by).offset(start).limit(limit).all() return [user.to_dict() for user in users]
def query(self, entity, limit=None, offset=None, limit_fields=None, order_by=None, desc=False, field_names=[], filters={}, substring_filters=[], **kw): entity = resolve_entity(entity) query = self.session.query(entity) filters = self._modify_params_for_dates(entity, filters) filters = self._modify_params_for_relationships(entity, filters) for field_name, value in filters.items(): field = getattr(entity, field_name) if self.is_relation(entity, field_name) and isinstance(value, list): value = value[0] query = query.filter(field.contains(value)) elif field_name in substring_filters and self.is_string(entity, field_name): escaped_value = re.sub('[\\\\%\\[\\]_]', '\\\\\g<0>', value.lower()) query = query.filter(func.lower(field).contains(escaped_value, escape='\\')) else: query = query.filter(field==value) count = query.count() if order_by is not None: if self.is_relation(entity, order_by): mapper = class_mapper(entity) class_ = None for prop in mapper.iterate_properties: try: class_ = prop.mapper.class_ except (AttributeError, KeyError): pass query = self.session.query(entity).join(order_by) f = self.get_view_field_name(class_, field_names) field = self.get_field(class_, f) else: field = self.get_field(entity, order_by) if desc: field = _desc(field) query = query.order_by(field) if offset is not None: query = query.offset(offset) if limit is not None: query = query.limit(limit) objs = query.all() return count, objs
def query(self, entity, limit=None, offset=None, limit_fields=None, order_by=None, desc=False, **kw): query = self.session.query(entity) count = query.count() if order_by is not None: field = self.get_field(entity, order_by) if desc: field = _desc(field) query = query.order_by(field) if offset is not None: query = query.offset(offset) if limit is not None: query = query.limit(limit) objs = query.all() return count, objs
def query(self, entity, limit=None, offset=None, limit_fields=None, order_by=None, desc=False, field_names=[], filters={}, substring_filters=[], **kw): '''Perform database query with given filters Based on the original SAORMProvider with query_modifier and some subtle enhancements (fail-safe modify_params, filter parsing) ''' query = self.session.query(entity) if self.query_modifier: query = self.query_modifier(query) # Process filters from url exc = False try: filters = self._modify_params_for_dates(entity, filters) except ValueError as e: log.info('Could not parse date filters', exc_info=True) # flash('Could not parse date filters: %s.' % e.message, 'error') exc = True try: filters = self._modify_params_for_relationships(entity, filters) except (ValueError, AttributeError) as e: log.info('Could not parse relationship filters', exc_info=True) # flash('Could not parse relationship filters: %s. ' # 'You can only filter by the IDs of relationships, not by their names.' % e.message, 'error') exc = True if exc: # Since any non-parsed filter is bad, we just have to ignore them all now filters = {} for field_name, value in filters.iteritems(): try: field = getattr(entity, field_name) if self.is_relation(entity, field_name) and isinstance(value, list): # pragma: no cover value = value[0] query = query.filter(field.contains(value)) else: # query = query.filter(field == value) typ = self.get_field(entity, field_name).type if isinstance(typ, Integer): value = int(value) query = query.filter(field == value) elif isinstance(typ, Numeric): # pragma: no cover value = float(value) query = query.filter(field == value) elif field_name in substring_filters and self.is_string(entity, field_name): # escaped_value = re.sub('[\\\\%\\[\\]_]', '\\\\\g<0>', value.lower()) # query = query.filter(func.lower(field).contains(escaped_value, escape='\\')) query = query.filter(func.lower(field).contains(value.lower())) else: query = query.filter(field == value) except: log.warn('Could not create filter on query', exc_info=True) # Get total count count = query.count() # Process ordering if order_by is not None: # pragma: no cover if self.is_relation(entity, order_by): mapper = class_mapper(entity) class_ = None for prop in mapper.iterate_properties: try: class_ = prop.mapper.class_ except (AttributeError, KeyError): pass query = self.session.query(entity).join(order_by) f = self.get_view_field_name(class_, field_names) field = self.get_field(class_, f) else: field = self.get_field(entity, order_by) if desc: field = _desc(field) query = query.order_by(field) # Process pager options if offset is not None: # pragma: no cover query = query.offset(offset) if limit is not None: # pragma: no cover query = query.limit(limit) return count, query
def query(self, entity, limit=None, offset=None, limit_fields=None, order_by=None, desc=False, field_names=[], filters={}, substring_filters=[], **kw): # Based on the original SAORMProvider with query_modifier and # some subtle enhancements (fail-safe modify_params, filter parsing) query = self.session.query(entity) if self.query_modifier: query = self.query_modifier(query) # Process filters from url exc = False try: filters = self._modify_params_for_dates(entity, filters) except ValueError as e: log.info('Could not parse date filters', exc_info=True) # flash('Could not parse date filters: %s.' % e.message, 'error') exc = True try: filters = self._modify_params_for_relationships(entity, filters) except (ValueError, AttributeError) as e: log.info('Could not parse relationship filters', exc_info=True) # flash('Could not parse relationship filters: %s. ' # 'You can only filter by the IDs of relationships, not by their names.' % e.message, 'error') exc = True if exc: # Since any non-parsed filter is bad, we just have to ignore them all now filters = {} for field_name, value in filters.iteritems(): try: field = getattr(entity, field_name) if self.is_relation(entity, field_name) and isinstance(value, list): value = value[0] query = query.filter(field.contains(value)) else: # query = query.filter(field == value) typ = self.get_field(entity, field_name).type if isinstance(typ, Integer): value = int(value) query = query.filter(field == value) elif isinstance(typ, Numeric): value = float(value) query = query.filter(field == value) elif field_name in substring_filters and self.is_string(entity, field_name): escaped_value = re.sub('[\\\\%\\[\\]_]', '\\\\\g<0>', value.lower()) query = query.filter(func.lower(field).contains(escaped_value, escape='\\')) else: query = query.filter(field == value) except: log.warn('Could not create filter on query', exc_info=True) # Get total count count = query.count() # Process ordering if order_by is not None: if self.is_relation(entity, order_by): mapper = class_mapper(entity) class_ = None for prop in mapper.iterate_properties: try: class_ = prop.mapper.class_ except (AttributeError, KeyError): pass query = self.session.query(entity).join(order_by) f = self.get_view_field_name(class_, field_names) field = self.get_field(class_, f) else: field = self.get_field(entity, order_by) if desc: field = _desc(field) query = query.order_by(field) # Process pager options if offset is not None: query = query.offset(offset) if limit is not None: query = query.limit(limit) return count, query
def query(self, entity, limit=None, offset=None, limit_fields=None, order_by=None, desc=False, filters={}, substring_filters=[], search_related=False, related_field_names=None, **kw): entity = resolve_entity(entity) query = self.session.query(entity) filters = self._modify_params_for_dates(entity, filters) if search_related: # Values for related fields contain the text to search filters = self._modify_params_for_related_searches(entity, filters, view_names=related_field_names, substrings=substring_filters) else: # Values for related fields contain the primary key filters = self._modify_params_for_relationships(entity, filters) for field_name, value in filters.items(): field = getattr(entity, field_name) if self.is_relation(entity, field_name) and isinstance(value, list): related_class = self._get_related_class(entity, field_name) related_pk = self.get_primary_field(related_class) related_pk_col = getattr(related_class, related_pk) related_pk_values = (getattr(v, related_pk) for v in value) if self._relates_many(entity, field_name): field_filter = field.any(related_pk_col.in_(related_pk_values)) else: field_filter = field.has(related_pk_col.in_(related_pk_values)) query = query.filter(field_filter) elif field_name in substring_filters and self.is_string(entity, field_name): escaped_value = self._escape_like(value.lower()) query = query.filter(func.lower(field).contains(escaped_value, escape='*')) else: query = query.filter(field==value) count = query.count() if order_by is not None: if not isinstance(order_by, (tuple, list)): order_by = [order_by] if not isinstance(desc, (tuple, list)): desc = [desc] for sort_by, sort_descending in zip_longest(order_by, desc): if self.is_relation(entity, sort_by): mapper = class_mapper(entity) class_ = self._get_related_class(entity, sort_by) query = query.outerjoin(sort_by) f = self.get_view_field_name(class_, related_field_names) field = self.get_field(class_, f) else: field = self.get_field(entity, sort_by) if sort_descending: field = _desc(field) query = query.order_by(field) if offset is not None: query = query.offset(offset) if limit is not None: query = query.limit(limit) objs = query.all() return count, objs
def query(self, entity, limit=None, offset=None, limit_fields=None, order_by=None, desc=False, filters={}, substring_filters=[], search_related=False, related_field_names=None, **kw): entity = resolve_entity(entity) query = self.session.query(entity) filters = self._modify_params_for_dates(entity, filters) if search_related: # Values for related fields contain the text to search filters = self._modify_params_for_related_searches( entity, filters, view_names=related_field_names, substrings=substring_filters) else: # Values for related fields contain the primary key filters = self._modify_params_for_relationships(entity, filters) for field_name, value in filters.items(): field = getattr(entity, field_name) if self.is_relation(entity, field_name) and isinstance( value, list): related_class = self._get_related_class(entity, field_name) related_pk = self.get_primary_field(related_class) related_pk_col = getattr(related_class, related_pk) related_pk_values = (getattr(v, related_pk) for v in value) if self._relates_many(entity, field_name): field_filter = field.any( related_pk_col.in_(related_pk_values)) else: field_filter = field.has( related_pk_col.in_(related_pk_values)) query = query.filter(field_filter) elif field_name in substring_filters and self.is_string( entity, field_name): escaped_value = self._escape_like(value.lower()) query = query.filter( func.lower(field).contains(escaped_value, escape='*')) else: query = query.filter(field == value) count = query.count() if order_by is not None: if not isinstance(order_by, (tuple, list)): order_by = [order_by] if not isinstance(desc, (tuple, list)): desc = [desc] for sort_by, sort_descending in zip_longest(order_by, desc): if self.is_relation(entity, sort_by): mapper = class_mapper(entity) class_ = self._get_related_class(entity, sort_by) query = query.outerjoin(sort_by) f = self.get_view_field_name(class_, related_field_names) field = self.get_field(class_, f) else: field = self.get_field(entity, sort_by) if sort_descending: field = _desc(field) query = query.order_by(field) if offset is not None: query = query.offset(offset) if limit is not None: query = query.limit(limit) objs = query.all() return count, objs
def query(self, entity, limit=None, offset=None, limit_fields=None, order_by=None, desc=False, field_names=[], filters={}, substring_filters=[], **kw): entity = resolve_entity(entity) query = self.session.query(entity) filters = self._modify_params_for_dates(entity, filters) filters = self._modify_params_for_relationships(entity, filters) for field_name, value in filters.items(): field = getattr(entity, field_name) if self.is_relation(entity, field_name) and isinstance( value, list): value = value[0] query = query.filter(field.contains(value)) elif field_name in substring_filters and self.is_string( entity, field_name): escaped_value = re.sub('[\\\\%\\[\\]_]', '\\\\\g<0>', value.lower()) query = query.filter( func.lower(field).contains(escaped_value, escape='\\')) else: query = query.filter(field == value) count = query.count() if order_by is not None: if self.is_relation(entity, order_by): mapper = class_mapper(entity) class_ = None for prop in mapper.iterate_properties: try: class_ = prop.mapper.class_ except (AttributeError, KeyError): pass query = self.session.query(entity).join(order_by) f = self.get_view_field_name(class_, field_names) field = self.get_field(class_, f) else: field = self.get_field(entity, order_by) if desc: field = _desc(field) query = query.order_by(field) if offset is not None: query = query.offset(offset) if limit is not None: query = query.limit(limit) objs = query.all() return count, objs