flask sqlalchemy query with keyword as variable

SQLAlchemy's filter_by takes keyword arguments:

filter_by(**kwargs)

In other words, the function will allow you to give it any keyword parameter. This is why you can use any keyword that you want in your code: SQLAlchemy basically sees the arguments a dictionary of values. See the Python tutorial for more information on keyword arguments.

So that allows the developers of SQLAlchemy to receive an arbitrary bunch of keyword arguments in a dictionary form. But you're asking for the opposite: can you pass an arbitrary bunch of keyword arguments to a function?

It turns out that in Python you can, using a feature called unpacking. Simply create the dictionary of arguments and pass it to the function preceded by **, like so:

kwargs = {'hometown': 'New York', 'university' : 'USC'}User.query.filter_by(**kwargs)# This above line is equivalent to saying...User.query.filter_by(hometown='New York', university='USC')

filter_by(**request.args) doesn't work well if you have non-model query parameters, like page for pagination, otherwise you get errors like these:

InvalidRequestError: Entity '<class 'flask_sqlalchemy.MyModelSerializable'>' has no property 'page'

I use something like this which ignores query parameters not in the model:

    builder = MyModel.query    for key in request.args:        if hasattr(MyModel, key):            vals = request.args.getlist(key) # one or many            builder = builder.filter(getattr(MyModel, key).in_(vals))    if not 'page' in request.args:        resources = builder.all()    else:        resources = builder.paginate(            int(request.args['page'])).items

Considering a model with a column called valid, something like this will work:

curl -XGET "http://0.0.0.0/mymodel_endpoint?page=1&valid=2&invalid=whatever&valid=1"

invalid will be ignored, and page is available for pagination and best of all, the following SQL will be generated: WHERE mymodel.valid in (1,2)

(get the above snippet for free if you use this boilerplate-saving module)

As pointed out by @opyate that filter_by(**request.args) doesn't work well if you have non-model query parameters, like page for pagination, the following alternative can be used too:

Assuming that page is being taken in the form of request.args.get(), then:

def get_list(**filters):    page = None    if 'page' in filters:        page = filters.pop('limit')    items = Price.query.filter_by(**filters)    if page is not None:        items = items.paginate(per_page=int(page)).items    else:        items = items.all()    return {        "items": items    }

and then the get function

def get(self):    hometown = request.args.get('hometown')    university = request.args.get('university')    page = request.args.get('page')    return get_list(**request.args)

I have tried implementing this on my flask application, and it works smoothly.

Of course, one drawback that can be is if there are multiple values like page that are not a part of the model, then each of them has to be defined separately in the get_list, but that can be done by list comprehension