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