SQL blocks

Getting started with SQL blocks
To make it easier to query databases, Deepnote includes so-called "SQL blocks". After connecting one of the database integrations to Deepnote (Postgres, Redshift, BigQuery, or Snowflake), you can create SQL blocks and begin writing SQL queries. When you run an SQL query, Deepnote displays a the results in a Pandas DataFrame.
SQL blocks only work within Python notebooks
SQL blocks and Pandas DataFrames
Deepnote saves the result into a Pandas DataFrame which you can name. In the above example, it's df_1. You can use this variable for further processing in normal Python code blocks below. 
You can even issue SQL queries against a DataFrame or CSV file. Click "+ Block" and choose "DataFrame SQL" (shown below). You can then query a DataFrame or CSV file as follows:
1
SELECT * 
2
FROM df
Copied!
1
SELECT * 
2
FROM 'path/to/my_data.csv'
Copied!
Creating a DataFrame SQL block
Using Python and SQL
Deepnote uses jinjasql templating which allows you to pass variables, functions, and control structures (e.g., if statements and for loops) into your SQL queries.
To inject a Python variable inside your SQL query use the{{ variable_name }} syntax. For example:
1
SELECT date, name
2
FROM fh-bigquery.weather_gsod.all
3
WHERE name = {{ station_name  }}
4
LIMIT 10
Copied!
Passing lists or tuples into your SQL queries requires the inclause keyword from jinjasql. As you can see below, we use a similar syntax as before but with this new keyword preceded by the | symbol.
1
SELECT date, name
2
FROM fh-bigquery.weather_gsod.all
3
WHERE name in {{ station_list | inclause}}
4
ORDER BY date DESC
Copied!
To inject column names and table names, use the sqlsafe keyword as follows: 
1
SELECT *
2
FROM {{ table_name | sqlsafe }}
Copied!
A common use-case is searching for a wildcard pattern containing (e.g., the % character to represent optional substrings). To combine this with a variable value, use the following syntax:
1
SELECT *
2
FROM users
3
WHERE name LIKE {{ '%' + first_name + '%' }}
Copied!
You can also use more advanced templating features like {% if condition } %{ endif } , conditional blocks, or anything else that's supported by jinjasql. For example, the following block loops through a Python list (column_names) to contruct the desired SQL fields.
1
SELECT date, name,
2
{% for col in column_names %}
3
    {% if not loop.last %}
4
        {{ col | sqlsafe }},
5
    {% else %}
6
        {{ col | sqlsafe }}
7
    {% endif %}
8
{% endfor %}
9
FROM fh-bigquery.weather_gsod.all
10
WHERE date > '2015-12-31'  and name = {{ station_name }}
11
ORDER BY date DESC
12
LIMIT 5 
Copied!
For more information, please check out the jinjasql project.

features - Previous

Integrations

Next - features

Chart blocks

Last modified 1mo ago

Copy link

Contents

Getting started with SQL blocks

SQL blocks and Pandas DataFrames

Using Python and SQL