Interactive SQL Queries

New in version 0.7:

pip install jupysql --upgrade

Interactive command allows you to visualize and manipulate widget and interact with your SQL clause. We will demonstrate how to create widgets and dynamically query the dataset.

Note

%sql --interact requires ipywidgets: pip install ipywidgets

%sql --interact {{widget_variable}}

First, you need to define the variable as the form of basic data type or ipywidgets Widget. Then pass the variable name into --interact argument

%load_ext sql
import ipywidgets as widgets

from pathlib import Path
from urllib.request import urlretrieve

if not Path("penguins.csv").is_file():
    urlretrieve(
        "https://raw.githubusercontent.com/mwaskom/seaborn-data/master/penguins.csv",
        "penguins.csv",
    )
%sql duckdb://

Basic Data Types

The simplest way is to declare a variable with basic data types (Numeric, Text, Boolean…), the ipywidgets will autogenerates UI controls for those variables

body_mass_min = 3500
%sql --interact body_mass_min SELECT * FROM penguins.csv WHERE body_mass_g > {{body_mass_min}} LIMIT 5

Interactive mode, please interact with below widget(s) to control the variable

island = (  # Try to change Torgersen to Biscoe, Torgersen or Dream in the below textbox
    "Torgersen"
)
%sql --interact island SELECT * FROM penguins.csv WHERE island == '{{island}}' LIMIT 5

Interactive mode, please interact with below widget(s) to control the variable

ipywidgets Widget

You can use widgets to build fully interactive GUIs for your SQL clause.

See more for complete Widget List

IntSlider

body_mass_lower_bound = widgets.IntSlider(min=2500, max=3500, step=25, value=3100)

%sql --interact body_mass_lower_bound SELECT * FROM penguins.csv WHERE body_mass_g <= {{body_mass_lower_bound}} LIMIT 5

Interactive mode, please interact with below widget(s) to control the variable

FloatSlider

bill_length_mm_lower_bound = widgets.FloatSlider(
    min=35.0, max=45.0, step=0.1, value=40.0
)

%sql --interact bill_length_mm_lower_bound SELECT * FROM penguins.csv WHERE bill_length_mm <= {{bill_length_mm_lower_bound}} LIMIT 5

Interactive mode, please interact with below widget(s) to control the variable

Complete Example

To demonstrate the way to combine basic data type and ipywidgets into our interactive SQL Clause

body_mass_lower_bound = 3600
show_limit = (0, 50, 1)
sex_selection = widgets.RadioButtons(
    options=["MALE", "FEMALE"], description="Sex", disabled=False
)
species_selections = widgets.SelectMultiple(
    options=["Adelie", "Chinstrap", "Gentoo"],
    value=["Adelie", "Chinstrap"],
    # rows=10,
    description="Species",
    disabled=False,
)

%%sql --interact show_limit --interact body_mass_lower_bound --interact species_selections --interact sex_selection
SELECT * FROM penguins.csv 
WHERE species IN{{species_selections}} AND 
body_mass_g > {{body_mass_lower_bound}} AND 
sex == '{{sex_selection}}'
LIMIT {{show_limit}} 

Interactive mode, please interact with below widget(s) to control the variable