Building Data-Driven Web Apps with Pyramid and SQLAlchemy Transcripts
Chapter: Using SQLAchemy
Lecture: Querying data: Latest releases

Learn more about this course
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.