Is there a dynamic query builder for Flask using Sqlalchemy?

user_993690 0
Viewed 9242

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)
flask-sqlalchemy mysql python
3 Answers

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
user_34781140

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
}
user_1349040
Related