Variables and Parameters

Jinja Parameters in Python

Working the in Python, the simples way to set parameters is as a Dictionary. You can then use your dictionary as an argument for the .render() method of the Jinja Template.

from jinja2 import Template

# Define the parameters for the query
parameters = {
  "column": "value",
  "min_date": "2022-01-01",
  "max_date": "2022-12-31"
}

# Create a Jinja template for the query
template = Template("""
SELECT * FROM table
WHERE column = '{{ column }}'
AND date BETWEEN '{{ min_date }}' AND '{{ max_date }}'
""")

# Render the template with the parameters
query = template.render(parameters)

# Print the results
print(query)

Jinja Parameters in SQL

To set parameters inside of your SQL Template, you can do so by adherring to the usual Jinja Syntax.

The following code would result in the same query as the code in the example above:

{% set column = "value" %}
{% set min_date = "2022-01-01" %}
{% set max_date = "2022-12-31" %}

SELECT * FROM table
WHERE column = '{{ column }}'
AND date BETWEEN '{{ min_date }}' AND '{{ max_date }}'

Why use jinja variables in SQL?

To reduce repetition.
Consider the follow CTE query:

{% set day_param = current_date - 1 %} 
with query_1 as (
	select date,
    	   *
    from table_one
    where date = {{ day_param }}
)
,query_2 as (
	select date,
    	   *
    from table_two
    where date = {{ day_param }}
)

select *
from query_1 one
join query_2 two on two.date = one.date

Variables in Query.me

In Query.me there are two ways to set variables that are then available in throughout your notebook, including all SQL, API, Email, or Slack blocks.

1. Parameter Blocks
Create a parameter block and reference it's name in SQL

2. Jinja in text blocks
In Query.me you can write Jinja anywere. To create a variable you can use the following Syntax:

{% set parameter = [1,2,3,4] %}

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.