Building data-driven web apps with Flask and SQLAlchemy Transcripts
Chapter: Using SQLAchemy
Lecture: Concept: Querying data
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
We've written a few interesting queries
0:02
and before we're done with this application
0:04
we'll write a couple more.
0:06
But let's talk about some of the
0:07
core concepts around querying data.
0:10
So here's a simple function that says
0:13
find an account by login.
0:14
We haven't written this one yet
0:16
but, you know, we're going to
0:17
when we get to the user side of things.
0:19
It starts like all interaction with SQLAlchemy.
0:22
We create a unit of work by creating a session.
0:24
Here in the slides we have a slightly different
0:26
factory method that we've written, but same idea.
0:29
We get a session back, we're calling it s.
0:31
We go to our session and we say
0:32
s.query of the type we're trying to query from
0:35
account, and then we can have one or more filter statements.
0:39
Here we're doing two filter statements.
0:41
Find where the account has this email
0:44
and the hashed password is the one
0:47
that we've created for them by rehashing it.
0:50
And now we're calling one, which gives us one
0:52
and exactly one, or None, items back
0:55
and we're going to return that account.
0:56
So if you actually look at what goes over to the database
0:59
it's something like this: select * from account
1:02
where account.email is some parameter
1:05
and account.password_hash is some other parameter
1:07
and the parameters are: Mike C. Kennedy, and abc.
1:11
You'll see that you can layer on
1:12
these filter statements, even conditionally.
1:14
Like, you can create the query and then say
1:16
if some other value is there, then also append
1:19
or apply another filter operation
1:21
so you can kind of build these up.
1:22
They don't actually execute until you do
1:25
like, a one operation, or you loop over them
1:27
or you do a first, or anything like that.
1:29
So here's returning a single record.
1:33
Also, it's worth noting that
1:34
the select * here is a simplification.
1:37
Everything is explicitly called out in SQLAlchemy.
1:40
The concept is, just give me all the records
1:42
or give me all the columns.
1:44
If we want to get a set of items back
1:47
like, show me all of the packages that a particular person
1:51
with their email has authored
1:53
we would go and again get our session
1:56
we would go and create a query based on package
1:59
we would say filter, package.author_email equals this email.
2:03
==, remember, double equal.
2:05
And then we can just say, all.
2:06
And that'll give us all of the packages
2:09
that match that query.
2:10
This one's not going against a primary key
2:11
so there'll be potentially more than one.
2:14
Of course, this maps down to select *
2:16
from packages, where package.author email equals
2:19
well, you know, the email that you passed.
2:20
Super simple, and exactly like you would expect.
2:23
So the double equal filter, pretty straightforward.
2:26
There's actually some that are not so straightforward.
2:28
So equals, obviously ==. user.name == Ed, simple.
2:34
If you want not equals, just use the != operator.
2:38
That's pretty simple. You can also use Like.
2:40
So one of the things that takes some getting used to
2:43
is these SQLAlchemy descriptor column field
2:48
the how you type multipurpose things here is
2:51
they actually have operations that you can do on them
2:55
when you're treating the static type
2:57
interacting with the static definition
2:59
rather than a record from the database.
3:01
So here we say, the user type.name.like
3:04
or N or things like that, and so there's, you know
3:07
we saw the descending sort operation on there as well.
3:11
So if we want to do the Like query, this is like
3:13
find the substring Ed in the name, then you can do .like
3:18
and then pass the percent to operators
3:20
as you would in a normal SQL query.
3:22
If you want to say, I want to find the user
3:24
whose name is contained in the set Ed, Wendy
3:26
or Jack, then you can do this .in_
3:29
remember the underscore is because in
3:31
is a keyword in Python, so in_.
3:35
If you want to do not, not in, this kind of a not obvious
3:38
but you do the Tilda operator at the beginning to negate it.
3:42
If you want to check for Null, == None
3:44
the And you just apply multiple queries.
3:47
The Or doesn't work that way, if you want to do an Or
3:49
you've got to apply a special Or operator to
3:52
a tuple of things.
3:54
So here are most of the SQL operators
3:57
in terms of SQLAlchemy.
3:59
You can do a lot of stuff with this.
4:00
It's not all of them, but many of them.