Building data-driven web apps with Flask and SQLAlchemy Transcripts
Chapter: Using SQLAchemy
Lecture: Querying data: Home page

Learn more about this course
Login or purchase this course to watch this video and the rest of the course contents.
0:00 Now that we've imported the data into our database
0:02 I think it's time to start using it.
0:04 How many projects did we import?
0:06 Negative one, unlikely.
0:08 That's not typically a count of real numbers, is it?
0:11 So, what we need to do is actually go
0:13 query the database to fill out those sections.
0:16 We also need to fill out our releases
0:17 down here at the bottom, but we're going to focus on
0:20 just this little stat slice as we called it.
0:24 So if we go over here, and we see right now
0:27 what we're doing is we're going to our package_service
0:29 and we're calling a function: get_latest_packages.
0:32 Well, that's pretty cool, but we could look over here
0:36 this is just fake data. So let's actually put in here
0:40 we're going to need to pass more data over to our template.
0:43 So we go to our template, it's super nicely organized.
0:46 We're in Home View, so we go into the Home folder
0:49 and run the Index function, so we go to Indexing.
0:52 Boom, there it is. Well, that looks like a problem.
0:55 Let's pass in some data.
0:58 Now, we might want to just drop the number here
1:00 like, package count, that'd be okay.
1:04 Except for, if there's a million packages
1:06 it's just 1-0-0-0-0-0-0 with no digit grouping.
1:10 So we could do a little bit better
1:12 if we did a little format statement, like this.
1:17 Like so, and to do the digit grouping.
1:19 Now let's just put that in for the others as well.
1:24 I have release count, and user count.
1:30 Of course, for that to work, if we try to run this again
1:33 we go to refresh it, not so much.
1:37 Unsupported format type.
1:40 And let's go pass this data along here.
1:43 So we're going to need to have a package count
1:46 and let's just make this fake for a minute.
1:50 Release. And user count.
1:59 This should be auto-refreshing down here at the bottom.
2:02 Here we go, it's active again, super. Refresh.
2:06 One, two, three, looks beautiful.
2:09 I was trying to pass None to format in digit grouping.
2:12 None doesn't go that way.
2:13 Right, so this is working in terms of the template
2:15 but it's not working in terms of the data.
2:17 So let's go and change this down here
2:20 I'm going to call Package_service.get_package_count
2:26 Now this doesn't exist, but it will in just a moment.
2:33 Release count, we're going to do users in a separate service.
2:36 So we can go over here and hit Alt + Enter
2:37 create this function, and it didn't really know what to do.
2:40 It's supposed to return an int.
2:43 And over here, we saw how to do queries.
2:45 If we start by creating a session
2:47 and we're going to import our db
2:50 so we'll say, import pypi.org.
3:01 Get our little db_session there
3:03 and we'll say create_session. That's cool.
3:06 And then we simply have to do a query
3:08 so we're going to come over here
3:09 and we're going to say, I would like to go to the session
3:12 and do a query, and then you say the type.
3:16 We want a query package
3:17 because that's what the count we're looking for.
3:21 And we might do a filter to say
3:23 where the package_id is this
3:24 or the publish date is such and such
3:26 or the author is this person or that
3:29 but all we want to do is a super simple count.
3:33 That's it, that's going to be our package count, and let's
3:36 going to let it write that as well
3:38 and we'll just grab this bit.
3:41 So this one is the same except for
3:43 instead of querying package, what are we querying?
3:45 We query release.
3:48 Clean that up, and then finally
3:50 let's go over here and figure out where we are.
3:53 We also want to have a user service.
3:55 So if I come over here, and just copy paste
3:57 I can change that to user. Do a little cleanup.
4:14 All right, that looks pretty good.
4:15 Now, it might seem silly to just have this one function
4:17 but of course, we're going to log in, we're going to register
4:19 there's all sorts of things this user session will be doing.
4:23 So now if we go back
4:24 we should be able to go to our import up here.
4:27 And we'll do user service as user service.
4:33 And here we'll do user_service.get_user_count.
4:37 Okay, moment of truth.
4:38 If we run this, it should be doing those queries
4:41 from the database, creating our unit of work, our session.
4:44 Now, it's pretty boring, all we do is do a quick query.
4:48 We're not, like, inserting and updating data yet
4:50 but it still should be doing some good stuff.
4:52 Let's do a Save, which should rerun it.
4:56 See the little green dot, it's still running
4:57 so it should've rerun. Moment of truth: refresh.
5:01 Bam, look at that! How cool is this?
5:03 So 96 projects, 5,400 releases, and 84 users.
5:09 That's exactly what we saw earlier.
5:12 And if we go over to our little inspector
5:14 and we go to the network, and we say just show us html
5:18 and we do this again a couple of times
5:22 you can see that the response time
5:24 even going to the database and doing three queries
5:26 is 11 milliseconds. That's pretty solid, right?
5:29 Not too bad at all. So our site's working fast
5:33 it's using the indexes that we set
5:35 and it's pulling back the data.
5:36 Well, it probably doesn't use an index for a count
5:38 but it would if we were doing the right kind of queries
5:40 like, for the new releases.
5:42 So, I think our little stat slice is up and running
5:45 and that's how we're getting started using this data
5:48 that we inserted, that we got from those JSON files.
5:51 Lots more of that to do really soon.