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

Talk Python's Mastodon Michael Kennedy's Mastodon