Error thrown with multiple parameters in the API

[Paul Stasiuk]

Get an error when we send multiple parameters to the API

[30/Jul/2013 15:38:11] "GET /analysis/data/query/json?users=dtreering&parameters=Chloride&start_date=1999-08-18&end_date=2001-06-18&agencies=MWRD&parameters_text=None HTTP/1.1" 200 23848
getting options for Chloride
getting options for Cyanide
[30/Jul/2013 15:39:06] "GET /analysis/data/parameters/query/options?parameters=Chloride|Cyanide HTTP/1.1" 200 23613
executing the get_data_json method
{'users': u'dtreering', 'end_date': u'2001-06-18', 'parameters_text': u'None', 'agencies': u'MWRD', 'parameters': u'Chloride|Cyanide', 'start_date': u'1999-08-18'}
SELECT * FROM cacw_all_data WHERE (date between '1999-08-18' AND '2001-06-18') AND  OR systematicname='Chloride'systematicname='Cyanide'  AND importedby='dtreering' AND name='MWRD'
there was an error when attempting to get all of the data: syntax error at or near "OR"
LINE 1: ...(date between '1999-08-18' AND '2001-06-18') AND  OR systema...
                                                             ^

[Paul Stasiuk]

Offending block of code is in analysistool>db_utils.py lnes 368-375

    start = time.time()

    date_to = lookup_parameters['start_date']
    date_from = lookup_parameters['end_date']

    lookup_statement = "SELECT * FROM cacw_all_data WHERE (date between '%s' AND '%s')"%(date_to,date_from)

    #First we look up the parameters, as long as they is not "All Parameters"
    if lookup_parameters['parameters'] != "All Parameters":
        lookup_statement = lookup_statement + " AND "
        if "|" in lookup_parameters['parameters']:
            parameter_lookup_list = lookup_parameters['parameters'].split('|')
            if len(parameter_lookup_list) == 1:
                appendage = " systematicname='%s' "%(parameter_lookup_list[0])
            else:
                for i in range(0,len(parameter_lookup_list)):
                    if i == (len(parameter_lookup_list)-1):
                        appendage = "systematicname='%s' "%(parameter_lookup_list[i])
                    else:
                        '''
                            CORRECTION BELOW HERE.
                        '''
                        if i==0:
                            appendage = " systematicname='%s'"%(parameter_lookup_list[i])
                        else:
                            appendage = " OR systematicname='%s'"%(parameter_lookup_list[i])
                    lookup_statement = lookup_statement + appendage
        else:
            appendage = "systematicname='%s'"%(lookup_parameters['parameters'])
            lookup_statement = lookup_statement + appendage

BUT that still doesn't build the query correctly:

SELECT * FROM cacw_all_data WHERE (date between '2000-06-07' AND '2001-06-18') AND  systematicname='Fluoride'systematicname='Chloride'  AND importedby='dtreering' AND name='MWRD'

[Paul Stasiuk]

Resolved with commit 13ba61e