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