Is there a dynamic query builder for Flask using Sqlalchemy?

Question

A simple query looks like this

User.query.filter(User.name == 'admin')

In my code, I need to check the parameters that are being passed and then filter the results from the database based on the parameter.

For example, if the User table contains columns like username, location and email, the request parameter can contain either one of them or can have combination of columns. Instead of checking each parameter as shown below and chaining the filter, I'd like to create one dynamic query string which can be passed to one filter and can get the results back. I'd like to create a separate function which will evaluate all parameters and will generate a query string. Once the query string is generated, I can pass that query string object and get the desired result. I want to avoid using RAW SQL query as it defeats the purpose of using ORM.

if location:
    User.query.filter(User.name == 'admin', User.location == location)
elif email:
    User.query.filter(User.email == email)

Ry- · Accepted Answer · 2017-05-11 23:19:24Z

10

You can apply filter to the query repeatedly:

query = User.query

if location:
    query = query.filter(User.location == location)

if email:
    query = query.filter(User.email == email)

If you only need exact matches, there’s also filter_by:

criteria = {}

# If you already have a dict, there are easier ways to get a subset of its keys
if location: criteria['location'] = location
if email: criteria['email'] = email

query = User.query.filter_by(**criteria)

If you don’t like those for some reason, the best I can offer is this:

from sqlalchemy.sql.expression import and_


def get_query(table, lookups, form_data):
    conditions = [
        getattr(table, field_name) == form_data[field_name]
        for field_name in lookups if form_data[field_name]
    ]

    return table.query.filter(and_(*conditions))


get_query(User, ['location', 'email', ...], form_data)

edited May 11, 2017 at 23:19

answered May 11, 2017 at 23:03

Ry-♦

226k56 gold badges496 silver badges504 bronze badges

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

8 Comments

pass-by-ref Over a year ago

that's what I want to avoid, there are a lot of columns on which I need to do filter including the combination of columns. Imagine how many ifs I'll have to use.

Ry- Over a year ago

@ShwetabhSharan: Can you give an example of how “the combination of columns” is an issue? It’s not going to be possible to avoid at least listing all the possible lookup keys, note, unless you want to pass user input directly into an SQLAlchemy function (bad idea).

pass-by-ref Over a year ago

To give you an example, query = User.query if location: query = query.filter(User.location == location) if email: query = query.filter(User.email == email) if location and email: query = query.filter(User.email == email, User.location == location) if username and location: query = query.filter(User.username == username, User.location == location) etc

pass-by-ref Over a year ago

filter_by seems to be the closest as of now. I was thinking if there was something available like we have in Django called Q docs.djangoproject.com/en/1.7/topics/db/queries/…

Ry- Over a year ago

@ShwetabhSharan: You don’t have to do the combinations. Applying a filter to a query doesn’t replace previous filters. There’s an equivalent to Q in that User.location == location is already a condition and you can combine conditions with and_, but I don’t see how that would make anything easier – you still need the same number of ifs.

|

Suyash Soni · Accepted Answer · 2018-06-29 18:38:25Z

2

Late to write an answer but if anyone is looking for the answer then sqlalchemy-json-querybuilder can be useful. It can be installed as -

pip install sqlalchemy-json-querybuilder

e.g.

filter_by = [{
    "field_name": "SomeModel.field1",
    "field_value": "somevalue",
    "operator": "contains"
}]

order_by = ['-SomeModel.field2']

results = Search(session, "pkg.models", (SomeModel,), filter_by=filter_by,order_by=order_by, page=1, per_page=5).results

answered Jun 29, 2018 at 18:38

Suyash Soni

2372 silver badges9 bronze badges

Comments

kolypto · Accepted Answer · 2020-06-15 22:27:48Z

0

https://github.com/kolypto/py-mongosql/

MongoSQL is a query builder that uses JSON as the input. Capable of:

Choosing which columns to load
Loading relationships
Filtering using complex conditions
Ordering
Pagination

Example:

{
  project: ['id', 'name'],  // Only fetch these columns
  sort: ['age+'],  // Sort by age, ascending
  filter: {
    // Filter condition
    sex: 'female',  // Girls
    age: { $gte: 18 },  // Age >= 18
  },
  join: ['user_profile'],  // Load the 'user_profile' relationship
  limit: 100,  // Display 100 per page
  skip: 10,  // Skip first 10 rows
}

answered Jun 15, 2020 at 22:27

kolypto

36.3k20 gold badges118 silver badges110 bronze badges

Comments

ViAchKoN · Accepted Answer · 2024-07-28 20:45:02Z

0

I've written a package which might help to achieve dynamic filtering. It allows to use dicts to dynamically construct queries. The link to a github.

answered Jul 28, 2024 at 20:45

ViAchKoN

8272 silver badges16 bronze badges

Collectives™ on Stack Overflow

Is there a dynamic query builder for Flask using Sqlalchemy?

4 Answers 4

8 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

8 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related