Queries Are Your Friend

Our models have five really useful methods that we'll be using a lot. 0:00 Create, adds a new record to the table. 0:04 Select, let's us pick rows out of the table to use. 0:06 Save, will update an existing row in the database. 0:10 Get, will fetch a single record from the database. 0:13 And finally, delete_instance will delete a row from a table. 0:15 There are many other methods on Peewee models but I leave them for 0:19 you to read about in the docs linked in the teacher's notes. 0:22 Okay. So let's setup some of the info that we 0:25 want to have inserted into our database. 0:28 Like I said, we're gonna use a dictionary to hold on to all the data. 0:32 And the reason for 0:37 that is because a dictionary is easy to look back to, it's easy to update. 0:38 We can play around with it. 0:43 We've all had a lot of experiences with databases or dictionaries. 0:44 So, not anything crazy. 0:48 So, I'm actually gonna make a list for all the students and 0:50 then a dictionary per student. 0:55 So, let's make a username and then I'm gonna say, 0:57 Kennethlove, and we'll say points and 4888. 1:04 All right. 1:10 And then username. 1:11 Chalkers, points. 1:14 [BLANK_AUDIO]. 1:17 Username. 1:22 [BLANK_AUDIO] 1:23 If you've explored the collections library, 1:31 then this is actually a really great place to use something like named tuple. 1:35 This is also something where we probably could have done a dictionary where 1:44 each key is the username, and the points are their value. 1:50 But this is a little bit more obvious of how we're going to 1:55 put things into the database in the end. 1:57 And [INAUDIBLE]. 2:04 Dave has a lot of points. 2:06 All right. 2:09 So, here's our dictionary or our list of dictionaries, rather. 2:10 So you can see, we've got two keys for every one of them, we've got username key 2:15 and we've got a points key for each one of them, and then they have a value that has 2:18 their username or their points, depending on which key it is. 2:22 So, let's add a function that will go through all of these, 2:26 and add them to our database, and let's call it add students. 2:31 And what we wanna do in here is, we wanna go through each student in students, and 2:39 we wanna use our modal, which is called Student, and I'm gonna call create. 2:45 And we're gonna say the user name is equal to the student username. 2:49 And the points is equal to the student points. 2:57 All right, so now let's come down here to our if name block and 3:03 let's add that in, so down here we wanna call add students. 3:11 'Kay. 3:17 So, let's try running this. 3:19 Python students.py. 3:22 Okay, no problems. 3:27 This is all great and wonderful until we run it again. 3:28 Look at this. We've got all, we got tons and 3:35 tons of these integrity errors. 3:37 Now, why did we get those? 3:39 We got those because, remember we made our username unique. 3:40 And what we're doing down here is a create. 3:46 We're saying, put in a new row for every single one of these. 3:50 So the first time through it puts in that new row for Kenneth Love. 3:53 And everything's cool, everything's copacetic. 3:57 The second time it comes through, it says hey, put in a row for 3:59 Kenneth Love, and the database says, no wait, I can't, I already have 4:02 a row where the username is Kenneth Love, I can't do a new one, because it's unique. 4:07 So let's change this, and 4:12 the way that we're gonna change this, is we're gonna do a tri-block. 4:13 Oops. 4:18 So we can indent that. 4:23 So we're gonna try to insert the the student. 4:24 And you notice that we're getting this integrity error down here in our 4:30 error messages. 4:34 So we're gonna except IntegrityError. 4:35 So if we get an integrity error, then let's do student_record. 4:39 Oops, student_record equals Student.get, username equals student, username. 4:44 Right, so we wanna get that student, it must exist, so we wanna get that student. 4:55 And then we want to do student_record.points equals 5:01 student points. 5:06 So set their points to the new one, and then student_record.save. 5:08 So, get the student out of the database. 5:14 Change the points to whatever the points is now. 5:16 If it's changed, it may not have changed. 5:19 But just set it to whatever it is now, and then save the record. 5:21 Now we can make this smarter, 5:24 as I just mentioned, by making it check to see if the points is different. 5:26 I'm gonna leave that up to you. 5:31 But that would be a good thing to do. 5:32 All right, so now, let's add another new function. 5:37 Actually, let's go ahead and test this one first. 5:42 Let's do Save. 5:45 Let's come back down here. 5:46 We should be able to run this again. 5:47 No complaints, great. 5:48 'Kay, so now let's make a new function that is 5:52 going to get our top performing student. 5:55 I want the student that has the, the highest number of points. 5:58 So we'll call this top student and 6:02 we'll say student equal student, our modal.select. 6:06 So what that does is that gets all of the students. 6:11 We've got all of the student records that are in the database, 'kay? 6:15 We don't want all of them, we just want the best one. 6:18 So let's do an order by, so let's sort them and 6:21 we're gonna sort them by the points attribute, right? 6:25 We wanna sort by whoever has the most points. 6:31 And we wanted to do this in descending order, so 6:34 we want the biggest number first, the smallest number last. 6:37 Ascending would be the other way around where we have the smallest number first 6:41 and the biggest number last. 6:44 So descending, it gets smaller. 6:45 Ascending, it gets bigger. 6:48 So let's add one more thing to this. 6:51 What we can do is we can say .get on the end of this. 6:54 And that's going to only get us the first record that comes back. 6:58 So we've got all the students. 7:03 Sort them all by their points so 7:06 that the biggest number comes first, and then get the first record. 7:08 I know there's a lot there, but that's what it is. 7:12 Okay. 7:16 So, let's return a student.username. 7:16 Actually, you know what? 7:21 Let's just return student. 7:21 Okay? 7:24 And so, what I wanna do is down here, after I add the students, 7:25 I want to print our top student right now is. 7:30 And then I'm gonna do a format here where I call top_student. 7:38 And so let's actually go here and do 0.username. 7:44 Cuz zero is whatever comes back from this, and 7:52 we know that has a username attribute so we're gonna print out 0.username. 7:56 All right, so moment of truth, let's give this a try. 8:01 oh. 8:09 I forgot a parenthesis. 8:11 All right, now let's try it. 8:13 Our top student right now is Dave Mcfarland. 8:14 Sweet. 8:18 Dave's got a lot of points. 8:19 Let's go change that. 8:22 Dave's got a whole bunch of points, but I wanna be the top student. 8:23 So, whatever Dave has, plus 1. 8:28 Okay, so now if I run this, it should come back as me being the top student. 8:33 Great, it did. 8:38 Now why did it change to me? 8:39 Just in case you didn't catch this part yet, it's because of this. 8:41 We had the integrity error, 8:45 there's already a row for me, so now we grab my student record, 8:46 update my points to whatever's in the dictionary, and we save it again. 8:50 Then when we run top student, it's gonna select my record. 8:54 So that's pretty awesome. 8:58 We're able to get our students from the dictionary. 8:59 Stick them into the database. 9:01 Select them, order them, pick out just one top one. 9:02 And then we were even able to update and change those records afterwards. 9:06 This work, creating, reading, updating, and deleting records is known as CRUD. 9:10 It's the backbone of pretty much any application that deals with a lot of data. 9:15 I can see you're getting antsy to build something real already. 9:19 Well, that's great because our next step is to start the real reason we're here. 9:21 A command line diary application. 9:25 We'll do that in the next stage. 9:27