Worked Example- Tracks.py (Chapter 15)

Hello, everybody and welcome to Python for Everybody. We’re going to do some code walk-throughs, actually running some code. And if you want to follow along with the code, the sample code is here in the materials of my Python for Everybody website. So you can take a look at that. So the code we’re going to look at is from the database chapter, and we’re going to look at tracks.py. So a lot of the lectures that I give in this database chapter are just about SQL. And this is really about SQL and Python, so I’ll go through this in some detail. So the code that I’m going through is in tracks, there’s also tracks.zip that you can grab that has these two things. It’s got this, Library.xml file which you can export from your, if you have iTunes, you can export this, or you can just play with my iTunes. And so this is also going to review how to read XML. So we’re going to actually pull all this data. And this XML that Apple produces out of iTunes, is a little weird, in that it’s kind of key values, and so you see key value pairs. And it even uses the word dictionary. And so it’s like, I’m going to make a dictionary that has this, then a dictionary within a dictionary. This, to me, would be so nice if it was JSON because it’s really a list of dictionaries. This is a dictionary, then another dictionary, then another dictionary, and then the key for that dictionary. And it’s a weird, weird, format, but we’ll write some Python to be able to read it. And so you export that from iTunes and you can use my file or you can use your file, might be more fun to use your file. So here’s tracks.py. We’re going to do some XML. And so we import that, we’re going to import sqlite3 because we’re going to talk to the database and then we’re going to make the database connection. And in this, once we run this, you’ll see that that file will exist, and so right now, if I’m in my tracks data, that file doesn’t exist. But what we’ll see is this is going to actually create it. Now remember that we have a cursor which is sort of like a file handle. It’s really a database handle, as it were. And in order to sort of bootstrap this nicely, we are going because this code is going to run all the time. It’s going to run and read all of the library docs XML and later things, well, we won’t wipe out the database every time. And so I’m executing a script which is a series of SQL commands separated by semi colons. So I’m going to throw away the artist table, album table, and track table. Very similar to the stuff we covered in lecture. And then I’m going to do the Create Table, and I’m doing this all automatically. And so, and you’ll notice this is a triple coded string, so this is just one big long string here. And it happens to know that it’s SQL, thank you Adam for that. And so, it creates all these things. Now, it’s not quite as rich as the data model we built because there’s no genres in here. And so it’s artist, album, track, and then there’s a foreign key for album ID, and a foreign key for artist ID, which is sort of a subset of what we’re doing. When that’s done, that actually creates all the tables, and we’ll see those in a moment once we run the code. Then it asks for a file name for the XML, right? And so that’s what that is. And we’re going to, I wrote a function that does a lookup, that it’s really weird because if you look at these files, like in this dictionary, there is a key, right? And so the key of this dictionary, this really could or should have been a key value pair. So there’s this weird thing where the key for an object is inside of the object, and so we’re going to grab for all, we’re going to loop through all of the children in this outer dictionary and find a child.tag that has a particular key. And so you’ll see how this works. And this was something I was going to use over and over again. And so the first thing we’re going to do is we’re going to just parse the string, and this is the string. And then this, of course, is an XML ET object. And then we’re going to do a find all. And so this shows how the find all, we’re going to go the third level dictionaries. We want to see all of the tracks, and so we have a dictionary, and a dictionary, and a dictionary. And so what we want is all of these guys, right? All those guys right there, right? Track ID, so we’re going to get a list of all those. That’ll be the first one. This will be the second one because the find all says go to the, Find the dictionary key, then a dictionary tag within that, and dictionary tag, and then we’ll tell how many things we got. And then we’re going to loop through an entry is going to iterate through each of these, and see, we will get our name and our artist, Another One Bites the Dust, Queen. And away we go, and the next time through the loop, we’ll hit this one, ok? So then, what we’re going to do is if we’re going to go through all those entries, and if there’s no track ID, that’s this track ID field, where are you hiding, track ID, we don’t have that. We’re going to continue. And then we’re going to look up the name, artist, album, play count, rating, and total time, okay? And so here they are, play count. A lot of those things that we had in the sample lecture that I did. And we’re going to look those things up, and we’re going to do some sanity checking if we didn’t get a name or an artist or an album, we’re going to continue. We’re going to print them out and then we are going to ask for git. Remember how you have to get the primary key of a row so you can use it. So the way we’re going to do this is we’re going to do an insert or ignore. And so this or ignore basically says, because I said that the artist name, go up here, I said the artist name is unique. Which means if I try to attempt to insert the same artist twice, it will blow up. Okay, because I put this constraint on that. Except, when I say insert or ignore, that basically says, hey, if it’s already there, don’t insert it again. So what I’m doing here is INSERT OR IGNORE INTO Artist. So this is putting a new row into the artist table, unless there’s already a row in that artist table. And this syntax right here, the question mark is sort of where this artist variable goes. And this is tuple. But I have to sort of put this comma in to force it to be a tuple. So this is a way you have a one tuple. And then what I need to know is I need to know the primary key of this particular artist row. Now, this line may or may not having actually done the insert. And so I need to know what the ID for that particular artist is. So I do a select ID from artist where name equals. Now it either was already there, or I’m getting it fresh and brand new. So I do an artist ID equals, I fetch one row and it’s going to be the first thing given that I only selected ID. And so this artist ID is going to be the ID. Now, now I have the foreign key for the album title, right? And so now, I’m going to insert in the title, artist id. This is the foreign key for the artist table. And I’ve got this value that I just moments ago retrieved and I’ve got the album title. But, this also is insert of ignore because and now if you look, I have unique on the album title. Yup, unique’s on the album title. So that will do nothing. It doesn’t blow up, or ignore says don’t blowup, just do nothing. Because this next line is going to select it. And I grab the album’s foreign key. For either the existing row or the new row, and then I’m going to insert or replace. And so, what this basically says is, if the unique constraint would be violated, this turns into an update. Now, not all SQLs have this, but SQLite has this that basically says insert or replace. Some SQL are totally standard, some things we do like this select statement is a totally standard part of SQL. Then the insert is totally standard but insert or replace, and insert or ignore is not totally standard. But that’s okay, it works for SQLite which is what we’re doing, and so we have the title, album id, length, rating, and count and then we have a tuple that does all that stuff and of course the title is unique, right? The title is unique in the track table as well and so we’ve inserted that. So the clever bit here is dealing with new or existing names in these three lines. And we see that pattern twice here where we’re doing that, okay? So there’s not much left to do except to run this code. Hopefully it runs. Python3 tracks.py and Library.xml, whoosh. Okay, so we found 404 of those dictionaries, 3D dictionaries. And now it’s starting to insert them, insert them, insert them. And we can take a look at so we can do an ls minus l or during on Windows, we’ll see that we made a track database. We extracted the data from this library and we made a track database, and we have all these foreign keys. So let’s go and take a look at the SQLite browser, File, Open database, trackdb.sqlite. And come on up, or do you hide? I got it minimized so there you go. Let’s look at the database structure. We have an album. This is the structure, artist and track. We don’t have no genre. And this is all like we did it by hand, except Python did all this work for us. If we take a look at the data, and we start from the outside in. We have the artist names and their primary keys, right, there’s the artist names and primary keys. And then we have the albums, and we have the artist IDs. Seeing the artist IDs, how nice those are. So we have the primary key here, and the foreign key there, and then we have the title. And if we get to the track, We have the album ID, and away we go. So, if I was clever, I could be able to type some SQL. Great. If I was smart, I’d had this in a paste buffer. So, Select. Select track.title, album.title, artist.name I think. Artist has names and albums have titles. Yes. Okay, so I can do that from track, join, album. Oops. Album, join. Let me make that a little bigger. Bring that over here. Album, track, join, album, join, artist. I need a on clause and I can say Track.album, id = Album. Notice how I know the name that I name these things, and album.artist, this is so great when you use a naming convention. Artist.id, golly I think that might work. So let’s just see what we get when we type that into the SQL box here. Execute SQL. Run. Yey, I got it right the first time. All right, so that’s basically my nice little joined up track list. I’m so happy that I got that right the first time. Okay well, so you can play with this yourself. Play with these tracks. Maybe make an export of your own iTunes library and run it with that. And so I hope that you found this particular bit of code useful, okay? Cheers.