JinjaSQL

https://github.com/sripathikrishnan/jinjasql
Admin
JinjaSQL is a template language for SQL statements that uses Jinja2 to generate parameterized...
Tokens:6,509
Snippets:64
Trust Score:8.9
License:MIT
Update:6 months ago
Show doc for...
Tokens:
Raw
### Setup Development Environment

Source: https://github.com/sripathikrishnan/jinjasql/blob/master/README.md

Commands to install dependencies and run tests on an Ubuntu machine.

```bash
sudo apt-get install gcc g++ python3-dev unixodbc unixodbc-dev 
pip install -r requirements.txt
python run_tests
```

--------------------------------

### Install JinjaSQL

Source: https://github.com/sripathikrishnan/jinjasql/blob/master/README.md

Commands for installing the library via PyPI or from the source repository.

```bash
pip install jinjasql
```

```bash
git clone https://github.com/sripathikrishnan/jinjasql
cd jinjasql
sudo python setup.py install
```

--------------------------------

### SQL Template Example

Source: https://github.com/sripathikrishnan/jinjasql/blob/master/README.md

A sample Jinja2 template for a SQL query with conditional clauses.

```sql
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 %}
```

```sql
select username, sum(spend)
from transaction
where start_date > %s
and end_date < %s
and organization = %s
```

--------------------------------

### Configure JinjaSql Parameter Styles

Source: https://context7.com/sripathikrishnan/jinjasql/llms.txt

Examples of initializing JinjaSql with different param_style options to match specific database driver requirements.

```python
j = JinjaSql(param_style='format')
query, params = j.prepare_query(template, data)
```

```python
j = JinjaSql(param_style='qmark')
query, params = j.prepare_query(template, data)
```

```python
j = JinjaSql(param_style='numeric')
query, params = j.prepare_query(template, data)
```

```python
j = JinjaSql(param_style='named')
query, params = j.prepare_query(template, data)
```

```python
j = JinjaSql(param_style='pyformat')
query, params = j.prepare_query(template, data)
```

```python
j = JinjaSql(param_style='asyncpg')
query, params = j.prepare_query(template, data)
```

--------------------------------

### Execute Query in Django

Source: https://github.com/sripathikrishnan/jinjasql/blob/master/README.md

Example of executing the generated query and parameters using the Django database connection.

```python
from django.db import connection
with connection.cursor() as cursor:
    cursor.execute(query, bind_params)
    for row in cursor.fetchall():
        # do something with the results
        pass
```

--------------------------------

### Prepare Query

Source: https://github.com/sripathikrishnan/jinjasql/blob/master/README.md

Generate the parameterized SQL query and the list of bind parameters from the template and context.

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

--------------------------------

### Manage SQL Templates with Includes and Imports

Source: https://context7.com/sripathikrishnan/jinjasql/llms.txt

Use a custom Jinja2 Environment with a DictLoader to organize and reuse SQL fragments via macros and includes.

```python
from jinjasql import JinjaSql
from jinja2 import Environment, DictLoader

# Define reusable SQL fragments
templates = {
    "utils.sql": """
        {% macro print_where(value) -%}
        WHERE dummy_col = {{value}}
        {%- endmacro %}
    """,
    "where_clause.sql": "WHERE project_id = {{request.project_id}}"
}

loader = DictLoader(templates)
env = Environment(loader=loader)
j = JinjaSql(env)

# Using import for macros
source_import = """
    {% import 'utils.sql' as utils %}
    SELECT * FROM dual {{ utils.print_where(100) }}
"""

data = {"request": {"project_id": 123}}
query, bind_params = j.prepare_query(source_import, data)
# query: "SELECT * FROM dual WHERE dummy_col = %s"
# bind_params: [100]

# Using include for SQL fragments
source_include = """
    SELECT * FROM dummy {% include 'where_clause.sql' %}
"""

query, bind_params = j.prepare_query(source_include, data)
# query: "SELECT * FROM dummy WHERE project_id = %s"
# bind_params: [123]
```

--------------------------------

### Initialize JinjaSql Instance

Source: https://context7.com/sripathikrishnan/jinjasql/llms.txt

Create a thread-safe JinjaSql instance with custom parameter styles, Jinja2 environments, or identifier quoting.

