Full Web Apps with FastAPI Transcripts
Chapter: async databases with SQLAlchemy
Lecture: The remaining database queries

Login or purchase this course to watch this video and the rest of the course contents.
0:00 Over here in the package service, those were the final queries that we needed to convert to async.
0:06 And because it's exactly the same as what we've been doing, I decided to go ahead and do the conversion, and we'll just talk through it real quick.
0:13 So for the release_count, just like we saw before, we're gonna do a select with a function count of Release.id and execute it, that's
0:20 pretty straightforward, at least given what we saw for users. package_count, exactly the same. But Package instead of Release. This latest_packages,
0:27 remember, this was our most complicated query that we had in our system. But from here to here, it was exactly the same as the old query.
0:36 We just have to, instead of doing session.query, we just say "select" and then you can't do the all() anymore, what you do is just scalars() like that.
0:44 Then we did this little set trick just like before to make sure we get only unique ones in case a package has had a release within a couple of times of
0:53 some of the other ones, right? We had a little duplication, and that gets rid of it. get_package_by_id again super similar.
0:58 You get a select, here your filter just like before, await executing it. And this first() is now scalar_one_or_none.
1:06 Also converting the latest release for a package. Very, very straightforward. This was basically the same thing.
1:12 Execute it, these are scalar_one_or_none, good to go. So that's it. Those are all the queries. But what I wanted to do together with you,
1:19 it's just integrate these queries back into the rest of the application. So let's go find where this is being used and update it.
1:26 It should be just that IndexViewModel right there and notice PyCharm is saying all
1:30 of these, all of these are a problem. None of them are returning integers or lists, what in fact they're returning is coroutines.
1:39 We gotta execute the coroutine and get its value out by awaiting it. And then this one, we now can say more concretely,
1:47 this is a list of Package. That will give us a tiny bit more autocomplete. So release count, package count,
1:58 we already just took care of. latest_packages, I believe this is also just used in that one location.
2:04 Yes, it is and we're doing things, I'll get there. get_package_by_id, where is that being used?
2:12 You'll find it, there you go. It's over in this DetailsViewModel. Now, this is one of those that's going to take some work. We now have to await this,
2:22 and this one is also async, so we're gonna have to await that. That means we will need to do some sort of work.
2:30 Let's reorder this a little bit, like this. We need to do some sort of work in order to allow us to await this. Like I said, constructors,
2:41 the dunder init, cannot be asynchronous. That was already down there, so I guess that was just duplicate.
2:47 So this part right here needs to run somewhere else. However, these things need to be defined as optional things of whatever they are, so
2:58 this means Optional[Package], so we can write this code here, define it correctly, and then load it up. Come over here,
3:07 same type of thing but for Release. Package release, the latest version, that's already set up there.
3:16 Okay, good. So now all we gotta do is make an asynchronous function that we can call wherever we were using this before.
3:27 Remember, it's called DetailsViewModel, and it's in packages. So that would mean it's in the package view detail function.
3:34 There's only one, apparently, so that kind of solves that right? But the last thing to do is make this async and await vm.load() we
3:45 should be good to go with that. We may have just done it. That might have been the last function. Let's see, where is this one being used?
3:55 Yeah, it's already being awaited. That's cool. Oh, however, I just now noticed, you may have have already noticed that, we need a "self" over here to
4:04 pass it along, perfect. All right, let's go click around the site and see if it works. Make sure there's no "you forgot to await something"
4:13 warning. So here we have number of projects, releases, users. Those all look real. Notice
4:19 I added two more by adding those Sarah Jones3 and 4 in, which is pretty cool. We have the awscli.
4:26 The part that we just worked on is, what happens if I click this? Mmm, not something good. What has happened here?
4:33 Multiple rows were returned, right here. Oh, yeah, we just want to say scalar I believe. It's, scalar_one_or_none is if I'm trying to get some kind
4:43 of like user or something were it's supposed to be unique. Let's see about that. There we go. Because, of course, there's multiple releases.
4:50 We just want one, which is the latest. Alright, perfect. This is working right here.
4:55 Looks like everything's good. We could check out the home page, that awscli sure looks like it, so that, that's working.
5:03 Let's click on one other, gevent, perfect. There's details about the gevent, the library, and this is all working.
5:09 So homepage, package is good. Let's just one more time. Log in, log out. We log in, log out and finally do one more register.
5:18 I'm sure this will work because I've done it so many times, but we'll do a test with the wrong one there, already exists. That's right,
5:31 we wanted Sarah Jones5, and Sarah Jones5 is now registered. That's it. Let's double check, really quick here,
5:40 make sure there's no "you forgot to await something". No, no warnings either. Looks like we got everything converted over to this new
5:49 SQLAlchemy style of programming. Now I want you to just think back to that diagram
5:53 where we saw the animations where we saw all the interactions, so over here. Here, where we saw those interactions when our request would come in to,
6:01 like, slash project slash whatever. What are we doing here? We're going. And we're saying I want to start a little bit of work.
6:08 So this whole section here is gonna run synchronously, which builds up the variables to work with.
6:16 And then we're gonna await this load jumping back over here. This is where we're going to the database to do a query and say: you know,
6:23 anything that's happening right now, if another request comes in while we're waiting for the database, cool, just go let that request do its thing,
6:31 maybe start off another database call, when this database gets back to us, just put the answer right there.
6:36 Oh, now we're gonna start another database call, and if anything happens during that, it's cool, just go process it. When this one's finished,
6:43 drop the answer right there. And then a little tiny bit of work, like checking for None on two items and a string format.
6:50 Most everything happening in this whole series right here is about waiting on the database, in
6:55 those two database calls. Were doing that asynchronously, that means we're not blocking up the
7:00 server, were not consuming hardly any resources while that's happening, on the web server.
7:04 Obviously the database is working its heart out, trying to give us the answers, right? using its indexes and doing its thing.
7:10 But as long as the database can handle the traffic, this particular part of our application is not gonna be the bottleneck,
7:16 and that is super, super cool. It's possible because async and await, we were able to use async and await because we're using the latest SQLAlchemy,
7:27 the beta version that supports actually asynchronously talking to the database. Super cool, hopefully you'll appreciate this a ton.
7:35 I think it's really, really neat, and it unlocks some amazing potential for your web app.

Talk Python's Mastodon Michael Kennedy's Mastodon