Building data-driven web apps with Pyramid and SQLAlchemy Transcripts
Chapter: Modeling data with SQLAlchemy classes
Lecture: Relationships

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