Building data-driven web apps with Pyramid and SQLAlchemy Transcripts
Chapter: Using SQLAchemy
Lecture: Querying data: Home page

Learn more about this course
Login or purchase this course to watch this video and the rest of the course contents.
0:00 Here we are back at our demo app
0:01 and remember, this is all being driven with fake data
0:04 but we're about to change that.
0:06 Notice these 0 projects, 0 releases, 0 users.
0:09 Not so amazing
0:11 and here, these are just things that we've hacked in here.
0:15 So our goal during this video, this lecture, is
0:18 to actually fill out of those pieces right there.
0:22 So first of all, where is it happening?
0:23 Let's close some of this stuff off.
0:26 Right here we're returning the test packages
0:28 and in our template, we literally just have
0:32 zero, zero, zero.
0:34 So first of all, we need to pass that data along.
0:37 So let's say package_count, release_count, user_count
0:41 is zero and then we can use these over in our template.
0:48 So we can put 'em like that.
0:49 We could actually get a little better format
0:51 if we do it like so.
0:54 Like this, that way we'll get comma separators
0:57 or digit grouping there.
1:01 So do the same for releases and users.
1:08 Let's rerun it and see how it works.
1:11 Ooh, releases_count.
1:14 Release, singular, count, it didn't like that.
1:17 Amazing, it's still zero but now
1:19 it's coming from that place.
1:20 We can see if we were to put something here
1:25 like 10,000, remember you've got to rerun it
1:27 for the Python code to change.
1:29 There we are.
1:30 Now we have 10,000 projects, awesome.
1:32 So it is being driven with that data.
1:34 Now our goal, of course, is not to type zeros here
1:37 but to go and get the data from the database.
1:40 There's two ways we can do this.
1:41 Well, there's probably infinitely many ways to do this
1:43 but there's two obvious ways in which we can do this.
1:45 One obvious way would just literally be
1:47 to start writing queries inside this home index.
1:51 That is not the way we want to do things.
1:53 It makes it hard to test our code.
1:56 It makes detangling the controller logic
2:00 from the data access logic problematic and so on.
2:04 So a pattern that I've settled in on is putting
2:07 what I call data access services
2:09 and sort of grouping them by their roles.
2:12 So what we're going to do is we're going to come over here.
2:13 We're going to define I guess a Python package.
2:17 We'll call this services
2:18 and these are not external services
2:19 these are the data services I'm talking about
2:22 and let's go and add Python file called package service.
2:27 In here we can have some functions, def.
2:39 Do a release count and let's take something similar
2:44 and we'll put that in and do a user services
2:46 well they're going to do many more things than count
2:48 of course, but this will get us started.
3:00 All right, so up here we're going to save
3:03 from pypi.data, import, oh not data, sorry.
3:10 services, ah, it looks like I made that
3:14 in the wrong place, oops.
3:20 Okay so we'll import this and then we can come down here
3:22 and we can just say, .package_count
3:26 and release_count and we'll do the same for users.
3:29 User service, okay?
3:34 Now, I think it returns None, which is probably going to
3:37 crash, so let's go and actually implement these.
3:40 All right, so they're all going to be basically the same.
3:43 In order to interact with the database, we need session
3:46 right, so we'll say session = DBSession.factory
3:51 like that and later, we're going to just call close
3:55 on the session.
3:56 We don't actually have to do anything
3:57 and we can just skip it.
3:58 I think it'll get garbage collected straightaway.
4:00 So we want to do a query so I'll return session.query
4:05 and what do we want to do a query on?
4:07 Well that's on packages, so we import that
4:11 and we could do a filter.
4:12 We could do an orderby
4:14 but really all we care about is a super simple count.
4:17 That's pretty easy, right?
4:18 See the release
4:23 and let's go do the user.
4:32 All right, run it again.
4:33 This thing should work.
4:35 Let's go refresh and look at that.
4:38 How many packages did we import, 96.
4:40 How many releases do we have, 5,400
4:42 with 84 users, awesome.
4:45 So everything is in place and now we can start doing
4:49 things like get our new releases by just simply
4:52 adding one more function over here.
4:54 So we'll do that later but everything's coming together
4:57 and our little data access piece, well
4:59 it's working really nicely.