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!

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