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​

Querying Dataframes with SQL

You can create a Dataframe SQL block that lets you directly query a dataframe with SQL. Use the dataframe's variable name as a table name in the FROM or JOIN clauses. The result of the dataframe query is then saved into a new dataframe (in the example below it's df_1).

A dataframe "df" queried with SQL

The DataFrame SQL uses the SQLite dialect. It's implemented using DuckDB behind the scenes, check out their docs for more info.