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!
Preview
Video Player
00:00
00:00
00:00
- 2x 2x
- 1.75x 1.75x
- 1.5x 1.5x
- 1.25x 1.25x
- 1.1x 1.1x
- 1x 1x
- 0.75x 0.75x
- 0.5x 0.5x
Learn how to have more control over your relational database with cascade.
SQLAlchemy Docs
Relational DB Ideas -
Create a small app to complete all CRUD actions
- E-Commerce: Table for customers and a table for purchases - customer id is the foreign key
- Food: Table for brands and a table for products - brand id is the foreign key
- Doggy Daycare: Table for pet parents and a table for pets - parent id is the foreign key
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
Hi there, welcome back.
0:00
I'm in the SQLAlchemy docks
looking at the Delete section.
0:02
This is also in the teachers notes.
0:05
There's this cascade option that has
this all, delete, and delete-orphan.
0:08
If I scroll down a bit, I see there's
a little more information about cascades.
0:15
So let's traverse to this
new land of knowledge.
0:20
Okay, I'm going to give you the cliff
notes version of what's on the page here.
0:24
So take some time to read through
the details if you want more specifics.
0:29
Essentially, this option tells SQL Alchemy
what to do with the children when
0:33
a parent is deleted.
0:38
In our example,
the animal class is the parent and
0:41
the logbook class is the child.
0:46
The default value is this save-update.
0:50
If I scroll down a bit, I can see
the details of what save-update does.
0:56
It takes related elements and
updates them when one element is updated.
1:01
The example here,
they've created a user and
1:07
a couple addresses and
they've added the addresses to the user.
1:11
Added their user to their session, and
then they've added a third address.
1:16
Let's read what this means.
1:26
When an object is placed in
a session via session.add,
1:29
like when we're about to add
something to a database, all objects
1:33
associated with it via a relationship
are also added to the session.
1:38
So that means anytime
that we make a change,
1:43
anything that is related to
that change also gets updated.
1:46
So it saves us a bit of time.
1:50
This is functionality that we want
to keep since it's super helpful.
1:52
If I scroll back up,
we can see that all Is a synonym for
1:57
save-update and
a few other things that we wanna keep.
2:01
So we can use all just like they did over
here, but let's also see what's delete and
2:05
what's delete-orphan?
2:10
Let's figure out what those mean.
2:12
I scroll, we get to delete.
2:15
Delete cascade indicates that when
a parent object is marked for deletion,
2:18
its related child objects should
also be marked for deletion.
2:23
So what this means in our example, is that
if an animal is deleted from our zoo,
2:27
any related logs will also be deleted.
2:33
So if we delete the lion from our zoo,
2:36
all lion logs will be
deleted from the logbook.
2:39
Now let's find delete-orphan and
see what that means.
2:42
So this adds to the behavior of delete
where a child object will be marked for
2:50
deletion when it's
de-associated from the parent,
2:55
not just when the parent is marked for
deletion.
2:59
So that means if we were looking at
a lion's list of logs and we deleted
3:03
one of the logs from our list, it would
also delete that from the log book table.
3:08
The best way to solidify this new
information is to play around with it.
3:15
So let's go back to the code.
3:20
Since we'll now change
how our database works,
3:26
we'll need to delete
this zoo database file.
3:30
Yes, goodbye.
3:34
Let's add the cascade option.
3:36
We're gonna add it to
our Relationshiprhere.
3:38
So comma, and
I'm gonna come down here on the next line,
3:42
Cascade =, and it takes the string, all,
3:47
delete, and we want delete-orphan.
3:52
Don't forget to save.
3:59
And now let's rerun the file in
the console python3 models.py.
4:00
Oops, I spelled something wrong,
delete-orphan, save.
4:07
Try that again.
4:12
There we go and our database is recreated.
4:15
I'll run a clear, and then I'm gonna
pull our terminal up so that we can see.
4:18
Now because our database is brand new,
it has absolutely nothing in it, so
4:26
we're gonna have to create a few things.
4:29
So let's pop into the Python shell,
and let's create two animals and
4:31
we're gonna create two logs for
each animal.
4:35
So to save some time,
I'm gonna do a little movie magic, and
4:39
I'm just gonna cut when this is all done.
4:42
So on your own, go ahead and
add two animals, and then two logs for
4:44
each animal,
which is a total of four logs.
4:48
Cool.
4:52
Pause me and I'll see you in a bit.
4:53
Got your animals in logs?
4:55
Great, let's go.
4:57
So the first thing we added
was the delete option,
4:59
which deletes children when
the parent is deleted.
5:02
So if we delete our, I added a wombat and
a tiger to my tables.
5:05
If I delete my wombat, then all of
my wombat logs will be deleted.
5:11
So, let's try that out.
5:17
Let's do models.session.delete,
5:18
and let's do (wombat).
5:23
Models.session.commit().
5:30
And then I'm gonna do a for loop just
to see everything in our logs now.
5:35
So for logs in
models.session.query(models.Logbook),
5:41
and I think I can just do it like that.
5:49
Print, we'll see pretty quick.
5:55
There we go.
5:58
So you can see we now only have two items
in the logbook, which relates to my tiger.
5:59
All of my wombat logs are also gone.
6:07
And if I do the same thing and I do for
6:12
animal in
models.session.query(models.Animal),
6:18
print(animal).
6:27
You can see I only get animal with the ID
of 2, the animal with the ID of 1 is gone.
6:31
And same up here, our log books with
the idea IDs of 1 and 2 are also gone.
6:36
Since we deleted our wombat,
6:43
it also deleted all logs
associated with that animal.
6:45
Now let's try seeing how
delete-orphan works.
6:51
If we gather our logs for
our tiger and delete one,
6:55
it will also be deleted
from the logbook tables.
6:59
So let's do tiger.logs and
let's just run it to see so
7:03
we have a list with two logs inside of it.
7:08
To del tiger.logs, and
let's just leave that first one.
7:11
And now we can do our for loop again,
7:24
I'm gonna do an up arrow just
because it's kind of a long typing.
7:27
So now when I do my query and try to
see all of the logs in our logbook now,
7:40
we are left with only one.
7:45
Because I deleted this
log using our animal and
7:47
checking our relationship,
which is what's up here,
7:52
which showed us that it
had two associated logs.
7:58
And when we deleted this first one,
8:03
it automatically deleted it
also from the logbook table.
8:06
Cascade gives you more control
over how your database works.
8:12
These options are essential to keep in
mind when creating a relational database.
8:17
How do you want deleting to work?
8:22
If an animal's deleted, do you want
all their logs to be deleted also?
8:24
And if an animal's log is deleted,
8:29
do you want it to be removed
from the logbook table, too?
8:31
There are reasons for and
against both options,
8:35
it really depends on what you
want your application to do.
8:38
This is why the planning stage in
the beginning is so important.
8:42
Take the time to think about how you
want your application to run and
8:46
how your database will need to function.
8:50
Amazing job.
8:54
We played around with a relational
database quite a bit.
8:56
My suggestion is to create a small project
to practice and test your new knowledge.
9:00
In the teacher's notes below,
9:05
I added a few ideas to get
those creative juices flowing.
9:07
Have fun.
9:10
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