JinjaSQL

0.1.8 · maintenance · verified Mon Apr 13

JinjaSQL is a Python library (version 0.1.8) that enables the generation of SQL queries and their corresponding bind parameters using Jinja2 templates. It leverages Jinja2's powerful templating features (conditionals, loops, macros) while automatically binding parameters to mitigate common SQL injection vulnerabilities for templated values. It prepares the query and parameters, leaving actual execution to the database driver. While its latest release was in May 2020, this release included a critical bug fix, suggesting it is in a maintenance state rather than active development.

Warnings

breaking A critical bug in versions prior to 0.1.8 could lead to SQL injection if SQL templates used string concatenation or other Python operators directly. Users are strongly advised to upgrade to 0.1.8 or later.
Fix: Upgrade to JinjaSQL version 0.1.8 or higher.
breaking JinjaSQL versions are incompatible with Jinja2 versions 3.1.0 and higher due to internal changes in Jinja2's `Markup` and `escape` classes. Importing JinjaSQL with Jinja2 >= 3.1.0 will likely result in an `ImportError`.
Fix: Downgrade Jinja2 to a version below 3.1.0 (e.g., `pip install 'Jinja2<3.1.0'`). A fix for JinjaSQL itself is not yet available in the latest release.
gotcha When binding a list or tuple to create an SQL `IN` clause (e.g., `WHERE id IN (...)`), you must explicitly apply the `|inclause` filter to the variable in the template. Failure to do so will result in a `MissingInClauseException`.
Fix: Use `{{ my_list_variable | inclause }}` in your SQL template instead of `{{ my_list_variable }}`.
gotcha To insert dynamic table names, column names, or other SQL identifiers (which cannot be bound as parameters), the `|sqlsafe` filter must be used. However, using `|sqlsafe` bypasses automatic parameter binding, making the developer responsible for preventing SQL injection in such cases.
Fix: For dynamic SQL identifiers, use `{{ column_name | sqlsafe }}`. Always sanitize inputs passed to `|sqlsafe` to prevent injection.
gotcha The return type for bind parameters from `j.prepare_query()` depends on the `param_style` used. For `named` or `pyformat` styles, it returns a dictionary. For `format`, `qmark`, or `numeric` styles, it returns a list. Ensure your code handles both possibilities or explicitly sets a `param_style`.
Fix: Check the `param_style` or the type of the returned `bind_params` to process correctly, or initialize `JinjaSql(param_style='...')` explicitly to ensure a consistent return type.
deprecated As of v0.1.3, JinjaSQL now utilizes Jinja2's autoescape feature, making the output of macros automatically SQL safe. This means manually applying the `|sqlsafe` filter to macro outputs is no longer necessary and is considered deprecated for this specific use case.
Fix: Remove redundant `|sqlsafe` filters from macro outputs if your JinjaSQL version is 0.1.3 or higher.

Install

pip install jinjasql Install JinjaSQL

Imports

JinjaSql
```
from jinjasql import JinjaSql
```

Quickstart

This quickstart demonstrates how to initialize JinjaSQL, define a Jinja2 template for an SQL query, and prepare the query along with its bind parameters using a context dictionary. It shows how conditional logic in the template affects the final query and parameters. The prepared query and parameters can then be passed to any database driver for execution.

from jinjasql import JinjaSql

j = JinjaSql()

template = """
SELECT username, sum(spend)
FROM transactions
WHERE start_date > {{ request.start_date }}
  AND end_date < {{ request.end_date }}
{% if request.organization %}
  AND organization = {{ request.organization }}
{% endif %}
"""

data = {
    "request": {
        "start_date": "2026-01-01",
        "end_date": "2026-03-31",
        "organization": "ExampleOrg"
    }
}

query, bind_params = j.prepare_query(template, data)

print("Generated Query:", query)
print("Bind Parameters:", bind_params)

# Example with missing organization
data_no_org = {
    "request": {
        "start_date": "2026-01-01",
        "end_date": "2026-03-31"
    }
}
query_no_org, bind_params_no_org = j.prepare_query(template, data_no_org)

print("\nGenerated Query (no organization):", query_no_org)
print("Bind Parameters (no organization):", bind_params_no_org)

view raw JSON →