Building data-driven web apps with Flask and SQLAlchemy Transcripts
Chapter: Using SQLAchemy
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
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.