JinjaSQL is our new open source library to generate SQL using a Jinja template.

At HashedIn, Django is used extensively. The Django ORM is great for most of the use cases, however, there are times when you just need to write a raw SQL query and bypass the ORM altogether. The most common use cases are reports and listing pages that need complex joins.
When you hit a 5% use case that requires the expressiveness and power of raw SQL query, it is unlikely that your query is a simple one-liner.
For those use cases, JinjaSQL helps you maintain the queries in an external template file. You can put in placeholder variables, add if/else conditions, use macros and all the power that is available to a regular Jinja template. You don’t have to manually track your bind parameters. It tracks them and binds them appropriately.

As a matter of fact templates are not a new idea, however, they haven’t been popular because they are vulnerable to SQL Injection. JinjaSQL never inserts values directly into the query. Instead, it gives you the generated SQL query, and a list of bind parameters. It is then up to you to use them to execute the query. Take a look at an example:

SELECT username, sum(spend)
FROM transactions
WHERE start_date >
AND end_date <

If this template is executed using plain-old Jinja2, you’d get :

SELECT username, sum(spend)
FROM transactions
WHERE start_date > '2016-01-01'
AND end_date < '2016-12-31'

With JinjaSQL, you get back two things,

SELECT username, sum(spend)
FROM transactions
WHERE start_date > %s
AND end_date < %s

and a list of bind parameters:

['2016-01-01' ,'2016-12-31']

The list of bind parameters can be integers, strings, or for that matter even python datetime objects.

Try using it in your projects. In case you have any questions/comments – create an issue in GitHub.