- What is SQLAlchemy? 6:37
- Creating a Model 8:37
- Creating a Movie Model 3 objectives
- Create and Read with SQLAlchemy 8:57
- ORM, Models, and Creation 6 questions
- Update, Rollback, and Delete 11:32
- Movie Database Continued 4 objectives
- Querying 15:18
- Querying the Movie Database 2 objectives
- SQLAlchemy Fundamentals 5 questions
Preview
Video Player
00:00
00:00
Update, Rollback, and Delete
11:32Learn how to tackle updating entries, rolling back changes, and deleting entries.
We have a few users in our database,
but what if we need to update a value?
0:00
Let's remove this old code and
move into the Python shell.
0:05
Don't forget to save.
0:15
And if you're like me, and
you've closed your terminals since we were
0:17
last hanging out together,
you probably need to activate it again,
0:22
like this, and then Python or
Python3 to enter your Python shell.
0:27
At the top, we'll need to import models,
0:32
this will import our
entire models.py file.
0:37
Next let's create a new user.
0:42
We'll need to write it as models.User
to access our model class,
0:44
since we're importing the whole file.
0:51
This tells Python to go
into our models file and
0:55
find our user class, and
then use it to create this new user.
1:00
We're gonna make our terminal
a bit bigger, there we go.
1:08
All right, so
let's finish adding our new user.
1:11
This is Jethro, says my dog.
1:13
And nickname, oops.
1:24
Name = Bubba,
that's what we typically call her.
1:28
Awesome, and
let's add our new user to our session.
1:35
So models.session.add(jethro), and
1:38
then we can make sure it got
added with models.session.new.
1:43
Which remember shows new users or
1:49
new entries in our database, and great.
1:52
Looking back though, I noticed that
I spelled Jethro's name wrong,
1:57
and so we need to change the value
to make it spelled correctly.
2:01
To do that, super easy.
2:05
Jethro, we use our same variable
name that we created up above .name
2:08
to access the name value, and then we
just set it equal to the correct value.
2:14
Oops, let's capitalize that.
2:20
Great, now let's make sure it got saved.
2:24
So I'm gonna hit the up arrow
on my keyboard to go back
2:26
up to models.session.new, and perfect.
2:30
You can see that it tracks
that we made a change and
2:33
the session has that change saved for us.
2:36
Awesome, right?
2:39
All right, now let's commit our new user.
2:41
So let's do models.session.commit.
2:43
And this is a function call, so
don't forget those parentheses.
2:50
Perfect, and now I'm gonna go up arrow
just to show that our session.new
2:53
is empty because we've now
committed Jethro to our database.
2:59
But what if we need to change something on
a user that is already in our database?
3:04
Well, we can still use Jethro, and
3:11
let's do jethro.nickname this time,
and I'm gonna do jetty.
3:13
This is what my mom likes to call Jethro.
3:18
Now let's see if session
has tracked our change.
3:22
So let's do session.new, and it's empty.
3:25
But we need to remember, session.new only
tracks new entries into our database.
3:29
And this is no longer a new entry in
our database because we committed it.
3:35
So instead, we need to call
models.session.dirty, and there's Jethro.
3:40
And you can see,
nickname = jetty has been updated.
3:46
So let's do a quick recap.
3:54
So we created a new user in
our database called Jethro and
3:58
added it to our session, but
we misspelled Jethro's name.
4:02
In order to change Jethro's name,
you just use the variable
4:07
that we used to hold our new user
.name to access the name value,
4:12
and then set it equal
to our correct value.
4:17
Then we checked our session, and
4:21
it automatically tracked that
the name value had changed.
4:23
And it works the same way after
we've committed that value.
4:27
But instead when we change something,
we don't check session.new,
4:31
we check session.dirty, and that showed us
that we did in fact, change the nickname.
4:35
Let's go ahead and models.session.commit.
4:41
And that change is now
committed to the database.
4:46
Let's check this in the SQLite shell.
4:49
So to exit out of our Python shell,
I'm gonna call exit.
4:51
And then I'm gonna do sqlite3 users.db,
remember for
4:57
my Windows users,
it might be sqllite3.exe.
5:03
Hit Enter, I'm gonna do .tables, and
5:08
then select star or all from users oops,
5:12
we don't need the.db, but
we do need that semicolon.
5:17
Awesome, and
you can see we have number 5, and
5:24
it has Jethro's name spelled correctly,
and the new nickname that we gave Jethro.
5:27
So it tracked all of our changes.
5:31
Awesome, okay?
5:33
So now let me exit, sqlite.exit.
5:35
And then let's pop back
over into the Python shell.
5:40
Don't forget to import models again, so we
have access still to our models.py file.
5:44
Okay, so
we've covered how to change a value,
5:51
but what if you make a change
that you shouldn't have?
5:54
Can you take it back?
5:57
Luckily SQL alchemy has got you covered,
it's called rollback.
5:59
When you roll back,
everything in your session is removed,
6:04
kind of like an undo button.
6:08
Let's show this off.
6:09
Let's do two things to practice
what we've learned so far.
6:11
First, let's change the nickname for
the user we just updated.
6:14
Okay, so to grab our Jethro,
I'm gonna write a query, and
6:20
don't worry, we're gonna be
working on queries real soon.
6:23
So just follow along
with what I'm gonna type.
6:27
Need models.session.query, and then we
need to tell it what model to query.
6:30
So models.user, and
6:36
we're going to filter by where
6:40
the models.user.name = = jethro.
6:44
And we want to get only one response.
6:50
And then I can ran Jethro,
just make sure, we got Jethro, perfect.
6:54
Okay, so now let's change
the nickname of Jethro back to Bubba.
6:59
jethro.nickname = Bubba, great.
7:04
We'll run models.session.dirty,
7:11
we see that change has been
tracked by our sessions.
7:15
Now let's create a new user,
you can create anyone you want.
7:20
So models.user(name = Aang.
7:27
Fullname= Avatar Aang,
7:37
and nickname= Aangi.
7:44
Cool, and
then let's do models.session.add,
7:52
and let's add our new user.
7:58
And then let's run our models.session.new,
8:01
and there's our new user
sitting in our session.
8:05
So let's say we don't want
these changes to happen.
8:10
So we're going to call
models.session.rollback, and
8:14
then we're going to check our session, and
see if our items have been rolled back.
8:19
.session.rollback, and
let's run models.session.dirty,
8:26
this is where we made a change to
Jethro's nickname, nothing there anymore.
8:32
models.session.new to
see if our Avatar Aang
8:40
is still in our identity set,
and it's not.
8:44
So our rollback will rollback or remove
everything that is currently in session.
8:48
So it's best not to pile your
session full of additions and
8:54
updates in case you accidentally
do rollback and lose them all.
8:59
So it's always good to make a change or
update and
9:04
then commit that to the database, so
that you don't lose your hard work.
9:06
Lastly, how would you delete a user?
9:11
There will be times when items in
your database need to be removed.
9:15
Let's use our same user we just created,
I'm just gonna up arrow.
9:20
Let me just go back to session.add(aang),
and
9:25
then I'm gonna go check our session.new.
9:28
Cool, it's back in there and
I'm gonna do models.session.commit.
9:31
So all I'm doing is adding
a new user to the database, so
9:36
that we can use that user.
9:40
Okay, so now that that user
is added to the database,
9:42
we can remove them with
models.session.delete,
9:47
and then we'll pass in Aang.
9:51
We'll also need to call
models.session.commit
9:54
to make sure that is pushed
through to the database.
9:59
Now if I try to run a query to find Aang,
and
10:04
we'll learn more about query soon,
so don't worry.
10:07
You can just follow along
with what I'm typing, and
10:12
we'll learn more about what each
piece means in just a minute.
10:15
So run models.session.query,
and then we do need
10:18
to do models.User to say we're
querying our user model.
10:24
And we're going to filter for
10:29
models.User.name = Aang.
10:36
And we're gonna say one.
10:42
Okay, and you are gonna see an error,
it says no result found.
10:47
No row was found, which is great.
10:52
That means it was deleted
from our database.
10:54
Now we can also run, instead of one,
we can do count to make sure that
10:57
the count that we return of how many rows
match this filter is 0, which it is.
11:02
0 rows that match our filter.
11:09
Wonderful, you've tackled
updating user values,
11:13
rolling back items in a session, and
deleting entries from the database.
11:16
Take some time to practice
these skills some more.
11:22
Try adding, updating, and
deleting an entry to your database.
11:25
See you next time.
11:30