%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://
There's a new jupysql version available (0.7.4), you're running 0.7.5.dev0. To upgrade: pip install jupysql --upgrade
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
* duckdb://
Done.
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
* duckdb://
Done.
Done.
Done.
Count |
---|
344 |
%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
* duckdb://
Done.
Done.
Done.
Done.
Count |
---|
%sqlcmd tables -s s1
Show 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 |