Parameterizing SQL queries

Note

This feature will be released in version 0.7, but you can give it a try now!

pip uninstall jupysql -y
pip install git+https://github.com/ploomber/jupysql

Variable Expansion as {{variable}}

We support the variable expansion in the form of {{variable}}, this also allows the user to write the query as template with some dynamic variables

Now, let’s give a simple query template and define some variables where we will apply in the template:

dynamic_limit = 5
dynamic_column = "island, sex"

%sql SELECT {{dynamic_column}} FROM penguins.csv LIMIT {{dynamic_limit}}

*  duckdb://
Done.

island	sex
Torgersen	MALE
Torgersen	FEMALE
Torgersen	FEMALE
Torgersen	None
Torgersen	FEMALE

Note that variables will be fetched from the local namespace into the SQL statement.

Two way to expand $variable or {variable_name} has been deprecated in current and all future versions, see more.