Insert Record

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:31:14 AM MST; Query executed in 0.00 m

Insert One Record

%%read_sql -d  

INSERT INTO staff (first_name, city_name, age) 
VALUES ('Chris', 'Albon', 30);
Query started at 10:31:15 AM MST; Query executed in 0.00 m

Insert Multiple Records

%%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:31:15 AM MST; Query executed in 0.00 m

View Table

%%read_sql

-- Select all records from the staff table
SELECT * FROM staff;
Query started at 10:31:15 AM MST; Query executed in 0.00 m
first_name city_name age
0 Chris Albon 30
1 Jill Miller 30
2 Steve Miller 24
3 Sarah Jackson 25