Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
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