No project description provided
Project description
Injection-safe SQL templating engine, Powered by Jinja+Psycopg
Why jinja templates are not enough
env = Environment()
template = env.from_string(
"""
select * from {{ table }}
{% if name is not None %}
where name = {{ name }}
{% endif %}
"""
)
template.render(
table="foo",
name="R'lyeh" # This will fail
)
Pros:
- if blocks, loops, filters, custom code, etc
Cons:
- just a string templating engine, doesn't do any escaping
Psycopg's built-in string composition
Similarly, psycopg3 has its own way of building queries, based on python's string formatting syntax
composed = SQL(
"""
select * from {table}
where name = {name}
"""
).format(table=Identifier("foo"), name="R'lyeh")
with psycopg.connect() as conn:
# db connection is needed to actually render the query
query = composed.as_string(conn)
Pros:
- Can differentiate between identifiers, literals and plain SQL
- Native escaping using libpq
Cons:
- Limited templating functionality
JinjaPsycopg, best of both worlds
from jinja_psycopg import JinjaPsycopg
from psycopg.sql import Identifier
query = """\
select * from {{ table }}
{% if name is not None %}
where name = {{ name }}
{% endif %}"""
renderer = JinjaPsycopg()
template = renderer.from_string(query)
composed = template.render(table=Identifier("foo"), name="R'lyeh")
with psycopg.connect() as conn, conn.cursor() as cursor:
# Render to string
query = composed.as_string(cursor)
# Execute
cursor.execute(composed)
or, as a shortcut:
composed = renderer.render(query, { "table": Identifier("foo"), "name": "R'lyeh" })
make_module is also supported, allowing you to extract configuration values from your template
sqlmodule = renderer.from_string(
"""
{% set config = { 'option': True } %}
select field from {{ table }}
"""
).make_module({ "table": Identifier("foo") })
assert getattr(sqlmodule.module, "config")['option'] == True
# Render to SQL
composed = sqlmodule.render()
Custom SQL Objects
@dataclass
class Table:
schema: str
name: str
def __sql__(self):
return SQL("{}.{}").format(
Identifier(self.schema), Identifier(self.name)
)
renderer.render("select * from {{ table }}", { "table": Table("public", "foo") })
Filters
psycopg
This filter is applied automatically to all jinja blocks:
{{ value }}
is equivalent to {{ (value) | psycopg }}
It stores the actual value inside a ContextVar,
replacing {{value}}
with a placeholder like {dictionary_key}
to later be passed to SQL.format
sql
Treat a string value as plain SQL, not as a literal
ALTER TABLE foo {{ 'ADD COLUMN html TEXT' | sql }}
sqljoin
Same as jinja's join filter, but operates on SQL objects
{{ [Identifier("foo"), Identifier("bar")] | sqljoin(',') }}
Project details
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
Built Distribution
Hashes for jinja_psycopg-0.1.1-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | c9a26749c8e2b5387b4e0bb087a7dffc1e38b957483f5a26c461cacabf6a64b8 |
|
MD5 | b45a909ec0901ce393dc589740c433ad |
|
BLAKE2b-256 | f0d8ec6b0b17a360b5e29196242684331c2c798534779ff8dcf670d62010d87b |