%sqlcmd snippets

`%sqlcmd snippets`#

%sqlcmd snippets returns the query snippets saved using --save

Load Data#

%load_ext sql
%sql duckdb://

Connecting to 'duckdb://'

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
SELECT * FROM penguins.csv LIMIT 3

Running query in 'duckdb://'

species	island	bill_length_mm	bill_depth_mm	flipper_length_mm	body_mass_g	sex
Adelie	Torgersen	39.1	18.7	181	3750	MALE
Adelie	Torgersen	39.5	17.4	186	3800	FEMALE
Adelie	Torgersen	40.3	18.0	195	3250	FEMALE

Let’s save a couple of snippets.

%%sql --save gentoo
SELECT * FROM penguins.csv where species == 'Gentoo'

Show code cell output Hide code cell output

Running query in 'duckdb://'

species	island	bill_length_mm	bill_depth_mm	flipper_length_mm	body_mass_g	sex
Gentoo	Biscoe	46.1	13.2	211	4500	FEMALE
Gentoo	Biscoe	50.0	16.3	230	5700	MALE
Gentoo	Biscoe	48.7	14.1	210	4450	FEMALE
Gentoo	Biscoe	50.0	15.2	218	5700	MALE
Gentoo	Biscoe	47.6	14.5	215	5400	MALE
Gentoo	Biscoe	46.5	13.5	210	4550	FEMALE
Gentoo	Biscoe	45.4	14.6	211	4800	FEMALE
Gentoo	Biscoe	46.7	15.3	219	5200	MALE
Gentoo	Biscoe	43.3	13.4	209	4400	FEMALE
Gentoo	Biscoe	46.8	15.4	215	5150	MALE

Truncated to displaylimit of 10.

%%sql --save chinstrap
SELECT * FROM penguins.csv where species == 'Chinstrap'

Show code cell output Hide code cell output

Running query in 'duckdb://'

species	island	bill_length_mm	bill_depth_mm	flipper_length_mm	body_mass_g	sex
Chinstrap	Dream	46.5	17.9	192	3500	FEMALE
Chinstrap	Dream	50.0	19.5	196	3900	MALE
Chinstrap	Dream	51.3	19.2	193	3650	MALE
Chinstrap	Dream	45.4	18.7	188	3525	FEMALE
Chinstrap	Dream	52.7	19.8	197	3725	MALE
Chinstrap	Dream	45.2	17.8	198	3950	FEMALE
Chinstrap	Dream	46.1	18.2	178	3250	FEMALE
Chinstrap	Dream	51.3	18.2	197	3750	MALE
Chinstrap	Dream	46.0	18.9	195	4150	FEMALE
Chinstrap	Dream	51.3	19.9	198	3700	MALE

Truncated to displaylimit of 10.

`%sqlcmd snippets`#

Returns all the snippets saved in the environment

%sqlcmd snippets

Stored snippets
gentoo
chinstrap

Arguments:

{snippet_name} Return a snippet.

-d/--delete Delete a snippet.

-D/--delete-force Force delete a snippet. This may be useful if there are other dependent snippets, and you still need to delete this snippet.

-A/--delete-force-all Force delete a snippet and all dependent snippets.

chinstrap_snippet = %sqlcmd snippets chinstrap
print(chinstrap_snippet)

SELECT * FROM penguins.csv where species == 'Chinstrap'

This returns the stored snippet chinstrap.

Calling %sqlcmd snippets {snippet_name} also works on a snippet that is dependent on others. To demonstrate it, let’s create a snippet dependent on the chinstrap snippet.

%%sql --save chinstrap_sub
SELECT * FROM chinstrap where island == 'Dream'

Generating CTE with stored snippets: 'chinstrap'

Running query in 'duckdb://'

species	island	bill_length_mm	bill_depth_mm	flipper_length_mm	body_mass_g	sex
Chinstrap	Dream	46.5	17.9	192	3500	FEMALE
Chinstrap	Dream	50.0	19.5	196	3900	MALE
Chinstrap	Dream	51.3	19.2	193	3650	MALE
Chinstrap	Dream	45.4	18.7	188	3525	FEMALE
Chinstrap	Dream	52.7	19.8	197	3725	MALE
Chinstrap	Dream	45.2	17.8	198	3950	FEMALE
Chinstrap	Dream	46.1	18.2	178	3250	FEMALE
Chinstrap	Dream	51.3	18.2	197	3750	MALE
Chinstrap	Dream	46.0	18.9	195	4150	FEMALE
Chinstrap	Dream	51.3	19.9	198	3700	MALE

Truncated to displaylimit of 10.

chinstrap_sub_snippet = %sqlcmd snippets chinstrap_sub
print(chinstrap_sub_snippet)

WITH chinstrap AS (
SELECT * FROM penguins.csv where species == 'Chinstrap')
SELECT * FROM chinstrap where island == 'Dream'

This returns the stored snippet chinstrap_sub.

Now, let’s see how to delete a stored snippet.

%sqlcmd snippets -d gentoo

'gentoo has been deleted.\nStored snippets: chinstrap, chinstrap_sub'

