1
00:00:05,300 --> 00:00:11,200
Welcome back in this lecture we're going to talk about relationships and databases often for larger

2
00:00:11,200 --> 00:00:16,330
projects you're going to want to have multiple models to represent different tables inside your database

3
00:00:16,900 --> 00:00:19,540
and these models may have relationships to each other.

4
00:00:19,540 --> 00:00:23,770
For example you could have a model for puppies and another model for their owners.

5
00:00:23,830 --> 00:00:30,080
So there's a relationship there between an owner and it's puppy so to understand model relationships

6
00:00:30,140 --> 00:00:37,040
we need to first review primary keys and foreign keys and tables a primary key is a unique identifier

7
00:00:37,040 --> 00:00:40,550
column for every row inside of a table.

8
00:00:40,550 --> 00:00:47,750
So something could be like a Social Security number or national ID card for a person a name is not unique

9
00:00:47,750 --> 00:00:51,080
identifier because different people could have the same name.

10
00:00:51,110 --> 00:00:57,110
So the primary key is going to be a column whose value is going to be unique for every single row a

11
00:00:57,110 --> 00:01:05,120
foreign key is just the primary key from another table and that allows you to connect two tables together.

12
00:01:05,360 --> 00:01:11,810
So let's show you an example let's imagine we have two models or two tables inside of our database.

13
00:01:11,850 --> 00:01:17,340
We can have a puppy model where is an ID tag or a primary key for the puppy.

14
00:01:17,460 --> 00:01:22,620
And then the puppies name the puppy's name is not a primary key because different puppies could have

15
00:01:22,620 --> 00:01:30,060
the same name the owner table has three columns a national ID number such as a social security number

16
00:01:30,390 --> 00:01:32,090
or a national ID card.

17
00:01:32,220 --> 00:01:37,260
And that would be a primary key because every single owner has a unique national ID number.

18
00:01:37,260 --> 00:01:42,330
Then we have the owner name and then we have the puppy ID for the owners puppy.

19
00:01:42,330 --> 00:01:48,300
So the puppy ID inside of the owner table that's a foreign key because that's the column that directly

20
00:01:48,300 --> 00:01:55,230
connects the owner table to the puppy because the puppy I.D. is the primary key inside of the puppy

21
00:01:55,620 --> 00:01:56,280
table.

22
00:01:56,520 --> 00:01:59,420
So that's a difference when a primary key any foreign key.

23
00:01:59,490 --> 00:02:00,680
It's pretty simple.

24
00:02:00,690 --> 00:02:05,910
The primary key is a unique identifier inside of the table and then a foreign key is just that same

25
00:02:05,910 --> 00:02:09,630
column referenced inside of another model or table.

26
00:02:10,290 --> 00:02:14,270
So later on we're going to discuss more complex relationships between two models.

27
00:02:14,430 --> 00:02:19,380
For now let's work through a simple example of connecting two tables together by connecting their respective

28
00:02:19,380 --> 00:02:21,840
models inside of the pie script.

29
00:02:21,840 --> 00:02:25,560
We're going to get a little more practice so we're going to start completely from scratch so we can

30
00:02:25,560 --> 00:02:29,670
review things like Flast migrate as well as importing D-B models.

31
00:02:29,670 --> 00:02:30,690
Let's get started.

32
00:02:30,690 --> 00:02:30,980
All right.

33
00:02:30,990 --> 00:02:37,110
Here we are at a brand new Python script I've called this model's pie and it's actually going to contain

34
00:02:37,140 --> 00:02:41,250
pretty much the whole application but it's really going to focus on just three models.

35
00:02:41,250 --> 00:02:45,190
We'll have a puppy model a toy model and then an owner model.

36
00:02:45,210 --> 00:02:46,770
Let's go ahead and get started.

37
00:02:46,780 --> 00:02:48,410
I'm going say at the very top.

38
00:02:48,420 --> 00:02:49,950
Import os.

39
00:02:50,280 --> 00:02:57,630
And then also same from flask imports flask.

40
00:02:57,840 --> 00:03:06,950
And then we'll say from flask underscore sequel whoops sequel alchemy import sequel alchemy.

41
00:03:07,110 --> 00:03:13,860
And then finally from flask underscore migrate import migrate.

