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