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