```python
from jinjasql import JinjaSql
from jinja2 import Environment, DictLoader

# Basic initialization with default settings (format style: %s placeholders)
j = JinjaSql()

# With custom parameter style for different databases
j_postgres = JinjaSql(param_style='asyncpg')      # $1, $2, $3 placeholders
j_oracle = JinjaSql(param_style='named')          # :name placeholders
j_sqlite = JinjaSql(param_style='qmark')          # ? placeholders
j_numeric = JinjaSql(param_style='numeric')       # :1, :2, :3 placeholders
j_pyformat = JinjaSql(param_style='pyformat')     # %(name)s placeholders

# With custom Jinja2 environment for template loading
loader = DictLoader({
    "utils.sql": """
        {% macro print_where(value) -%}
        WHERE dummy_col = {{value}}
        {%- endmacro %}
    """
})
env = Environment(loader=loader)
j_custom = JinjaSql(env)

# With MySQL-style backtick quoting for identifiers
j_mysql = JinjaSql(identifier_quote_character='`')
```

--------------------------------

### prepare_query() Method

Source: https://context7.com/sripathikrishnan/jinjasql/llms.txt

Generates a parameterized SQL query string and a corresponding list or dictionary of bind parameters from a template and context data.

```APIDOC
## prepare_query()

### Description
Processes a SQL template and a context dictionary to produce a safe, parameterized SQL query and the associated bind parameters.

### Parameters
- **template** (string or Template) - Required - The SQL template string or a pre-compiled Jinja2 Template object.
- **data** (dict) - Required - The context dictionary containing values to be bound to the template.

### Response
- **query_string** (string) - The generated SQL query with placeholders.
- **bind_params** (list or dict) - The parameters to be bound to the query placeholders.

### Request Example
```python
template = "SELECT * FROM users WHERE id = {{ user_id }}"
data = {"user_id": 1}
query, params = j.prepare_query(template, data)
```
```

--------------------------------

### Generate Parameterized SQL with prepare_query

Source: https://context7.com/sripathikrishnan/jinjasql/llms.txt

Convert a template string and context dictionary into a parameterized SQL query and bind parameters.

```python
from jinjasql import JinjaSql
from datetime import date

j = JinjaSql()

# Basic query with simple parameter binding
template = """
    SELECT project, timesheet, hours
    FROM timesheet
    WHERE user_id = {{ user_id }}
    {% if project_id %}
    AND project_id = {{ project_id }}
    {% endif %}
"""

data = {
    "project_id": 123,
    "user_id": "sripathi"
}

query, bind_params = j.prepare_query(template, data)
# query: "SELECT project, timesheet, hours FROM timesheet WHERE user_id = %s AND project_id = %s"
# bind_params: ['sripathi', 123]

# With nested context objects
data_nested = {
    "request": {
        "project": {"id": 123, "name": "Acme Project"},
        "project_id": 123,
        "start_date": date.today(),
    },
    "session": {
        "user_id": "sripathi"
    }
}

template_nested = """
    SELECT project, timesheet, hours
    FROM timesheet
    WHERE project_id = {{request.project_id}}
    AND user_id = {{ session.user_id }}
"""

query, bind_params = j.prepare_query(template_nested, data_nested)
# query: "SELECT project, timesheet, hours FROM timesheet WHERE project_id = %s AND user_id = %s"
# bind_params: [123, 'sripathi']

# Using pre-compiled template for better performance
compiled_template = j.env.from_string(template)
query, bind_params = j.prepare_query(compiled_template, data)
```

--------------------------------

### Initialize JinjaSql

Source: https://github.com/sripathikrishnan/jinjasql/blob/master/README.md

Import the JinjaSql class and instantiate it. The object is thread-safe and suitable for application-wide use.

```python
from jinjasql import JinjaSql
j = JinjaSql()
```

--------------------------------

### Apply Bind Filter Manually

Source: https://github.com/sripathikrishnan/jinjasql/blob/master/README.md

Demonstrates the core bind filter which emits a placeholder and stores the value in a thread-local list.

```python
jinja.prepare_query("select * from user where id = {{userid | bind}}", 
                    {userid: 143})
```

--------------------------------

### Prepare Query with Named Parameters

Source: https://context7.com/sripathikrishnan/jinjasql/llms.txt

Prepares a SQL query using named parameters, suitable for dynamic WHERE clauses with multiple conditions.

```python
from jinjasql import JinjaSql

