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.

Talk Python's Mastodon Michael Kennedy's Mastodon