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.
