labpack/databases/sql.py
def list(self, filter=None, sort=None, limit=100, cursor=None, ids_only=False):

        '''
            a method to retrieve records from table which match query criteria

        :param filter: dictionary of dot path field name and jsonmodel query criteria
        :param sort: list of single key-pair dictionaries with dot path field names
        :param limit: integer with number of results to return
        :param cursor: string form of integer with offset to continue query
        :param ids_only: boolean to enable return of only ids (reduces 'read' use to 1)
        :return: list of results

        filter:
        {
            'path.to.field': 'equal to value',
            'path.to.number': {
                'min_value': 4.5
            },
            'path.to.string': {
                'discrete_values': [ 'pond', 'lake', 'stream', 'brook' ]
            }
        }

        NOTE:   sql only supports a limited number of query conditions and all list
                fields in a record are stored as a blob. this method constructs a
                sql query which contains clauses wherever the query conditions can
                be translated one-to-one into sql keywords and returns the entire
                record of each qualifying record. once sql returns its results, the
                remaining query conditions are applied to the record and only those
                results which match all conditions are yield by the generator. as
                such, depending upon the conditions selected, this method acts more
                or less like a SCAN of the entire database. if no sql supported
                conditions are provided, the method will look through all records.

                native SQL supported conditions

                float, integer & strings:
                    value_exists
                    equal_to
                    discrete_values
                    excluded_values
                    greater_than
                    less_than
                    max_value
                    min_value

                booleans:
                    value_exists
                    equal_to

                lists:
                    value_exists

        NOTE:   the full list of all filter criteria supported by post-processing
                are found in the reference page for the jsonmodel module as well 
                as the query-rules.json file included in the module. 
                http://collectiveacuity.github.io/jsonModel/reference/#query-criteria

        an example of how to construct the sort argument:

        sort = [
            { 'path.to.number': 'descend' }, 
            { 'path.to.string': '' }
        ]

        NOTE:   results can be ordered either by ascending or descending values. to
                order in ascending order, leave the value for the field empty. a
                declared value automatically is interpreted as descending order.
        
        output:
        [ { 'id': '...', 'email': '...', ... }, { ... } ], cursor_string

        output (if ids_only):
        [ 'abc', 'xyz', 'ijk', ... ], cursor_string
        '''

        # import dependencies
        from sqlalchemy import desc as order_desc
        
        title = '%s.list' % self.__class__.__name__

        # validate inputs
        args = {
            'filter': filter,
            'sort': sort,
            'limit': limit
        }
        for key, value in args.items():
            if value:
                titlex = '%s(%s=%s)' % (title, key, str(value))
                self.fields.validate(value, '.%s' % key, titlex)

        # validate cursor
        if cursor:
            titlex = '%s(cursor=%s)' % (title, str(cursor))
            try:
                cursor = int(cursor)
            except:
                raise ValueError('%s must be an integer or a string datatype that converts to an integer.' % titlex)
            self.fields.validate(cursor, '.cursor', titlex)

        # validate filter
        if filter:
            self.model.query(filter)
        else:
            filter = {}

        # validate sort
        if sort:
            for i in range(len(sort)):
                criterion = sort[i]
                msg = '%s(sort=[...]) item %s' % (title, i)
                for key, value in criterion.items():
                    if key.find('.') == 0:
                        raise ValueError('%s key %s must not begin with "."' % (msg, key))
                    criteria_key = '.%s' % key
                    if criteria_key not in self.model.keyMap.keys():
                        raise ValueError('%s key %s does not exist in record_schema.' % (msg, key))
        else:
            sort = []

        # construct select statement with sql supported conditions
        # https://docs.sqlalchemy.org/en/13/core/tutorial.html
        # http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#common-filter-operators
        select_object = self.table.select()
        for key, value in filter.items():
            record_key = key
            map_key = key
            if key.find('.') == 0:
                record_key = key[1:]
            else:
                map_key = '.%s' % key
            if record_key:
                if self.item_key.findall(record_key):
                    pass
                else:
                    test_value = value
                    if not isinstance(value, dict):
                        test_value = {'equal_to': value}
                    column_object = getattr(self.table.c, record_key)
                    for k, v in test_value.items():
                        if k == 'value_exists':
                            if self.model.keyMap[map_key]['value_datatype'] in ('string', 'number', 'boolean', 'list'):
                                if v:
                                    select_object = select_object.where(column_object != None)
                                else:
                                    select_object = select_object.where(column_object == None)
                        else:
                            if self.model.keyMap[map_key]['value_datatype'] in ('string', 'number', 'boolean'):
                                if k == 'equal_to':
                                    select_object = select_object.where(column_object == v)
                                elif k == 'discrete_values':
                                    select_object = select_object.where(column_object.in_(v))
                                elif k == 'excluded_values':
                                    select_object = select_object.where(~column_object.in_(v))
                                elif k == 'greater_than':
                                    select_object = select_object.where(column_object.__gt__(v))
                                elif k == 'less_than':
                                    select_object = select_object.where(column_object.__lt__(v))
                                elif k == 'max_value':
                                    select_object = select_object.where(column_object.__le__(v))
                                elif k == 'min_value':
                                    select_object = select_object.where(column_object.__ge__(v))

        # add order criteria
        for criterion in reversed(sort):
            key, value = next(iter(criterion.items()))
            record_key = key
            if key.find('.') == 0:
                record_key = key[1:]
            if record_key:
                if self.item_key.findall(record_key):
                    pass
                else:
                    column_object = getattr(self.table.c, record_key)
                    if value:
                        select_object = select_object.order_by(order_desc(column_object))
                    else:
                        select_object = select_object.order_by(column_object)

        # apply results and cursor offset
        select_object = select_object.limit(limit)
        if cursor:
            select_object = select_object.offset(cursor)

        # execute query on database
        records = []
        next_cursor = 0
        while True:
            count = 0
            for record in self.session.execute(select_object).fetchall():
                record_details = self._reconstruct_record(record)
    
                # filter results with non-sql supported conditions
                if filter:
                    if self.model.query(filter, record_details):
                        if ids_only:
                            records.append(record_details['id'])
                        else:
                            records.append(record_details)
                elif ids_only:
                    records.append(record_details['id'])
                else:
                    records.append(record_details)
                count += 1
                # return if total records equals limit
                if len(records) >= limit:
                    next_cursor = next_cursor + count
                    return records, str(next_cursor)

            # return if all records have been examined
            if count == 0 or count < limit:
                next_cursor = ''
                return records, next_cursor
            # else update offset and repeat
            next_cursor = next_cursor + count
            select_object.offset(next_cursor)

            a method to retrieve records from table which match query criteria

        :param filter: dictionary of dot path field name and jsonmodel query criteria
        :param sort: list of single key-pair dictionaries with dot path field names
        :param limit: integer with number of results to return
        :param cursor: string form of integer with offset to continue query
        :param ids_only: boolean to enable return of only ids (reduces 'read' use to 1)
        :return: list of results

        filter:
        {
            'path.to.field': 'equal to value',
            'path.to.number': {
                'min_value': 4.5
            },
            'path.to.string': {
                'discrete_values': [ 'pond', 'lake', 'stream', 'brook' ]
            }
        }

        NOTE:   sql only supports a limited number of query conditions and all list
                fields in a record are stored as a blob. this method constructs a
                sql query which contains clauses wherever the query conditions can
                be translated one-to-one into sql keywords and returns the entire
                record of each qualifying record. once sql returns its results, the
                remaining query conditions are applied to the record and only those
                results which match all conditions are yield by the generator. as
                such, depending upon the conditions selected, this method acts more
                or less like a SCAN of the entire database. if no sql supported
                conditions are provided, the method will look through all records.

                native SQL supported conditions

                float, integer & strings:
                    value_exists
                    equal_to
                    discrete_values
                    excluded_values
                    greater_than
                    less_than
                    max_value
                    min_value

                booleans:
                    value_exists
                    equal_to

                lists:
                    value_exists

        NOTE:   the full list of all filter criteria supported by post-processing
                are found in the reference page for the jsonmodel module as well 
                as the query-rules.json file included in the module. 
                http://collectiveacuity.github.io/jsonModel/reference/#query-criteria

        an example of how to construct the sort argument:

        sort = [
            { 'path.to.number': 'descend' }, 
            { 'path.to.string': '' }
        ]

        NOTE:   results can be ordered either by ascending or descending values. to
                order in ascending order, leave the value for the field empty. a
                declared value automatically is interpreted as descending order.
        
        output:
        [ { 'id': '...', 'email': '...', ... }, { ... } ], cursor_string

        output (if ids_only):
        [ 'abc', 'xyz', 'ijk', ... ], cursor_string
        
