[MUSIC] Hello, everybody, welcome to Python for Everybody. We're going to do some code walkthroughs 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 is 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, could 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. So you export that from iTunes, and you can use my file or you can use your file. It might be more fun to user your file. And 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 want to talk to the database. And then we're going to make a 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 actually going to 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 library docs XML. In later things, 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 semicolons. 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 you'll notice this is a triple-quoted string, so this is just one big, long string here. And it happens to know that it's SQL, thank you, Atom, 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 artists, 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. And so 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. 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've should've been a key value pair. 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 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 this sting. And this is the string. And then this, of course, is an XML ET object. And now we're going to say, 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, Find the dictionary key, then a dictionary tag within that, and a dictionary tag. And then it will tell how many thing we got. And then we're going to loop through, and entry is going to iterate through each of these. And see we'll get our name, and our artist, Another One Bites the Dust, the Queen, and away we go. And then the next time to the loop we'll hit this one, okay? So then what we're going to do is we're going to go through all those entries, and if there is no track ID, and if 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 lookup 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 or get, 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 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 is 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 a tuple, but I have to sort of put the 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, if it actually done the insert. And so I need to know what the ID for that articular artist is. So I do a select id FROM Artist WHERE name =. 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 to the artist table. And I got this value that I just moments ago retrieved, and I got the album title. But this also is insert or ignore because, and now if you look, I have unique on the album title. Yeah, unique's on the album title, so that'll do nothing. It doesn't blow up or ignore, it says, don't blow up, 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 SQLs 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, an 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, all 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 [LAUGH] there's not much left to do except run this code. Hopefully it runs. python3 tracks.py, and Library.xml, whoosh! Okay, so that is my, so we found 404 of those dictionaries, 3D dictionaries. And now it's starting to insert them, insert them, insert them, insert them. And we can take a look at, so we can do an ls -l or Duron windows, we'll see that we made a track database. We extracted this data from the library and we made a track database, and we have all these foreign keys. So let's go take a look at the SQLite browser, File > Open Database > trackdb.sqlite. And come on up, where'd 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 have no genre 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, [SOUND] 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. Artists has names, and albums have titles, yes. Okay, so I can do that. FROM Track JOIN Album, oops, Album JOIN, make that little bigger. Then over here, Album Track JOIN Album JOIN Artist. Now I need an ON clause, and I can say Track.album_, id = Album. Notice how I know the name that I named these things. AND Album.artist, this is so great when you use a naming convention, Artist.id, I think that might work. So let's just see what get when we type that into the SQL box here, Execute SQL. Run, yay, 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 this 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. [MUSIC]
