Building data-driven web apps with Flask and SQLAlchemy Transcripts
Chapter: Using SQLAchemy
Lecture: Concept: Querying data

Learn more about this course
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.