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