Python-powered chat apps with Twilio and SendGrid Transcripts
Chapter: Appendix: 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 The last thing we have to do
0:01 to round out our relational database model
0:04 is to set up the relationships.
0:05 So far we've effectively have just a bunch of tables
0:09 in the database with no relationships.
0:11 We're not really taking advantage of what they have to offer
0:13 so in order to work with these let's go up here
0:16 I'm going to do a little bit more of an import the ORM aspect
0:20 to we'll talk about relationships
0:22 and Object Relational Mappers and so on.
0:24 So over here, what we want to do is we want to have
0:28 just a field releases and when we interact with that field
0:32 we would like it to effectively go to the database
0:35 do a join to pull back all the releases
0:37 through this relationship
0:38 that are associated with this package.
0:41 So basically where the package ID
0:43 on the release matches whatever ideas here.
0:46 So the way that we're going to do this
0:48 is we're going to go over her and say this is an orm.relation
0:51 relation just singular like that
0:54 and then in here we talk about what class
0:57 in SQLAlchemy terms this is related to.
1:00 It's going to be a release, that's good
1:03 and that alone would be enough but often
1:06 you want the stuff you get back through
1:08 this relationship to have some kind of order.
1:11 Wouldn't you rather be able to go through this lesson
1:14 say it's oldest to newest or newest to oldest releases?
1:18 That would be great if you had to do no effort
1:20 to make that happen right?
1:22 Well guess what, we can set it up
1:23 so there's no effort so we can say, order_by=
1:28 Now we can put one of two things here.
1:30 We could say I want to order my one column
1:32 on the release class so we'll just say release
1:34 and import it at the top.
1:36 You could say we want to say accreted data descending
1:39 like this and that would be a totally fine option
1:43 but what we want to do is actually show, if we go over here
1:47 we want to order first by the major version
1:49 then the minor version, then the build version.
1:51 All of those descending so in order to order
1:54 by multiple things, we're going to put a list right here
1:57 like so and let's say we're going to order it by major version
2:03 minor version, and build version.
2:06 All right so that means we get it back
2:08 it's going to go to the database
2:09 and have the database do the sort.
2:11 Databases are awesome at doing sorts
2:13 especially with things with indexes
2:15 like these three columns right here have.
2:18 Okay so that's pretty good and we're also going to have
2:21 a relationship in the reverse direction
2:23 so package has many releases.
2:26 Each release is associated with one package.
2:29 So over here when I have a package for a moment
2:32 I'm going to leave it empty
2:34 but we're going to have this field right here.
2:36 So what we can do is we can say is back populates package
2:45 what does that mean?
2:46 That means when I go here and I get these all filled out
2:50 and I iterate over my get one of the releases off
2:53 maybe I hand it somewhere else
2:55 somebody says .package on that release
2:58 it will refer back to the same object
3:00 that we originally had that we accessed it's here
3:03 so it kind of maintains this bidirectional
3:06 relationship behavior between a package, it's releases
3:10 and a given release and it's packaged automatically
3:12 so might as well throw that on there.
3:15 All right this one's looking pretty good
3:16 let's do the flip side.
3:17 How does it know that there's some relationship?
3:20 I just said this class is related to that boom done.
3:24 Well that's not super obvious what this is.
3:26 So what we're going to do is like standard database stuff
3:29 is we're going to say there's a package_id here
3:33 and this is a field or column in the release table
3:36 to maintain that relationship
3:38 and this will be a sa.Column, SQLAlchemy Column
3:43 and what type is it going to be?
3:45 It has to correspond exactly to the type up there.
3:49 Okay so this has to be SQLAlchemy.string
3:52 but in addition that it'll be SQLAlchemy.ForeignKey.
3:57 So it's a little tricky to keep these things straight
3:59 in your mind but sometimes we speak in terms of classes
4:02 sometimes we speak in terms of databases.
4:05 Over here I said the class name for a relationship
4:09 but in this part, the foreign key
4:11 you speak in terms of the databases, it'll be
4:15 lowercase right that's this thing we're referring to
4:18 that and the ID.
4:20 So here we're going to have that foreign key right there
4:22 and then this we can set that up
4:24 to be a relationship as well.
4:26 So again we got to get ahold of our orm
4:30 orm.relation and it's going to relate back to package.
4:37 Okay, I think that might do it.
4:40 We're going to run and find out but we have it working
4:42 in both directions so here we can go to the releases
4:45 and then we can go, this will establish that relationship
4:49 and this will be that referring
4:51 the thing that refers back to it.
4:53 Now we've already created those tables
4:55 so in order for this to have any effect
4:57 we have to drop those in this temporary form
4:59 or member migrations later but not now.
5:03 All right, let's run it and see if this works.
5:07 All right, it seems happy that's a pretty good sign
5:09 SQLAlchemy's pretty picky so we go over here
5:12 there really shouldn't be anything we notice
5:14 about this table but here we should now have a package
5:18 and notice in that blue key, even right here
5:20 there's a foreign key relationship if we go back
5:23 and interact with this, say modify table
5:27 we now have one foreign key from package_id
5:30 which references packages ID, that's exactly what we wanted.
5:35 Now we don't have any data here yet
5:36 so this is not going to be super impressive
5:38 but let me show you how this will work.
5:41 Imagine somehow magically through the database
5:44 through a query which we don't have anything yet
5:46 but if we did, I'm going to come over here
5:48 I'm going to go give me a package, right?
5:50 Now I'm just allocating a new one
5:52 but you could do a query to get to it right?
5:54 So then I could say print P., I don't know what
5:58 ID would be the name and then I could say print
6:03 our releases and say for r in p.releases
6:10 that will print out, here we go
6:13 we go through and print them out.
6:17 All right and we would only have to do one query
6:21 except when explicit query here to get that
6:24 and then down here, once it's back
6:26 this would go back to the database potentially
6:29 depending how we define that query
6:31 and then do a query for all the releases
6:33 ordered by the way we said
6:34 and then get them back, that's pretty cool.
6:36 Maybe like notice it says you can't iterate over this
6:39 it's kind of annoying, let's see if I can fix this
6:41 to say this is a list of release.
6:46 Import that from typing, all right.
6:50 So now it's happy and if I say r. notice that
6:53 so maybe this is a good idea, I'm not sure
6:55 if it's going to freak it out or not
6:56 but I'll leave it in there until it becomes a problem.
6:59 All right, let's actually just run it real quick
7:01 make sure this works.
7:03 Yeah that worked, didn't crash
7:05 when we did that little print out.
7:07 There was nothing to print but still, pretty good.
7:10 So those are the releases.
7:11 Once we get some data inserted
7:12 you'll see how to leverage them even for inserts
7:15 they're pretty awesome.