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.