Query builder for Python applications. Designed to work with MUI-QueryBuilder.

python version downloads

Py-QueryBuilder is available as a pypi package.

pip install py-querybuilder

Here is a quick example to get you started. First you'll need a templates folder in your module, containing Jinja2 templates with a {{ where }} placeholder:

SELECT *
FROM my_table
WHERE {{ where }}

You can now rely on the QueryBuilder class to render queries:

from py_querybuilder import QueryBuilder
qb = QueryBuilder("app.articles", [
 {
 "label": "Article",
 "options": [
 {
 "label": "Title",
 "value": "title",
 "type": "text",
 },
 {
 "label": "URL",
 "value": "url",
 "type": "text",
 },
 ],
 },
])
sql_query, sql_params = qb.render("query.sql", {
 "combinator": "and",
 "rules": [
 {
 "field": "title",
 "operator": "contains",
 "value": "Brazil",
 },
 ],
})

The query is generated with JinjaSQL, a template language for SQL statements and scripts. Since it's based in Jinja2, you have all the power it offers: conditional statements, macros, looping constructs, blocks, inheritance, and more. We use sqlparse to format the queries.

SELECT *
FROM my_table
WHERE title ~* ? AS "Title"

The default operators used for generating queries are:

{
 "after": ">",
 "after_equal": ">=",
 "before": "<",
 "before_equal": "<=",
 "contains": "~*",
 "greater": ">",
 "greater_equal": ">=",
 "equal": "=",
 "in": "in",
 "less": "<",
 "less_equal": "<=",
 "not_contains": "!~*",
 "not_equal": "!=",
 "not_in": "not in",
 "not_null": "is not null",
 "null": "is null",
}

In case the database you're targeting uses different operators, it's possible to customize those at QueryBuilder's instantiation:

qb = QueryBuilder(my_module, my_query, operators={
 # Custom operators.
})

SQL parameters are returned from render() in a list of values corresponding to the placeholders that need to be bound to the query.

print(sql_params)
# ["Brazil"]

Finally, you can now use the SQL query and its bind parameters to fetch data. For example, in Django, you would do something like this:

from django.db import connection
with connection.cursor() as cursor:
 cursor.execute(sql_query, sql_params)
 for row in cursor.fetchall():
 # Do something with the results.
 pass

Check out the API documentation. To learn more about the query's data structure, and how filters and operators work, please refer to MUI-QueryBuilder.

This project is licensed under the terms of the MIT license.