42
00:03:13,860 --> 00:03:17,130
So we've already seen how to do all these commands and separate lectures but we're kind of just putting

43
00:03:17,130 --> 00:03:18,570
them together first.

44
00:03:18,570 --> 00:03:24,570
First step is to set up our school like database so we create our base directory based the IRA and we

45
00:03:24,570 --> 00:03:29,610
do this by saying oh s path the absolute path of.

46
00:03:29,910 --> 00:03:32,550
And then we're going to grab our current location.

47
00:03:32,550 --> 00:03:38,610
So say oh estop half that character's name and then passen the builtin file variable.

48
00:03:38,610 --> 00:03:44,520
Once you've done that will create our application by saying app equal to flask and the will passen the

49
00:03:44,630 --> 00:03:45,980
builtin name variable.

50
00:03:46,530 --> 00:03:49,330
And then we want to set up our configuration.

51
00:03:49,370 --> 00:04:01,530
Say app config and we're going to do here is say sequel alchemy underscore database underscore you are.

52
00:04:02,110 --> 00:04:09,540
And then we set that equal to sequel like colon three forward slashes and then we're just going to grab

53
00:04:09,540 --> 00:04:14,290
a pass again O S path and we'll join our basestar victory.

54
00:04:14,310 --> 00:04:18,040
With data dot sequel lights.

55
00:04:18,160 --> 00:04:22,000
I'm going to collapse this left hand tab to get a lower room here.

56
00:04:23,030 --> 00:04:23,290
OK.

57
00:04:23,310 --> 00:04:27,000
So we've created our the victory for the sequel alchemy database.

58
00:04:27,000 --> 00:04:31,920
Essentially just creating a new file data that sequel like where we're currently located and then also

59
00:04:31,920 --> 00:04:41,820
set up the configuration for Sequel alchemy underscore track underscore modifications and anoints set

60
00:04:41,820 --> 00:04:44,460
that equal to false.

61
00:04:44,500 --> 00:04:51,670
Then finally I'll say D-B is equal to sequel alchemy and I'll pass in my application and then I will

62
00:04:51,670 --> 00:04:57,000
connect them by calling migrate and saying app D-B.

63
00:04:57,070 --> 00:04:57,350
All right.

64
00:04:57,350 --> 00:05:02,080
Hopefully there was a nice review of how you actually set up your configuration when dealing with a

65
00:05:02,080 --> 00:05:03,460
sequel like database.

66
00:05:03,460 --> 00:05:07,300
Now let's move on and actually create a couple of classes or models.

67
00:05:07,300 --> 00:05:09,510
So start off with a puppy class.

68
00:05:09,580 --> 00:05:16,960
I'm going to say class puppy and we inherit from D-B that model and then you can supply a table name

69
00:05:16,960 --> 00:05:17,910
here.

70
00:05:17,980 --> 00:05:25,660
So what I will do is say that this table underscore underscore table name underscore underscore is equal

71
00:05:25,660 --> 00:05:27,720
to puppies.

72
00:05:28,720 --> 00:05:34,030
That also the ID equal to D-B column.

73
00:05:34,270 --> 00:05:42,390
And I'll say D-B integer and then will say primary key equals true.

74
00:05:42,530 --> 00:05:48,140
So notice I'm setting that this is a unique identifier for every single puppy so each pup is going to

75
00:05:48,140 --> 00:05:56,530
have their own unique ID and then we're going to say name is equal to D-B the column d text.

76
00:05:56,530 --> 00:06:01,070
So every puppy can have a name and there were ways to create a One to Many relationship.

77
00:06:01,120 --> 00:06:04,980
So we're going to say that one puppy can have many toys.

78
00:06:05,020 --> 00:06:08,050
So later on I'm going to make a model for toys.

79
00:06:08,050 --> 00:06:12,010
So again this is a one to many relationship that the puppy is going to have.

80
00:06:12,010 --> 00:06:18,340
So I will say toys is equal to D-B dot relationship.

81
00:06:18,430 --> 00:06:26,770
And then I'm going to pass in the string toy and then we're going to say back ref to the back reference.

82
00:06:27,740 --> 00:06:30,360
Is lower case puppy.

83
00:06:30,390 --> 00:06:35,880
And then we'll say lazy is equal to dynamic.

