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