Preview
Video Player
00:00
00:00
Queries Are Your Friend
9:28Now 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.
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 model, 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