j_named = JinjaSql(param_style='named')
query, bind_params = j_named.prepare_query(template, data)
```

--------------------------------

### Assert Bind Parameters

Source: https://github.com/sripathikrishnan/jinjasql/blob/master/README.md

Verification of the generated bind parameters and query string.

```python
self.assertEquals(bind_params, [u'sripathi', 123])
self.assertEquals(query.strip(), expected_query.strip())
```

--------------------------------

### Define SQL Template

Source: https://github.com/sripathikrishnan/jinjasql/blob/master/README.md

Create a template string using Jinja2 syntax for dynamic SQL generation.

```python
template = """
    SELECT project, timesheet, hours
    FROM timesheet
    WHERE user_id = {{ user_id }}
    {% if project_id %}
    AND project_id = {{ project_id }}
    {% endif %}
"""
```

--------------------------------

### Define Context Data

Source: https://github.com/sripathikrishnan/jinjasql/blob/master/README.md

Prepare a dictionary containing the variables to be injected into the SQL template.

```python
data = {
    "project_id": 123,
    "user_id": u"sripathi"
}
```

--------------------------------

### Reference Parameter Styles

Source: https://context7.com/sripathikrishnan/jinjasql/llms.txt

JinjaSQL supports various PEP-249 parameter styles to ensure compatibility with different database drivers.

```python
from jinjasql import JinjaSql

template = """
    SELECT * FROM users
    WHERE name = {{ name }}
    AND status IN {{ statuses | inclause }}
"""

data = {"name": "john", "statuses": ["active", "pending"]}
```

--------------------------------

### JinjaSql Class Initialization

Source: https://context7.com/sripathikrishnan/jinjasql/llms.txt

The JinjaSql class is the core component for creating instances. It is thread-safe and supports various parameter styles and custom Jinja2 environments.

```APIDOC
## JinjaSql Class Initialization

### Description
Initializes a new JinjaSql instance. This class is thread-safe and can be reused throughout the application.

### Parameters
- **env** (Jinja2 Environment) - Optional - Custom Jinja2 environment for template loading.
- **param_style** (string) - Optional - Parameter placeholder style (e.g., 'format', 'qmark', 'numeric', 'named', 'pyformat', 'asyncpg').
- **identifier_quote_character** (string) - Optional - Character used for quoting table or column names.

### Request Example
```python
from jinjasql import JinjaSql
j = JinjaSql(param_style='asyncpg', identifier_quote_character='`')
```
```

--------------------------------

### Reusable SQL Components with Jinja2 Macros

Source: https://context7.com/sripathikrishnan/jinjasql/llms.txt

Utilizes Jinja2 macros to create reusable SQL fragments, including conditional logic and function wrappers, for dynamic query generation.

```python
from jinjasql import JinjaSql

j = JinjaSql()

# Optional AND clause macro
template = """
    {% macro OPTIONAL_AND(condition, expression, value) -%}
        {%- if condition -%}AND {{expression | sqlsafe}}{{value}} {%- endif-%}
    {%- endmacro -%}
    SELECT 'x' FROM dual
    WHERE 1=1
    {{ OPTIONAL_AND(request.project_id != -1, "project_id = ", request.project_id) }}
    {{ OPTIONAL_AND(request.department, "department = ", request.department) }}
    AND fixed_column = {{ session.user_id }}
"""

data = {
    "request": {
        "project_id": 123,
        "department": None  # Falsy, so this AND clause is omitted
    },
    "session": {"user_id": "sripathi"}
}

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

```python
# SQL function wrapper macro
template_func = """
    {% macro week(value) -%}
    DATE_TRUNC('week', {{value}})
    {%- endmacro %}
    SELECT * FROM events WHERE created_date > {{ week(request.start_date) }}
"""

data_func = {"request": {"start_date": "2024-01-15"}}

query, bind_params = j.prepare_query(template_func, data_func)
```

--------------------------------

### Verify Generated Query

Source: https://github.com/sripathikrishnan/jinjasql/blob/master/README.md

Expected output format for the generated SQL query.

```python
expected_query = """
    SELECT project, timesheet, hours
    FROM timesheet
    WHERE user_id = %s
    
    AND project_id = %s
"""
```

--------------------------------

### Configure JinjaSQL Parameter Style

Source: https://github.com/sripathikrishnan/jinjasql/blob/master/README.md

Sets the parameter style for query generation. The resulting bind_params structure depends on the chosen style.

```python
j = JinjaSql(param_style='named')
query, bind_params = j.prepare_query(template, data)
```

--------------------------------

### Use SQL Safe Strings

Source: https://github.com/sripathikrishnan/jinjasql/blob/master/README.md

Allows dynamic table or column names by bypassing automatic bind parameter conversion. Use with caution to avoid SQL injection.

```sql
select {{column_names | sqlsafe}} from dual
```

--------------------------------

### Integrate with Database Drivers

Source: https://context7.com/sripathikrishnan/jinjasql/llms.txt

Execute generated queries using various Python database drivers like Django ORM, psycopg2, asyncpg, sqlite3, and PyMySQL.

```python
from jinjasql import JinjaSql