84
00:06:35,880 --> 00:06:40,590
And before we actually break this down I'm going to create the other two classes that we're going to

85
00:06:40,590 --> 00:06:41,310
be working with.

86
00:06:41,310 --> 00:06:45,550
We'll say a toy class that inherits from D-B model.

87
00:06:45,840 --> 00:06:51,280
And right now I'll just say pass here and then I'll creates a class for the owner.

88
00:06:51,420 --> 00:06:54,190
So say owner D-B model.

89
00:06:54,510 --> 00:06:57,100
And here here I'm going to say pass as well.

90
00:06:57,120 --> 00:06:59,300
So this is the other two tables in the database.

91
00:06:59,340 --> 00:07:02,240
One for the toy and then one for the owner.

92
00:07:02,280 --> 00:07:05,400
But right now let's focus on this relationship right here.

93
00:07:05,400 --> 00:07:08,250
So again this is one too many.

94
00:07:08,480 --> 00:07:17,330
And we're connecting a puppy to what may be many toys and then a little bit we'll see kind of a one

95
00:07:17,330 --> 00:07:18,290
to one.

96
00:07:18,290 --> 00:07:20,610
So here's one too many puppy too many toys.

97
00:07:20,660 --> 00:07:26,510
And we have several parameters inside of this called D-B that relationship.

98
00:07:26,540 --> 00:07:29,050
So what we have here are three main parameters.

99
00:07:29,120 --> 00:07:33,260
The first one is just connecting it to the actual other model.

100
00:07:33,290 --> 00:07:39,750
So we're saying that this column toys is a relationship with this toy model here.

101
00:07:39,980 --> 00:07:44,720
Then we have a back ref reference variable and or parameter.

102
00:07:44,710 --> 00:07:46,720
And that's basically just going to add a back reference.

103
00:07:46,730 --> 00:07:48,890
And the other model in the relationship.

104
00:07:48,920 --> 00:07:54,740
So it's going to add a reference to the toy model to remember that that's also related to but B through

105
00:07:54,740 --> 00:07:58,480
this relationship column and then we have this lazy parameter.

106
00:07:58,700 --> 00:08:04,280
And this is for people who are a little more advanced in databases but it essentially specifies how

107
00:08:04,280 --> 00:08:06,440
the related items are to be loaded.

108
00:08:06,440 --> 00:08:11,060
There's actually lots of possible values here he can provide select as a string immediate as a string

109
00:08:11,390 --> 00:08:14,210
joined subquery and no load.

110
00:08:14,210 --> 00:08:20,300
Often it's just easiest to use this string dynamic which basically means that instead of loading the

111
00:08:20,300 --> 00:08:23,690
related items the query that can load them is given.

112
00:08:23,690 --> 00:08:27,650
So that's usually best suited for general purpose.

113
00:08:27,650 --> 00:08:32,630
And you can check out the documentation for the D-B relationship to see if you want to use other string

114
00:08:32,630 --> 00:08:33,350
codes there.

115
00:08:33,440 --> 00:08:37,280
But for pretty much all or use cases will just be setting in as dynamic.

116
00:08:37,280 --> 00:08:42,840
All right so now let's move on and fill out a couple of more methods inside of this puppy model.

117
00:08:43,760 --> 00:08:49,970
First we want to set our method and this will allow a sexy create new instances of a puppy essentially

118
00:08:49,970 --> 00:08:51,000
adding new rows.

119
00:08:51,230 --> 00:09:04,010
So say self name also a self-taught name is equal to name and then we also add a representation.

120
00:09:04,010 --> 00:09:12,660
So say the representation of this actual puppy model and we're going to do here is something a little

121
00:09:12,660 --> 00:09:13,070
different.

122
00:09:13,080 --> 00:09:22,800
I'm going to add in one more relationship column inside puppie and this will be a one to one and this

123
00:09:22,800 --> 00:09:27,770
will connect one puppy to one owner.

124
00:09:27,870 --> 00:09:32,230
So we're going to assume that every puppy in the world only has one owner and vice versa.

125
00:09:32,310 --> 00:09:34,810
Every owner can only have one puppy.

126
00:09:34,890 --> 00:09:41,040
So here we're representing a one to many puppies too many toys and a one to one one a puppy to one owner.

