flask sqlalchemy query with keyword as variable

python sql sqlalchemy flask flask-sqlalchemy
SeanPlusPlus picture SeanPlusPlus · Oct 22, 2013 · Viewed 19.4k times · Source

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

Mark Hildreth picture Mark Hildreth · Oct 22, 2013

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

Related questions

How to execute raw SQL in Flask-SQLAlchemy app

How do you execute raw SQL in SQLAlchemy? I have a python web app that runs on flask and interfaces to the database through SQLAlchemy. I need a way to run the raw SQL. The query involves multiple table joins …

Read More
SqlAlchemy and Flask, how to query many-to-many relationship

I need help creating SqlAlchemy query. I'm doing a Flask project where I'm using SqlAlchemy. I have created 3 tables: Restaurant, Dish and restaurant_dish in my models.py file. restaurant_dish = db.Table('restaurant_dish', db.Column('dish_id', db.…

Read More
sqlalchemy: how to join several tables by one query?

I have the following SQLAlchemy mapped classes: class User(Base): __tablename__ = 'users' email = Column(String, primary_key=True) name = Column(String) class Document(Base): __tablename__ = "documents" name = Column(String, primary_key=True) author = Column(String, ForeignKey("users.email")) class DocumentsPermissions(…

Read More