Jinja2SQL (Jinja To SQL)

Jinja2SQL is a simple and efficient library for generating SQL queries from Jinja2 templates. It is type-friendly and offers async support, drawing significant inspiration from the excellent library at jinjasql.

---

Documentation

http://jinja2sql.readthedocs.io/

---

Requirements

Python 3.10+ and Jinja2 3.1.2+.

Installation

Install using pip:

pip install jinja2sql

or using poetry:

poetry add jinja2sql

Quick Example

from jinja2sql import Jinja2SQL


j2sql = Jinja2SQL()  # default param_style is "named"

query, params = j2sql.from_string(
    "SELECT * FROM {{ table | identifier }} WHERE email = {{ email }}",
    context={"table": "users", "email": "user@mail.com"},
)

# using with your favorite database driver connection

conn.execute(query, params)

Param Styles

Jinja2SQL supports different param styles depending on the database driver you are using.

You can choose between the following supported param styles:

from jinja2sql import Jinja2SQL


j2sql = Jinja2SQL(param_style="named")  # default

query, params = j2sql.from_string(
    "SELECT * FROM table WHERE param = {{ param }}",
    context={"param": ...},
    param_style="named",  # or "qmark", "numeric", "format", "pyformat", "asyncpg"
)

param_style	Example
named	:param
qmark	?
numeric	:1
format	%s
pyformat	%(param)s
asyncpg	$1

or you can provide a custom function to format your database specific param style:

from jinja2sql import Jinja2SQL


j2sql = Jinja2SQL()

query, params = j2sql.from_string(
    "SELECT * FROM table WHERE column = {{ param }}",
    context={"param": ...},
    param_style=lambda key, _: f"{{{key}}}",
)

assert query == "SELECT * FROM table WHERE column = {email}"