Building data-driven web apps with Pyramid and SQLAlchemy Transcripts
Chapter: Using SQLAchemy
Lecture: Querying data: Latest releases
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
Now it's time to write another query.
0:02
Actually we're going to write a couple queries
0:04
to do something interesting and this actually might be
0:07
the most complicated thing we do in the entire website.
0:10
But, yeah you'll get to see a lot of stuff in action.
0:12
So let's look over here real quick, here's our homepage.
0:15
We're now driving that little slice out of the database.
0:19
But remember this, this is just our fake data.
0:21
So we want to actually go get the latest releases
0:25
and then correlate those back
0:26
to the latest packages and then show them here.
0:30
Okay, how do we do that?
0:31
Well, we're going to write a function.
0:35
We want to say how many releases we want.
0:37
So, we'll have a limit by default that limit is going to be 10.
0:41
And it's going to return a list of package.
0:45
List comes out of the typing model
0:47
and the package that we already have.
0:49
As always, everything starts like this
0:51
and then we're going to return
0:53
something down here at the end.
0:55
So what happens at the middle?
0:57
Well, the data model that I have chosen
1:00
which was the most sort of naive and straightforward one
1:02
maybe isn't the best for this site.
1:05
Maybe it would have been better
1:06
to have a little information on the package itself
1:08
about when it was last modified but, it doesn't matter.
1:12
What we're going to do is we're going to go
1:13
get the latest releases
1:14
find out the package IDs those correlate to
1:17
and then we're going to go
1:19
and get those packages out of the database.
1:22
Great, so how do we do that?
1:23
First we say releases is going to be
1:26
and we're going to do a query.
1:27
And the way that queries work is you to your session
1:29
and you say I'd like to create a query
1:32
based on a particular thing.
1:34
Here we're querying Release.
1:36
Now we can do where clauses like this, filter.
1:41
We can do orderby.
1:43
So we actually don't want to do a filter.
1:44
We just want to say show us the newest ones.
1:47
And whenever you put some sort of qualifier here
1:50
like a filter or a something that you're sorting by
1:53
you use the type name.
1:56
Release. and then what are you going to order by?
1:58
Created date descending.
2:01
Now PyCharm gives a warning
2:02
that says descending doesn't exist.
2:04
Nevermind it definitely does.
2:06
That is going to give us back the releases.
2:11
And we probably want to limit those by something.
2:14
We don't want to get every release
2:15
that's going to be a lot.
2:16
So we'll say limit, we don't know for sure
2:19
if maybe there's some kind
2:21
of release done really quickly on the same package.
2:25
So if we want to be sure that we give them
2:27
say 10 or however many they're asking for
2:29
we should probably double this or something.
2:32
So I'll say the limit times two.
2:35
And that's because we could have a really quick release
2:37
of requests and then another one of requests.
2:39
And then, you know we'd have two more
2:42
than just the 10 releases.
2:43
Actually get the 10 distinct packages.
2:46
So we've got that.
2:47
Now, what I want to do is come up with
2:48
just the package IDs as a list of strings.
2:54
I'm going to do that with a simple list comprehension.
2:56
So say r.package_id for r in releases.
3:00
In fact, if we wanted to do it I could even do it
3:02
as a set comprehension. and that would give us
3:06
distinct packages so we don't have to check
3:08
for them existing twice. Beautiful.
3:10
So, we've got the latest releases
3:12
and now we've converted those to the distinct
3:14
set of the latest package IDs.
3:16
Now all you got to do is actually get the packages.
3:21
I just realized my little trick here is going to be great but
3:25
we're going to need packages in order as well.
3:30
So let's do this.
3:35
So now we need to do a query
3:37
so we go back to the session.
3:38
Go to query and this time on package.
3:40
And how do we say I would like all the packages
3:42
that are in this set of package IDs.
3:45
Turns out that's super easy.
3:46
Say filter
3:49
package.id.in_
3:53
Because in_ is a keyword they say in underscore you give it
3:58
package IDs. That's it.
4:00
That's going to give us all the matching packages.
4:04
Now, we need to preserve the order.
4:06
So, if all we wanted was the packages
4:08
we could just kick those back.
4:10
But remember, we asked for more than one
4:12
so we don't get duplication, things like that.
4:15
So, let's go and actually turn this into a quick dictionary.
4:18
p. based on the ID.
4:23
Do a little dictionary comprehension on that result
4:27
and then finally we'll get these back.
4:37
so we're just going to put stuff into this
4:39
little results array right here and we're going to do it in order.
4:42
There's probably a cool join way if I was better
4:45
at data bases I could do that here
4:47
but we're doing it this sort of convoluted way
4:49
and it's going to work fine.
4:51
Maybe not the most efficient, but not too bad.
4:57
Alright, so we've got our results
4:58
going through the releases in order
4:59
and adding the packages in order
5:01
and then we're adding them back.
5:03
So this should work, but we're not calling it yet.
5:06
Lets go over here, remember our get_test_packages?
5:09
So we'll say, package service, not latest releases.
5:12
Now, this is also not going to work
5:14
because if we go over to our template
5:16
it was using that fake data which had names.
5:19
We don't have names, we have IDs.
5:21
We actually do now have a description
5:23
so let's put that in there. p.summary in here.
5:27
Alright, let's give it a shot, see how we're doing.
5:30
Here's where the stuff goes, we run it, Boom!
5:34
Sweet, look at that. We've got 1, 2, 3, 4, 5
5:38
6, 7, 8, 9, 10. Exactly like we asked for.
5:42
Awesome! So now we've got our packages right here.
5:46
It looks like awscli was the most recent release
5:50
out of the ones that we have.
5:52
And now if we click on it, well it doesn't take us anywhere.
5:54
Let's do one more thing.
5:56
We have this ID here, and lets fix r href.
6:00
This is project/{id}, refresh
6:06
Boom.
6:07
That page needs some help.
6:08
But this page, this page is done.
6:10
This is awesome.
