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
– Mark Hildreth, Commented Oct 21, 2013 at 22:48
When trying to assign a variable (example: kv = 'hometown') as a keyword arg, I get this error: nvalidRequestError: Entity '<class 'User'>' has no property 'kv' — SeanPlusPlus
– SeanPlusPlus, Commented Oct 21, 2013 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
– Mark Hildreth, Commented Oct 21, 2013 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
– Seberius, Commented Oct 21, 2013 at 23:09

Mark Hildreth · Accepted Answer · 2014-09-02 18:26:11Z

59

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

edited Sep 2, 2014 at 18:26

answered Oct 21, 2013 at 23:12

Mark Hildreth

43.3k12 gold badges125 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ricky Levi Over a year ago

how do you implement "in_" by doing this ?

Mark Hildreth Over a year ago

@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.

opyate · Accepted Answer · 2015-01-29 14:35:23Z

3

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)

edited Jan 29, 2015 at 14:35

answered Jan 2, 2015 at 12:44

opyate

5,4581 gold badge40 silver badges66 bronze badges

2 Comments

Angel Q Over a year ago

does this work with nested properties? for example, product.category.name ? if not, is there a way to do it?

opyate Over a year ago

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.

reinhardt · Accepted Answer · 2020-05-21 10:02:44Z

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

Collectives™ on Stack Overflow

flask sqlalchemy query with keyword as variable

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related