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 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.