Multiple field filtering using pyDAL

Question

EDIT: I think I solved it, I added the answer.

I am writing a REST API using python, Falcon as the web framework and pyDAL as the DAL for MySQL.

I want to filter(where statement) using the fields that I get in the query string of the get request.

For example I receive the the following get request:

http://127.0.0.1:5000/users?firstName=FirstN&id=1

And I want that pyDAL will query generate the following SQL:

SELECT * FROM users WHERE firstName = 'FirstN' AND id = '1'

I could not find something that can do that because pyDAL would like to receive something like:

self.db((self.db.users.id == 1) & (self.db.users.firstName == 'FirstN')).select()

But I can't specify the fields because I don't know which field I am going to filter on, Thats why I wrote this:

def on_get(self, req, resp):
    if req.query_string is not '':
        input = req.query_string
        sql = 'SELECT * FROM users WHERE '
        sql += ' AND '.join(['{col} = \'{value}\''.format(col=item.split('=')[0], value=item.split('=')[1]) for item in input.split('&')])
        resp.body = json.dumps(self.db.executesql(sql, as_dict=True))
    else:
        resp.body = json.dumps(self.db(self.db.users).select().as_dict())

But I think is is awful and should be a better why.

Answer 1

I created a function that receives a Table object and the query string and does:

def generate_filter(table, query_string):
    statement = True
    for field in query_string.split('&'):
        field = field.split('=')
        statement &= getattr(table, field[0]) == field[1]
    return statement

Than I execute:

self.db(generate_filter(self.db.users, req.query_string)).select().as_dict()