Flask Sqlalchemy Query With Keyword As Variable

Let's say I have a model like this: class User(db.Model): id = db.Column(db.Integer, primary_key=True) hometown = db.Column(db.String(140)) university = db.Column(db.St

Solution 1:

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')

Solution 2:

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:
        ifhasattr(MyModel, key):
            vals = request.args.getlist(key) # one or many
            builder = builder.filter(getattr(MyModel, key).in_(vals))
    ifnot'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)

Solution 3:

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:

defget_list(**filters):
    page = Noneif'page'in filters:
        page = filters.pop('limit')
    items = Price.query.filter_by(**filters)
    if page isnotNone:
        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