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