# Prepare a query once
j = JinjaSql()
template = """
    SELECT username, SUM(spend) as total_spend
    FROM transactions
    WHERE start_date > {{ start_date }}
    AND end_date < {{ end_date }}
    {% if organization %}
    AND organization = {{ organization }}
    {% endif %}
    GROUP BY username
"""

data = {
    "start_date": "2024-01-01",
    "end_date": "2024-12-31",
    "organization": 1321
}

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

# Django ORM raw SQL
from django.db import connection
with connection.cursor() as cursor:
    cursor.execute(query, bind_params)
    for row in cursor.fetchall():
        print(row)

# psycopg2 (PostgreSQL)
import psycopg2
conn = psycopg2.connect("dbname=mydb user=user password=pass")
cursor = conn.cursor()
cursor.execute(query, bind_params)
results = cursor.fetchall()

# asyncpg (PostgreSQL async) - use asyncpg param style
j_async = JinjaSql(param_style='asyncpg')
query_async, bind_params_async = j_async.prepare_query(template, data)
# query uses $1, $2, $3 placeholders
import asyncpg
async def fetch_data():
    conn = await asyncpg.connect('postgresql://user:pass@localhost/db')
    rows = await conn.fetch(query_async, *bind_params_async)
    return rows

# SQLite
import sqlite3
j_sqlite = JinjaSql(param_style='qmark')
query_sqlite, bind_params_sqlite = j_sqlite.prepare_query(template, data)
conn = sqlite3.connect('mydb.sqlite')
cursor = conn.cursor()
cursor.execute(query_sqlite, bind_params_sqlite)

# MySQL with PyMySQL
import pymysql
j_mysql = JinjaSql(param_style='format')  # %s is default
conn = pymysql.connect(host='localhost', user='user', password='pass', db='mydb')
cursor = conn.cursor()
cursor.execute(query, bind_params)
```

--------------------------------

### Perform String Concatenation and Set Blocks

Source: https://context7.com/sripathikrishnan/jinjasql/llms.txt

Utilize the tilde operator for string concatenation and set blocks to define reusable SQL fragments within templates.

```python
from jinjasql import JinjaSql

j = JinjaSql()

# String concatenation for LIKE patterns
template = """
    SELECT * FROM users
    WHERE user_name LIKE {{ '%' ~ search_term ~ '%' }}
"""

data = {"search_term": "sripathi"}

query, bind_params = j.prepare_query(template, data)
# query: "SELECT * FROM users WHERE user_name LIKE %s"
# bind_params: ['%sripathi%']

# Using set blocks for dynamic SQL fragments
template_set = """
    {% set columns -%}
    project, timesheet, hours
    {%- endset %}
    SELECT {{ columns | sqlsafe }} FROM dual
"""

query, _ = j.prepare_query(template_set, {})
# query: "SELECT project, timesheet, hours FROM dual"

# Combining multiple techniques
template_complex = """
    {% set date_filter -%}
    DATE(created_at) BETWEEN {{ start_date }} AND {{ end_date }}
    {%- endset %}
    SELECT * FROM orders
    WHERE {{ date_filter }}
    AND status IN {{ statuses | inclause }}
"""

data_complex = {
    "start_date": "2024-01-01",
    "end_date": "2024-12-31",
    "statuses": ["completed", "shipped"]
}

query, bind_params = j.prepare_query(template_complex, data_complex)
# bind_params: ['2024-01-01', '2024-12-31', 'completed', 'shipped']
```

--------------------------------

### Automatic Query Transformation

Source: https://github.com/sripathikrishnan/jinjasql/blob/master/README.md

Shows how JinjaSQL automatically injects the bind filter into variable expressions.

```sql
select * from user where id = {{userid}}
```

```sql
select * from user where id = {{userid | bind}}
```

--------------------------------

### Handle Large IN Clauses Efficiently

Source: https://context7.com/sripathikrishnan/jinjasql/llms.txt

Generates a SQL query with a large number of placeholders for an IN clause efficiently, handling thousands of parameters.

```python
large_data = {"ids": list(range(50000))}
template_large = "SELECT * FROM users WHERE id IN {{ ids | inclause }}"
query, bind_params = j.prepare_query(template_large, large_data)
```

--------------------------------

### Insert Raw SQL Values with sqlsafe Filter

Source: https://context7.com/sripathikrishnan/jinjasql/llms.txt

Uses the `sqlsafe` filter to insert values directly into SQL, bypassing parameter binding. Ensure user input is validated to prevent SQL injection.

```python
from jinjasql import JinjaSql