a method to retrieve records from table which match query criteria
a method to retrieve records from table which match query criteria
def list(self, filter=None, sort=None, limit=100, cursor=None, ids_only=False):
        from sqlalchemy import desc as order_desc
        title = '%s.list' % self.__class__.__name__
        args = {
            'filter': filter,
            'sort': sort,
            'limit': limit
        }
        for key, value in args.items():
            if value:
                titlex = '%s(%s=%s)' % (title, key, str(value))
                self.fields.validate(value, '.%s' % key, titlex)
        if cursor:
            titlex = '%s(cursor=%s)' % (title, str(cursor))
            try:
                cursor = int(cursor)
            except:
                raise ValueError('%s must be an integer or a string datatype that converts to an integer.' % titlex)
            self.fields.validate(cursor, '.cursor', titlex)
        if filter:
            self.model.query(filter)
        else:
            filter = {}
        if sort:
            for i in range(len(sort)):
                criterion = sort[i]
                msg = '%s(sort=[...]) item %s' % (title, i)
                for key, value in criterion.items():
                    if key.find('.') == 0:
                        raise ValueError('%s key %s must not begin with "."' % (msg, key))
                    criteria_key = '.%s' % key
                    if criteria_key not in self.model.keyMap.keys():
                        raise ValueError('%s key %s does not exist in record_schema.' % (msg, key))
        else:
            sort = []
        select_object = self.table.select()
        for key, value in filter.items():
            record_key = key
            map_key = key
            if key.find('.') == 0:
                record_key = key[1:]
            else:
                map_key = '.%s' % key
            if record_key:
                if self.item_key.findall(record_key):
                    pass
                else:
                    test_value = value
                    if not isinstance(value, dict):
                        test_value = {'equal_to': value}
                    column_object = getattr(self.table.c, record_key)
                    for k, v in test_value.items():
                        if k == 'value_exists':
                            if self.model.keyMap[map_key]['value_datatype'] in ('string', 'number', 'boolean', 'list'):
                                if v:
                                    select_object = select_object.where(column_object != None)
                                else:
                                    select_object = select_object.where(column_object == None)
                        else:
                            if self.model.keyMap[map_key]['value_datatype'] in ('string', 'number', 'boolean'):
                                if k == 'equal_to':
                                    select_object = select_object.where(column_object == v)
                                elif k == 'discrete_values':
                                    select_object = select_object.where(column_object.in_(v))
                                elif k == 'excluded_values':
                                    select_object = select_object.where(~column_object.in_(v))
                                elif k == 'greater_than':
                                    select_object = select_object.where(column_object.__gt__(v))
                                elif k == 'less_than':
                                    select_object = select_object.where(column_object.__lt__(v))
                                elif k == 'max_value':
                                    select_object = select_object.where(column_object.__le__(v))
                                elif k == 'min_value':
                                    select_object = select_object.where(column_object.__ge__(v))
        for criterion in reversed(sort):
            key, value = next(iter(criterion.items()))
            record_key = key
            if key.find('.') == 0:
                record_key = key[1:]
            if record_key:
                if self.item_key.findall(record_key):
                    pass
                else:
                    column_object = getattr(self.table.c, record_key)
                    if value:
                        select_object = select_object.order_by(order_desc(column_object))
                    else:
                        select_object = select_object.order_by(column_object)
        select_object = select_object.limit(limit)
        if cursor:
            select_object = select_object.offset(cursor)
        records = []
        next_cursor = 0
        while True:
            count = 0
            for record in self.session.execute(select_object).fetchall():
                record_details = self._reconstruct_record(record)
                if filter:
                    if self.model.query(filter, record_details):
                        if ids_only:
                            records.append(record_details['id'])
                        else:
                            records.append(record_details)
                elif ids_only:
                    records.append(record_details['id'])
                else:
                    records.append(record_details)
                count += 1
                if len(records) >= limit:
                    next_cursor = next_cursor + count
                    return records, str(next_cursor)
            if count == 0 or count < limit:
                next_cursor = ''
                return records, next_cursor
            next_cursor = next_cursor + count
            select_object.offset(next_cursor)
