Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Well done!
You have completed Using Databases in Python!
You have completed Using Databases in Python!
Preview
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
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
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
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up