Get Selected Columns

Note: This tutorial was created in a Jupyter Notebook. %%read_sql is a Jupyter magic command and can be ignored when not using Jupyter Notebooks.

Preliminaries

# Load libraries
from sqlalchemy import create_engine
from sqlalchemy_utils import create_database, database_exists, drop_database

# Create PostgreSQL connection
engine = create_engine("postgres://localhost/notes_db")

# Load sql_magic so we can write SQL in Jupyter Notebooks
%load_ext sql_magic

# Setup SQL connection to the postgreSQL engine we created
%config SQL.conn_name = 'engine'

Create Database

# If a PostgreSQL database with this name exists
if database_exists(engine.url):
    # Delete PostgreSQL database 
    drop_database(engine.url)
    # Create empty PostgreSQL database
    create_database(engine.url)
# Otherwise
else:
    # Create empty PostgreSQL database
    create_database(engine.url)

Create Table

%%read_sql -d

CREATE TABLE staff ( 
    first_name varchar(255), 
    city_name varchar(255),
    age int
);
Query started at 10:44:26 AM MST; Query executed in 0.00 m

Populate Table With Data

%%read_sql -d 

INSERT INTO staff (first_name, city_name, age) 
VALUES ('Jill', 'Miller', 30),
       ('Steve', 'Miller', 24),
       ('Sarah', 'Jackson', 25);
Query started at 10:44:26 AM MST; Query executed in 0.00 m

Retrieve All Records In Selected Columns

%%read_sql

-- Select all rows from the columns city_name and age in the staff table
SELECT city_name, age FROM staff;
Query started at 10:44:26 AM MST; Query executed in 0.00 m
city_name age
0 Miller 30
1 Miller 24
2 Jackson 25