Repository navigation
Expand file tree
/
Copy pathsql_queries.py
More file actions
144 lines (127 loc) · 4.94 KB
/
Copy pathsql_queries.py
File metadata and controls
144 lines (127 loc) · 4.94 KB
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
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
# 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,
start_time BIGINT NOT NULL,
user_id INT 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 INT NOT NULL PRIMARY KEY,
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 NOT NULL 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 NOT NULL PRIMARY KEY,
name VARCHAR NOT NULL,
location VARCHAR NOT NULL,
latitude FLOAT,
longitude FLOAT
);""")
time_table_create = ("""CREATE TABLE IF NOT EXISTS time
(
start_time BIGINT NOT NULL PRIMARY KEY,
hour INT,
day INT,
week INT,
month INT,
year INT,
weekday INT
);""")
# INSERT RECORDS
songplay_table_insert = ("""INSERT INTO songplays
(
songplay_id,
start_time,
user_id,
level,
song_id,
artist_id,
session_id,
location,
user_agent
)
VALUES (DEFAULT, %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)
ON CONFLICT DO NOTHING
;""")
artist_table_insert = ("""INSERT INTO artists
(
artist_id,
name,
location,
latitude,
longitude
)
VALUES (%s, %s, %s, %s, %s)
ON CONFLICT 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 DO NOTHING;
""")
# FIND SONGS
song_select = ("""
SELECT s.song_id, a.artist_id
FROM songs s
JOIN artists a
ON s.artist_id = a.artist_id
WHERE s.title = %s AND a.name = %s AND s.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]