%sqlcmd tables/%sqlcmd columns

%sqlcmd tables returns the current table names saved in environments.

%sqlcmd columns returns the column information in a specified table.

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 the file penguins.csv as a table penguins.

%%sql 
DROP TABLE IF EXISTS penguins;

CREATE TABLE penguins (
    species VARCHAR(255),
    island VARCHAR(255),
    bill_length_mm DECIMAL(5, 2),
    bill_depth_mm DECIMAL(5, 2),
    flipper_length_mm DECIMAL(5, 2),
    body_mass_g INTEGER,
    sex VARCHAR(255)
);

COPY penguins FROM 'penguins.csv' WITH (FORMAT CSV, HEADER TRUE);

Show code cell output Hide code cell output

Running query in 'duckdb://'

Count

%sqlcmd tables

Returns the current table names saved in environments.

%sqlcmd tables

Name
penguins

Arguments:

-s/--schema Get all table names under this schema

To show the usage of schema, let’s put two tables under two schema. In this code example, we create schema s1 and s2. We put t1 under schema s1, t2 under schema s2

%%sql
CREATE SCHEMA IF NOT EXISTS s1;
CREATE SCHEMA IF NOT EXISTS s2;
CREATE TABLE s1.t1(id INTEGER PRIMARY KEY, other_id INTEGER);
CREATE TABLE s2.t2(id INTEGER PRIMARY KEY, j VARCHAR);

Show code cell output Hide code cell output

Running query in 'duckdb://'

Count

%sqlcmd tables -s s1

Show code cell output Hide code cell output

Name
t1

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 an example:

schema = "s1"

%sqlcmd tables -s {{schema}}

Show code cell output Hide code cell output

Name
t1

As expected, the argument returns the table names under schema s1, which is t1.

%sqlcmd columns

Arguments:

-t/--table (Required) Get the column features of a specified table.

-s/--schema (Optional) Get the column features of a table under a schema

%sqlcmd columns -t penguins

name	type	nullable	default	autoincrement	comment
species	VARCHAR	True	None	False	None
island	VARCHAR	True	None	False	None
bill_length_mm	NUMERIC(5, 2)	True	None	False	None
bill_depth_mm	NUMERIC(5, 2)	True	None	False	None
flipper_length_mm	NUMERIC(5, 2)	True	None	False	None
body_mass_g	INTEGER	True	None	False	None
sex	VARCHAR	True	None	False	None

%sqlcmd columns -s s1 -t t1

name	type	nullable	default	autoincrement	comment
id	INTEGER	False	None	False	None
other_id	INTEGER	True	None	False	None

JupySQL also supports variable expansion of arguments of columns. Let’s see an example:

table = "t1"
schema = "s1"

%sqlcmd columns -s {{schema}} -t {{table}}

name	type	nullable	default	autoincrement	comment
id	INTEGER	False	None	False	None
other_id	INTEGER	True	None	False	None