127
00:09:41,160 --> 00:09:45,610
And the way we're going to do that is just say owner is equal to.

128
00:09:45,650 --> 00:09:48,830
Now I will say D-B relationship.

129
00:09:49,040 --> 00:09:52,570
And then we're going to pasan owner to connect it to that actual model.

130
00:09:53,710 --> 00:09:55,750
Well say the back reference is puppie again.

131
00:09:56,020 --> 00:09:57,340
So that gives it the back reference.

132
00:09:57,370 --> 00:10:02,770
And the other one and we can actually just leave the lazy parameter to be the default for this one to

133
00:10:02,770 --> 00:10:03,670
one relationship.

134
00:10:03,850 --> 00:10:11,380
But what I going to do is pass in a parameter called Use List instead of equal to false.

135
00:10:11,380 --> 00:10:16,870
So the reason I'm doing that is because by default it's usually true which would make sense for a one

136
00:10:16,870 --> 00:10:23,170
to many relationship because one puppy when you ask for its toys it would be a list of possible toys.

137
00:10:23,230 --> 00:10:25,830
But here we just have one puppy to one owner.

138
00:10:25,900 --> 00:10:28,960
So we don't need a list of voters because it's just going to be a single owner.

139
00:10:29,080 --> 00:10:34,030
So we expect to be returned which is why we use this useless parameter and we set it equal to false

140
00:10:34,600 --> 00:10:35,270
by default.

141
00:10:35,300 --> 00:10:39,300
It's equal to true which is why we didn't actually play around it in the toys column.

142
00:10:39,310 --> 00:10:44,770
So these are two relationship columns that get a one to many and they wouldn't put me to one owner.

143
00:10:44,780 --> 00:10:47,540
Let's move on to this representation and we're going to do the following.

144
00:10:47,540 --> 00:10:55,740
We'll see if self owner meaning if the actual puppy has an owner.

145
00:10:55,740 --> 00:11:04,580
We're going to say return and it is going to use some of string literals will say puppy name is and

146
00:11:04,590 --> 00:11:05,330
wants her back.

147
00:11:05,330 --> 00:11:14,750
The puppies name Elsie and owner is I'll say say self the owner and then the owner is going to have

148
00:11:14,750 --> 00:11:15,350
their own name.

149
00:11:15,350 --> 00:11:17,850
So say Salta owner name.

150
00:11:18,110 --> 00:11:21,800
So again that's just grabbing the attribute owner here.

151
00:11:22,010 --> 00:11:25,940
And then from that owner object you'll see later on the owners are going to have their own name which

152
00:11:25,940 --> 00:11:30,410
is why we're using this kind of double dot notation of Celta owner that name.

153
00:11:30,410 --> 00:11:32,830
Now let's imagine that the puppy does not have an owner.

154
00:11:33,080 --> 00:11:43,150
Then what we're going to do is say else return using string notation puppy name is self-taught name

155
00:11:44,090 --> 00:11:46,400
and has no owner yet.

156
00:11:49,110 --> 00:11:49,370
OK.

157
00:11:49,390 --> 00:11:52,270
So that's going to be the representation when we print out a puppy.

158
00:11:52,450 --> 00:11:53,800
Either return the name.

159
00:11:53,800 --> 00:11:58,600
And if they have an owner return the name of their owners and then can say puppy name self-taught name

160
00:11:58,600 --> 00:12:00,010
has no owner yet.

161
00:12:00,010 --> 00:12:06,040
Now let's finally add one more method to this class in order to report back the number of toys that

162
00:12:06,070 --> 00:12:08,760
the owner or that the puppy has.

163
00:12:08,770 --> 00:12:17,790
So say if and I'm going to make a quick method here called Report toys will say self and what what it's

164
00:12:17,790 --> 00:12:19,490
going to do it's going to print.

165
00:12:19,770 --> 00:12:22,760
Here are my toys.

166
00:12:22,760 --> 00:12:27,770
And as we just discussed with use list use list is set to true by default.

167
00:12:27,840 --> 00:12:33,330
So that means when you actually ask for toys from a puppy meaning you safer self-taught toys that's

168
00:12:33,330 --> 00:12:34,760
going to be A-list.

