labpack/databases/sql.py
def list(self, query_criteria=None, order_criteria=None):
        
        '''
            a generator method to list records in table which match query criteria
            
        :param query_criteria: dictionary with schema dot-path field names and query qualifiers
        :param order_criteria: list of single keypair dictionaries with field names to order by 
        :return: generator object with string of primary key
        
        an example of how to construct the query_criteria argument:

        query_criteria = {
            '.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 criteria 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 order_criteria argument:
        
        order_criteria = [
            { '.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. any value
                for the field key automatically is interpreted as descending order
        
        '''
        
        title = '%s.list' % self.__class__.__name__
        
        from sqlalchemy import desc as order_desc
        
    # validate inputs
        if query_criteria:
            self.model.query(query_criteria)
        else:
            query_criteria = {}
        if order_criteria:
            object_title = '%s(%s=%s)' % (title, 'order_criteria', str(order_criteria))
            self.fields.validate(order_criteria, '.order_criteria', object_title)
            for i in range(len(order_criteria)):
                criterion = order_criteria[i]
                for key, value in criterion.items():
                    criteria_key = key
                    if key.find('.') != 0:
                        criteria_key = '.%s' % key
                    if criteria_key not in self.model.keyMap.keys():
                        raise ValueError('%s(order_criteria=[...]) item %s key %s does not exist in record_schema.' % (title, i, key))
        else:
            order_criteria = []

    # construct select statement with sql supported conditions
    # http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#common-filter-operators
        select_object = self.table.select()
        for key, value in query_criteria.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 order_criteria:
            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)

    # execute query on database
        # print(select_object)
        for record in self.session.execute(select_object).fetchall():
            record_details = self._reconstruct_record(record)

        # filter results with non-sql supported conditions
            if query_criteria:
                if self.model.query(query_criteria, record_details):
                    yield record_details
            else:
                yield record_details

            a generator method to list records in table which match query criteria
            
        :param query_criteria: dictionary with schema dot-path field names and query qualifiers
        :param order_criteria: list of single keypair dictionaries with field names to order by 
        :return: generator object with string of primary key
        
        an example of how to construct the query_criteria argument:

        query_criteria = {
            '.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 criteria 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 order_criteria argument:
        
        order_criteria = [
            { '.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. any value
                for the field key automatically is interpreted as descending order
        
        
a generator method to list records in table which match query criteria
a generator method to list records in table which match query criteria
def list(self, query_criteria=None, order_criteria=None):
        title = '%s.list' % self.__class__.__name__
        from sqlalchemy import desc as order_desc
        if query_criteria:
            self.model.query(query_criteria)
        else:
            query_criteria = {}
        if order_criteria:
            object_title = '%s(%s=%s)' % (title, 'order_criteria', str(order_criteria))
            self.fields.validate(order_criteria, '.order_criteria', object_title)
            for i in range(len(order_criteria)):
                criterion = order_criteria[i]
                for key, value in criterion.items():
                    criteria_key = key
                    if key.find('.') != 0:
                        criteria_key = '.%s' % key
                    if criteria_key not in self.model.keyMap.keys():
                        raise ValueError('%s(order_criteria=[...]) item %s key %s does not exist in record_schema.' % (title, i, key))
        else:
            order_criteria = []
        select_object = self.table.select()
        for key, value in query_criteria.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 order_criteria:
            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)
        for record in self.session.execute(select_object).fetchall():
            record_details = self._reconstruct_record(record)
            if query_criteria:
                if self.model.query(query_criteria, record_details):
                    yield record_details
            else:
                yield record_details
def list ( self , query_criteria = None , order_criteria = None ) : title = '%s.list' % self . __class__ . __name__ from sqlalchemy import desc as order_desc if query_criteria : self . model . query ( query_criteria ) else : query_criteria = { } if order_criteria : object_title = '%s(%s=%s)' % ( title , 'order_criteria' , str ( order_criteria ) ) self . fields . validate ( order_criteria , '.order_criteria' , object_title ) for i in range ( len ( order_criteria ) ) : criterion = order_criteria [ i ] for key , value in criterion . items ( ) : criteria_key = key if key . find ( '.' ) != 0 : criteria_key = '.%s' % key if criteria_key not in self . model . keyMap . keys ( ) : raise ValueError ( '%s(order_criteria=[...]) item %s key %s does not exist in record_schema.' % ( title , i , key ) ) else : order_criteria = [ ] select_object = self . table . select ( ) for key , value in query_criteria . 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 order_criteria : 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 ) for record in self . session . execute ( select_object ) . fetchall ( ) : record_details = self . _reconstruct_record ( record ) if query_criteria : if self . model . query ( query_criteria , record_details ) : yield record_details else : yield record_details
a generator method to list records in table which match query criteria
a generator method to list records in table which match query criteria
'''
            a generator method to list records in table which match query criteria
            
        :param query_criteria: dictionary with schema dot-path field names and query qualifiers
        :param order_criteria: list of single keypair dictionaries with field names to order by 
        :return: generator object with string of primary key
        
        an example of how to construct the query_criteria argument:

        query_criteria = {
            '.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 criteria 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 order_criteria argument:
        
        order_criteria = [
            { '.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. any value
                for the field key automatically is interpreted as descending order
        
        ''' # validate inputs # construct select statement with sql supported conditions # http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#common-filter-operators # add order criteria # execute query on database # print(select_object) # filter results with non-sql supported conditions
generator object with string of primary key
an example of how to construct the query_criteria argument.



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 criteria 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 order_criteria argument.



results can be ordered either by ascending or descending values. to
order in ascending order, leave the value for the field empty. any value
for the field key automatically is interpreted as descending order
generator object with string of primary key an example of how to construct the query_criteria argument . 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 criteria 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 order_criteria argument . results can be ordered either by ascending or descending values . to order in ascending order leave the value for the field empty . any value for the field key automatically is interpreted as descending order
self
query_criteria
dictionary with schema dot-path field names and query qualifiers
dictionary with schema dot - path field names and query qualifiers
order_criteria
list of single keypair dictionaries with field names to order by
list of single keypair dictionaries with field names to order by