SQL blocks
SQL is a first-class citizen in Deepnote notebooks
To make it easier to query databases, Deepnote has a special type of block – the SQL block. After connecting one of the database integrations to Deepnote (Postgres, Redshift, BigQuery, or Snowflake), you can create SQL blocks and write a SQL query. When you run it, Deepnote displays a pandas dataframe.
SQL blocks only work within Python notebooks
Querying a Postgres database using a Deepnote SQL block

Dataframe variable

Deepnote saves the result into a python variable of the chosen name. In the above example, it's df_rna. You can use this variable for further processing in normal python code blocks below.

Using Python variables in a SQL query

You can use the {{ variable_name }} syntax to inject your python variables into the SQL query.
Using the min_length python variable in a query
Deepnote uses jinjasql templates, which is a specialized dialect of the jinja system.

Injecting raw variable value

The above syntax prevents SQL injection by parametrizing the query and passing your variables as parameters to the respective Python SQL connector. This can result in e.g. the variable value being surrounded by quotes.
To inject the raw variable value, you can use the sqlsafe filter like this:
FROM {{ table_name | sqlsafe }}

IN clause syntax

To make your life easier, jinjasql also supports using python lists with the SQL IN clause using the inclause filter:
# Python block
-- SQL block
FROM users
WHERE email IN {{ user_emails | inclause }}

Advanced templates

You can also use more advanced templating features like {% if condition } %{ endif } conditional blocks or anything else that's supported by jinjasql. Check out the jinjasql repo for more examples: https://github.com/sripathikrishnan/jinjasql
Last modified 2mo ago