Full Web Apps with FastAPI Transcripts
Chapter: Storing data with SQLAlchemy
Lecture: Home page queries
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
It's time to start using our real data. After all, we have a database, we have a database model, we have the database full of data from actual real
0:07
live PyPI data. Let's start using that. In order to do that, we need to actually do queries against the database instead
0:15
of having this fake data. Now the way I like to approach this is sort of a top down mode. Let's look at this homepage and see what queries we
0:22
might need to rewrite in order to make that using this live data instead of this fake data. But just to remind you,
0:29
Let's click here, this is the home page. And the most important things to see are how many projects are there,
0:35
releases, users and then show me the latest releases to the projects that have recent releases Okay, so we want to go and basically do that.
0:44
Let's just start here at this index method. We'll go to the IndexViewModel, hold down command or control on Windows,
0:50
and you can use these hyperlinks, so I'll jump to that. Here we have our package_service,
0:55
which has given us a release count and a package count and our user service, which has given us that. Let's do the three counts real quick.
1:01
I'll jump over to the package_service release_count. That looks real, doesn't it? No, no, it doesn't. So we're going to need to create a unit of work,
1:09
a session in SQLAlchemy's nomenclature. So I'm gonna do, from data import db_session. That is the thing that creates the units of work.
1:19
And it's easiest if we do this kind of stuff within a try, finally. And then we close up the session, clean it up there at the end right away.
1:29
So what we'll do, is we're gonna say session = db_session.create_session().
1:33
And remind yourself this returns a Session object. By calling the factory, turns off some of the, force you to reload it too frequently
1:44
in my opinion, types of behaviors. So over here we're gonna have the session. And then instead of this, we're gonna write a query right here.
1:50
Do this in line, it's really simple. So, say query, what do we want to query? We wanna query the release, to which
1:56
you wanna go to the table, to the query and just run a count against all of it. And here we'll say session.close() like that.
2:04
Let's see if we got this working, just before we go any further. Let's go ahead and run it. Now, we should have, what was it?
2:08
5,400. If we look over here without refreshing, there's some huge number of releases to simulate the real PyPI but our fake data is
2:17
just a couple hundred. Here we go, look at that! Perfect, 5,400 out of the database, that's what it's gonna look super duper similar down here,
2:28
only one word changes, we want to change that to package. That's it, we re run, we refresh. That should go to 96. It did, and then users.
2:40
I think we had 83 users. Let's go down and write really similar code for the users, but that's over, work our way back.
2:48
That's right here. They're having this fake thing, we'll do that.
2:53
Gotta import this, and this is gonna be user, just like so. That should be the three count queries that we have for our little bar.
3:03
Look at that, it's all live data. How incredibly easy was that? That's awesome. That's why we're using an ORM, because once we get it all set,
3:10
it's incredibly easy to do. The last thing to do here is to get the latest releases. So we're gonna go over to our package service.
3:21
This may be the most complicated query we're doing in the entire application. So what I wanna do is say releases, we're
3:27
gonna start by getting the releases from the table, the releases, here. What I wanna do is come over here and say
3:31
session= db_session.create_session() and then try this. And finally session.close(). So then we're gonna come to our session, and we'll
3:48
do a query against the release table. Remember the releases know which package they're related back to. In order to do this query,
3:56
we're going to have to, well because we're closing the session and we don't wanna have a bunch
4:01
of slow, come back and do extra queries against the database. We wanna do just a one shot thing, we're gonna do a join. And the way we do that is
4:08
we come over here and say options and into the options, we're gonna pass the, the ORM. Now, that's not imported yet, but it's going to be.
4:25
We have a joinedload, and the way we do the joinedload is we specify the relationship on the thing that we're querying here.
4:32
So we wanna that relationship, basically its part of the join, so every time you get a release,
4:37
get it's related package as one query and let's wrap this around and then we also want to go and do an order by because we want the latest one.
4:45
So we're gonna go over here and do an order_by and then what are we gonna put in this? It's going to be release.created_date.desc(),
4:51
most recent ones first, and then finally, we only want a certain number here. Now this could cause a problem.
5:00
But there's a way to say limit and then you pass how many items you want to go over. It just happens to be that word, just the
5:06
same. And then finally, we wanna say, you wrap that around, and say .all(), wrap that around. Okay, so those are the releases, plural.
5:16
And what we should be able to do is go to each release and get its package. So that's what we want back, it's not a list of Release, a list of Package.
5:24
So finally, after that's all closed up, we can return. Just do a little list comprehension,
5:31
say, for each release, we're gonna get its package for r in releases. All right, Is that gonna do it? We're gonna find out, aren't we?
5:42
So let's run this and oh, look at that. It's working. So we've got 1, 2, 3, 4, 5 packages back. Let's go and change how many were passing over.
5:57
Let's say limit equals seven. Okay, Lokk at that, we've got gevent and awscli. Now notice,
6:04
I told you that this has a possibility of causing some kind of problem because of the way our data structured, if a package releases frequently,
6:12
it could, and if we ask for too many of them, it could show up here, but we can go, maybe be a little bit safe, I guess. Let's try to do this.
6:20
Let's go to package service. Say I wanna do a set comprehension here instead of a list and then we're gonna turn it into a list.
6:28
What will that do? Hopefully it will make it a unique, sets always only hold one of any given item,
6:35
it's, it's a matter of what counts as you, you know, the same item for these. I think this'll' work, we're gonna find out.
6:41
Here we go. We should have 6. 1, 2, 3, 4, 5, 6 because one was duplicated. So in this case, maybe we query for twice as many as we need
6:51
and then do this reduction and a limit. But it's just kind of messed up with the data that we have that it's, there's
6:57
a chance of getting a duplicate. But nonetheless, let's just take a moment and appreciate what we've got here.
7:02
This is real data coming out of our database that we populated with real data coming from PyPI. This whole section here is completely live.