Learning machine learning? Try my machine learning flashcards or Machine Learning with Python Cookbook.
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 |