Adding a CMS to Your Pyramid Web App Transcripts
Chapter: Appendix: Modeling data with SQLAlchemy classes
0:00 One of the core relationships in this model is that we have a package and packages have releases.
0:07 A release has a package and a package has many releases. Okay. So let's model that right here.
0:14 Now, SQLAlchemy has a way to build in these relationships which ultimately create foreign key constraints and relationships in the database itself.
0:23 So we come over here and we can just say releases is equal to and we need to go import up here...
0:31 some more stuff. We need to import the orm to explicit use that. I'll just say as orm... and down here we'll say orm.relationship.
0:40 The first, again it's *arg, **kwarg which I don't know, it's a pet peeve of mine. There's just a bunch of keyword arguments.
0:48 Let's just explicitly set them with default values but anyway, here we have, sum value the first thing
0:53 we have to pass, the first positional argument is the name of the class that we're relating to. There. I'm going to put release.
1:02 What we probably want is to have a certain order here. The way it's going to work is we're going to get a package back
1:09 and it's going to have a property or a field which is a list that we can just iterate over so for, r in p.releases, things like that.
1:19 Having some form of order on that is going to be really important. Either descending or ascending, of course we could sort it
1:26 every time we get it back but it's much better to let the database do the default sorting. If we need to change that, we can.
1:32 The thing that we're going to put here is we're going to say order by, equals. Now we could put two things here. One thing is I could say release
1:42 and we got to import that, and we could say major version like that and that would be ascending or I could even say descending like this.
1:50 That's going to show three, two, one but then there's other parts as well right? There's the minor version, if the major version matches
1:58 we want to sort by that. This is pretty common to write like this but in this case what we actually want to do is we want to put this into a list.
2:07 So we're going to do major and then minor, and then build. We'll leave the comma. So that's going to do the ordering.
2:18 If we go over here and look, these all have indexes so the ordering should be nice and fast. There's not that many anyway but still, good.
2:26 Over on the other side, we would, if we do a query for a package, we're going to get its releases but then on each individual release
2:36 we'd like to be able to navigate this relationship in code in reverse, without actually going back to the database.
2:43 We can say backpopulates equals package. That means over on the release, somewhere down here we're going to have a package property
2:57 which we'll do more details on that in a second. But when we get one of these packages and we interact with its releases
3:04 each one of the ones that comes back is going to have that set to the package that we got from the database. Makes sense? Okay.
3:11 This lets us navigate bidirectionally which is really really important. This side of things, I think is done.
3:20 The releases is a little more interesting. Let's go, how do we know that a release is related to a package in the database.
3:27 Well, it's going to have a package ID is equal to some SQLAlchemy thing. Right, some SQLAlchemy column here.
3:36 Now, this is going to be set by the relationship but this is a field or column in the database
3:41 that has to be set, and it's going to be SQLAlchemy not string. That's the way that IDs are packaged right, if we go up here and look.
3:50 This is a string, so that has to be a string there, right, those match. But in addition to being just a regular string
3:57 it's going to also be, a foreign key relationship. We'll say SQLAlchemy.ForeignKey. When we did our relationship back here
4:07 we spoke in terms of classes, the release class. When we talk about the foreign key we talk in terms of databases
4:14 so we'll say packages, not capital p package, dot ID. That is going to store the relationship in the database
4:22 but we also would like to be able to navigate it in memory and code, so here we're going to do orm again we got to import that.
4:33 Come down here, and this will be a relationship... To the class package and it's going to back populate releases.
4:44 Let's look at that over here, and it's going to back populate this so if we get a singular release it's going to go and we interact with its package
4:54 then it's going to automatically do this right so this bidirectional nature and then we're talking about this class here.
5:01 It can be confusing when you're working with these relationships when you speak in database terms when you speak in Python type terms
5:08 but here's how we're going to do it. Now in order for this to have any effect of course we need to go over here and drop these two tables.
5:17 If we rerun it we'll see if we got it right. Encouraging, let's look again. Now over here, actually if we look at releases...
5:26 if we look at releases, you can see we've got package ID... is a relationship over to over to package.ID.
5:33 Alright, we have that relationship modeled there which is pretty awesome. We have our foreign key constraint right there.
5:39 From package, back, did it put it on the other one as well? No, just on the releases table but that's all we need.
5:47 Now that's pretty interesting to see it show up in the database but what we'll see is if we can come over here and we
5:53 obviously this is not going to work, alright. This is not really a thing but let me just type it out. So if we have p as a package that we somehow
6:01 got from the database then we can say p.releases say for r in releases print r dot and then off it goes right, major version or whatever.
6:14 This relationship will mean that we can do a single query to get the package and then we can just navigate this relationship.
6:20 Similarly if we have this we can go .package, .ID if we wanted to print out the name right? And that would navigate these two tables in the database
6:30 using our classes here and that's what we've built with our ORM relationship on both sides of the story.