CODEWITHSUNDEEP
pythonflaskflask-sqlalchemy
Jeff Bluemel
Jeff Bluemel

Reputation: 486

simplifying dynamic sqlalchemy iterating request.args

I am having trouble putting all these pieces together. I am getting values from a form POST the name/id in the form are the actual column names in my database table.

The idea here is to filter cascading select2 inputs to create a dynamic filter. For example my current 3 select names are field, name. If I select I field I only want to provide only the names in that field. If no field is selected I provide all the names (and so on). Obviously the production model would have more than 2.

I am trying to iterate the request.args in order to get the name and the value, then add to the filter. below is my code.

@bp.route('/wells/name')
def wells_name():
    if request.args:
        qry = Wells.query
        for var in request.args:
            qry = qry.filter(getattr(Wells, var) == request.args['{}'.format(var)])
        query = qry.with_entities(Wells.name).group_by(Wells.name).order_by(Wells.name)
    else:
        query = Wells.query.with_entities(Wells.name).group_by(Wells.name) \
            .order_by(Wells.name)

I am using the following code to get the value of the variable;

request.args['{}'.format(var)]

The following code to represent Wells.variablename (Wells.field or Wells.name in this scenario)

getattr(Wells, var)

and the following example to try and 'put it all together' https://stackoverflow.com/questions/39137911/build-dynamic-filters-in-sqlalchemy-python.

Upvotes: 0

Views: 353

Answers (1)

Jeff Bluemel
Jeff Bluemel

Reputation: 486

OK, this problem was definitely not what I had anticipated. I move the with_entities before the iteration of filtering and it solved the problem. I'm sure there is still room for improvement but it is vastly improved over what I had. Here is the new code.

@bp.route('/wells/name')
def wells_name():
    if request.args:
        query = Wells.query.with_entities(Wells.field, Wells.name) \
            .group_by(Wells.field, Wells.name).order_by(Wells.name)
        for var in request.args:
            query = query.filter(getattr(Wells, var) \
                .ilike('%{}%'.format(request.args['{}'.format(var)])))
    else:
        query = Wells.query.with_entities(Wells.name).group_by(Wells.name) \
            .order_by(Wells.name)

And the old code

@bp.route('/wells/name')
def wells_name():
    if 'field' in request.args and 'name' not in request.args:
        field = request.args['field']
        query = Wells.query.filter(Wells.field.ilike('%{}%'.format(field))) \
            .with_entities(Wells.name).group_by(Wells.name).order_by(Wells.name)
    elif 'field' not in request.args and 'name' in request.args:
        name = request.args['name']
        query = Wells.query.filter(Wells.name.ilike('%{}%'.format(name))) \
            .with_entities(Wells.name).group_by(Wells.name).order_by(Wells.name)
    elif 'field' in request.args and 'name' in request.args:
        field = request.args['field']
        name = request.args['name']
        query = Wells.query.filter(Wells.field.ilike('%{}%'.format(field)), \
            Wells.name.ilike('%{}%'.format(name))) \
            .with_entities(Wells.name).group_by(Wells.name).order_by(Wells.name)
    else:
        query = Wells.query.with_entities(Wells.name).group_by(Wells.name) \
            .order_by(Wells.name)

With more variables obviously the 'old code' would have grown significantly, with even just a 3rd added.

Upvotes: 1

Related Questions