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)

flask sqlalchemy query with keyword as variable

Related

Recent Posts