Data_Modeling_Postgres/sql_queries.py at main · Ulli-Aech/Data_Modeling_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
# 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 UNIQUE,
                                start_time bigint NOT NULL REFERENCES time,
                                user_id int NOT NULL REFERENCES users,
                                level varchar,
                                song_id varchar REFERENCES songs,
                                artist_id varchar REFERENCES artists,
                                session_id int,
                                location varchar,
                                user_agent varchar,
                                PRIMARY KEY (start_time, user_id, song_id,
                                artist_id))
""")

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

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 NOT NULL,
                                                        duration float NOT NULL)
""")

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

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

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

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 songs.song_id, artists.artist_id
                  FROM songs
                  LEFT 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]