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
Access items in the database by creating queries.
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
Our database has entries.
0:00
But how do you access them?
0:02
We're going to work in the shell again to
see the results of our queries right away.
0:05
If you closed your terminal,
0:09
you probability need to activate
your environment again.
0:10
I'm on a Mac, so I'll type
the source./env/bin/activate command.
0:14
And you can see I now have this end here
that tells me my environment is activated.
0:20
Now, queries are how we access
the entries we want in our database.
0:26
We'll be using Python with SQLAlchemy,
which is then converted into a SQL
0:31
statement to grab the correct entries and
return them to us.
0:35
We'll start with returning all
the entries in our database,
0:40
pop into the shell and import models.
0:45
Now let's write our first query.
0:52
Models.session.query(models.User), which
tells SQLAlchemy,
0:55
which table and
model we want to use, and hit Enter.
1:03
When you run it,
you get a query object back.
1:10
This object is holding all of our entries.
1:13
To see them individually, we'll need
to print each out using a for loop.
1:16
For user in
models.session.query(models.User):
1:22
one, two, three, four print(user).
1:32
Hit Enter, Oops and I accidentally
did model instead of models.
1:38
Correct that mistake,
1:46
print(user), Enter.
1:51
And voila,
all of our entries are listed out.
1:57
Now, what if you only need to
access one specific column.
2:01
Inside of the query, you can specify
the specific columns you want to pull.
2:05
Let's pull only the name column.
2:11
So, for
2:14
user in
models.session.query(models.User.name),
2:15
which should only give us the name column,
2:25
and then print(user).
2:31
The return value here is a tuple,
this is important to remember.
2:38
To access the value and
not the full tuple,
2:44
you'll need to print
user.name instead.
2:47
Like this,
2:51
.name, awesome.
2:56
Now that we know how to access entries,
we can start adding on to our query.
3:02
Let's run the same query but
3:07
add .order_ by(models.User.name).
3:12
print(user.name).
3:25
Now, before you hit Enter, think
about what you expect to happen here.
3:30
Hit Enter.
3:37
And you can see all of the names in our
database are in alphabetical order.
3:39
To switch to descending order,
we need to add at the end here, dot desc,
3:45
for descending, with parentheses, and
then close our order by parentheses.
3:50
And then, our colon for our for loop.
3:58
And there we go,
it's in descending order now.
4:07
We can also add on to our order
by to include limits and offsets.
4:11
You'll want to use them
on an ordered result, so
4:17
you get a more predictable outcome.
4:20
Python slices are what gives us
the limits and offsets we're looking for.
4:23
Let's start with the limit of two users.
4:28
What slice will give us
the first two users?
4:31
Let's also remove descending here.
4:37
And we'll need our slice syntax.
4:42
Print(user.name).
4:49
Hit Enter, and the first two users
in our database are printed out.
4:54
If we reference our alphabetical list,
we can see we grabbed the first two.
4:59
Remember, slices work
because we start at 0, 1, 2.
5:06
So we have stopping at 2 means we grab 0,
1, stop.
5:13
Now, what if we wanted to skip or offset
by two users and then grab the next two?
5:25
What would that slice look like?
5:33
See if you can figure it out.
5:35
Pause me for a second, and then hit Play
when you're ready to see what I did.
5:37
Okay, so
we know the first number is our start.
5:43
So we wanna start with the number 2,
5:49
which is the third value in our users.
5:53
And then, we want to stop at the fourth.
5:58
So before we run this, let me pop back
up here to our alphabetical list.
6:03
So we have start at 2, so 0, 1, 2,
6:09
we're gonna start here,
and we're gonna stop at 4.
6:12
So this is 2, 3, stop.
6:17
Let's run it.
6:23
(User.name).
6:29
Jethro and Catherine.
6:33
And if I pop back up to our list,
in alphabetical order,
6:34
we skipped the first two,
grabbed the second two, and then stopped.
6:39
Perfect.
6:45
Now, there are few other ways to control
how many responses you receive and
6:48
what they look like.
6:53
If you wanna return all the values
as a list instead of a tuple,
6:54
you can add .all to the end
of your query like this.
6:59
Models.session.query(models.User).all().
7:04
And you can see it's now a list.
7:15
You can also return only the first entry
from your query by adding a dot first.
7:19
I'm gonna use up arrow, so
I don't have to type so much,
7:27
and I'm gonna add our order
_by(models.User.name).first().
7:33
And you can see this query grabbed the
first user alphabetically in our database.
7:42
Check the teacher's notes for some more.
7:49
Lastly, let's dive into grabbing
specific values using filter by and
7:52
filter models.session.query(models.User).
7:59
And we'll do dot filter_by.
8:05
Filter_by takes keyword arguments.
8:11
So it's great for
when you know what you're looking for.
8:14
For instance, let's look for users with
the name of Jethro, (name= 'Jethro').
8:17
And you can see it worked
because it found a query object,
8:27
which means it found
something in our database.
8:31
Now, filter uses regular Python operators.
8:34
So if we were to write the same query,
but with filter,
8:38
we would need
(models.User.name==' jethro').
8:45
So instead of a key value pair,
8:54
we're now filtering by whether
the name equals Jethro.
8:57
Run the file and we get the same result.
9:02
We get a query object saying
that we've returned something.
9:04
And if you wanna check it, you can do for
user in models.session.query,
9:08
(models.User).filter, where
9:18
(models.User.name =
9:26
='Jethro' ).
9:32
print(user).
9:36
And great, it is pulling our Jethro user.
9:40
You can also add more than one
filter to be even more precise.
9:45
Let's add another user to
our database real quick.
9:50
I'm just gonna add myself again.
9:56
I'm gonna give myself
a different nickname.
10:11
Okay, models.session.add(me).
10:15
models.session.commit().
10:23
Great, now let's write a for loop for
user in and let's write our query.
10:31
models.session.query(models.User).filter.
10:36
(models.User.name== 'Megan').
10:52
And I'm gonna run it like this first.
11:02
User.
11:09
Okay, so we get our two entries, Megan and
Megan but with two different nicknames.
11:12
So now I'm gonna use my
up arrow to go back, and
11:17
we're gonna add a second
filter on to our query here.
11:21
So this one we're gonna do dot
11:25
filter(models.user.nickname== 'Megatron').
11:29
One, two, three, four, print(user).
11:38
Oops, In my second one,
11:45
I forgot to do uppercase.
11:50
Quick fix.
11:53
Enter, two, three, four, print(user).
11:58
There we go.
12:04
And you can see there's
the new user that I just made.
12:06
You can combine multiple filter to help
drill down to the specific value that you
12:10
are looking for.
12:15
Another thing to know about queries is
they can push items held in your session
12:17
to the data base.
12:22
Let's create one more new user.
12:24
And I create a cat user
12:27
models.User(name= ' Joni ',
12:32
fullname= 'Joni the Cat',
12:39
nickname= ' Key Grip ').
12:45
Okay, and let's do models.session.add.
12:51
Let's add our cat to our session.
12:58
Okay, so
now we have something inside our session.
13:01
And we can check real quick
with doing models.session.new,
13:04
just to prove it is in our session.
13:07
Now, let's run a query of the database
to grab all users and print them out.
13:10
for user in
models.session.query(models.User):
13:16
that will grab all of our users.
13:24
One, two, three, four, print(user)
13:29
You can see our new user is here.
13:35
Let's try updating their name to
something else and run the query again.
13:39
So let's do cat dot nickname equals,
13:44
change them to producer.
13:49
They've been promoted.
13:53
And then, let's run our query again.
13:55
So I'm just gonna do up arrow, Enter.
13:58
And then up arrow, Enter.
14:00
Enter one more time.
14:04
And again, the changes that were in our
session, were pushed to the database.
14:05
That's why it's important to keep in mind
what information is in your session and
14:10
commit as you go.
14:15
Now, to get rid of this
users.db-journal file that's popped up.
14:16
We can run models.session.commit.
14:22
And there it goes, that file is just
a temporary file that creates and
14:28
stores the most recent
state of your database.
14:33
If you see it pop up,
you can run a commit just to clear it out.
14:37
Nice work Pythonistas,
you've learned a lot.
14:41
See the teacher's notes below for
a link to the SQLAlchemy docs on querying.
14:45
This will be an excellent
reference to bookmark.
14:50
You've learned a ton, so
14:53
I highly suggest taking some time to
practice what you've learned so far.
14:55
Practice is what makes things stick.
15:01
Create a small database of your plants,
pets or
15:04
items in your fridge, whatever you want.
15:08
And then try accessing, adding, deleting,
updating values in your database.
15:11
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