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)

Hellraiser

Answer 1

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)

Deegan

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. – Hellraiser

@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). – Deegan

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 – Hellraiser

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/… – Hellraiser

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

agreed but the code looks ugly and not concise when using chaining filters. – Hellraiser

Oh _and works fine. I didn't know about that. Thanks a lot, this helps. – Hellraiser

Is there a good way available if I want to do it for raw query using execute – Submersible

Answer 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

Schreck

Answer 3

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
}

Illustration

Answer 4

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.

Pazpaza