"Java Objects (Retired)" was retired on January 31, 2017. 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 SQLAlchemy Basics!
You have completed SQLAlchemy Basics!
Preview
Learn how to tackle updating entries, rolling back changes, and deleting entries.
Installing SQLite and Accessing it on Windows
Follow the directions listed out in the answer on this forum post
SQLAlchemy Doc Links
User List
[User(name='Grace', fullname='Grace Hopper', nickname='Pioneer'), User(name='Alan', fullname='Alan Turing', nickname='Computer Scientist'), User(name='Katherine', fullname='Katherine Johnson', nickname='') ]
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
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
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