What is Jinja?

What is the Jinja templating syntax? How is it used?

Jinja is a powerful templating language for Python, which allows you to generate text output from input data and a template.
The Jinja templating syntax provides a set of rules and constructs that define how the templating language should be used and how the output should be generated.

Templating engines in general are widely used for the dynamic creation of HTML, JSON, XML files where a template file is "fused" with underlying data into a final file.
Apart from being the most popular templating engine for Python projects, being used as default in projects like FLASK, DJANGO, ANSIBLE; Jinja has gained recent popularity in combination with SQL for interaction with databases, which has likely arisen from more comon use alongside Python in datascience, and the hugely popular "DBT" (data build tool) uses a combination of JINJA and YAML files to create complex data engineering tasks.

A simple example of a Jinja template:

Hello, {{ name }}! Today is {{ date }} and the time is {{ time }}.

In this example, the Jinja template uses the {{ name }}, {{ date }}, and {{ time }} variables to insert dynamic data into the output. When the template is rendered with the input data, the variables will be replaced with the corresponding values from the data, resulting in output like the following:

Hello, John! Today is December 1, 2022 and the time is 3:00 PM.

Jinja templates can also include expressions and control structures to create more complex and dynamic outputs. For example, you can use conditional statements like if / else and for loops to include or exclude parts of the output based on the input data.

Here is an example of a Jinja template with an if / else statement and a for loop:

{% if message %} {{ message }} {% else %} No message was provided. {% endif %}

{% for item in items %} {{ item }} {% endfor %}

In this example, the if / else statement checks the value of the message variable and outputs it if it is not null or undefined. If message is null or undefined, the else block will be executed and the string "No message was provided" will be output instead.

The for loop iterates over the items list, outputting each item in the list on a separate line. When the template is rendered with the input data, the resulting output will depend on the values of the message and items variables.

Overall, the Jinja templating syntax provides a set of rules and constructs for creating Jinja templates that generate text output based on input data. It is a powerful and flexible language that allows you to build complex and dynamic templates.


Real World Use Cases

Jinja in Web development

One of the major use cases of Jinja is the templating of HTML code and the reason why frameworks like Django make lots of use of Jinja.

In the example below, we connect to a database, run a query, then feed the fetched data into an HTML style table.

# Import the necessary libraries
import mysql.connector
from jinja2 import Template

# Connect to the database
cnx = mysql.connector.connect(user='username', password='password', host='hostname', database='database')

# Create a cursor for executing SQL queries
cursor = cnx.cursor()

# Execute a SQL query to retrieve data from the database
query = "SELECT * FROM table"
cursor.execute(query)

# Fetch the results of the query
results = cursor.fetchall()

# Close the cursor and connection
cursor.close()
cnx.close()

# Create a Jinja template for the HTML page
template = Template("""
<html>
<head>
  <title>Results</title>
</head>
<body>
  <table>
    <tr>
      {% for column in columns %}
        <th>{{ column }}</th>
      {% endfor %}
    </tr>
    {% for row in results %}
      <tr>
        {% for value in row %}
          <td>{{ value }}</td>
        {% endfor %}
      </tr>
    {% endfor %}
  </table>
</body>
</html>
""")

# Render the template with the retrieved data
html = template.render(columns=column_names, results=results)

# Print the resulting HTML page
print(html)

A Python ETL job using Jinja

In this example, we query a source database, then iterate over the results and run a templated INSERT statement for each row of results to insert our data to the destination database.

# Import the necessary libraries
import mysql.connector
from jinja2 import Template

# Connect to the source database
src_cnx = mysql.connector.connect(user='username', password='password', host='hostname', database='database')

# Create a cursor for executing SQL queries
src_cursor = src_cnx.cursor()

# Execute a SQL query to retrieve data from the source database
src_query = "SELECT * FROM table"
src_cursor.execute(src_query)

# Fetch the results of the query
src_results = src_cursor.fetchall()

# Close the cursor and connection
src_cursor.close()
src_cnx.close()

# Connect to the destination database
dst_cnx = mysql.connector.connect(user='username', password='password', host='hostname', database='database')

# Create a cursor for executing SQL queries
dst_cursor = dst_cnx.cursor()

# Create a Jinja template for the INSERT statement
template = Template("""
INSERT INTO table ({{ ",".join(columns) }}) VALUES ({{ ",".join(["%s"] * len(columns)) }})
""")

# Execute the INSERT statement for each row in the source data
for row in src_results:
  # Transform the data as needed
  transformed_row = transform_data(row)

  # Execute the INSERT statement
  insert_stmt = template.render(columns=column_names, values=transformed_row)
  dst_cursor.execute(insert_stmt, transformed_row)

# Commit the transaction
dst_cnx.commit()

# Close the cursor and connection
dst_cursor.close()
dst_cnx.close()

This ETL job extracts data from the source database, transforms the data as needed, and loads the transformed data into the destination database. Using Jinja and SQL together in this way allows you to create dynamic ETL jobs that can be used to automate data processing tasks and streamline your data engineering workflow.




 


Jump to

Start shipping smarter reports today

Sign up and start using Query.me for free.
Or schedule a demo and discuss your use case.