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)
Hide code cell output
*  sqlite://
Done.
[]
%%sql
CREATE TABLE people (name TEXT, birth_year INT)
Hide 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
Hide 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.