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
| import sys
import requests
import base64
import json
import logging
import pymysql
import csv
import sys, os, argparse
def main(host, user, passwd, db, port, client_id, client_secret):
try:
conn = pymysql.connect(host=host, user=username, passwd=password, db=database, port=port, use_unicode=True, charset='utf8')
cursor = conn.cursor()
except:
logging.error("could not connect to rds")
sys.exit(1)
headers = get_headers(client_id, client_secret)
artists = []
with open('artist_list.csv') as f:
raw = csv.reader(f)
for row in raw:
artists.append(row[0])
for a in artists:
params = {
"q": a,
"type": "artist",
"limit": "1"
}
r = requests.get("https://api.spotify.com/v1/search", params=params, headers=headers)
raw = json.loads(r.text)
artist = {}
try:
artist_raw = raw['artists']['items'][0]
if artist_raw['name'] == params['q']:
artist.update(
{
'id': artist_raw['id'],
'name': artist_raw['name'],
'followers': artist_raw['followers']['total'],
'popularity': artist_raw['popularity'],
'url': artist_raw['external_urls']['spotify'],
'image_url': artist_raw['images'][0]['url']
}
)
insert_row(cursor, artist, 'artists')
except:
logging.error('something worng')
continue
conn.commit()
sys.exit(0)
try:
r = requests.get("https://api.spotify.com/v1/search", params=params, headers=headers)
except:
logging.error(r.text)
sys.exit(1)
r = requests.get("https://api.spotify.com/v1/search", params=params, headers=headers)
if r.status_code != 200:
logging.error(r.text)
if r.status_code == 429:
retry_after = json.loads(r.headers)['Retry-After']
time.sleep(int(retry_after))
r = requests.get("https://api.spotify.com/v1/search", params=params, headers=headers)
elif r.status_code == 401:
headers = get_headers(client_id, client_secret)
r = requests.get("https://api.spotify.com/v1/search", params=params, headers=headers)
else:
sys.exit(1)
def get_headers(client_id, client_secret):
endpoint = "https://accounts.spotify.com/api/token"
encoded = base64.b64encode("{}:{}".format(client_id, client_secret).encode('utf-8')).decode('ascii')
headers = {
"Authorization": "Basic {}".format(encoded)
}
payload = {
"grant_type": "client_credentials"
}
r = requests.post(endpoint, data=payload, headers=headers)
access_token = json.loads(r.text)['access_token']
headers = {
"Authorization": "Bearer {}".format(access_token)
}
return headers
def insert_row(cursor, data, table):
placeholders = ', '.join(['%s'] * len(data))
columns = ', '.join(data.keys())
key_placeholders = ', '.join(['{0}=%s'.format(k) for k in data.keys()])
sql = "INSERT INTO %s ( %s ) VALUES ( %s ) ON DUPLICATE KEY UPDATE %s" % (table, columns, placeholders, key_placeholders)
cursor.execute(sql, list(data.values())*2)
if __name__ == '__main__':
parser = argparse.ArgumentParser()
parser.add_argument('--client_id', type=str, help='Spotify app client id')
parser.add_argument('--client_secret', type=str, help='Spotify client secret')
parser.add_argument('--host', type=str, help='end point host')
parser.add_argument('--username', type=str, help='AWS RDS id')
parser.add_argument('--database', type=str, help='DB name')
parser.add_argument('--password', type=str, help='AWS RDS password')
args = parser.parse_args()
port = 3306
main(host=args.host, user=args.username, passwd=args.password, db=args.database, port=port, client_id=args.client_id, client_secret=args.client_secret)
|
Spotify
toy project DB관련 도식
Spotify Web reference
Spotify ERD
Get Audio Features for Several Tracks
artist 테이블 생성
artist genres 테이블 생성
table의 상위 10개 record