#100DaysOfWeb in Python Transcripts
Chapter: Days 33-36: Database access with SQLAlchemy
Lecture: Relationships
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
Let's come back and address this ToDo I left here about relationships. We want to set up a foreign key constraint
0:07
a relationship between the scooter and a location. A scooter can be out in which case it has no location or it can be parked
0:15
where it has to be in a location that's specified in one of the locations over here. So we can model this in SQLAlchemy. This comes in two parts
0:25
and its also sort of bidirectional. So what we want to do is say that it's possible that there's a location id which is in sa.Column integer like this.
0:37
So the scooter may have an id set on it and that id will correspond over to one of the primary keys coming from our location.
0:48
This would let us model that in the database but it won't actually create that true relationship. That database integrity that we want to get.
0:56
So what we can do over here is we can add a few things. We can say this is an sa.ForeignKey
1:03
and the foreign key we speak in terms of the database names. So over here we said the name of the database is locations
1:11
and the thing we want to map to is this id. So we're going to say locations.id. And they may not always be there so we're going to say noble is true.
1:23
So this is going to set up the relationship the foreign key constraint. But what it does not do is it doesn't let us navigate.
1:30
When you think about the scooter or let's look at it form the Location's perspective. The location is going to have a bunch of scooters parked at it.
1:38
And it would be nice to go to any given location to say dot scooters and that's a list of scooters. Well, we can actually model that over here as well
1:48
and it'll say scooters equals. Notice I'm importing sqlalchemy.orm. So you can say orm.relation. I'm going to say scooter.
2:00
Now it's a little confusing because when you speak in terms of these relations you say the type name. You say that name, not that name.
2:09
Whereas in the foreign key constraints you say the database name. That's okay. I'm going to come out right here and say this is going to backpopulates.
2:19
Location. What is this? As I told you, it's biderectional. I can go to any given location I got from the database and say
2:26
show me all of the scooters, a one to many relationship. From the scooter, it may be located in a location.
2:32
So, we're going to go and do the reverse of this as well. So we're going to have a location and again, like before, we're going to
2:40
from sqlalchemy import orm I mean orm.relation. And this is going to go back to location. And it's this id that actually drives
2:50
the database, and then this lets us navigate memory. So there's, for every scooter there's one or zero locations. For every location, there's
2:59
potentially many scooters. So here's our one, sort of models our one side of the one to many. And then this part just models the many to one here.
3:13
Okay, that should totally set up our database and if I run it, you might think things are going to be okay. They are not. Notice our
3:22
scooter is supposed to have a location id. Does not have it here. Let's rerun our code. We can exit out again. It should have recreated this right?
3:33
Hmm, why isn't that showing up? Well it turns out this create_tables thing that we did here this create_all will create new tables, it will not
3:44
update or modify existing tables. We need something totally different for that something called migrations.
3:50
Do that in another whole chapter later in the course. But for now, what you need to understand is it will not apply those changes
3:57
till either you delete the tables or recreate it or you do this migration thing that we're going to do much later in the course. Since we have no data
4:04
we're gong that delete route. Delete, rerun. Now if we do the little refresh whoohoo, location is there. And you can see, it has a little key
4:13
foreign key on it. And that's supposed to map over to as you can see right here from location_id to locations.id.
4:22
Perfect, our relationships are all set up.