def list ( self , filter = None , sort = None , limit = 100 , cursor = None , ids_only = False ) : from sqlalchemy import desc as order_desc title = '%s.list' % self . __class__ . __name__ args = { 'filter' : filter , 'sort' : sort , 'limit' : limit } for key , value in args . items ( ) : if value : titlex = '%s(%s=%s)' % ( title , key , str ( value ) ) self . fields . validate ( value , '.%s' % key , titlex ) if cursor : titlex = '%s(cursor=%s)' % ( title , str ( cursor ) ) try : cursor = int ( cursor ) except : raise ValueError ( '%s must be an integer or a string datatype that converts to an integer.' % titlex ) self . fields . validate ( cursor , '.cursor' , titlex ) if filter : self . model . query ( filter ) else : filter = { } if sort : for i in range ( len ( sort ) ) : criterion = sort [ i ] msg = '%s(sort=[...]) item %s' % ( title , i ) for key , value in criterion . items ( ) : if key . find ( '.' ) == 0 : raise ValueError ( '%s key %s must not begin with "."' % ( msg , key ) ) criteria_key = '.%s' % key if criteria_key not in self . model . keyMap . keys ( ) : raise ValueError ( '%s key %s does not exist in record_schema.' % ( msg , key ) ) else : sort = [ ] select_object = self . table . select ( ) for key , value in filter . items ( ) : record_key = key map_key = key if key . find ( '.' ) == 0 : record_key = key [ 1 : ] else : map_key = '.%s' % key if record_key : if self . item_key . findall ( record_key ) : pass else : test_value = value if not isinstance ( value , dict ) : test_value = { 'equal_to' : value } column_object = getattr ( self . table . c , record_key ) for k , v in test_value . items ( ) : if k == 'value_exists' : if self . model . keyMap [ map_key ] [ 'value_datatype' ] in ( 'string' , 'number' , 'boolean' , 'list' ) : if v : select_object = select_object . where ( column_object != None ) else : select_object = select_object . where ( column_object == None ) else : if self . model . keyMap [ map_key ] [ 'value_datatype' ] in ( 'string' , 'number' , 'boolean' ) : if k == 'equal_to' : select_object = select_object . where ( column_object == v ) elif k == 'discrete_values' : select_object = select_object . where ( column_object . in_ ( v ) ) elif k == 'excluded_values' : select_object = select_object . where ( ~ column_object . in_ ( v ) ) elif k == 'greater_than' : select_object = select_object . where ( column_object . __gt__ ( v ) ) elif k == 'less_than' : select_object = select_object . where ( column_object . __lt__ ( v ) ) elif k == 'max_value' : select_object = select_object . where ( column_object . __le__ ( v ) ) elif k == 'min_value' : select_object = select_object . where ( column_object . __ge__ ( v ) ) for criterion in reversed ( sort ) : key , value = next ( iter ( criterion . items ( ) ) ) record_key = key if key . find ( '.' ) == 0 : record_key = key [ 1 : ] if record_key : if self . item_key . findall ( record_key ) : pass else : column_object = getattr ( self . table . c , record_key ) if value : select_object = select_object . order_by ( order_desc ( column_object ) ) else : select_object = select_object . order_by ( column_object ) select_object = select_object . limit ( limit ) if cursor : select_object = select_object . offset ( cursor ) records = [ ] next_cursor = 0 while True : count = 0 for record in self . session . execute ( select_object ) . fetchall ( ) : record_details = self . _reconstruct_record ( record ) if filter : if self . model . query ( filter , record_details ) : if ids_only : records . append ( record_details [ 'id' ] ) else : records . append ( record_details ) elif ids_only : records . append ( record_details [ 'id' ] ) else : records . append ( record_details ) count += 1 if len ( records ) >= limit : next_cursor = next_cursor + count return records , str ( next_cursor ) if count == 0 or count < limit : next_cursor = '' return records , next_cursor next_cursor = next_cursor + count select_object . offset ( next_cursor )
a method to retrieve records from table which match query criteria
a method to retrieve records from table which match query criteria
'''
            a method to retrieve records from table which match query criteria

        :param filter: dictionary of dot path field name and jsonmodel query criteria
        :param sort: list of single key-pair dictionaries with dot path field names
        :param limit: integer with number of results to return
        :param cursor: string form of integer with offset to continue query
        :param ids_only: boolean to enable return of only ids (reduces 'read' use to 1)
        :return: list of results

        filter:
        {
            'path.to.field': 'equal to value',
            'path.to.number': {
                'min_value': 4.5
            },
            'path.to.string': {
                'discrete_values': [ 'pond', 'lake', 'stream', 'brook' ]
            }
        }

        NOTE:   sql only supports a limited number of query conditions and all list
                fields in a record are stored as a blob. this method constructs a
                sql query which contains clauses wherever the query conditions can
                be translated one-to-one into sql keywords and returns the entire
                record of each qualifying record. once sql returns its results, the
                remaining query conditions are applied to the record and only those
                results which match all conditions are yield by the generator. as
                such, depending upon the conditions selected, this method acts more
                or less like a SCAN of the entire database. if no sql supported
                conditions are provided, the method will look through all records.

                native SQL supported conditions

                float, integer & strings:
                    value_exists
                    equal_to
                    discrete_values
                    excluded_values
                    greater_than
                    less_than
                    max_value
                    min_value

                booleans:
                    value_exists
                    equal_to

                lists:
                    value_exists

        NOTE:   the full list of all filter criteria supported by post-processing
                are found in the reference page for the jsonmodel module as well 
                as the query-rules.json file included in the module. 
                http://collectiveacuity.github.io/jsonModel/reference/#query-criteria

        an example of how to construct the sort argument:

        sort = [
            { 'path.to.number': 'descend' }, 
            { 'path.to.string': '' }
        ]

        NOTE:   results can be ordered either by ascending or descending values. to
                order in ascending order, leave the value for the field empty. a
                declared value automatically is interpreted as descending order.
        
        output:
        [ { 'id': '...', 'email': '...', ... }, { ... } ], cursor_string

        output (if ids_only):
        [ 'abc', 'xyz', 'ijk', ... ], cursor_string
        ''' # import dependencies # validate inputs # validate cursor # validate filter # validate sort # construct select statement with sql supported conditions # https://docs.sqlalchemy.org/en/13/core/tutorial.html # http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#common-filter-operators # add order criteria # apply results and cursor offset # execute query on database # filter results with non-sql supported conditions # return if total records equals limit # return if all records have been examined # else update offset and repeat
