"How to Build a WordPress Theme" was retired on January 1, 2015. You are now viewing the recommended replacement.
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
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