List tables and columns

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.

%load_ext sql
%sql sqlite://

Let’s create some sample tables in the default schema:

%%sql
CREATE TABLE coordinates (x INT, y INT)

%%sql
CREATE TABLE people (name TEXT, birth_year INT)

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

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

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

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.