Full Web Apps with FastAPI Transcripts
Chapter: async databases with SQLAlchemy
Lecture: User service async queries

Login or purchase this course to watch this video and the rest of the course contents.
0:00 Now that we have our db session with async capabilities,
0:03 it's time to go use that for some queries.
0:05 And here's where we're gonna get into the new query syntax for SQLAlchemy
0:10 So we don't need db_session anymore,
0:11 we're just gonna use it and let's go work on the user_service
0:15 for now. There's a couple of queries that we want to go and rewrite.
0:18 For example, let's work on the count.
0:20 Recall, this is the one that's shown right on the home page when, it's got
0:24 the little gray bar that says,
0:25 how many packages, releases and users there are.
0:28 So we're gonna do something similar to this,
0:30 but not exactly the same. So let me comment this out and leave it here
0:34 to guide us. We'll say. You'd like to say db_session = db_session.create_async_session()
0:40 but things are a little bit different,
0:42 as I've indicated here with this async
0:45 API. What we're gonna do is we're actually gonna do an asynchronous
0:49 context block, a with block,
0:52 but one that's async, that's a little bit funky syntax in Python,
0:55 but that's how it goes. So we'd like to say something like this with that as
0:59 such and such, and then we're gonna use it.
1:01 And I, for one, am really happy to see the session being able to
1:04 be used in a context block,
1:05 that means it'll clean itself up like you saw where
1:07 you're kind of simulating that with
1:09 try, finally. Well, now,
1:11 in this new API you can just put it right here.
1:13 So this is cool, but because this is asynchronous,
1:16 what we need to do is do an async with block here,
1:20 and then we're gonna do a query.
1:22 Now, the queries look a little bit different in this new API.
1:27 Instead of saying session.query,
1:30 what we do is we actually create a select statement and then we execute it. So we'll
1:34 say, select, I'm gonna import that
1:36 likely from sqlalchemy.future,
1:40 and in here, we're going to put some kind of statement.
1:43 Now, normally, it's pretty straightforward,
1:45 like user, and we do like a filter and and so on.
1:48 But for count, it's a little bit funky.
1:50 So what we're gonna do is, we're gonna say, I'm gonna import this thing called "func",
1:53 which comes from sqlalchemy, and then there's a way to say count(User.id).
1:58 So we're gonna pass a function that's counting the user id over to the select.
2:02 Now we're gonna go get the results,
2:05 because this is just a query that has yet to be executed.
2:08 So here's where the session comes into
2:09 play. We'll say execute and we give it the query.
2:12 This is where we go talk to the database,
2:14 and when we talk to the database,
2:16 this is our chance to do other work while we're waiting,
2:19 right? If I were to,
2:21 look at this and then print out the type here,
2:23 let's just do a, a really quick print type of result and then I'll, let's return
2:29 12, how about 42. That's a good number of users.
2:33 So when we run it, you'll see something happen, and then this, and should probably
2:37 see a warning as well. So let's run this and okay,
2:41 so we've gotta do one thing first. In order to use async or await
2:45 within a function, we do have to make it async here.
2:49 Alright, and in order to actually call it when we're calling an async
2:52 function, we need to sort of go up the stack and await that as well.
2:56 So let's go and do that.
2:58 We'll go see where this is used,
2:59 which is going to be in our home IndexViewModel
3:03 and notice, already right here PyCharm is
3:05 saying there's a problem. What is the problem?
3:08 Well, the problem is that we got a coroutine and we wanted an integer
3:13 Oh, this is an non executed,
3:16 but could be computed, could be executed asynchronous function.
3:20 And in order for us to actually interact with it,
3:24 what we have to do is we have to await it.
3:27 We'd have to type this await
3:28 here. That's cool, except for there's a small challenge here.
3:32 Constructors cannot be asynchronous, so that means we're gonna need to split this apart
3:37 into two pieces. Let me do it like this def load or something like that.
3:42 And here, we're going to just make this all equal to zero at the start
3:45 and then we'll set them to something meaningful. I'll just use an empty list for now.
3:52 Okay, so over here because this is also async,
3:55 right? This kind of propagates up the stack,
3:57 like I said, this is now going to be an async function that we
4:01 can then call. Once this one's
4:02 async we're gonna need to call it so let's go further up the
4:05 stack here. Typically, how this goes.
4:07 And over here, we didn't previously had to call vm.load()
4:11 but now we do. And if I try to run this,
4:13 you'll see some weird stuff happening.
4:16 When I request it. It worked
4:18 but there is no data. And if you go down here,
4:21 you can see warning, warning, Index, scroll over,
4:27 load was never awaited. Okay,
4:29 so this actually never really started,
4:32 it never did anything. So we can await this,
4:36 that's the warning I was looking to show
4:37 you. We just gotta work our way out far enough to get it to actually run,
4:40 so you can see it. Now, when we do this in order for us to have
4:44 that keyword there, this has to be an async function.
4:46 And here is where FastAPI
4:48 is awesome. What do we have to change in the way we're running the
4:51 program, like the web server were using, the way we might deploy it, the way that
4:56 we specify a route or any of those things?
4:58 Nothing, nothing changes. It's all exactly the same, FastAPI
5:03 just knows that this is an async function
5:05 and it'll automatically execute it correctly.
5:07 So let's run this again. And now if we go and request that page,
5:14 you can see we now have our 42 right there.
5:17 And somewhere down, there's another warning that there's another coroutine that was not awaited,
5:21 this execute. Took me a while to get to be able to show you that,
5:24 but there it is. We go down here,
5:29 this is actually an asynchronous function.
5:32 So when we're talking to the database here,
5:34 we need to tell Python we're about to wait on something external,
5:39 You can go do other things until this finishes and then please pick up here on.
5:44 Okay, So if you look at that print somewhere back here,
5:49 you can see that this is a coroutine just like we already saw.
5:52 But now if we await it,
5:54 this is actually going to be just a result.
5:57 But let's go print it out one more time.
5:58 There's, there's something different as well
6:00 here, result, and let's put the type of result as well.
6:07 We'll do a quick refresh, we have an iterator
6:10 result and also just says that thing.
6:13 So if we put, let's put this into a list so you can actually see what
6:17 comes out of it, not the same shape as it was in the previous
6:20 API. So look what we got,
6:21 we got 87. That's good,
6:23 that's the number of users. But what is this?
6:26 That is a tuple. We got a tuple here.
6:30 So the way this works, in the new query syntax in SQLAlchemy, what you get
6:34 back, our table rows are the value or the object that you tried to query
6:41 and then maybe other things. I'm not sure exactly all the different things that might
6:45 be returning there, but you get a tuple of results.
6:48 So in order to actually get the result that we're looking for is we need to
6:53 go and say, return the scalar,
6:57 I thnink scalar will do it. If you go and request that, this should now turn that
7:01 42 to an 87 and it does.
7:03 Okay, so remember when I said this is probably the API
7:06 you know, and this is a little bit different.
7:08 I'm sure that this looks quite a bit different.
7:10 Both because it uses this select thing, you pass in additional functions,
7:14 you await the execution. But also because the results you get back are these tuples
7:20 where one of the elements is what you actually used to get back previously.
7:25 Okay, so not really a big deal at all.
7:27 It's quite easy. It's just like I said,
7:29 not that similar. So let's go over here.
7:33 The next one we gotta write is create_account and then login and then get
7:40 this two users by id and by email,
7:43 both are gonna be pretty similar to the one we just wrote.