Get Data Based On A Condition

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 11:49:43 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 11:49:43 AM MST; Query executed in 0.00 m

Retrieve Records Based On A Condition

%%read_sql

-- Select all rows
SELECT * FROM staff
-- Where age is greater than 25
WHERE age > 25
Query started at 11:49:43 AM MST; Query executed in 0.00 m
first_name city_name age
0 Jill Miller 30
%%read_sql

-- Select all rows
SELECT * FROM staff
-- Where age is greater than or equal to 25
WHERE age >= 25
Query started at 11:49:43 AM MST; Query executed in 0.00 m
first_name city_name age
0 Jill Miller 30
1 Sarah Jackson 25
%%read_sql

-- Select all rows */
SELECT * FROM staff
-- Where city_name is "Miller" */
WHERE city_name = 'Miller'
Query started at 11:49:43 AM MST; Query executed in 0.00 m
first_name city_name age
0 Jill Miller 30
1 Steve Miller 24
%%read_sql

-- Select all rows
SELECT * FROM staff
-- Where city_name is not "Miller"
WHERE city_name != 'Miller'
Query started at 11:49:43 AM MST; Query executed in 0.00 m
first_name city_name age
0 Sarah Jackson 25