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 Relational Databases With SQLAlchemy!
You have completed Relational Databases With SQLAlchemy!
Preview
Practice updating and deleting entries in a relational database.
Queries
lion = models.session.query(models.Animal).filter(models.Animal.name=="lion").first()
wombat = models.session.query(models.Animal).filter(models.Animal.name=="wombat").first()
lion_log = models.session.query(models.Logbook).filter(models.Logbook.animal_id==1).first()
seal_log = models.session.query(models.Logbook).filter(models.Logbook.animal_id==2).first()
SQLAlchemy Docs
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
Hello again.
0:00
In this video, we will update entries and
delete entries in our tables to see
0:01
how these actions may change
when working with relationships.
0:06
If you closed your project, don't forget
to reactivate your virtual environment and
0:10
then pop back into the Python shell.
0:15
And go ahead and import models.
0:19
Let's create some variables to
hold the entries we have so
0:23
far to make things a bit easier.
0:26
I'm gonna run a few queries that are also
in the teacher's notes to speed things up.
0:29
So lion =, and
0:34
I'm gonna copy-paste to make this
a little bit easier on myself as well.
0:35
Models.session.query(models.Animal).filte-
r(models.Animal.name=="lion").
0:44
And then you'll wanna put this dot first
in there, otherwise it's gonna give you
0:52
a query object instead of the actual
thing you're looking for.
0:56
So we hit that and we check lion.name,
just to make sure.
0:59
Lion, perfect.
1:02
Okay, I'm gonna go ahead and
copy-paste the rest of these.
1:03
I'm pulling it straight from
the teacher's notes just like y'all,
1:06
just to speed things up.
1:09
So the other animal we had was wombat.
1:10
wombat.name, just to check, perfect.
1:13
I'm just gonna grab the first lion log, I
know that we put two in the database, but
1:17
I'm just gonna grab the first one.
1:21
.notes, great pouncer, perfect.
1:25
And then the other log that we did for
1:29
a different animal was where we did our
mistake one, which was first a seal.
1:31
So we copy-paste that one,
seal_log.notes, likes to wave, perfect.
1:36
Okay, we're all set up.
1:42
Now that we've got our all of our
variables, let's try updating some values.
1:44
Let's change the lion's habitat
from savannah to grasslands.
1:48
I did want 100% google lion habitat to get
that answer, just being [LAUGH] honest.
1:54
So lion.habitat = "grasslands", hit Enter.
2:00
And if I do lion.habitat,
we get grasslands.
2:08
And we can also check
models.session.dirty,
2:12
which if you don't remember,
It's not callable, oops.
2:17
It's just .dirty, there we go,
which holds our change.
2:25
The session dirty is always what
holds any changes that you make until
2:30
you commit them.
2:35
So let's go ahead and
commit that to the database,
2:36
models.session.commit, perfect.
2:41
And then we can check
the lion_log.animal attribute,
2:44
and it pulls up our changes as well.
2:50
So they are reflected in the database.
2:54
Pretty easy, right?
2:56
It works pretty much the same
as it did with a single table.
2:57
Let's try changing our wombat animal
into a seal so that our log and
3:02
our animal actually make sense.
3:06
So I'm gonna do seal, just so
that things are a little less confusing,
3:09
I'm gonna do seal = wombat.
3:15
And then we can do seal.name,
and you can see it says wombat,
3:17
so we need to change that.
3:22
seal.name = "seal",
3:24
Ooh, not sale,
seal.name = "seal", there we go.
3:31
And seal.habitat =, "ocean".
3:38
And now let's do seal_log.animal,
and we get our changes.
3:48
So if you see this zoo.db-journal,
don't worry about it,
3:55
it just means that things
are moving with the database.
3:59
So let's do our models.session.commit,
hit Enter.
4:02
And you can see,
then it goes away, awesome.
4:08
Lastly, let's see what happens if I
change seal_log.animal_id to number 1.
4:12
If I then do you seal_log.animal,
4:22
which is our relationship,
it's now pulling the lion.
4:26
And if I do lion.logs,
I'm getting three logs now instead of two,
4:33
great pouncer, really likes meat,
and likes to wave.
4:38
So it's also important to know,
if you change what the foreign key is,
4:43
it will attach itself
to a different entry.
4:48
So something to keep in mind.
4:52
I'm gonna go ahead and
reset that back to what it was,
4:53
so seal_log.animal_id = 2.
4:59
Let's do seal.logs, perfect.
5:02
And just to make sure,
models.session.commit, Awesome.
5:06
Now let's look at deleting some entries.
5:13
Let's do models.session.delete(seal),
5:16
so we're gonna delete an animal,
and models.session.commit.
5:21
With that deleted, now let's see
what happens to the seal log.
5:29
Let's do seal_log, I'm gonna print it.
5:35
Okay, so you can see our Animal ID
is now None instead of 2.
5:45
So if you delete the animal that relates
to your log, it's important to remember
5:52
that that ID will then be switched
over to None instead of an integer.
5:58
Let's try deleting lion log.
6:04
models.session.delete(lion_log),
6:07
models.session.commit.
6:15
Okay, now we can look at the logs and
let's look, lion.logs.
6:20
And we can see we only have one log now.
6:26
The log is completely gone.
6:29
So deleting entries will work the same
way, but will also affect relationships.
6:31
But there's also something cool
that you can build into your model,
6:38
it's called cascade.
6:42
And we'll look into it
more in the next video.
6:43
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