This deletes the stored snippet gentoo.

Now, let’s see how to delete a stored snippet that other snippets are dependent on. Recall we have created chinstrap_sub which is dependent on chinstrap.

print(chinstrap_sub_snippet)

WITH chinstrap AS (
SELECT * FROM penguins.csv where species == 'Chinstrap')
SELECT * FROM chinstrap where island == 'Dream'

Trying to delete the chinstrap snippet will display an error message:

%sqlcmd snippets -d chinstrap

UsageError: The following tables are dependent on chinstrap: chinstrap_sub.
Pass --delete-force to only delete chinstrap.
Pass --delete-force-all to delete chinstrap_sub and chinstrap

If you still wish to delete this snippet, you should use force-delete by running the below command:

%sqlcmd snippets -D chinstrap

'chinstrap has been deleted.\nchinstrap_sub depend on chinstrap\nStored snippets: chinstrap_sub'

Now, let’s see how to delete a snippet and all other dependent snippets. We’ll create a few snippets again.

%%sql --save chinstrap
SELECT * FROM penguins.csv where species == 'Chinstrap'

Show code cell output Hide code cell output

Running query in 'duckdb://'

species	island	bill_length_mm	bill_depth_mm	flipper_length_mm	body_mass_g	sex
Chinstrap	Dream	46.5	17.9	192	3500	FEMALE
Chinstrap	Dream	50.0	19.5	196	3900	MALE
Chinstrap	Dream	51.3	19.2	193	3650	MALE
Chinstrap	Dream	45.4	18.7	188	3525	FEMALE
Chinstrap	Dream	52.7	19.8	197	3725	MALE
Chinstrap	Dream	45.2	17.8	198	3950	FEMALE
Chinstrap	Dream	46.1	18.2	178	3250	FEMALE
Chinstrap	Dream	51.3	18.2	197	3750	MALE
Chinstrap	Dream	46.0	18.9	195	4150	FEMALE
Chinstrap	Dream	51.3	19.9	198	3700	MALE

Truncated to displaylimit of 10.

%%sql --save chinstrap_sub
SELECT * FROM chinstrap where island == 'Dream'

Show code cell output Hide code cell output

Generating CTE with stored snippets: 'chinstrap'

Running query in 'duckdb://'

species	island	bill_length_mm	bill_depth_mm	flipper_length_mm	body_mass_g	sex
Chinstrap	Dream	46.5	17.9	192	3500	FEMALE
Chinstrap	Dream	50.0	19.5	196	3900	MALE
Chinstrap	Dream	51.3	19.2	193	3650	MALE
Chinstrap	Dream	45.4	18.7	188	3525	FEMALE
Chinstrap	Dream	52.7	19.8	197	3725	MALE
Chinstrap	Dream	45.2	17.8	198	3950	FEMALE
Chinstrap	Dream	46.1	18.2	178	3250	FEMALE
Chinstrap	Dream	51.3	18.2	197	3750	MALE
Chinstrap	Dream	46.0	18.9	195	4150	FEMALE
Chinstrap	Dream	51.3	19.9	198	3700	MALE

Truncated to displaylimit of 10.

Now, force delete chinstrap and its dependent chinstrap_sub:

%sqlcmd snippets -A chinstrap

'chinstrap_sub, chinstrap has been deleted.\nThere are no stored snippets'

Parameterizing arguments#

JupySQL supports variable expansion of arguments in the form of {{variable}}. This allows the user to specify arguments with placeholders that can be replaced by variables dynamically.

Let’s see some examples:

snippet_name = "gentoo"

%%sql --save {{snippet_name}}
SELECT * FROM penguins.csv where species == 'Gentoo'

Running query in 'duckdb://'

species	island	bill_length_mm	bill_depth_mm	flipper_length_mm	body_mass_g	sex
Gentoo	Biscoe	46.1	13.2	211	4500	FEMALE
Gentoo	Biscoe	50.0	16.3	230	5700	MALE
Gentoo	Biscoe	48.7	14.1	210	4450	FEMALE
Gentoo	Biscoe	50.0	15.2	218	5700	MALE
Gentoo	Biscoe	47.6	14.5	215	5400	MALE
Gentoo	Biscoe	46.5	13.5	210	4550	FEMALE
Gentoo	Biscoe	45.4	14.6	211	4800	FEMALE
Gentoo	Biscoe	46.7	15.3	219	5200	MALE
Gentoo	Biscoe	43.3	13.4	209	4400	FEMALE
Gentoo	Biscoe	46.8	15.4	215	5150	MALE

Truncated to displaylimit of 10.

gentoo_snippet = %sqlcmd snippets {{snippet_name}}
print(gentoo_snippet)

SELECT * FROM penguins.csv where species == 'Gentoo'

%sqlcmd snippets -d {{snippet_name}}

'gentoo has been deleted.\nThere are no stored snippets'

%sqlcmd snippets

Contents

%sqlcmd snippets#

Load Data#

%sqlcmd snippets#

Parameterizing arguments#

`%sqlcmd snippets`#

`%sqlcmd snippets`#