· 6 years ago · Dec 22, 2018, 01:38 AM
1{
2 "cells": [
3 {
4 "cell_type": "markdown",
5 "metadata": {},
6 "source": [
7 "## Creating another Database out of XMl data from my Itunes Lib"
8 ]
9 },
10 {
11 "cell_type": "code",
12 "execution_count": 61,
13 "metadata": {},
14 "outputs": [],
15 "source": [
16 "import xml.etree.ElementTree as ET\n",
17 "import sqlite3\n",
18 "import os\n"
19 ]
20 },
21 {
22 "cell_type": "code",
23 "execution_count": 62,
24 "metadata": {},
25 "outputs": [],
26 "source": [
27 "os.chdir('/home/zeski/Documents/Data_Science/SQL/SQL_DBS')\n",
28 "\n",
29 "conn = sqlite3.connect('trackdb11_26_18.sqlite')\n",
30 "c = conn.cursor()"
31 ]
32 },
33 {
34 "cell_type": "markdown",
35 "metadata": {},
36 "source": [
37 "## Making fresh tables using executescript()"
38 ]
39 },
40 {
41 "cell_type": "code",
42 "execution_count": 64,
43 "metadata": {},
44 "outputs": [],
45 "source": [
46 "c.executescript('''\n",
47 " DROP TABLE IF EXISTS Artist;\n",
48 " DROP TABLE IF EXISTS Album;\n",
49 " DROP TABLE IF EXISTS Track;\n",
50 " DROP TABLE IF EXISTS Genre;\n",
51 " \n",
52 " CREATE TABLE Artist(\n",
53 " id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,\n",
54 " name TEXT UNIQUE\n",
55 " );\n",
56 " \n",
57 " CREATE TABLE Genre(\n",
58 " id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,\n",
59 " name TEXT UNIQUE\n",
60 " );\n",
61 " \n",
62 " CREATE TABLE Album(\n",
63 " id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,\n",
64 " artist_id INTEGER,\n",
65 " title TEXT UNIQUE\n",
66 " \n",
67 " );\n",
68 " \n",
69 " CREATE TABLE Track(\n",
70 " id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, \n",
71 " title TEXT UNIQUE,\n",
72 " genre_id INTEGER,\n",
73 " album_id INTEGER,\n",
74 " len INTEGER, \n",
75 " rating INTEGER,\n",
76 " count INTEGER\n",
77 " );\n",
78 "''')\n",
79 "\n",
80 "conn.commit()"
81 ]
82 },
83 {
84 "cell_type": "code",
85 "execution_count": 65,
86 "metadata": {},
87 "outputs": [],
88 "source": [
89 "fname = input('Enter file name: ')\n",
90 "if (len(fname) < 1) : fname = 'Library.xml'\n",
91 "\n",
92 " "
93 ]
94 },
95 {
96 "cell_type": "code",
97 "execution_count": 66,
98 "metadata": {},
99 "outputs": [],
100 "source": [
101 "def lookup(d, key):\n",
102 " found = False\n",
103 " for child in d:\n",
104 " if found : return child.text\n",
105 " if child.tag == 'key' and child.text == key : \n",
106 " found = True \n",
107 " return None\n"
108 ]
109 },
110 {
111 "cell_type": "code",
112 "execution_count": 69,
113 "metadata": {},
114 "outputs": [
115 {
116 "name": "stdout",
117 "output_type": "stream",
118 "text": [
119 "Dict count: 404\n",
120 "Another One Bites The Dust Queen Greatest Hits Rock 55 100 217103\n",
121 "Asche Zu Asche Rammstein Herzeleid Industrial 79 100 231810\n",
122 "Beauty School Dropout Various Grease Soundtrack 48 100 239960\n",
123 "Black Dog Led Zeppelin IV Rock 109 100 296620\n",
124 "Bring The Boys Back Home Pink Floyd The Wall [Disc 2] Rock 33 100 87118\n",
125 "Circles Bryan Lee Blues Is Funk 54 60 355369\n",
126 "Comfortably Numb Pink Floyd The Wall [Disc 2] Rock 36 100 384130\n",
127 "Crazy Little Thing Called Love Queen Greatest Hits Rock 38 100 163631\n",
128 "Electric Funeral Black Sabbath Paranoid Metal 44 100 293015\n",
129 "Fat Bottomed Girls Queen Greatest Hits Rock 38 100 257515\n",
130 "For Those About To Rock (We Salute You) AC/DC Who Made Who Rock 84 100 353750\n",
131 "Four Sticks Led Zeppelin IV Rock 84 100 284421\n",
132 "Furious Angels Rob Dougan The Matrix Reloaded Soundtrack 54 100 330004\n",
133 "Gelle Bryan Lee Blues Is Blues/R&B 45 60 199836\n",
134 "Going To California Led Zeppelin IV Rock 100 100 215666\n",
135 "Grease Various Grease Soundtrack 42 100 205792\n",
136 "Hand of Doom Black Sabbath Paranoid Metal 36 100 429609\n",
137 "Hells Bells AC/DC Who Made Who Rock 82 100 312946\n",
138 "Hey You Pink Floyd The Wall [Disc 2] Rock 23 100 282305\n",
139 "I Worry Bryan Lee Blues Is Blues/R&B 33 100 341315\n",
140 "Iron Man Black Sabbath Paranoid Metal 39 100 358530\n",
141 "Is There Anybody Out There? Pink Floyd The Wall [Disc 2] Rock 26 100 160679\n",
142 "It was a Very Good Year Frank Sinatra Greatest Hits Easy Listening 39 100 268852\n",
143 "Its Your Move Bryan Lee Blues Is Blues/R&B 40 100 245002\n",
144 "Jack the Stripper/Fairies Wear Boots Black Sabbath Paranoid Metal 35 100 373995\n",
145 "Killer Queen Queen Greatest Hits Rock 34 100 181368\n",
146 "Laichzeit Rammstein Herzeleid Industrial 41 100 262844\n",
147 "Let me Down Easy Bryan Lee Blues Is Blues/R&B 43 100 331441\n",
148 "Misty Mountain Hop Led Zeppelin IV Rock 88 100 278831\n",
149 "No Low Down Bryan Lee Blues Is Blues/R&B 39 100 245760\n",
150 "Now You Are Gone America Greatest Hits Easy Listening 52 100 187559\n",
151 "Outside The Wall Pink Floyd The Wall [Disc 2] Rock 16 100 104437\n",
152 "Paranoid Black Sabbath Paranoid Metal 36 100 172930\n",
153 "Planet Caravan Black Sabbath Paranoid Metal 38 100 274938\n",
154 "Pretty Jeanie Bryan Lee Blues Is Blues/R&B 34 100 225175\n",
155 "Rammstein Rammstein Herzeleid Industrial 45 100 265090\n",
156 "Rat Salad Black Sabbath Paranoid Metal 46 100 150230\n",
157 "Rock & Roll Led Zeppelin IV Rock 109 100 220891\n",
158 "Rode Across the Desert America Greatest Hits Easy Listening 60 100 249887\n",
159 "Sandy Various Grease Soundtrack 36 100 155742\n",
160 "Shake Your Foundations AC/DC Who Made Who Rock 85 100 233769\n",
161 "Sister Golden Hair America Greatest Hits Easy Listening 60 100 202057\n",
162 "Somebody To Love Queen Greatest Hits Rock 17 100 297586\n",
163 "Stairway To Heaven Led Zeppelin IV Rock 93 100 481567\n",
164 "Strangers in the Night Frank Sinatra Greatest Hits Easy Listening 42 100 158484\n",
165 "Summer Nights Various Grease Soundtrack 35 100 216973\n",
166 "Summer Wind Frank Sinatra Greatest Hits Easy Listening 46 100 177554\n",
167 "Thats Life Frank Sinatra Greatest Hits Easy Listening 43 100 189988\n",
168 "The Battle Of Evermore Led Zeppelin IV Rock 110 100 351529\n",
169 "The Blues \"Is\" Bryan Lee Blues Is Blues/R&B 45 100 252238\n",
170 "There it Is Bryan Lee Blues Is Blues/R&B 35 100 395441\n",
171 "Think Bryan Lee Blues Is Blues/R&B 41 100 320339\n",
172 "This Town Frank Sinatra Greatest Hits Easy Listening 44 100 185051\n",
173 "Tin Man America Greatest Hits Easy Listening 50 100 210076\n",
174 "Track 01 Billy Price Danger Zone Blues/R&B 47 100 261825\n",
175 "Track 02 Billy Price Danger Zone Blues/R&B 42 100 165459\n",
176 "Track 03 Billy Price Danger Zone Blues/R&B 41 100 206471\n",
177 "Messin with the Kid The Canettes Blues Band Self Titled Blues/R&B 23 100 226377\n",
178 "Track 04 Billy Price Danger Zone Blues/R&B 37 100 257175\n",
179 "Stormy Monday The Canettes Blues Band Self Titled Blues/R&B 29 100 464143\n",
180 "Track 05 Billy Price Danger Zone Blues/R&B 42 100 230295\n",
181 "Waiting on Ice Bryan Lee Blues Is Blues/R&B 37 100 289384\n",
182 "War Pigs/Luke's Wall Black Sabbath Paranoid Metal 38 100 478197\n",
183 "We Are The Champions Queen Greatest Hits Rock 24 100 181864\n",
184 "We Will Rock You Queen Greatest Hits Rock 33 100 123559\n",
185 "When Somebody Loves You Frank Sinatra Greatest Hits Easy Listening 43 100 117263\n",
186 "When The Levee Breaks Led Zeppelin IV Rock 83 100 427624\n",
187 "You are the One that I Want Various Grease Soundtrack 31 100 169900\n",
188 "You Done Me Wrong Bryan Lee Blues Is Blues/R&B 35 100 200097\n",
189 "You Shook Me All Night Long AC/DC Who Made Who Rock 92 100 210938\n",
190 "You're My Best Friend Queen Greatest Hits Rock 31 100 172643\n",
191 "Zion Fluke The Matrix Reloaded Soundtrack 47 100 273528\n",
192 "Who Made Who AC/DC Who Made Who Rock 110 100 207203\n",
193 "D.T. AC/DC Who Made Who Rock 90 None 173714\n",
194 "Sink the Pink AC/DC Who Made Who Rock 83 None 253466\n",
195 "Ride On AC/DC Who Made Who Rock 75 None 351268\n",
196 "Chase the Ace AC/DC Who Made Who Rock 93 None 181420\n",
197 "Wollt Ihr Das Bett In Flammen Sehen Rammstein Herzeleid Industrial 50 None 317387\n",
198 "Der Meister Rammstein Herzeleid Industrial 46 None 250801\n",
199 "Weisses Fleisch Rammstein Herzeleid Industrial 52 None 215902\n",
200 "Seemann Rammstein Herzeleid Industrial 34 None 288235\n",
201 "Du Riechst So Gut Rammstein Herzeleid Industrial 50 None 289332\n",
202 "Das Alte Leid Rammstein Herzeleid Industrial 46 None 344581\n",
203 "Heirate Mich Rammstein Herzeleid Industrial 39 None 284734\n",
204 "Herzeleid Rammstein Herzeleid Industrial 42 None 223425\n",
205 "Baba O'Riley The Who Who's Next Rock 45 None 298448\n",
206 "Bargain The Who Who's Next Rock 37 None 331676\n",
207 "Love Ain't for Keeping The Who Who's Next Rock 24 None 130560\n",
208 "My Wife The Who Who's Next Rock 17 None 213786\n",
209 "The Song Is Over The Who Who's Next Rock 14 None 375797\n",
210 "Getting In Tune The Who Who's Next Rock 27 None 288809\n",
211 "Going Mobile The Who Who's Next Rock 28 100 221910\n",
212 "Behind Blue Eyes The Who Who's Next Rock 43 None 221570\n",
213 "Won't Get Fooled Again The Who Who's Next Rock 21 None 511111\n",
214 "Folsom Prison Blues Johnny Cash The Legend Of Johnny Cash Country 47 100 170004\n",
215 "I Walk The Line Johnny Cash The Legend Of Johnny Cash Country 33 100 165720\n",
216 "Get Rhythm Johnny Cash The Legend Of Johnny Cash Country 35 100 134530\n",
217 "Big River Johnny Cash The Legend Of Johnny Cash Country 51 None 152711\n",
218 "Guess Things Happen That Way Johnny Cash The Legend Of Johnny Cash Country 35 None 111386\n",
219 "Ring Of Fire Johnny Cash The Legend Of Johnny Cash Country 30 100 157100\n",
220 "Jackson Johnny Cash The Legend Of Johnny Cash Country 35 100 166530\n",
221 "A Boy Named Sue (live) Johnny Cash The Legend Of Johnny Cash Country 37 None 226063\n",
222 "You Raise Me Up Selah Hiding Place Gospel & Religious 9 None 302262\n",
223 "Hold On, I'm Coming The Canettes Blues Band On Tap & In the Can Blues/R&B 34 100 282566\n",
224 "Got my Mojo Working The Canettes Blues Band On Tap & In the Can Blues/R&B 52 100 310517\n",
225 "Sweet Home Chicago The Canettes Blues Band On Tap & In the Can Blues/R&B 27 100 375849\n",
226 "Heavy Love The Canettes Blues Band On Tap & In the Can Blues/R&B 28 100 253544\n",
227 "Cold Cold Feeling The Canettes Blues Band On Tap & In the Can Blues/R&B 43 100 261929\n",
228 "Lonely Avenue The Canettes Blues Band On Tap & In the Can Blues/R&B 24 100 292022\n",
229 "Good Morning Little Schoolgirl The Canettes Blues Band On Tap & In the Can Blues/R&B 20 100 278648\n",
230 "Dust My Broom The Canettes Blues Band On Tap & In the Can Blues/R&B 24 100 299311\n",
231 "Gimme' Some Lovin The Canettes Blues Band On Tap & In the Can Blues/R&B 35 100 227239\n",
232 "Everybody Needs Somebody to Love The Canettes Blues Band On Tap & In the Can Blues/R&B 24 100 195186\n",
233 "Black Magic Woman The Canettes Blues Band On Tap & In the Can Blues/R&B 31 100 442644\n",
234 "Steppin' Rooster The Canettes Blues Band On Tap & In the Can Blues/R&B 21 100 704862\n",
235 "Our Love is Drifting The Canettes Blues Band On Tap & In the Can Blues/R&B 15 100 292649\n",
236 "Doin' 100 The Canettes Blues Band On Tap & In the Can Blues/R&B 20 100 343980\n",
237 "Mother Joy Matt Ender Natural Wonders Music Sampler 1999 New Age 463 None 287294\n",
238 "Soldier's Lament Steve McDonald Natural Wonders Music Sampler 1999 New Age 371 None 347193\n",
239 "Dulaman Altan Natural Wonders Music Sampler 1999 New Age 403 None 223007\n",
240 "The Arrow Chris Spheeris Natural Wonders Music Sampler 1999 New Age 416 None 319869\n",
241 "Spanish Eyes La Esperanza Natural Wonders Music Sampler 1999 New Age 375 None 259030\n",
242 "Banana Bay Luis Villegas Natural Wonders Music Sampler 1999 New Age 403 None 198060\n",
243 "Aguas De Marco Rosa Passos Natural Wonders Music Sampler 1999 New Age 407 None 179408\n",
244 "Tamborea Energipsy Natural Wonders Music Sampler 1999 New Age 355 None 235389\n",
245 "Gone Hollie Smith Natural Wonders Music Sampler 1999 New Age 362 None 196414\n",
246 "The Immigrant Joanie Madden Natural Wonders Music Sampler 1999 New Age 350 None 362631\n",
247 "Pahrump-Big Water Cusco Natural Wonders Music Sampler 1999 New Age 342 None 191634\n",
248 "Seeker's Quest Cheryl Gunn Natural Wonders Music Sampler 1999 New Age 337 None 236434\n",
249 "Floating To Forever Dean Everson Natural Wonders Music Sampler 1999 New Age 337 None 351686\n",
250 "Open Road Jeff Bailey Relaxing Jazz Jazz 10 None 318563\n",
251 "Ruby Kaiser Chiefs Yours Truly, Angry Mob Alternative & Punk 13 None 205008\n",
252 "The Angry Mob Kaiser Chiefs Yours Truly, Angry Mob Alternative & Punk 41 None 288313\n",
253 "Heat Dies Down Kaiser Chiefs Yours Truly, Angry Mob Alternative & Punk 12 None 237061\n",
254 "Highroyds Kaiser Chiefs Yours Truly, Angry Mob Alternative & Punk 10 None 199575\n",
255 "Love's Not A Competition (But I'm Winning) Kaiser Chiefs Yours Truly, Angry Mob Alternative & Punk 11 None 197799\n",
256 "Thank You Very Much Kaiser Chiefs Yours Truly, Angry Mob Alternative & Punk 11 None 157753\n",
257 "I Can Do It Without You Kaiser Chiefs Yours Truly, Angry Mob Alternative & Punk 11 None 204199\n",
258 "My Kind Of Guy Kaiser Chiefs Yours Truly, Angry Mob Alternative & Punk 10 None 246595\n",
259 "Everything Is Average Nowadays Kaiser Chiefs Yours Truly, Angry Mob Alternative & Punk 16 None 164754\n",
260 "Learnt My Lesson Well Kaiser Chiefs Yours Truly, Angry Mob Alternative & Punk 9 None 325955\n",
261 "Try Your Best Kaiser Chiefs Yours Truly, Angry Mob Alternative & Punk 8 None 222511\n",
262 "Retirement Kaiser Chiefs Yours Truly, Angry Mob Alternative & Punk 10 None 237426\n",
263 "The Angry Mob [Live From Berlin] Kaiser Chiefs Yours Truly, Angry Mob Alternative & Punk 40 None 279066\n",
264 "I Like To Fight Kaiser Chiefs Yours Truly, Angry Mob Alternative & Punk 8 None 218566\n",
265 "From The Neck Down Kaiser Chiefs Yours Truly, Angry Mob Alternative & Punk 15 None 147226\n",
266 "The Wisdom of Crowds: Why the Many Are Smarter than the Few Part 1 of 3 James Surowiecki The Wisdom of Crowds: Why the Many Are Smarter than the Few (Abridged Nonfiction) Business 15 None 8586749\n",
267 "The Wisdom of Crowds: Why the Many Are Smarter than the Few Part 2 of 3 James Surowiecki The Wisdom of Crowds: Why the Many Are Smarter than the Few (Abridged Nonfiction) Business 9 None 8443432\n",
268 "The Wisdom of Crowds: Why the Many Are Smarter than the Few Part 3 of 3 James Surowiecki The Wisdom of Crowds: Why the Many Are Smarter than the Few (Abridged Nonfiction) Business 3 None 4168829\n",
269 "The Wisdom of Crowds: Why the Many Are Smarter Than the Few (Unabridged) Part 1 of 5 James Surowiecki The Wisdom of Crowds: Why the Many Are Smarter Than the Few (Unabridged) Nonfiction 4 None 7920893\n",
270 "The Wisdom of Crowds: Why the Many Are Smarter Than the Few (Unabridged) Part 2 of 5 James Surowiecki The Wisdom of Crowds: Why the Many Are Smarter Than the Few (Unabridged) Nonfiction None None 7921106\n",
271 "The Wisdom of Crowds: Why the Many Are Smarter Than the Few (Unabridged) Part 3 of 5 James Surowiecki The Wisdom of Crowds: Why the Many Are Smarter Than the Few (Unabridged) Nonfiction None None 7965736\n",
272 "The Wisdom of Crowds: Why the Many Are Smarter Than the Few (Unabridged) Part 4 of 5 James Surowiecki The Wisdom of Crowds: Why the Many Are Smarter Than the Few (Unabridged) Nonfiction 1 None 9653330\n",
273 "The Wisdom of Crowds: Why the Many Are Smarter Than the Few (Unabridged) Part 5 of 5 James Surowiecki The Wisdom of Crowds: Why the Many Are Smarter Than the Few (Unabridged) Nonfiction None None 746536\n",
274 "Misty David Osborne Moonlight And Love Songs Classical 302 None 249312\n",
275 "My Funny Valentine David Osborne Moonlight And Love Songs Classical 301 None 235885\n",
276 "Since I Don't Have You David Osborne Moonlight And Love Songs Classical 288 None 251846\n",
277 "Tenderly David Osborne Moonlight And Love Songs Classical 284 None 227395\n",
278 "When I Fall In Love David Osborne Moonlight And Love Songs Classical 281 None 187768\n",
279 "Unforgettable David Osborne Moonlight And Love Songs Classical 280 None 272927\n",
280 "The Way You Look Tonight David Osborne Moonlight And Love Songs Classical 277 None 247222\n",
281 "I've Grown Accustomed To Your Face David Osborne Moonlight And Love Songs Classical 280 None 148035\n",
282 "Smoke Gets In Your Eyes David Osborne Moonlight And Love Songs Classical 277 None 198504\n",
283 "Fly Me To The Moon David Osborne Moonlight And Love Songs Classical 275 None 182543\n",
284 "Unchained Melody David Osborne Moonlight And Love Songs Classical 265 None 259369\n",
285 "These Foolish Things Remind Me Of You David Osborne Moonlight And Love Songs Classical 267 None 184711\n",
286 "La Vie En Rose David Osborne Moonlight And Love Songs Classical 259 None 276767\n",
287 "As Time Goes By David Osborne Moonlight And Love Songs Classical 290 None 257985\n",
288 "Pilot Fairly Legal Fairly Legal, Season 1 Drama 1 None 3847006\n",
289 "hte postal service - the impor The Postal Service unreleased demo Electronic None None 193959\n",
290 "Winter Wonderland Bing Crosby Seasons Greatings Holiday 163 None 144744\n",
291 "Jingle Bells Frank Sinatra Seasons Greatings Holiday 271 None 121756\n",
292 "White Christmas Bing Crosby Seasons Greatings Holiday 158 None 95634\n",
293 "The Christmas Song Frank Sinatra Seasons Greatings Holiday 272 None 209632\n",
294 "Little Drummer Boy Bing Crosby Seasons Greatings Holiday 154 None 178311\n",
295 "Silent Night Frank Sinatra Seasons Greatings Holiday 257 None 149054\n",
296 "Let It Snow Bing Crosby Seasons Greatings Holiday 154 None 125126\n",
297 "Mistletoe And Holly Frank Sinatra Seasons Greatings Holiday 257 None 138501\n",
298 "O Holy Night Bing Crosby Seasons Greatings Holiday 150 None 214021\n",
299 "Have Yourself A Merry Little Christmas Frank Sinatra Seasons Greatings Holiday 252 None 208248\n",
300 "Do You Hear What I Hear Bing Crosby Seasons Greatings Holiday 155 None 164728\n",
301 "I'll Be Home For Christmas Frank Sinatra Seasons Greatings Holiday 249 None 190876\n",
302 "Voices Disturbed The Sickness Alternative None None 191764\n",
303 "The Game Disturbed The Sickness Alternative 1 None 227134\n",
304 "Stupify Disturbed The Sickness Alternative 1 None 274233\n",
305 "Down With The Sickness Disturbed The Sickness Alternative 1 None 278726\n",
306 "Violence Fetish Disturbed The Sickness Alternative None None 203833\n",
307 "Fear Disturbed The Sickness Alternative 4 None 227004\n",
308 "Numb Disturbed The Sickness Alternative None None 224940\n",
309 "Want Disturbed The Sickness Alternative None None 232803\n",
310 "Conflict Disturbed The Sickness Alternative 5 None 275330\n",
311 "Shout 2000 Disturbed The Sickness Alternative 1 None 257802\n",
312 "Droppin' Plates Disturbed The Sickness Alternative 4 None 229302\n",
313 "Meaning Of Life Disturbed The Sickness Alternative 3 None 242599\n",
314 "PY4INF-01-Intro.mp3 Recording by Dr. Chuck Python for Informatics's official Podcast. Podcast 1 None 3428075\n",
315 "Eben Upton: Raspberry Pi IEEE Computer Society Computing Conversations Podcast 2 None 661368\n",
316 "Andrew Tanenbaum: Writing the Book on Networks IEEE Computer Society Computing Conversations Podcast 4 None 535040\n",
317 "Massimo Banzi: Building Arduino IEEE Computer Society Computing Conversations Podcast 1 None 567745\n",
318 "PY4INF-04-Functions.mp3 Recording by Dr. Chuck Python for Informatics's official Podcast. Podcast None None 1683800\n",
319 "PY4INF-02-Expressions.mp3 Recording by Dr. Chuck Python for Informatics's official Podcast. Podcast None None 2578416\n",
320 "PY4INF-03-Conditional.mp3 Recording by Dr. Chuck Python for Informatics's official Podcast. Podcast None None 2296058\n",
321 "PY4INF-05-Iterations.mp3 Recording by Dr. Chuck Python for Informatics's official Podcast. Podcast 1 None 2804297\n",
322 "PY4INF-06-Strings.mp3 Recording by Dr. Chuck Python for Informatics's official Podcast. Podcast None None 1677844\n",
323 "PY4INF-07-Files.mp3 Recording by Dr. Chuck Python for Informatics's official Podcast. Podcast None None 1478713\n",
324 "PY4INF-08-Lists.mp3 Recording by Dr. Chuck Python for Informatics's official Podcast. Podcast None None 1636231\n",
325 "PY4INF-09-Dictionaries.mp3 Recording by Dr. Chuck Python for Informatics's official Podcast. Podcast None None 2254445\n",
326 "PY4INF-10-Tuples.mp3 Recording by Dr. Chuck Python for Informatics's official Podcast. Podcast None None 1597596\n",
327 "PY4INF-11-Regex.mp3 Recording by Dr. Chuck Python for Informatics's official Podcast. Podcast None None 2123702\n",
328 "John C. Hollar: History of Computing IEEE Computer Society Computing Conversations Podcast 1 None 694073\n",
329 "Bob Metcalfe: Ethernet at Forty IEEE Computer Society Computing Conversations Podcast None None 1031392\n",
330 "Gordon Bell: Building Blocks of Computing IEEE Computer Society Computing Conversations Podcast 1 None 744620\n",
331 "Ian Horrocks: Standardizing OWL IEEE Computer Society Computing Conversations Podcast 1 None 572212\n",
332 "Katie Hafner: The Origins of the Internet IEEE Computer Society Computing Conversations Podcast 1 None 652460\n",
333 "Larry Smarr: Building Mosaic IEEE Computer Society Computing Conversations Podcast None None 894693\n",
334 "Len Kleinrock: The Theory of Packets IEEE Computer Society Computing Conversations Podcast 1 None 674742\n",
335 "Mitchell Baker: The Mozilla Foundation IEEE Computer Society Computing Conversations Podcast None None 994246\n",
336 "Pooja Sankar: Building the Piazza Collaboration System IEEE Computer Society Computing Conversations Podcast None None 496404\n",
337 "Van Jacobson: Content-Centric Networking IEEE Computer Society Computing Conversations Podcast None None 780251\n",
338 "The Apache Software Foundation IEEE Computer Society Computing Conversations Podcast None None 542484\n",
339 "A Brief History of Packets IEEE Computer Society Computing Conversations Podcast None None 1004643\n",
340 "Discovering JavaScript Object Notation IEEE Computer Society Computing Conversations Podcast None None 650971\n",
341 "Inventing PHP IEEE Computer Society Computing Conversations Podcast None None 494915\n",
342 "Monash Museum of Computing History IEEE Computer Society Computing Conversations Podcast None None 362657\n",
343 "The Rise of JavaScript IEEE Computer Society Computing Conversations Podcast None None 526132\n",
344 "Joseph Hardin: NCSA Mosaic IEEE Computer Society Computing Conversations Podcast None None 845635\n",
345 "Len Kleinrock on the Internet's First Two Packets IEEE Computer Society Computing Conversations Podcast 1 None 594390\n",
346 "Doug Van Houweling on Building the NSFNet IEEE Computer Society Computing Conversations Podcast None None 773146\n",
347 "Computing Conversations: Nathaniel Borenstein on MIME IEEE Computer Society Computing Conversations Podcast None None 682135\n",
348 "You Don't Mess Around With Jim Jim Croce Classic Hits Folk None None 184398\n",
349 "Andrew S. Tanenbaum on MINIX IEEE Computer Society Computing Conversations Podcast None None 603000\n",
350 "Computing Conversations: Elizabeth Fong on SQL Standards IEEE Computer Society Computing Conversations Podcast None None 533577\n",
351 "Nii Quaynor on Bringing the Internet to Africa IEEE Computer Society Computing Conversations Podcast 1 None 673332\n",
352 "PHP-09-Transactions.mp3 Created by Sakai SI 664 W14's official Podcast. Podcast None None 1728888\n",
353 "PHP-01-Intro.mp3 Created by Sakai SI 664 W14's official Podcast. Podcast None None 2250624\n",
354 "PHP-02-Install.mp3 Created by Sakai SI 664 W14's official Podcast. Podcast 1 None None\n",
355 "PHP-04-Expressions.mp3 Created by Sakai SI 664 W14's official Podcast. Podcast None None 2168064\n",
356 "PHP-05-Functions.mp3 Created by Sakai SI 664 W14's official Podcast. Podcast None None 1446792\n",
357 "PHP-06-Strings.mp3 Created by Sakai SI 664 W14's official Podcast. Podcast None None 417696\n",
358 "PHP-12-Sessions.mp3 Created by Sakai SI 664 W14's official Podcast. Podcast None None 3624312\n",
359 "PHP-06-Objects.mp3 Created by Sakai SI 664 W14's official Podcast. Podcast None None 2455368\n",
360 "SI664-13-JSON-A.mp3 Created by Sakai SI 664 W14's official Podcast. Podcast None None 4407672\n",
361 "PHP-13-JavaScript.mp3 Created by Sakai SI 664 W14's official Podcast. Podcast None None 3650088\n",
362 "PHP-09-Database-Design.mp3 Created by Sakai SI 664 W14's official Podcast. Podcast None None 3591072\n",
363 "PHP-Tsugi-Install.mp3 Created by Sakai SI 664 W14's official Podcast. Podcast None None 3181656\n",
364 "PHP-10-MySQL-PDO.mp3 Created by Sakai SI 664 W14's official Podcast. Podcast None None 4536685\n",
365 "PHP-08-MySQL.mp3 Created by Sakai SI 664 W14's official Podcast. Podcast None None 3994656\n",
366 "SI664-13-JSON-B.mp3 Created by Sakai SI 664 W14's official Podcast. Podcast None None 1484616\n",
367 "IMS-Learning-Tools-Interoperability.mp3 Created by Sakai SI 664 W14's official Podcast. Podcast None None 2185848\n",
368 "An Interview with Don Waters Matt Pasiewicz CNI Event Coverage Speech 2 None 1411082\n",
369 "PY4INF-11-Regex.mp3 Recording by Dr. Chuck Python for Informatics's official Podcast. Podcast None None 2123702\n",
370 "PY4INF-10-Tuples.mp3 Recording by Dr. Chuck Python for Informatics's official Podcast. Podcast None None 1597596\n",
371 "PY4INF-09-Dictionaries.mp3 Recording by Dr. Chuck Python for Informatics's official Podcast. Podcast None None 2254445\n",
372 "PY4INF-08-Lists.mp3 Recording by Dr. Chuck Python for Informatics's official Podcast. Podcast None None 1636231\n",
373 "PY4INF-07-Files.mp3 Recording by Dr. Chuck Python for Informatics's official Podcast. Podcast None None 1478713\n",
374 "PY4INF-06-Strings.mp3 Recording by Dr. Chuck Python for Informatics's official Podcast. Podcast None None 1677844\n",
375 "PY4INF-05-Iterations.mp3 Recording by Dr. Chuck Python for Informatics's official Podcast. Podcast None None 2804297\n",
376 "PY4INF-04-Functions.mp3 Recording by Dr. Chuck Python for Informatics's official Podcast. Podcast None None 1683800\n",
377 "PY4INF-03-Conditional.mp3 Recording by Dr. Chuck Python for Informatics's official Podcast. Podcast None None 2296058\n",
378 "PY4INF-02-Expressions.mp3 Recording by Dr. Chuck Python for Informatics's official Podcast. Podcast None None 2578416\n",
379 "PY4INF-01-Intro.mp3 Recording by Dr. Chuck Python for Informatics's official Podcast. Podcast None None 3428075\n",
380 "Py4Inf-11-Regex.mp3 Recording by Dr. Chuck Python for Informatics's official Podcast. Podcast None None 2123702\n",
381 "Py4Inf-10-Tuples.mp3 Recording by Dr. Chuck Python for Informatics's official Podcast. Podcast None None 1597596\n",
382 "Py4Inf-09-Dictionaries.mp3 Recording by Dr. Chuck Python for Informatics's official Podcast. Podcast None None 2254445\n",
383 "Py4Inf-08-Lists.mp3 Recording by Dr. Chuck Python for Informatics's official Podcast. Podcast None None 1636231\n",
384 "Py4Inf-07-Files.mp3 Recording by Dr. Chuck Python for Informatics's official Podcast. Podcast None None 1478713\n",
385 "Py4Inf-06-Strings.mp3 Recording by Dr. Chuck Python for Informatics's official Podcast. Podcast None None 1677844\n",
386 "Py4Inf-05-Iterations.mp3 Recording by Dr. Chuck Python for Informatics's official Podcast. Podcast None None 2804297\n",
387 "Py4Inf-04-Functions.mp3 Recording by Dr. Chuck Python for Informatics's official Podcast. Podcast None None 1683800\n",
388 "Py4Inf-03-Conditional.mp3 Recording by Dr. Chuck Python for Informatics's official Podcast. Podcast None None 2296058\n",
389 "Py4Inf-02-Expressions.mp3 Recording by Dr. Chuck Python for Informatics's official Podcast. Podcast None None 2578416\n",
390 "Py4Inf-01-Intro.mp3 Recording by Dr. Chuck Python for Informatics's official Podcast. Podcast None None 3428075\n",
391 "Ian Foster on the Globus Project IEEE Computer Society Computing Conversations Podcast 1 None 538305\n",
392 "Khan Academy and Computer Science IEEE Computer Society Computing Conversations Podcast 1 None 558837\n",
393 "Guido van Rossumon the Early Years of Python IEEE Computer Society Computing Conversations Podcast 1 None 701884\n",
394 "Guido van Rossum on the Modern Era of Python IEEE Computer Society Computing Conversations Podcast None None 741616\n",
395 "John Resig on Building jQuery IEEE Computer Society Computing Conversations Podcast None None 595983\n",
396 "Roy T. Fielding on Understanding the REST Style IEEE Computer Society Computing Conversations Podcast 1 None 683650\n",
397 "Anil Jain: 25 Years of Biometric Recognition IEEE Computer Society Computing Conversations Podcast None None 661368\n",
398 "Waste Management Undercover Boss Undercover Boss, Season 1 Reality TV 1 None 2565232\n",
399 "Sleep Like a Baby Tonight U2 Songs of Innocence Rock None None 301641\n",
400 "Evergreen The Black Crowes Warpaint Rock None None 261373\n",
401 "Oh Josephine The Black Crowes Warpaint Rock None None 398907\n",
402 "Movin' On Down the Line The Black Crowes Warpaint Rock None None 342693\n",
403 "Wounded Bird The Black Crowes Warpaint Rock None None 263453\n",
404 "Volcano U2 Songs of Innocence Rock None None 194116\n",
405 "Wee Who See the Deep The Black Crowes Warpaint Rock None None 290520\n",
406 "Cedarwood Road U2 Songs of Innocence Rock None None 265268\n",
407 "Locust Street The Black Crowes Warpaint Rock None None 255240\n",
408 "Raised By Wolves U2 Songs of Innocence Rock None None 245599\n",
409 "The Troubles U2 Songs of Innocence Rock None None 285843\n",
410 "This Is Where You Can Reach Me Now U2 Songs of Innocence Rock None None 305134\n",
411 "God's Got It The Black Crowes Warpaint Rock None None 202653\n",
412 "California (There Is No End to Love) U2 Songs of Innocence Rock None None 239846\n",
413 "Every Breaking Wave U2 Songs of Innocence Rock None None 252162\n",
414 "Interview With the Onion News Network Onion News Network Onion News Network, Season 1 Comedy 1 None 302002\n",
415 "Song for Someone U2 Songs of Innocence Rock None None 226763\n",
416 "The Miracle (Of Joey Ramone) U2 Songs of Innocence Rock None None 255382\n",
417 "Iris (Hold Me Close) U2 Songs of Innocence Rock None None 319457\n",
418 "Goodbye Daughters of the Revolution The Black Crowes Warpaint Rock None None 304147\n",
419 "Walk Believer Walk The Black Crowes Warpaint Rock None None 280253\n",
420 "Choice Hotels International Undercover Boss Undercover Boss, Season 2 Reality TV 1 None 2614047\n",
421 "Microchip Jason Farnham YouTube Audio Library Dance & Electronic None None 114336\n",
422 "Anant Agarwal IEEE Computer Society Computing Conversations Podcast None None 494000\n"
423 ]
424 }
425 ],
426 "source": [
427 "stuff = ET.parse(fname)\n",
428 "\n",
429 "all = stuff.findall('dict/dict/dict')\n",
430 "print('Dict count:', len(all))\n",
431 "for entry in all:\n",
432 " if (lookup(entry, 'Track ID') is None ) : continue\n",
433 " \n",
434 " name = lookup(entry,'Name')\n",
435 " artist = lookup(entry, 'Artist')\n",
436 " album = lookup(entry, 'Album')\n",
437 " genre = lookup(entry, 'Genre')\n",
438 " count = lookup(entry, 'Play Count')\n",
439 " rating = lookup(entry, 'Rating')\n",
440 " length = lookup(entry, 'Total Time')\n",
441 " \n",
442 " \n",
443 " if name is None or artist is None or album is None or genre is None : \n",
444 " continue \n",
445 " print(name, artist, album, genre, count, rating, length)\n",
446 " \n",
447 " #Artist\n",
448 " c.execute('''\n",
449 " INSERT OR IGNORE INTO Artist (name) \n",
450 " VALUES (?)\n",
451 " ''', (artist, ))\n",
452 " \n",
453 " c.execute('''\n",
454 " SELECT id FROM Artist WHERE name = ? \n",
455 " \n",
456 " ''',(artist, ))\n",
457 " \n",
458 " artist_id = c.fetchone()[0]\n",
459 " \n",
460 " \n",
461 " #Album\n",
462 " c.execute('''\n",
463 " INSERT OR IGNORE INTO Album (title, artist_id) \n",
464 " VALUES (?,?)\n",
465 " ''', (album, artist_id))\n",
466 " c.execute('''\n",
467 " SELECT id FROM Album WHERE title = ?\n",
468 " ''', (album, ))\n",
469 " album_id = c.fetchone()[0]\n",
470 " \n",
471 " #Genre\n",
472 " c.execute('''\n",
473 " INSERT OR IGNORE INTO Genre(name)\n",
474 " VALUES(?)\n",
475 " ''', (genre, ))\n",
476 " c.execute('''\n",
477 " SELECT id FROM Genre WHERE name = ?\n",
478 " ''', (genre, ))\n",
479 " genre_id = c.fetchone()[0]\n",
480 " \n",
481 " #Track\n",
482 " c.execute(\"\"\"\n",
483 " INSERT OR REPLACE INTO Track(title, genre_id, album_id, len, rating, count)\n",
484 " VALUES (?,?,?,?,?,?)\n",
485 " \"\"\", (name, genre_id, album_id, length, rating, count))\n",
486 " \n",
487 " conn.commit()"
488 ]
489 },
490 {
491 "cell_type": "code",
492 "execution_count": 71,
493 "metadata": {},
494 "outputs": [
495 {
496 "name": "stdout",
497 "output_type": "stream",
498 "text": [
499 "[('Another One Bites The Dust', 'Greatest Hits', 'Queen', 'Rock'), ('Asche Zu Asche', 'Herzeleid', 'Rammstein', 'Industrial'), ('Beauty School Dropout', 'Grease', 'Various', 'Soundtrack'), ('Black Dog', 'IV', 'Led Zeppelin', 'Rock'), ('Bring The Boys Back Home', 'The Wall [Disc 2]', 'Pink Floyd', 'Rock'), ('Circles', 'Blues Is', 'Bryan Lee', 'Funk'), ('Comfortably Numb', 'The Wall [Disc 2]', 'Pink Floyd', 'Rock'), ('Crazy Little Thing Called Love', 'Greatest Hits', 'Queen', 'Rock'), ('Electric Funeral', 'Paranoid', 'Black Sabbath', 'Metal'), ('Fat Bottomed Girls', 'Greatest Hits', 'Queen', 'Rock'), ('For Those About To Rock (We Salute You)', 'Who Made Who', 'AC/DC', 'Rock'), ('Four Sticks', 'IV', 'Led Zeppelin', 'Rock'), ('Furious Angels', 'The Matrix Reloaded', 'Rob Dougan', 'Soundtrack'), ('Gelle', 'Blues Is', 'Bryan Lee', 'Blues/R&B'), ('Going To California', 'IV', 'Led Zeppelin', 'Rock'), ('Grease', 'Grease', 'Various', 'Soundtrack'), ('Hand of Doom', 'Paranoid', 'Black Sabbath', 'Metal'), ('Hells Bells', 'Who Made Who', 'AC/DC', 'Rock'), ('Hey You', 'The Wall [Disc 2]', 'Pink Floyd', 'Rock'), ('I Worry', 'Blues Is', 'Bryan Lee', 'Blues/R&B'), ('Iron Man', 'Paranoid', 'Black Sabbath', 'Metal'), ('Is There Anybody Out There?', 'The Wall [Disc 2]', 'Pink Floyd', 'Rock'), ('It was a Very Good Year', 'Greatest Hits', 'Queen', 'Easy Listening'), ('Its Your Move', 'Blues Is', 'Bryan Lee', 'Blues/R&B'), ('Jack the Stripper/Fairies Wear Boots', 'Paranoid', 'Black Sabbath', 'Metal'), ('Killer Queen', 'Greatest Hits', 'Queen', 'Rock'), ('Laichzeit', 'Herzeleid', 'Rammstein', 'Industrial'), ('Let me Down Easy', 'Blues Is', 'Bryan Lee', 'Blues/R&B'), ('Misty Mountain Hop', 'IV', 'Led Zeppelin', 'Rock'), ('No Low Down', 'Blues Is', 'Bryan Lee', 'Blues/R&B'), ('Now You Are Gone', 'Greatest Hits', 'Queen', 'Easy Listening'), ('Outside The Wall', 'The Wall [Disc 2]', 'Pink Floyd', 'Rock'), ('Paranoid', 'Paranoid', 'Black Sabbath', 'Metal'), ('Planet Caravan', 'Paranoid', 'Black Sabbath', 'Metal'), ('Pretty Jeanie', 'Blues Is', 'Bryan Lee', 'Blues/R&B'), ('Rammstein', 'Herzeleid', 'Rammstein', 'Industrial'), ('Rat Salad', 'Paranoid', 'Black Sabbath', 'Metal'), ('Rock & Roll', 'IV', 'Led Zeppelin', 'Rock'), ('Rode Across the Desert', 'Greatest Hits', 'Queen', 'Easy Listening'), ('Sandy', 'Grease', 'Various', 'Soundtrack'), ('Shake Your Foundations', 'Who Made Who', 'AC/DC', 'Rock'), ('Sister Golden Hair', 'Greatest Hits', 'Queen', 'Easy Listening'), ('Somebody To Love', 'Greatest Hits', 'Queen', 'Rock'), ('Stairway To Heaven', 'IV', 'Led Zeppelin', 'Rock'), ('Strangers in the Night', 'Greatest Hits', 'Queen', 'Easy Listening'), ('Summer Nights', 'Grease', 'Various', 'Soundtrack'), ('Summer Wind', 'Greatest Hits', 'Queen', 'Easy Listening'), ('Thats Life', 'Greatest Hits', 'Queen', 'Easy Listening'), ('The Battle Of Evermore', 'IV', 'Led Zeppelin', 'Rock'), ('The Blues \"Is\"', 'Blues Is', 'Bryan Lee', 'Blues/R&B'), ('There it Is', 'Blues Is', 'Bryan Lee', 'Blues/R&B'), ('Think', 'Blues Is', 'Bryan Lee', 'Blues/R&B'), ('This Town', 'Greatest Hits', 'Queen', 'Easy Listening'), ('Tin Man', 'Greatest Hits', 'Queen', 'Easy Listening'), ('Track 01', 'Danger Zone', 'Billy Price', 'Blues/R&B'), ('Track 02', 'Danger Zone', 'Billy Price', 'Blues/R&B'), ('Track 03', 'Danger Zone', 'Billy Price', 'Blues/R&B'), ('Messin with the Kid', 'Self Titled', 'The Canettes Blues Band', 'Blues/R&B'), ('Track 04', 'Danger Zone', 'Billy Price', 'Blues/R&B'), ('Stormy Monday', 'Self Titled', 'The Canettes Blues Band', 'Blues/R&B'), ('Track 05', 'Danger Zone', 'Billy Price', 'Blues/R&B'), ('Waiting on Ice', 'Blues Is', 'Bryan Lee', 'Blues/R&B'), (\"War Pigs/Luke's Wall\", 'Paranoid', 'Black Sabbath', 'Metal'), ('We Are The Champions', 'Greatest Hits', 'Queen', 'Rock'), ('We Will Rock You', 'Greatest Hits', 'Queen', 'Rock'), ('When Somebody Loves You', 'Greatest Hits', 'Queen', 'Easy Listening'), ('When The Levee Breaks', 'IV', 'Led Zeppelin', 'Rock'), ('You are the One that I Want', 'Grease', 'Various', 'Soundtrack'), ('You Done Me Wrong', 'Blues Is', 'Bryan Lee', 'Blues/R&B'), ('You Shook Me All Night Long', 'Who Made Who', 'AC/DC', 'Rock'), (\"You're My Best Friend\", 'Greatest Hits', 'Queen', 'Rock'), ('Zion', 'The Matrix Reloaded', 'Rob Dougan', 'Soundtrack'), ('Who Made Who', 'Who Made Who', 'AC/DC', 'Rock'), ('D.T.', 'Who Made Who', 'AC/DC', 'Rock'), ('Sink the Pink', 'Who Made Who', 'AC/DC', 'Rock'), ('Ride On', 'Who Made Who', 'AC/DC', 'Rock'), ('Chase the Ace', 'Who Made Who', 'AC/DC', 'Rock'), ('Wollt Ihr Das Bett In Flammen Sehen', 'Herzeleid', 'Rammstein', 'Industrial'), ('Der Meister', 'Herzeleid', 'Rammstein', 'Industrial'), ('Weisses Fleisch', 'Herzeleid', 'Rammstein', 'Industrial'), ('Seemann', 'Herzeleid', 'Rammstein', 'Industrial'), ('Du Riechst So Gut', 'Herzeleid', 'Rammstein', 'Industrial'), ('Das Alte Leid', 'Herzeleid', 'Rammstein', 'Industrial'), ('Heirate Mich', 'Herzeleid', 'Rammstein', 'Industrial'), ('Herzeleid', 'Herzeleid', 'Rammstein', 'Industrial'), (\"Baba O'Riley\", \"Who's Next\", 'The Who', 'Rock'), ('Bargain', \"Who's Next\", 'The Who', 'Rock'), (\"Love Ain't for Keeping\", \"Who's Next\", 'The Who', 'Rock'), ('My Wife', \"Who's Next\", 'The Who', 'Rock'), ('The Song Is Over', \"Who's Next\", 'The Who', 'Rock'), ('Getting In Tune', \"Who's Next\", 'The Who', 'Rock'), ('Going Mobile', \"Who's Next\", 'The Who', 'Rock'), ('Behind Blue Eyes', \"Who's Next\", 'The Who', 'Rock'), (\"Won't Get Fooled Again\", \"Who's Next\", 'The Who', 'Rock'), ('Folsom Prison Blues', 'The Legend Of Johnny Cash', 'Johnny Cash', 'Country'), ('I Walk The Line', 'The Legend Of Johnny Cash', 'Johnny Cash', 'Country'), ('Get Rhythm', 'The Legend Of Johnny Cash', 'Johnny Cash', 'Country'), ('Big River', 'The Legend Of Johnny Cash', 'Johnny Cash', 'Country'), ('Guess Things Happen That Way', 'The Legend Of Johnny Cash', 'Johnny Cash', 'Country'), ('Ring Of Fire', 'The Legend Of Johnny Cash', 'Johnny Cash', 'Country'), ('Jackson', 'The Legend Of Johnny Cash', 'Johnny Cash', 'Country'), ('A Boy Named Sue (live)', 'The Legend Of Johnny Cash', 'Johnny Cash', 'Country'), ('You Raise Me Up', 'Hiding Place', 'Selah', 'Gospel & Religious'), (\"Hold On, I'm Coming\", 'On Tap & In the Can', 'The Canettes Blues Band', 'Blues/R&B'), ('Got my Mojo Working', 'On Tap & In the Can', 'The Canettes Blues Band', 'Blues/R&B'), ('Sweet Home Chicago', 'On Tap & In the Can', 'The Canettes Blues Band', 'Blues/R&B'), ('Heavy Love', 'On Tap & In the Can', 'The Canettes Blues Band', 'Blues/R&B'), ('Cold Cold Feeling', 'On Tap & In the Can', 'The Canettes Blues Band', 'Blues/R&B'), ('Lonely Avenue', 'On Tap & In the Can', 'The Canettes Blues Band', 'Blues/R&B'), ('Good Morning Little Schoolgirl', 'On Tap & In the Can', 'The Canettes Blues Band', 'Blues/R&B'), ('Dust My Broom', 'On Tap & In the Can', 'The Canettes Blues Band', 'Blues/R&B'), (\"Gimme' Some Lovin\", 'On Tap & In the Can', 'The Canettes Blues Band', 'Blues/R&B'), ('Everybody Needs Somebody to Love', 'On Tap & In the Can', 'The Canettes Blues Band', 'Blues/R&B'), ('Black Magic Woman', 'On Tap & In the Can', 'The Canettes Blues Band', 'Blues/R&B'), (\"Steppin' Rooster\", 'On Tap & In the Can', 'The Canettes Blues Band', 'Blues/R&B'), ('Our Love is Drifting', 'On Tap & In the Can', 'The Canettes Blues Band', 'Blues/R&B'), (\"Doin' 100\", 'On Tap & In the Can', 'The Canettes Blues Band', 'Blues/R&B'), ('Mother Joy', 'Natural Wonders Music Sampler 1999', 'Matt Ender', 'New Age'), (\"Soldier's Lament\", 'Natural Wonders Music Sampler 1999', 'Matt Ender', 'New Age'), ('Dulaman', 'Natural Wonders Music Sampler 1999', 'Matt Ender', 'New Age'), ('The Arrow', 'Natural Wonders Music Sampler 1999', 'Matt Ender', 'New Age'), ('Spanish Eyes', 'Natural Wonders Music Sampler 1999', 'Matt Ender', 'New Age'), ('Banana Bay', 'Natural Wonders Music Sampler 1999', 'Matt Ender', 'New Age'), ('Aguas De Marco', 'Natural Wonders Music Sampler 1999', 'Matt Ender', 'New Age'), ('Tamborea', 'Natural Wonders Music Sampler 1999', 'Matt Ender', 'New Age'), ('Gone', 'Natural Wonders Music Sampler 1999', 'Matt Ender', 'New Age'), ('The Immigrant', 'Natural Wonders Music Sampler 1999', 'Matt Ender', 'New Age'), ('Pahrump-Big Water', 'Natural Wonders Music Sampler 1999', 'Matt Ender', 'New Age'), (\"Seeker's Quest\", 'Natural Wonders Music Sampler 1999', 'Matt Ender', 'New Age'), ('Floating To Forever', 'Natural Wonders Music Sampler 1999', 'Matt Ender', 'New Age'), ('Open Road', 'Relaxing Jazz', 'Jeff Bailey', 'Jazz'), ('Ruby', 'Yours Truly, Angry Mob', 'Kaiser Chiefs', 'Alternative & Punk'), ('The Angry Mob', 'Yours Truly, Angry Mob', 'Kaiser Chiefs', 'Alternative & Punk'), ('Heat Dies Down', 'Yours Truly, Angry Mob', 'Kaiser Chiefs', 'Alternative & Punk'), ('Highroyds', 'Yours Truly, Angry Mob', 'Kaiser Chiefs', 'Alternative & Punk'), (\"Love's Not A Competition (But I'm Winning)\", 'Yours Truly, Angry Mob', 'Kaiser Chiefs', 'Alternative & Punk'), ('Thank You Very Much', 'Yours Truly, Angry Mob', 'Kaiser Chiefs', 'Alternative & Punk'), ('I Can Do It Without You', 'Yours Truly, Angry Mob', 'Kaiser Chiefs', 'Alternative & Punk'), ('My Kind Of Guy', 'Yours Truly, Angry Mob', 'Kaiser Chiefs', 'Alternative & Punk'), ('Everything Is Average Nowadays', 'Yours Truly, Angry Mob', 'Kaiser Chiefs', 'Alternative & Punk'), ('Learnt My Lesson Well', 'Yours Truly, Angry Mob', 'Kaiser Chiefs', 'Alternative & Punk'), ('Try Your Best', 'Yours Truly, Angry Mob', 'Kaiser Chiefs', 'Alternative & Punk'), ('Retirement', 'Yours Truly, Angry Mob', 'Kaiser Chiefs', 'Alternative & Punk'), ('The Angry Mob [Live From Berlin]', 'Yours Truly, Angry Mob', 'Kaiser Chiefs', 'Alternative & Punk'), ('I Like To Fight', 'Yours Truly, Angry Mob', 'Kaiser Chiefs', 'Alternative & Punk'), ('From The Neck Down', 'Yours Truly, Angry Mob', 'Kaiser Chiefs', 'Alternative & Punk'), ('The Wisdom of Crowds: Why the Many Are Smarter than the Few Part 1 of 3', 'The Wisdom of Crowds: Why the Many Are Smarter than the Few (Abridged Nonfiction)', 'James Surowiecki', 'Business'), ('The Wisdom of Crowds: Why the Many Are Smarter than the Few Part 2 of 3', 'The Wisdom of Crowds: Why the Many Are Smarter than the Few (Abridged Nonfiction)', 'James Surowiecki', 'Business'), ('The Wisdom of Crowds: Why the Many Are Smarter than the Few Part 3 of 3', 'The Wisdom of Crowds: Why the Many Are Smarter than the Few (Abridged Nonfiction)', 'James Surowiecki', 'Business'), ('The Wisdom of Crowds: Why the Many Are Smarter Than the Few (Unabridged) Part 1 of 5', 'The Wisdom of Crowds: Why the Many Are Smarter Than the Few (Unabridged)', 'James Surowiecki', 'Nonfiction'), ('The Wisdom of Crowds: Why the Many Are Smarter Than the Few (Unabridged) Part 2 of 5', 'The Wisdom of Crowds: Why the Many Are Smarter Than the Few (Unabridged)', 'James Surowiecki', 'Nonfiction'), ('The Wisdom of Crowds: Why the Many Are Smarter Than the Few (Unabridged) Part 3 of 5', 'The Wisdom of Crowds: Why the Many Are Smarter Than the Few (Unabridged)', 'James Surowiecki', 'Nonfiction'), ('The Wisdom of Crowds: Why the Many Are Smarter Than the Few (Unabridged) Part 4 of 5', 'The Wisdom of Crowds: Why the Many Are Smarter Than the Few (Unabridged)', 'James Surowiecki', 'Nonfiction'), ('The Wisdom of Crowds: Why the Many Are Smarter Than the Few (Unabridged) Part 5 of 5', 'The Wisdom of Crowds: Why the Many Are Smarter Than the Few (Unabridged)', 'James Surowiecki', 'Nonfiction'), ('Misty', 'Moonlight And Love Songs', 'David Osborne', 'Classical'), ('My Funny Valentine', 'Moonlight And Love Songs', 'David Osborne', 'Classical'), (\"Since I Don't Have You\", 'Moonlight And Love Songs', 'David Osborne', 'Classical'), ('Tenderly', 'Moonlight And Love Songs', 'David Osborne', 'Classical'), ('When I Fall In Love', 'Moonlight And Love Songs', 'David Osborne', 'Classical'), ('Unforgettable', 'Moonlight And Love Songs', 'David Osborne', 'Classical'), ('The Way You Look Tonight', 'Moonlight And Love Songs', 'David Osborne', 'Classical'), (\"I've Grown Accustomed To Your Face\", 'Moonlight And Love Songs', 'David Osborne', 'Classical'), ('Smoke Gets In Your Eyes', 'Moonlight And Love Songs', 'David Osborne', 'Classical'), ('Fly Me To The Moon', 'Moonlight And Love Songs', 'David Osborne', 'Classical'), ('Unchained Melody', 'Moonlight And Love Songs', 'David Osborne', 'Classical'), ('These Foolish Things Remind Me Of You', 'Moonlight And Love Songs', 'David Osborne', 'Classical'), ('La Vie En Rose', 'Moonlight And Love Songs', 'David Osborne', 'Classical'), ('As Time Goes By', 'Moonlight And Love Songs', 'David Osborne', 'Classical'), ('Pilot', 'Fairly Legal, Season 1', 'Fairly Legal', 'Drama'), ('hte postal service - the impor', 'unreleased demo', 'The Postal Service', 'Electronic'), ('Winter Wonderland', 'Seasons Greatings', 'Bing Crosby', 'Holiday'), ('Jingle Bells', 'Seasons Greatings', 'Bing Crosby', 'Holiday'), ('White Christmas', 'Seasons Greatings', 'Bing Crosby', 'Holiday'), ('The Christmas Song', 'Seasons Greatings', 'Bing Crosby', 'Holiday'), ('Little Drummer Boy', 'Seasons Greatings', 'Bing Crosby', 'Holiday'), ('Silent Night', 'Seasons Greatings', 'Bing Crosby', 'Holiday'), ('Let It Snow', 'Seasons Greatings', 'Bing Crosby', 'Holiday'), ('Mistletoe And Holly', 'Seasons Greatings', 'Bing Crosby', 'Holiday'), ('O Holy Night', 'Seasons Greatings', 'Bing Crosby', 'Holiday'), ('Have Yourself A Merry Little Christmas', 'Seasons Greatings', 'Bing Crosby', 'Holiday'), ('Do You Hear What I Hear', 'Seasons Greatings', 'Bing Crosby', 'Holiday'), (\"I'll Be Home For Christmas\", 'Seasons Greatings', 'Bing Crosby', 'Holiday'), ('Voices', 'The Sickness', 'Disturbed', 'Alternative'), ('The Game', 'The Sickness', 'Disturbed', 'Alternative'), ('Stupify', 'The Sickness', 'Disturbed', 'Alternative'), ('Down With The Sickness', 'The Sickness', 'Disturbed', 'Alternative'), ('Violence Fetish', 'The Sickness', 'Disturbed', 'Alternative'), ('Fear', 'The Sickness', 'Disturbed', 'Alternative'), ('Numb', 'The Sickness', 'Disturbed', 'Alternative'), ('Want', 'The Sickness', 'Disturbed', 'Alternative'), ('Conflict', 'The Sickness', 'Disturbed', 'Alternative'), ('Shout 2000', 'The Sickness', 'Disturbed', 'Alternative'), (\"Droppin' Plates\", 'The Sickness', 'Disturbed', 'Alternative'), ('Meaning Of Life', 'The Sickness', 'Disturbed', 'Alternative'), ('Eben Upton: Raspberry Pi', 'Computing Conversations', 'IEEE Computer Society', 'Podcast'), ('Andrew Tanenbaum: Writing the Book on Networks', 'Computing Conversations', 'IEEE Computer Society', 'Podcast'), ('Massimo Banzi: Building Arduino', 'Computing Conversations', 'IEEE Computer Society', 'Podcast'), ('John C. Hollar: History of Computing', 'Computing Conversations', 'IEEE Computer Society', 'Podcast'), ('Bob Metcalfe: Ethernet at Forty', 'Computing Conversations', 'IEEE Computer Society', 'Podcast'), ('Gordon Bell: Building Blocks of Computing', 'Computing Conversations', 'IEEE Computer Society', 'Podcast'), ('Ian Horrocks: Standardizing OWL', 'Computing Conversations', 'IEEE Computer Society', 'Podcast'), ('Katie Hafner: The Origins of the Internet', 'Computing Conversations', 'IEEE Computer Society', 'Podcast'), ('Larry Smarr: Building Mosaic', 'Computing Conversations', 'IEEE Computer Society', 'Podcast'), ('Len Kleinrock: The Theory of Packets', 'Computing Conversations', 'IEEE Computer Society', 'Podcast'), ('Mitchell Baker: The Mozilla Foundation', 'Computing Conversations', 'IEEE Computer Society', 'Podcast'), ('Pooja Sankar: Building the Piazza Collaboration System', 'Computing Conversations', 'IEEE Computer Society', 'Podcast'), ('Van Jacobson: Content-Centric Networking', 'Computing Conversations', 'IEEE Computer Society', 'Podcast'), ('The Apache Software Foundation', 'Computing Conversations', 'IEEE Computer Society', 'Podcast'), ('A Brief History of Packets', 'Computing Conversations', 'IEEE Computer Society', 'Podcast'), ('Discovering JavaScript Object Notation', 'Computing Conversations', 'IEEE Computer Society', 'Podcast'), ('Inventing PHP', 'Computing Conversations', 'IEEE Computer Society', 'Podcast'), ('Monash Museum of Computing History', 'Computing Conversations', 'IEEE Computer Society', 'Podcast'), ('The Rise of JavaScript', 'Computing Conversations', 'IEEE Computer Society', 'Podcast'), ('Joseph Hardin: NCSA Mosaic', 'Computing Conversations', 'IEEE Computer Society', 'Podcast'), (\"Len Kleinrock on the Internet's First Two Packets\", 'Computing Conversations', 'IEEE Computer Society', 'Podcast'), ('Doug Van Houweling on Building the NSFNet', 'Computing Conversations', 'IEEE Computer Society', 'Podcast'), ('Computing Conversations: Nathaniel Borenstein on MIME', 'Computing Conversations', 'IEEE Computer Society', 'Podcast'), (\"You Don't Mess Around With Jim\", 'Classic Hits', 'Jim Croce', 'Folk'), ('Andrew S. Tanenbaum on MINIX', 'Computing Conversations', 'IEEE Computer Society', 'Podcast'), ('Computing Conversations: Elizabeth Fong on SQL Standards', 'Computing Conversations', 'IEEE Computer Society', 'Podcast'), ('Nii Quaynor on Bringing the Internet to Africa', 'Computing Conversations', 'IEEE Computer Society', 'Podcast'), ('PHP-09-Transactions.mp3', \"SI 664 W14's official Podcast.\", 'Created by Sakai', 'Podcast'), ('PHP-01-Intro.mp3', \"SI 664 W14's official Podcast.\", 'Created by Sakai', 'Podcast'), ('PHP-02-Install.mp3', \"SI 664 W14's official Podcast.\", 'Created by Sakai', 'Podcast'), ('PHP-04-Expressions.mp3', \"SI 664 W14's official Podcast.\", 'Created by Sakai', 'Podcast'), ('PHP-05-Functions.mp3', \"SI 664 W14's official Podcast.\", 'Created by Sakai', 'Podcast'), ('PHP-06-Strings.mp3', \"SI 664 W14's official Podcast.\", 'Created by Sakai', 'Podcast'), ('PHP-12-Sessions.mp3', \"SI 664 W14's official Podcast.\", 'Created by Sakai', 'Podcast'), ('PHP-06-Objects.mp3', \"SI 664 W14's official Podcast.\", 'Created by Sakai', 'Podcast'), ('SI664-13-JSON-A.mp3', \"SI 664 W14's official Podcast.\", 'Created by Sakai', 'Podcast'), ('PHP-13-JavaScript.mp3', \"SI 664 W14's official Podcast.\", 'Created by Sakai', 'Podcast'), ('PHP-09-Database-Design.mp3', \"SI 664 W14's official Podcast.\", 'Created by Sakai', 'Podcast'), ('PHP-Tsugi-Install.mp3', \"SI 664 W14's official Podcast.\", 'Created by Sakai', 'Podcast'), ('PHP-10-MySQL-PDO.mp3', \"SI 664 W14's official Podcast.\", 'Created by Sakai', 'Podcast'), ('PHP-08-MySQL.mp3', \"SI 664 W14's official Podcast.\", 'Created by Sakai', 'Podcast'), ('SI664-13-JSON-B.mp3', \"SI 664 W14's official Podcast.\", 'Created by Sakai', 'Podcast'), ('IMS-Learning-Tools-Interoperability.mp3', \"SI 664 W14's official Podcast.\", 'Created by Sakai', 'Podcast'), ('An Interview with Don Waters', 'CNI Event Coverage', 'Matt Pasiewicz', 'Speech'), ('PY4INF-11-Regex.mp3', \"Python for Informatics's official Podcast.\", 'Recording by Dr. Chuck', 'Podcast'), ('PY4INF-10-Tuples.mp3', \"Python for Informatics's official Podcast.\", 'Recording by Dr. Chuck', 'Podcast'), ('PY4INF-09-Dictionaries.mp3', \"Python for Informatics's official Podcast.\", 'Recording by Dr. Chuck', 'Podcast'), ('PY4INF-08-Lists.mp3', \"Python for Informatics's official Podcast.\", 'Recording by Dr. Chuck', 'Podcast'), ('PY4INF-07-Files.mp3', \"Python for Informatics's official Podcast.\", 'Recording by Dr. Chuck', 'Podcast'), ('PY4INF-06-Strings.mp3', \"Python for Informatics's official Podcast.\", 'Recording by Dr. Chuck', 'Podcast'), ('PY4INF-05-Iterations.mp3', \"Python for Informatics's official Podcast.\", 'Recording by Dr. Chuck', 'Podcast'), ('PY4INF-04-Functions.mp3', \"Python for Informatics's official Podcast.\", 'Recording by Dr. Chuck', 'Podcast'), ('PY4INF-03-Conditional.mp3', \"Python for Informatics's official Podcast.\", 'Recording by Dr. Chuck', 'Podcast'), ('PY4INF-02-Expressions.mp3', \"Python for Informatics's official Podcast.\", 'Recording by Dr. Chuck', 'Podcast'), ('PY4INF-01-Intro.mp3', \"Python for Informatics's official Podcast.\", 'Recording by Dr. Chuck', 'Podcast'), ('Py4Inf-11-Regex.mp3', \"Python for Informatics's official Podcast.\", 'Recording by Dr. Chuck', 'Podcast'), ('Py4Inf-10-Tuples.mp3', \"Python for Informatics's official Podcast.\", 'Recording by Dr. Chuck', 'Podcast'), ('Py4Inf-09-Dictionaries.mp3', \"Python for Informatics's official Podcast.\", 'Recording by Dr. Chuck', 'Podcast'), ('Py4Inf-08-Lists.mp3', \"Python for Informatics's official Podcast.\", 'Recording by Dr. Chuck', 'Podcast'), ('Py4Inf-07-Files.mp3', \"Python for Informatics's official Podcast.\", 'Recording by Dr. Chuck', 'Podcast'), ('Py4Inf-06-Strings.mp3', \"Python for Informatics's official Podcast.\", 'Recording by Dr. Chuck', 'Podcast'), ('Py4Inf-05-Iterations.mp3', \"Python for Informatics's official Podcast.\", 'Recording by Dr. Chuck', 'Podcast'), ('Py4Inf-04-Functions.mp3', \"Python for Informatics's official Podcast.\", 'Recording by Dr. Chuck', 'Podcast'), ('Py4Inf-03-Conditional.mp3', \"Python for Informatics's official Podcast.\", 'Recording by Dr. Chuck', 'Podcast'), ('Py4Inf-02-Expressions.mp3', \"Python for Informatics's official Podcast.\", 'Recording by Dr. Chuck', 'Podcast'), ('Py4Inf-01-Intro.mp3', \"Python for Informatics's official Podcast.\", 'Recording by Dr. Chuck', 'Podcast'), ('Ian Foster on the Globus Project', 'Computing Conversations', 'IEEE Computer Society', 'Podcast'), ('Khan Academy and Computer Science', 'Computing Conversations', 'IEEE Computer Society', 'Podcast'), ('Guido van Rossumon the Early Years of Python', 'Computing Conversations', 'IEEE Computer Society', 'Podcast'), ('Guido van Rossum on the Modern Era of Python', 'Computing Conversations', 'IEEE Computer Society', 'Podcast'), ('John Resig on Building jQuery', 'Computing Conversations', 'IEEE Computer Society', 'Podcast'), ('Roy T. Fielding on Understanding the REST Style', 'Computing Conversations', 'IEEE Computer Society', 'Podcast'), ('Anil Jain: 25 Years of Biometric Recognition', 'Computing Conversations', 'IEEE Computer Society', 'Podcast'), ('Waste Management', 'Undercover Boss, Season 1', 'Undercover Boss', 'Reality TV'), ('Sleep Like a Baby Tonight', 'Songs of Innocence', 'U2', 'Rock'), ('Evergreen', 'Warpaint', 'The Black Crowes', 'Rock'), ('Oh Josephine', 'Warpaint', 'The Black Crowes', 'Rock'), (\"Movin' On Down the Line\", 'Warpaint', 'The Black Crowes', 'Rock'), ('Wounded Bird', 'Warpaint', 'The Black Crowes', 'Rock'), ('Volcano', 'Songs of Innocence', 'U2', 'Rock'), ('Wee Who See the Deep', 'Warpaint', 'The Black Crowes', 'Rock'), ('Cedarwood Road', 'Songs of Innocence', 'U2', 'Rock'), ('Locust Street', 'Warpaint', 'The Black Crowes', 'Rock'), ('Raised By Wolves', 'Songs of Innocence', 'U2', 'Rock'), ('The Troubles', 'Songs of Innocence', 'U2', 'Rock'), ('This Is Where You Can Reach Me Now', 'Songs of Innocence', 'U2', 'Rock'), (\"God's Got It\", 'Warpaint', 'The Black Crowes', 'Rock'), ('California (There Is No End to Love)', 'Songs of Innocence', 'U2', 'Rock'), ('Every Breaking Wave', 'Songs of Innocence', 'U2', 'Rock'), ('Interview With the Onion News Network', 'Onion News Network, Season 1', 'Onion News Network', 'Comedy'), ('Song for Someone', 'Songs of Innocence', 'U2', 'Rock'), ('The Miracle (Of Joey Ramone)', 'Songs of Innocence', 'U2', 'Rock'), ('Iris (Hold Me Close)', 'Songs of Innocence', 'U2', 'Rock'), ('Goodbye Daughters of the Revolution', 'Warpaint', 'The Black Crowes', 'Rock'), ('Walk Believer Walk', 'Warpaint', 'The Black Crowes', 'Rock'), ('Choice Hotels International', 'Undercover Boss, Season 2', 'Undercover Boss', 'Reality TV'), ('Microchip', 'YouTube Audio Library', 'Jason Farnham', 'Dance & Electronic'), ('Anant Agarwal', 'Computing Conversations', 'IEEE Computer Society', 'Podcast')]\n"
500 ]
501 }
502 ],
503 "source": [
504 "c.execute('''\n",
505 " SELECT Track.title, Album.title, Artist.name, Genre.name FROM Track JOIN Album JOIN Artist JOIN Genre ON Track.album_id = Album.id AND Album.artist_id = Artist.id AND Track.genre_id = Genre.id\n",
506 "''')\n",
507 "print(c.fetchall())"
508 ]
509 },
510 {
511 "cell_type": "code",
512 "execution_count": null,
513 "metadata": {},
514 "outputs": [],
515 "source": []
516 }
517 ],
518 "metadata": {
519 "kernelspec": {
520 "display_name": "Python 3",
521 "language": "python",
522 "name": "python3"
523 },
524 "language_info": {
525 "codemirror_mode": {
526 "name": "ipython",
527 "version": 3
528 },
529 "file_extension": ".py",
530 "mimetype": "text/x-python",
531 "name": "python",
532 "nbconvert_exporter": "python",
533 "pygments_lexer": "ipython3",
534 "version": "3.6.5"
535 }
536 },
537 "nbformat": 4,
538 "nbformat_minor": 2
539}