Data-Modeling-with-Postgres/sql_queries.py at master · aksharas28/Data-Modeling-with-Postgres · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
# 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

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

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

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

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

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

# INSERT RECORDS

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
                      """)

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
                  """)

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
                """)

# FIND SONGS

song_select = ("""
SELECT song_id, songs.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
                """)

# QUERY LISTS

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