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/
Python 3.9+
and Jinja2 3.1.2+
.
Install using pip
:
pip install jinja2sql
or using poetry
:
poetry add jinja2sql
from jinja2sql import Jinja2SQL
j2sql = Jinja2SQL(param_style="named") # 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)
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}"