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
Running query in 'sqlite://'
%%sql
CREATE TABLE people (name TEXT, birth_year INT)
Show code cell output
Running query in 'sqlite://'
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)")
Show code cell output
<sqlalchemy.engine.cursor.CursorResult at 0x7f244fa08400>
%%sql
ATTACH DATABASE 'my.db' AS some_schema
Show code cell output
Running query in 'sqlite:///my.db'
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 |