Building Data-Driven Web Apps with Pyramid 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 core concepts
0:08 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 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 call calling it s.
0:31 We go to our session or we say s.query
0:34 of the type we're trying to query from, account
0:36 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 e-mail
0:44 and the hashed password is the one that we've created
0:48 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.
1:00 Select * from account where account.email is some parameter
1:05 and account.passwordhashed is some other parameter
1:07 and the parameters are Mysie Kennedy and ABC.
1:11 You'll see that you can layer on these filter statements
1:13 even conditionally, like you can create the query
1:16 and then say if some other value is there
1:18 then also append 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 like
1:25 a one operation or you loop over them or you do a first
1:28 or anything like that.
1:29 So here's returning a single record.
1:33 Also, it's worth noting that the select * here
1:35 is a simplification, everything is explicitly called out
1:39 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, like show me
1:47 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'd say filter, package.authoremail equals this email
2:02 ==, remember double equal
2:05 and then we can just say All and that'll give us
2:08 all of the packages 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 * from packages
2:16 where package.authoremail equals
2:19 when 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 not equals operator.
2:38 That's pretty simple.
2:39 You could also use like, so one of the things it takes
2:42 some getting used to is these SQLAlchemy descriptor
2:46 column field value type multi-purpose things
2:50 here is they actually have operations that you can do
2:54 on them when you're treating the static type
2:57 interacting with a static definition rather than
2:59 a record from the database.
3:01 So here we say the usertype.name.like_ or in_
3:05 or things like that.
3:06 So, 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
3:17 .like_ and then pass the % operators as you would
3:20 in a normal SQL query.
3:22 IF you want to say I want to find the user whose name
3:24 is contained in the set, Ed, Wendy or Jack
3:27 then you can do this .in_, remember the underscore
3:30 is because in is a key word in Python, so in_.
3:35 If you want to do not, not in, this is kind of not obvious
3:38 but you do the ~ operator at the beginning to negate it.
3:42 If you want to check for null, == None, the and you just apply
3:45 multiple queries, the or doesn't work that way.
3:48 If you want to do an or, you've got to apply a special
3:50 or operator to 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.