Python for Entrepreneurs Transcripts
Chapter: Accessing databases from Python: SQLAlchemy ORM
Lecture: Demo: Part 4 Relationships
Login or
purchase this course
to watch this video and the rest of the course contents.
0:01
There is a few final touches we need to put on this before you could call this a proper database model. So you can see we pretty much have our tracks
0:10
and our albums put together the way we want, we will be able to put them in the database and so on, but, one thing that is missing is the relationship,
0:17
obviously, a track should have an album associated with it, so we can come down here and do an album_id
0:24
and this can just be an integer column just like that one. This would let us be able to make the relationship in the database
0:31
in the sense there would be a number here we could do another query to go back and find the album and vice versa,
0:38
but this is not enough to actually create the relationship, so in order to do that we have to have a sqlalchemy.foreign_key
0:44
and here we have to say the name of the table and the field that we want before.
0:51
So this is going to be Album.id, in case we go back to that album class we are looking for the id here. Now that's part one of this relationship.
0:59
So we could use this to actually do the queries to find the tracks and so on, but SQLAlchemy will go a lot farther for us.
1:09
So if I were given a track, maybe just I'm in a method and somebody does a query to the database to get a track back
1:17
and they give it to me, instead of my trying to do another query to figure out what is the title of the album or something like that,
1:25
SQLAlchemy will actually map through lazy loading the actual album instance that this ID would represent so we can say this...
1:34
Now, for this relationship to work, this is going to be an ORM piece here, so we are going to import the ORM at the top,
1:42
there is a relationship that we'd like, now, there is all sorts of stuff that we can put in here, let's say album is going to be the relationship,
1:50
and we can say on the album class, there is probably a reverse of this relationship,
1:55
there is probably a set of tracks that is a list or something like that, that represents all the tracks that have the id for this particular album.
2:04
So we can when we get this album traversing this relationship, we'd like it to also know about its tracks.
2:11
So there is kind of this two way directional thing here, so we can do this by back_populates. And then we can say "tracks", like so.
2:20
And PyCharm wants a new line, so we'll go and give it to it. OK great, so this will give us the relationship back to the album,
2:27
we need the reverse of course. So let's go over here, now I think this side of the story is more interesting, because over in the track
2:38
there is just a singular album and a singular album id, but on this side, we are going to actually have a one-to-many relationship,
2:44
one album has many tracks, so we are going to have tracks here and what do we put to actually have a collection?
2:52
Well, again, sqlalchemy.orm.relationship, here this is going to be "Track", and just like before, it's going to back_populates this time "album",
3:08
that's the actual name over here, so that is talking about this, it's going to back populate that, and then there is
3:15
all sorts of other powerful things we can add. So for example, we can talk about the order,
3:20
so, remember we had that, we are going to talk more about this in a minute, we have a display order over here, so we can say
3:26
we would like things in this list to automatically be ordered by display order. And I think it has to be Track.display_order,
3:36
we can come over here and talk about what type of collection we'd like to use, by default I think it's a list but one of the problems you can run into
3:45
when you have an ordered set like this, like track should always be in order, if I put a new one in there, before it goes into the database
3:52
it's not necessarily ordered, so we can solve it like this. So we can create what's called an ordering list,
4:05
it comes out of SQLAlchemy as you can see on line 3, and we can say this is going to be display_order as well.
4:11
OK, so this way of we insert something into the object before it goes to the database, it's also still going to be ordered,
4:17
so we don't have like this weird well... has it been saved, has it not been saved, well you can't look at the order if it's not saved,
4:22
like... that's no fun, let's not do that. We could also talk about deletes, the tracks should not exist,
4:29
without the album. The album can exist without the tracks but not the other way, so when we delete the album, we would like to also delete the tracks.
4:36
So, we can say "cascade the deletes to all" there, that's quite the relationship but I think we're in quite a good place.
4:44
Let's see if we can get this to work again. Now, we are not going to be able to run this and have it work in fact,
4:49
it will probably crash, it might not, no, there is not enough of a change; if we go look at our database, there is no change here in terms of
4:57
like the track doesn't now have an album id, there is no additional indexes, so when you're just getting started
5:02
like this, honestly, the easiest thing to do is just blow away the database and rerun create_all, let's run it again,
5:10
OK, so that should recreate the data that will come back in a second, if we refresh this, now you can see there is a little bit more stuff,
5:16
we've got our album id we've got our foreign key constraint, things like this, OK.
5:22
Our relationships that we just modeled are now created in the database.