DOCSEARCHbyAlgolia
Submit Docs

JinjaSQL

repository·master·Indexed 21 days ago

https://github.com/sripathikrishnan/jinjasql

A template language for SQL statements leveraging Jinja2 to provide logic such as loops, conditionals, and macros while automatically handling parameter binding to prevent SQL injection. It supports multiple parameter styles including format, qmark, numeric, named, pyformat, and asyncpg, and provides specialized filters like |inclause for lists and |sqlsafe for dynamic identifiers.

Tokens
1K
Snippets
5
Records
6
Agent score
24%

What's inside jinjasql

  1. When to use JinjaSQL vs an ORM

    master

    JinjaSQL is not a replacement for an ORM (like SQLAlchemy or Django). You should use your ORM for standard CRUD operations.

    Use JinjaSQL for:

    • Reporting and Business Intelligence (BI) dashboards.
    • Complex queries requiring heavy aggregation or GROUP BY.
    • Queries involving data from multiple tables that are difficult to express in an ORM.
    • Migration scripts or bulk updates that benefit from Jinja macros.

  2. Basic Usage of JinjaSql

    master

    JinjaSQL is used to prepare SQL queries by evaluating a Jinja template against a context dictionary. It returns a parameterized SQL string and a list (or dictionary) of bind parameters, preventing SQL injection.

    1. Create a JinjaSql instance (it is thread-safe).
    2. Define your SQL template using Jinja syntax.
    3. Provide a context dictionary containing the variables.
    4. Call prepare_query(template, context) to get the query and parameters.
    from jinjasql import JinjaSql

# 1. Initialize
j = JinjaSql()

# 2. Define template
template = """
    SELECT project, timesheet, hours
    FROM timesheet
    WHERE user_id = {{ user_id }}
    {% if project_id %}
    AND project_id = {{ project_id }}
    {% endif %}
"""

# 3. Define context
data = {
    "project_id": 123,
    "user_id": "sripathi"
}

# 4. Prepare query
query, bind_params = j.prepare_query(template, data)

# Resulting query: "... WHERE user_id = %s AND project_id = %s"
# Resulting params: ['sripathi', 123]

  4. Configure multiple parameter styles

    master

    By default, JinjaSQL uses the format style (%s). You can change this by passing the param_style argument to the JinjaSql constructor. This is useful for compatibility with different database drivers (e.g., asyncpg for PostgreSQL).

    Supported Styles:

    • format: ... where name = %s (Default)
    • qmark: where name = ?
    • numeric: where name = :1 and last_name = :2
    • named: where name = :name and last_name = :last_name
    • pyformat: where name = %(name)s and last_name = %(last_name)s
    • asyncpg: where name = $1 and last_name = $2

    Note on Return Types:

    • If param_style is set to named or pyformat, prepare_query returns a dictionary of parameters instead of a list. The dictionary is flat and contains only the keys actually used in the query.
    j = JinjaSql(param_style='named')
query, bind_params = j.prepare_query(template, data)
# bind_params will be a dict

  5. Insert dynamic identifiers with the sqlsafe filter

    master

    By default, JinjaSQL treats all variables as values to be bound as parameters. This prevents you from using variables for dynamic table or column names. To allow dynamic identifiers, use the |sqlsafe filter.

    Warning: Using |sqlsafe bypasses parameter binding. You are responsible for ensuring the input is safe to prevent SQL injection.

    select {{column_names | sqlsafe}} from dual

  6. Handle IN clauses with the inclause filter

    master

    If you attempt to bind a list or tuple directly to a placeholder, JinjaSQL will raise a MissingInClauseException. To handle lists in an IN clause, you must use the |inclause filter. You do not need to wrap the expression in parentheses.

    select 'x' from dual
where project_id in {{ project_ids | inclause }}