sql_queries.py

# DROP TABLE QUERIES
songplay_table_drop = "DROP TABLE IF EXISTS songplays"
users_table_drop = "DROP TABLE IF EXISTS users"
songs_table_drop = "DROP TABLE IF EXISTS songs"
artists_table_drop = "DROP TABLE IF EXISTS artists"
time_table_drop = "DROP TABLE IF EXISTS time"


# CREATE TABLE QUERIES
songplay_table_create = """
CREATE TABLE IF NOT EXISTS songplays(
songplay_id serial PRIMARY KEY,
start_time timestamp NOT NULL,
user_id int NOT NULL,
level varchar,
song_id varchar NOT NULL,
artist_id varchar NOT NULL,
session_id int,
location varchar,
user_agent varchar
)
"""

user_table_create = """
CREATE TABLE IF NOT EXISTS users(
user_id int PRIMARY KEY,
first_name varchar NOT NULL,
last_name varchar NOT NULL,
gender varchar,
level varchar
)
"""

song_table_create = """
CREATE TABLE IF NOT EXISTS songs(
song_id varchar PRIMARY KEY,
title varchar NOT NULL,
artist_id varchar NOT NULL,
year int,
duration float
)
"""

artist_table_create = """
CREATE TABLE IF NOT EXISTS artists(
artist_id varchar PRIMARY KEY,
name varchar NOT NULL,
location varchar,
latitude float,
longitude float
)
"""

time_table_create = """
CREATE TABLE IF NOT EXISTS time(
start_time timestamp PRIMARY KEY,
hour int NOT NULL,
day int NOT NULL,
week int NOT NULL,
month int NOT NULL,
year int NOT NULL,
weekday varchar NOT NULL
)
"""

# TODO: use a variable called songs_columns insted of long string for columns

# INSERT QUERIES
song_table_insert = """
INSERT INTO songs(
song_id, title, artist_id, year, duration
)
VALUES(%s, %s, %s, %s, %s)
ON CONFLICT (song_id)
DO NOTHING
"""

artist_table_insert = """
INSERT INTO artists(
artist_id, name, location, latitude, longitude
)
VALUES(%s, %s, %s, %s, %s)
ON CONFLICT (artist_id)
DO NOTHING
"""

time_table_insert = """
INSERT INTO time(
start_time, hour, day, week, month, year, weekday
)
VALUES(%s, %s, %s, %s, %s, %s, %s)
ON CONFLICT (start_time)
DO NOTHING"""

user_table_insert = """
INSERT INTO users(
user_id, first_name, last_name, gender, level
)
VALUES(%s, %s, %s, %s, %s)
ON CONFLICT (user_id)
DO UPDATE SET level = EXCLUDED.level
"""

songplay_table_insert = """
INSERT INTO songplays(
start_time, user_id, level, song_id, artist_id, session_id, location, user_agent
)
VALUES(%s, %s, %s, %s, %s, %s, %s, %s)
ON CONFLICT (songplay_id)
DO NOTHING
"""

# SELECT QUERY
song_select = """
SELECT songs.song_id, artists.artist_id
FROM (songs INNER JOIN artists ON songs.artist_id = artists.artist_id)
WHERE songs.title = %s AND artists.name = %s AND songs.duration = %s 
"""

# QUERY LIST
create_table_queries = [
    songplay_table_create,
    user_table_create,
    song_table_create,
    artist_table_create,
    time_table_create,
]

drop_table_queries = [
    songplay_table_drop,
    users_table_drop,
    songs_table_drop,
    artists_table_drop,
    time_table_drop,
]