List tables and columns#
Note
This example uses SQLite
but the same commands work for other databases.
With JupySQL, you can quickly explore what tables are available in your database and which columns each table has.
Setup#
%load_ext sql
%sql sqlite://
Let’s create some sample tables in the default schema:
%%sql
CREATE TABLE coordinates (x INT, y INT)
Show code cell output
* sqlite://
Done.
[]
%%sql
CREATE TABLE people (name TEXT, birth_year INT)
Show code cell output
* sqlite://
Done.
[]
List tables#
Use %sqlcmd tables
to print the tables for the current connection:
%sqlcmd tables
Name |
---|
coordinates |
people |
Pass --schema/-s
to get tables in a different schema:
%sqlcmd tables --schema schema
List columns#
Use %sqlcmd columns --table/-t
to get the columns for the given table.
%sqlcmd columns --table coordinates
name | type | nullable | default | autoincrement | primary_key |
---|---|---|---|---|---|
x | INTEGER | True | None | auto | 0 |
y | INTEGER | True | None | auto | 0 |
%sqlcmd columns -t people
name | type | nullable | default | autoincrement | primary_key |
---|---|---|---|---|---|
name | TEXT | True | None | auto | 0 |
birth_year | INTEGER | True | None | auto | 0 |
If the table isn’t in the defautl schema, pass --schema/-s
. Let’s create a new table in a new schema:
import sqlite3
with sqlite3.connect("my.db") as conn:
conn.execute("CREATE TABLE numbers (n FLOAT)")
%%sql
ATTACH DATABASE 'my.db' AS some_schema
Show code cell output
* sqlite://
Done.
[]
Get the columns for the table in the newly created schema:
%sqlcmd columns --table numbers --schema some_schema
name | type | nullable | default | autoincrement | primary_key |
---|---|---|---|---|---|
n | FLOAT | True | None | auto | 0 |
Run Tests on Column#
Use %sqlcmd test
to run tests on your dataset.
For example, to see if all the values in the column birth_year are greater than 100:
%sqlcmd test --table people --column birth_year --greater 100
True
Four different comparator commands exist: greater
, greater-or-equal
, less-than
, less-than-or-equal
, and no-nulls
.
Command will return True if all tests pass, otherwise an error with sample breaking cases will be printed out.