j = JinjaSql()

# Dynamic column selection
template = "SELECT {{ columns | sqlsafe }} FROM timesheet"
data = {"columns": "project, timesheet, hours"}

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

```python
# Dynamic operators and SQL fragments
template_operator = "SELECT * FROM dual WHERE x {{ operator | sqlsafe }} 1"
data = {"operator": "<"}

query, _ = j.prepare_query(template_operator, data)
```

```python
# Combining sqlsafe with regular parameters
template_mixed = """
    SELECT {{ columns | sqlsafe }} FROM {{ table | sqlsafe }}
    WHERE user_id = {{ user_id }}
    AND status IN {{ statuses | inclause }}
"""
data = {
    "columns": "id, name, email",
    "table": "users",
    "user_id": 123,
    "statuses": ["active", "pending"]
}

query, bind_params = j.prepare_query(template_mixed, data)
```

--------------------------------

### Handle IN Clauses

Source: https://github.com/sripathikrishnan/jinjasql/blob/master/README.md

Applies the inclause filter to lists or tuples to generate the correct number of bind expressions without manual parentheses.

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

--------------------------------

### Handle IN Clauses with inclause Filter

Source: https://context7.com/sripathikrishnan/jinjasql/llms.txt

Use the inclause filter to automatically generate placeholders for list or tuple parameters in SQL IN clauses.

```python
from jinjasql import JinjaSql

j = JinjaSql()

# Basic IN clause with list parameter
template = """
    SELECT * FROM timesheet
    WHERE day IN {{ days | inclause }}
"""

data = {"days": ["mon", "tue", "wed", "thu", "fri"]}

query, bind_params = j.prepare_query(template, data)
# query: "SELECT * FROM timesheet WHERE day IN (%s,%s,%s,%s,%s)"
# bind_params: ['mon', 'tue', 'wed', 'thu', 'fri']

# IN clause with different param styles
j_qmark = JinjaSql(param_style='qmark')
query, _ = j_qmark.prepare_query(template, data)
# query: "SELECT * FROM timesheet WHERE day IN (?,?,?,?,?)"

j_numeric = JinjaSql(param_style='numeric')
query, _ = j_numeric.prepare_query(template, data)
```

--------------------------------

### inclause Filter

Source: https://context7.com/sripathikrishnan/jinjasql/llms.txt

A specialized Jinja2 filter used to handle list or tuple parameters within SQL IN clauses by generating the correct number of placeholders.

```APIDOC
## inclause Filter

### Description
Applies the 'inclause' filter to a list or tuple in a Jinja2 template to automatically generate the required number of placeholders for an SQL IN clause.

### Usage
- **Syntax**: {{ variable | inclause }}

### Request Example
```python
template = "SELECT * FROM items WHERE id IN {{ ids | inclause }}"
data = {"ids": [1, 2, 3]}
query, params = j.prepare_query(template, data)
```
```

--------------------------------

### Safe Table/Column Quoting with identifier Filter

Source: https://context7.com/sripathikrishnan/jinjasql/llms.txt

Safely quotes table and column names using the `identifier` filter, supporting different quoting styles for various databases.

```python
from jinjasql import JinjaSql

# PostgreSQL/Oracle style with double quotes (default)
j = JinjaSql()

template = "SELECT * FROM {{ table_name | identifier }}"

# Simple table name
query, _ = j.prepare_query(template, {"table_name": "users"})

# Schema.table as tuple
query, _ = j.prepare_query(template, {"table_name": ("myschema", "users")})

# Handles special characters by escaping
query, _ = j.prepare_query(template, {"table_name": 'table"with"quotes'})
```

```python
# MySQL style with backticks
j_mysql = JinjaSql(identifier_quote_character='`')

query, _ = j_mysql.prepare_query(template, {"table_name": "users"})

query, _ = j_mysql.prepare_query(template, {"table_name": ("myschema", "users")})

# Escapes backticks in MySQL mode
query, _ = j_mysql.prepare_query(template, {"table_name": "table`name"})
```
Tokens:
Raw