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