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
Running query in 'sqlite://'
ResultSet : to convert to pandas, call .DataFrame() or to polars, call .PolarsDataFrame()
%%sql
CREATE TABLE people (name TEXT, birth_year INT)
Hide code cell output
Running query in 'sqlite://'
ResultSet : to convert to pandas, call .DataFrame() or to polars, call .PolarsDataFrame()

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 primary_key
x INTEGER True None 0
y INTEGER True None 0 
%sqlcmd columns -t people
name type nullable default primary_key
name TEXT True None 0
birth_year INTEGER True None 0

If the table isn’t in the default schema, pass --schema/-s. Let’s create a new table in a new schema:

from sqlalchemy import create_engine
from sql.connection import SQLAlchemyConnection

conn = SQLAlchemyConnection(engine=create_engine("sqlite:///my.db"))
conn.execute("CREATE TABLE numbers (n FLOAT)")
Hide code cell output 
<sqlalchemy.engine.cursor.CursorResult at 0x7f244fa08400>

%%sql
ATTACH DATABASE 'my.db' AS some_schema
Hide code cell output
Running query in 'sqlite:///my.db'
ResultSet : to convert to pandas, call .DataFrame() or to polars, call .PolarsDataFrame()

Get the columns for the table in the newly created schema:

%sqlcmd columns --table numbers --schema some_schema
name type nullable default primary_key
n REAL True None 0