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