flask sqlalchemy query with keyword as variable

Question

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.String(140))

To get a list of users from New York, this is my query:

User.query.filter_by(hometown='New York').all()

To get a list of users who go to USC, this is my query:

User.query.filter_by(university='USC').all()

And to get a list of users from New York, and who go to USC, this is my query:

User.query.filter_by(hometown='New York').filter_by(university='USC').all()

Now, I would like to dynamically generate these queries based on the value of a variable.

For example, my variable might look like this:

    {'hometown': 'New York'}

Or like this:

    {'university': 'USC'}

... Or even like this:

    [{'hometown': 'New York'}, {'university': 'USC'}]

Can you help me out with writing a function which takes a dictionary (or list of dictionaries) as an input, and then dynamically builds the correct sqlalchemy query?

If I try to use a variable for the keyword, I get this err:

key = 'university'
User.query.filter_by(key='USC').all()

InvalidRequestError: Entity '<class 'User'>' has no property 'key'

Secondly, I am not sure how to chain multiple filter_by expressions together dynamically.

I can explicitly, call out a filter_by expression, but how do I chain several together based on a variable?

Hope this makes more sense.

Thanks!

Yes, we can help you build that function. What is the problem you're running into when you try to build it yourself? — Mark Hildreth, Oct 21 '13 at 22:48
When trying to assign a variable (example: kv = 'hometown') as a keyword arg, I get this error: nvalidRequestError: Entity '' has no property 'kv' — SeanPlusPlus, Oct 21 '13 at 22:51
Can you show that code as part of the question? As it is, your question seems to show what you know, rather than asking for an explanation for what you are trying to have answered. — Mark Hildreth, Oct 21 '13 at 22:52
I believe you can pass a single dictionary (not a list of dictionaries) directly into `filter_by`. So `dict = {'hometown': 'New York', 'university': 'USC'}` and `User.query.filter_by(dict).all()`. I am not positive on this, however. — Seberius, Oct 21 '13 at 23:09
when i pass a dictionary to filter_by ( example: {'university: 'USC'} ), i get this err: TypeError: filter_by() takes exactly 1 argument (2 given) — SeanPlusPlus, Oct 21 '13 at 23:13

Mark Hildreth · Accepted Answer · 2014-09-02T18:26:11.407

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

@Ricky Unfortunately I haven't used SQLAlchemy in awhile, so I don't know off the top of my head. You might be better off asking a new question. — Mark Hildreth, Apr 27 '16 at 14:09

opyate · Answer 2 · 2015-01-29T14:35:23.470

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)

does this work with nested properties? for example, product.category.name ? if not, is there a way to do it? — Angel Q, Dec 03 '19 at 08:47
Do you mean where a category is foreign-keyed to a product? In that case, no :) There probably is a way to do it, yes. — opyate, Dec 04 '19 at 14:16

score 0 · Answer 3 · answered May 21 '20 at 10:02

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

flask sqlalchemy query with keyword as variable

3 Answers3

Linked