list of results
filter:
{
'path.to.field': 'equal to value',
'path.to.number': {
'min_value': 4.5
},
'path.to.string': {
'discrete_values': [ 'pond', 'lake', 'stream', 'brook' ]
}
}

sql only supports a limited number of query conditions and all list
fields in a record are stored as a blob. this method constructs a
sql query which contains clauses wherever the query conditions can
be translated one-to-one into sql keywords and returns the entire
record of each qualifying record. once sql returns its results, the
remaining query conditions are applied to the record and only those
results which match all conditions are yield by the generator. as
such, depending upon the conditions selected, this method acts more
or less like a SCAN of the entire database. if no sql supported
conditions are provided, the method will look through all records.

native SQL supported conditions

float, integer & strings:
value_exists
equal_to
discrete_values
excluded_values
greater_than
less_than
max_value
min_value





the full list of all filter criteria supported by post-processing
are found in the reference page for the jsonmodel module as well
as the query-rules.json file included in the module.

an example of how to construct the sort argument.



results can be ordered either by ascending or descending values. to
order in ascending order, leave the value for the field empty. a
declared value automatically is interpreted as descending order.




list of results filter : { ' path . to . field ' : ' equal to value ' ' path . to . number ' : { ' min_value ' : 4 . 5 } ' path . to . string ' : { ' discrete_values ' : [ ' pond ' ' lake ' ' stream ' ' brook ' ] } } sql only supports a limited number of query conditions and all list fields in a record are stored as a blob . this method constructs a sql query which contains clauses wherever the query conditions can be translated one - to - one into sql keywords and returns the entire record of each qualifying record . once sql returns its results the remaining query conditions are applied to the record and only those results which match all conditions are yield by the generator . as such depending upon the conditions selected this method acts more or less like a SCAN of the entire database . if no sql supported conditions are provided the method will look through all records . native SQL supported conditions float integer & strings : value_exists equal_to discrete_values excluded_values greater_than less_than max_value min_value the full list of all filter criteria supported by post - processing are found in the reference page for the jsonmodel module as well as the query - rules . json file included in the module . an example of how to construct the sort argument . results can be ordered either by ascending or descending values . to order in ascending order leave the value for the field empty . a declared value automatically is interpreted as descending order .
self
filter
dictionary of dot path field name and jsonmodel query criteria
dictionary of dot path field name and jsonmodel query criteria
sort
list of single key-pair dictionaries with dot path field names
list of single key - pair dictionaries with dot path field names
limit
integer with number of results to return
integer with number of results to return
cursor
string form of integer with offset to continue query
string form of integer with offset to continue query
ids_only
boolean to enable return of only ids (reduces 'read' use to 1)
boolean to enable return of only ids ( reduces ' read ' use to 1 )