169
00:12:34,770 --> 00:12:35,970
So that means I can do the following.

170
00:12:35,970 --> 00:12:41,620
I can say for toy in self-taught toys because useless by default is true.

171
00:12:41,970 --> 00:12:49,530
And then I'm going to say print grab that toys Item Name an item name is going to be an attribute that

172
00:12:49,530 --> 00:12:52,040
we create inside of our toy model.

173
00:12:52,410 --> 00:12:56,910
So we have this name attribute that we suddenly create owner as well as this item name that we still

174
00:12:56,910 --> 00:12:59,340
need to create inside of our story class.

175
00:12:59,610 --> 00:13:05,060
So let's quickly fill out class toy and class owner.

176
00:13:05,190 --> 00:13:07,750
So we'll go ahead and manually give them table names.

177
00:13:07,980 --> 00:13:10,690
Technically this isn't required but we'll do it.

178
00:13:11,040 --> 00:13:16,860
So say toys for the table name and then create a primary key for our tables.

179
00:13:16,860 --> 00:13:20,300
So this is going to be D-B column.

180
00:13:20,500 --> 00:13:21,870
And then this will be an integer.

181
00:13:21,880 --> 00:13:30,660
So it's going to be a unique identifier for each toy and they'll say primary key is equal to true and

182
00:13:30,660 --> 00:13:39,160
then we'll say item name is equal to D-B column D-B text.

183
00:13:39,200 --> 00:13:41,680
So the name of whatever toy or item it is.

184
00:13:41,900 --> 00:13:48,290
And then we need to connect the toy to the puppy that owns it and we use puppies ID because the table

185
00:13:48,290 --> 00:13:52,970
name we set up here if we come back up here it was puppies.

186
00:13:52,970 --> 00:13:54,760
So Mishu what I mean by that.

187
00:13:54,800 --> 00:14:04,550
So this is going to be the column that connects toys to puppies to say puppy ID is equal to D-B column

188
00:14:05,870 --> 00:14:07,930
and it's an integer type column.

189
00:14:08,210 --> 00:14:09,960
So say D-B integer.

190
00:14:10,190 --> 00:14:13,230
But then we also clarify that it's actually a foreign key.

191
00:14:13,490 --> 00:14:20,510
So we'll say foreign key and then we pass in the string puppies ID.

192
00:14:20,570 --> 00:14:23,110
So where does puppies the ID comes from.

193
00:14:23,360 --> 00:14:30,200
Well remember the table name here for puppy weeks clarify that it's puppies and the column that we're

194
00:14:30,200 --> 00:14:33,690
seeking with the primary key a puppy is called ID.

195
00:14:33,740 --> 00:14:40,530
So puppies that ID is what we're passing in as a string here which goes into the foreign key which remember

196
00:14:40,530 --> 00:14:43,290
a foreign key is just a primary key of another table.

197
00:14:43,310 --> 00:14:45,860
So that's why we're passing and Papy the in that manner.

198
00:14:47,220 --> 00:14:53,970
Now finally let's give in in its statement so we can actually create toys later on we'll say self item

199
00:14:55,260 --> 00:14:58,710
item name actually and then we'll take in the puppy.

200
00:14:58,710 --> 00:15:04,110
So that basically says when you create a toy you have to assign it to a puppy will say self item name

201
00:15:04,170 --> 00:15:11,990
is equal to the item name that's passed in and then we'll say self puppy ID is equal to the puppy ID

202
00:15:12,020 --> 00:15:21,700
that's passed in save that and move on to owner We're going to do here say table name and set it equal

203
00:15:21,700 --> 00:15:22,090
to.

204
00:15:22,090 --> 00:15:31,300
Owners will create a primary key here will say D-B column and this will be an integer column very similar

205
00:15:31,300 --> 00:15:32,860
to what we've just been doing before.

206
00:15:33,010 --> 00:15:37,310
For the other unique IDs so D-B integer.

207
00:15:37,530 --> 00:15:41,960
And then we'll also set this to be a primary key primary key is true.

208
00:15:42,300 --> 00:15:44,980
And then every owner can have a name.

209
00:15:45,120 --> 00:15:50,580
So this will be a column and then the type are going to pass and here it's just a normal text column

