sql_queries.py

#drop tables 
songplay_table_drop="DROP TABLE IF EXISTS songplays ;"
user_table_drop = "DROP TABLE IF EXISTS users; "
song_table_drop = "DROP TABLE IF EXISTS songs; "
artist_table_drop = "DROP TABLE IF EXISTS artists; "
time_table_drop = "DROP TABLE IF EXISTS time; "

#create tables
song_table_create="""
CREATE TABLE IF NOT EXISTS songs(
							song_id varchar PRIMARY KEY,
							title varchar ,
							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, 
							location varchar NOT NULL,
							latitude float,
							longitude float)"""

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

user_table_create="""
CREATE TABLE IF NOT EXISTS users(
								userId int PRIMARY KEY,
								firstName varchar,
								lastName varchar,
								gender varchar, 
								level varchar NOT NULL)"""


songplay_table_create = (""" CREATE TABLE IF NOT EXISTS songplays (
songplay_id serial PRIMARY KEY, 
start_time timestamp REFERENCES time (start_time ) ,
userId int REFERENCES users ( userId ) ,
song_id varchar REFERENCES songs (song_id),
artist_id varchar REFERENCES artists (artist_id),
level varchar NOT NULL,
session_id int NOT NULL,
location varchar NOT NULL ,
user_agent varchar NOT NULL)
""")


# insert data
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, dayofweek) 
VALUES (%s, %s, %s, %s, %s, %s, %s)
ON CONFLICT (start_time) DO NOTHING """

user_table_insert="""
INSERT INTO users (userId, firstName, lastName, gender, level) 
VALUES ( %s, %s, %s, %s, %s)
ON CONFLICT (userId) DO NOTHING """

songplay_table_insert="""
INSERT INTO songplays ( start_time, userId, song_id, artist_id,level,  session_id, location, user_agent) 
VALUES ( %s, %s, %s, %s,%s, %s, %s, %s)
"""


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


drop_table_queries=[songplay_table_drop, user_table_drop, song_table_drop, artist_table_drop, time_table_drop]
create_table_queries=[song_table_create, artist_table_create, time_table_create, user_table_create, songplay_table_create]