· 5 years ago · Feb 18, 2020, 01:10 AM
1#!/usr/bin/env python3
2import spotipy
3import os
4import sqlite3
5import json
6
7conn = sqlite3.connect('test.db')
8c = conn.cursor()
9
10
11def table_contains(table, col, val):
12 return c.execute('SELECT COUNT(*) FROM {} WHERE {}=?'.format(table, col), (val,)).fetchone()[0] > 0
13
14
15if __name__ == '__main__':
16 spotify_user = 'agenteclair'
17 os.environ['SPOTIPY_CLIENT_ID'] = '1144b98613f84fcba1ee451ca3214d2e'
18 os.environ['SPOTIPY_CLIENT_SECRET'] = 'this is a secret'
19 os.environ['SPOTIPY_REDIRECT_URI'] = 'http://localhost/'
20
21 # init spotify object with oauth token
22 scope = "user-library-read"
23 token = spotipy.util.prompt_for_user_token(spotify_user, scope)
24 sp = spotipy.Spotify(auth=token)
25
26 library = []
27 tracks = sp.current_user_saved_tracks(limit=50)
28 while tracks['next']:
29 library.extend(tracks['items'])
30 print("Retrieved {} tracks".format(len(library)))
31 tracks = sp.next(tracks)
32 library.extend(tracks['items'])
33 print("Retrieved {} tracks".format(len(library)))
34
35 c.execute('''
36 CREATE TABLE IF NOT EXISTS library (
37 added_at TEXT,
38 spotify_track INTEGER
39 );''')
40 c.execute('''
41 CREATE TABLE IF NOT EXISTS spotify_tracks(
42 album TEXT,
43 artists TEXT,
44 disc_number INTEGER,
45 duration_ms INTEGER,
46 explicit INTEGER,
47 external TEXT,
48 spotify_href TEXT,
49 spotify_id TEXT,
50 name TEXT,
51 track_number INTEGER,
52 spotify_uri TEXT
53 );
54 ''')
55 c.execute('''
56 CREATE TABLE IF NOT EXISTS spotify_artists(
57 external TEXT,
58 spotify_href TEXT,
59 spotify_id TEXT,
60 name TEXT,
61 spotify_uri TEXT
62 );
63 ''')
64 c.execute('''
65 CREATE TABLE IF NOT EXISTS spotify_albums(
66 album_type TEXT,
67 artists TEXT,
68 external TEXT,
69 spotify_href TEXT,
70 spotify_id TEXT,
71 images BLOB,
72 name TEXT,
73 release_date TEXT,
74 release_date_precision TEXT,
75 spotify_uri TEXT
76 )
77 ''')
78
79 for t in library:
80 artists = t['track']['artists']
81 for artist in artists:
82 id = artist['id']
83 if not table_contains('spotify_artists', 'spotify_id', id):
84 c.execute('''
85 INSERT INTO spotify_artists (
86 external,
87 spotify_href,
88 spotify_id,
89 name,
90 spotify_uri)
91 VALUES (?,?,?,?,?);
92 ''', (
93 json.dumps(artist['external_urls']),
94 artist['href'],
95 id,
96 artist['name'],
97 artist['uri']
98 ))
99
100 album = t['track']['album']
101 if not table_contains('spotify_albums', 'spotify_id', id):
102 c.execute('''
103 INSERT INTO spotify_albums (
104 album_type,
105 artists,
106 external,
107 spotify_href,
108 spotify_id,
109 images,
110 name,
111 release_date,
112 release_date_precision,
113 spotify_uri)
114 VALUES (?,?,?,?,?,?,?,?,?,?);
115 ''', (
116 album['album_type'],
117 json.dumps([a['id'] for a in album['artists']]),
118 json.dumps(album['external_urls']),
119 album['href'],
120 id,
121 json.dumps(album['images']),
122 album['name'],
123 album['release_date'],
124 album['release_date_precision'],
125 album['uri']
126 ))
127
128 track = t['track']
129 id = track['id']
130 if not table_contains('spotify_tracks', 'spotify_id', id):
131 c.execute('''
132 INSERT INTO spotify_tracks (
133 album,
134 artists,
135 disc_number,
136 duration_ms,
137 explicit,
138 external,
139 spotify_href,
140 spotify_id,
141 name,
142 track_number,
143 spotify_uri)
144 VALUES (?,?,?,?,?,?,?,?,?,?,?);
145 ''', (
146 album['id'],
147 json.dumps([a['id'] for a in track['artists']]),
148 track['disc_number'],
149 track['duration_ms'],
150 int(track['explicit']),
151 json.dumps(track['external_urls']),
152 track['href'],
153 track['id'],
154 track['name'],
155 track['track_number'],
156 track['uri']
157 ))
158
159 track_rowid = c.execute('SELECT rowid FROM spotify_tracks WHERE spotify_id=?', (track['id'],)).fetchone()[0]
160 if not table_contains('library', 'spotify_track', track_rowid):
161 c.execute('''
162 INSERT INTO library (
163 added_at,
164 spotify_track)
165 VALUES (?,?);
166 ''', (
167 t['added_at'],
168 track_rowid
169 ))
170
171 conn.commit()