210
00:15:51,210 --> 00:15:52,470
for the owner.

211
00:15:52,740 --> 00:15:55,530
And then we're going to connect the owner to the puppy.

212
00:15:55,540 --> 00:16:02,760
So remember the the one to one relationship we're going to say puppy underscore ID and said equal to

213
00:16:02,760 --> 00:16:14,710
D-B column and it's an integer column and very similar we say D-B foreign key and we passen the string

214
00:16:15,250 --> 00:16:15,940
puppies.

215
00:16:15,970 --> 00:16:18,340
ID as we just mentioned earlier.

216
00:16:18,400 --> 00:16:24,220
So now we have the owner connected to the puppy Id call him up here.

217
00:16:24,220 --> 00:16:31,290
Last thing I want to do is set init method so we can actually create owners will say if underscore and

218
00:16:31,290 --> 00:16:38,430
that underscore underscore then we'll say self name puppy ID.

219
00:16:38,770 --> 00:16:45,230
So again this basically concludes that when we create an owner We're going to assign them a puppy a

220
00:16:45,240 --> 00:16:54,430
self-taught name sequel to name and then soft top puppy ID is equal to puppy ID.

221
00:16:54,430 --> 00:16:54,830
All right.

222
00:16:55,030 --> 00:16:57,620
So we just went through a lot of code in the next lecture.

223
00:16:57,640 --> 00:17:01,960
We're actually going to connect all of this together and perform the migrations as well as create some

224
00:17:01,960 --> 00:17:05,310
puppies some toys and some owners and see how that all plays out.

225
00:17:05,530 --> 00:17:08,060
Let's run a very quick review of what we just covered here.

226
00:17:09,710 --> 00:17:15,170
We went through the basics of setting up your flask model as well as your sequel light database created

227
00:17:15,170 --> 00:17:21,380
the base directories configure application and then creator D-B as well as migration capabilities that

228
00:17:21,380 --> 00:17:21,940
we created.

229
00:17:21,950 --> 00:17:25,830
Three main models our first model puppy we set the table name.

230
00:17:26,000 --> 00:17:31,580
We have basic columns such as a primary key column for ID the name of the puppy that we have a one to

231
00:17:31,580 --> 00:17:37,610
many relationship where a puppy can have many toys and then we have a one to one relationship where

232
00:17:37,610 --> 00:17:39,790
one puppy can only have one owner.

233
00:17:39,830 --> 00:17:45,020
So we say owner and then we set the back references to puppy and then here we say use listicle to false

234
00:17:45,050 --> 00:17:47,570
because it's a one to one relationship.

235
00:17:47,690 --> 00:17:52,430
Then we set the init method so we can quickly create a puppy with just their name and then we have the

236
00:17:52,430 --> 00:17:53,950
representation of the puppy.

237
00:17:53,990 --> 00:17:58,490
So it's going to be if the puppy has an owner they'll report back the name of the puppy and the owner

238
00:17:58,730 --> 00:18:03,290
or if not I'll just report back the name and then each puppy has a method where they can report the

239
00:18:03,290 --> 00:18:09,910
toys they have number self-taught toys a list of toy items that is connected to then we have the toy

240
00:18:09,910 --> 00:18:11,020
class here.

241
00:18:11,020 --> 00:18:16,630
Very similar structure except now we set the relationship Habi ID to be a foreign key or we say puppies

242
00:18:16,630 --> 00:18:17,470
the ID.

243
00:18:17,650 --> 00:18:22,810
And then here's our method to actually create a toy and essentially do the same thing for owner.

244
00:18:22,810 --> 00:18:25,380
Set the table name create the relationship column.

245
00:18:25,420 --> 00:18:29,410
Again if a foreign key and then set the method in order to create owners.

246
00:18:29,410 --> 00:18:29,990
All right.

247
00:18:30,160 --> 00:18:31,060
That's it for this lecture.

248
00:18:31,060 --> 00:18:34,840
We're going to continue on the part two and the very next lecture where we actually implement these

249
00:18:34,840 --> 00:18:39,730
models implement the database do the migrations and then play around with the actual entries in the

250
00:18:39,730 --> 00:18:40,540
table.

251
00:18:40,540 --> 00:18:41,720
We'll see at the next lecture.