Python-powered chat apps with Twilio and SendGrid Transcripts
Chapter: Using our database with the SQLAlchemy ORM
Lecture: Relationships between orders and users
0:00 Before we call our ORM model is completely done and move on from
0:03 them. Let's just quickly follow up with one thing that we did touch on,
0:07 but we didn't finish, and that's our relationships, over here,
0:11 I would like to come over here and say that this is a relationship and in
0:15 order to specify relationships, I need to go over here and import something else Will
0:19 say from sqlalchemy.import orm, Now,
0:26 in the ORM, we can use that to define a relationship like this,
0:29 and then we just specify the class name.
0:33 These relationships can be tricky because sometimes they speak in table name like lowercase users,
0:39 lowercase orders, and sometimes they speak in class or object terms.
0:44 Right here. What we put is the class that we're related over to.
0:48 So we call this order, now,
0:52 This user class kind of stand alone,
0:55 it doesn't actually store any information about the orders.
0:58 It only has the fundamental ideas of the user itself.
1:03 Remember, it has something over here that actually does have information linking them together.
1:08 So this is where we know this order belongs to a particular user.
1:13 But what we can do is we can go over here and say,
1:15 This is an sa foreign key And then and here we put the name the
1:19 column, again. Sometimes you speak in class terms.
1:23 Sometimes you speak in table database terms.
1:27 This one we speak in database terms.
1:29 So its users id not capital user,
1:32 little case users. Plural, right?
1:36 So that says, there's a relationship between the order table and the user table,
1:40 and the relationship is go back to the users table by the id.
1:43 Which is the primary key. Great,
1:47 but we'd also like to have a friendly thing.
1:50 When I go to order, I'd like to say .user and actually get the
1:53 user. And if I haven't loaded from the database SQL
1:55 Alchemy will go and do a lazy load,
1:57 a secondary query to pull that information up.
2:01 And that's this information right there.
2:04 So again, this is a relationship.
2:05 So we'll say from sqllchemy import orm.
2:13 This one. We speak in class terms,
2:15 so it's going to be user like that.
2:18 Now there's a relationship of final order.
2:20 There's the user it belongs to.
2:22 If I go to the user.
2:23 There's all the orders and it would be nice if I could say,
2:26 If I happen to go user,
2:28 let's say order.user.orders, that that already is loaded because I've actually
2:34 done the queries on both directions out of the database don't need to go back.
2:38 So what we can do is we can say these back_populate,
2:45 and then you say the name
2:48 This one will be user. You say the name of the field over there.
2:52 So here it's called User. So when we go to the orders,
2:55 it sets this object to point to that field there and similarly orders.
3:05 Okay, now that should do it.
3:07 Let's try to run our app and see what happens.
3:09 Looks like everything's fine. We go back to our database and I say,
3:13 Refresh this. There's nothing changed about this right.
3:19 We should probably least have a key to represent the foreign key,
3:22 and you don't see it here.
3:23 What's going on? The problem or not really a problem.
3:26 It's just the way it works,
3:27 is the challenges SQLAlchemy will never change a table once it exists.
3:31 No matter what you do to the class once the table exists,
3:34 it's not going to make that change.
3:36 It wants to make sure like you don't to lead a column and it throws away
3:39 data in production. That would be bad so we can use alembic and migrations.
3:45 And that's fairly complicated, really beyond the scope of this course.
3:48 So what we're gonna do is a simpler style.
3:51 We're going to hit delete and rerun the app, that's going to come over here
3:57 and recreate. Regenerate this if we go now and refresh this.
4:05 Hey, look at that. We've got a foreign key from user id over
4:08 the users Perfect, and you can see the little key.
4:11 The Blue key means foreign key,
4:14 this one. I don't think it has any changes in the database,
4:16 but it's the orders that now have that foreign key relationship.
4:19 Alright, perfect. So quick Review.
4:23 We went over and had our user id,
4:25 and we upgraded it to a foreign key and then we use that foreign key to
4:28 define a relationship. We also wanted to make sure that if you work with one
4:35 we automatically set the values for the other case you kind of bounce back and
4:38 forth. So orders back populates user user, back populates orders and there we have it
4:44 I'm very easy way for us to work with the relationships between a user and it's orders.