1
00:00:05,200 --> 00:00:07,260
All right so we're right where we left off last time.

2
00:00:07,290 --> 00:00:10,280
I have a basic PI file with all my code in this.

3
00:00:10,320 --> 00:00:13,890
And then I created a new that PI file that says setup database.

4
00:00:13,970 --> 00:00:14,780
Pi.

5
00:00:14,970 --> 00:00:22,790
What I'm going to do is say from basic import D-B and puppy.

6
00:00:22,860 --> 00:00:27,900
So again you may be calling this slightly different in our notes that we provide to it's called from

7
00:00:27,900 --> 00:00:31,990
basic model app right now for this video lecture I just called the Basic about pi.

8
00:00:32,010 --> 00:00:35,030
So that's where this basic is coming from and D-B and puppy.

9
00:00:35,130 --> 00:00:36,210
Well they're right here.

10
00:00:36,210 --> 00:00:37,650
Debes right here on line 12.

11
00:00:37,800 --> 00:00:39,330
And the puppy is only 15.

12
00:00:39,330 --> 00:00:43,650
So that's what I'm importing from this file and everything that comes along with that.

13
00:00:44,460 --> 00:00:47,420
And the next step is actually to set up our database.

14
00:00:47,460 --> 00:00:52,800
We're going to say D-B dots and we're going to call create underscore all.

15
00:00:53,100 --> 00:00:58,230
Keep in mind usually we're not going to run a separate PI file to actually create the database.

16
00:00:58,230 --> 00:01:01,210
There's some command line tools that make that a lot easier.

17
00:01:01,260 --> 00:01:05,490
But right now just for lower starting out we're going to manually create that database.

18
00:01:05,490 --> 00:01:12,930
So what this line does right here at the create all that creates all the tables essentially transforming

19
00:01:12,960 --> 00:01:17,450
your model class to a database table.

20
00:01:17,930 --> 00:01:20,650
So that's what that line is doing D.B that create all.

21
00:01:20,780 --> 00:01:23,320
From there we can create new entries in the database.

22
00:01:23,330 --> 00:01:30,940
So the way we do that is going to create a puppy object we'll see Sam as an instance of puppy where

23
00:01:30,940 --> 00:01:42,190
the name is Sammy and he's three years old and they say Frank is equal to puppy.

24
00:01:42,440 --> 00:01:46,880
And I will say Frankie and we'll have Frankie be four years old.

25
00:01:47,140 --> 00:01:47,380
OK.

26
00:01:47,380 --> 00:01:49,530
So I have these two puppy objects.

27
00:01:49,630 --> 00:01:53,710
What I'm going to do is check the IDs of them going to say Prince Sam.

28
00:01:53,840 --> 00:01:58,530
The idea that I'm going to say Prince Frank thought Id.

29
00:01:58,870 --> 00:02:03,350
So this is just to kind of prove to you that I haven't added sound Frank to the database.

30
00:02:03,370 --> 00:02:09,190
So both of these should be none because remember if we come back here inside of our in that statement

31
00:02:09,340 --> 00:02:10,870
it's just taking name and age.

32
00:02:10,900 --> 00:02:13,170
So it's only going to have the name and the age.

33
00:02:13,300 --> 00:02:16,870
We haven't added it to the database yet at this line.

34
00:02:16,870 --> 00:02:18,870
So Id these should be none.

35
00:02:19,180 --> 00:02:23,870
So first we should see none and none on this print.

36
00:02:24,460 --> 00:02:32,230
Then all we're going to do after that is the following line will say D-B session and I'm going to say

37
00:02:32,260 --> 00:02:39,610
add underscore all and I'm going to pass in a list of my puppy objects so that will be Sam and frank

38
00:02:40,120 --> 00:02:42,620
as an alternative you can add them individually.

39
00:02:42,670 --> 00:02:51,250
So it's also common to see something like D-B session ad and then you can add Sam and then say D-B session

40
00:02:52,240 --> 00:02:53,310
at Frank.

41
00:02:53,320 --> 00:02:57,570
So Line 14 is the same as line 16 and 17 put together.

42
00:02:57,580 --> 00:03:03,610
So you can either add objects individually one by one or by just saying D-B session and or you can add

43
00:03:03,610 --> 00:03:09,320
a list of objects in one single line by saying at all and then passing in a list of those objects.

44
00:03:09,370 --> 00:03:11,540
So we'll just use at all.

45
00:03:11,590 --> 00:03:19,630
So we're saying Sam and Frank are now in the database and what we're going to do then is say D-B session

46
00:03:20,500 --> 00:03:26,350
and I'm going to call commit and they actually will then save those changes to the database and then

47
00:03:26,350 --> 00:03:27,430
I'll say print.

48
00:03:27,520 --> 00:03:31,430
Sam ID and then I will say print.

49
00:03:31,880 --> 00:03:36,060
Frank ID OK so I'm going to save this.

50
00:03:36,060 --> 00:03:39,120
Let's really quickly go over what we just did.

51
00:03:39,120 --> 00:03:42,390
We said from Mike that Paice grip this basic one over here.

52
00:03:42,650 --> 00:03:47,350
Import my database and import this puppy model.

53
00:03:47,460 --> 00:03:53,130
So once it imported the top I say create all my database which essentially converts all those models

54
00:03:53,220 --> 00:03:54,480
into tables.

55
00:03:54,480 --> 00:04:00,300
Then it created to put the objects Sam and frank with their names and their ages and then ask hey can

56
00:04:00,300 --> 00:04:05,550
you print out their IDs and what's going happen is going to say none because we never really provided

57
00:04:05,550 --> 00:04:06,810
them with IDS.

58
00:04:06,870 --> 00:04:09,120
Then I called D-B session.

59
00:04:09,160 --> 00:04:13,030
I'm going to be able to add Sam and Frank when I want a call like this.

60
00:04:13,030 --> 00:04:14,330
We're passing it into a list.

61
00:04:14,340 --> 00:04:16,420
The alternative is to add them one by one to session.

62
00:04:16,500 --> 00:04:22,920
And then I'm going to commit those changes to my database and then I'm going to ask for their ID's again.

63
00:04:22,920 --> 00:04:27,060
Now keep in mind there's a lot of stuff going on in the background that siecle alchemy is taking care

64
00:04:27,060 --> 00:04:27,790
of us.

65
00:04:27,810 --> 00:04:30,990
So this is just a couple of lines of Python code.

66
00:04:30,990 --> 00:04:35,370
When you look at this I did this all in about less than 10 lines really.

67
00:04:35,670 --> 00:04:38,750
And there's a lot of heavy lifting that sequel alchemy does for us.

68
00:04:38,880 --> 00:04:43,980
So things like adding the ID at the end of this you can kind of think about that as a little bit of

69
00:04:43,980 --> 00:04:46,970
magic that's happening in the back and it's going to save this.

70
00:04:47,010 --> 00:04:49,090
And let's try running and see what happens.

71
00:04:49,950 --> 00:04:58,870
So to do that I will expand right here to clear my window and I'm going to run Python and I call this

72
00:04:58,920 --> 00:05:01,000
set up database stop pi.

73
00:05:01,090 --> 00:05:03,630
Your name may be slightly different.

74
00:05:04,060 --> 00:05:05,810
And it looks like I get the results.

75
00:05:05,830 --> 00:05:07,510
None none.

76
00:05:07,510 --> 00:05:08,840
One and Two.

77
00:05:09,010 --> 00:05:11,000
So that matches up with what I expected.

78
00:05:11,020 --> 00:05:15,570
The Ids actually created until I added them to my database.

79
00:05:15,610 --> 00:05:19,830
So then when I print Samite the end Frnk ID I get back 1 and 2.

80
00:05:20,080 --> 00:05:23,110
So I think I previously mentioned a 0 as an index.

81
00:05:23,170 --> 00:05:28,050
I should correct myself and say that indexing for these databases starts at once so sequel.

82
00:05:28,210 --> 00:05:30,970
Your first index or ID is going to be at one.

83
00:05:30,960 --> 00:05:33,460
So keep that in mind it's like exactly like Python there.

84
00:05:33,910 --> 00:05:36,750
And then you'll also notice that some files got created for you.

85
00:05:36,820 --> 00:05:41,560
You should now see after running this script a data that sequel lights you aren't really going to be

86
00:05:41,560 --> 00:05:46,290
able to read it too much but notice here there is some sequel code along with some stuff that Adam's

87
00:05:46,300 --> 00:05:47,800
having a hard time reading.

88
00:05:47,830 --> 00:05:49,560
So that's her data that sequel line.

89
00:05:49,840 --> 00:05:54,890
And then you should also see a PI cache and that essentially is just some Python code that automatically

90
00:05:54,890 --> 00:05:57,710
gets created that way in case you want to run your python files again.

91
00:05:57,730 --> 00:05:59,620
They run slightly faster.

92
00:05:59,620 --> 00:06:02,890
So we set up our database and the database is this file right here.

93
00:06:02,920 --> 00:06:04,090
Data at that school.

94
00:06:04,280 --> 00:06:06,120
And that's actually holding the information.

95
00:06:06,200 --> 00:06:11,230
Again it's not a data type that you can directly read in your text editor but you can kind of make out

96
00:06:11,230 --> 00:06:12,090
what's happening here.

97
00:06:12,100 --> 00:06:16,110
So make sure not to make any edits on the data that sequel like file.

98
00:06:16,150 --> 00:06:18,430
Ok so we just created our database.

99
00:06:18,430 --> 00:06:20,500
Now it's time to talk about crud.

100
00:06:20,500 --> 00:06:25,200
So we want to be able to create read update and delete from our database.

101
00:06:25,630 --> 00:06:33,160
So to do that I'm going to create a new file and I'm going to call it crud that pi and we're going to

102
00:06:33,170 --> 00:06:38,900
go through each of the main phases so our table that was just created and we're going to do now is say

103
00:06:38,900 --> 00:06:48,970
from basic import D-B and puppie that should be a comma D-B and puppy.

104
00:06:48,980 --> 00:06:49,800
There we go.

105
00:06:49,910 --> 00:06:53,210
And then we'll start off with create.

106
00:06:53,220 --> 00:06:59,640
So show you how to create an entry into this table and it's going to be essentially what we just did.

107
00:06:59,660 --> 00:07:05,180
We're going to see my puppy is equal to and you create a puppy object or whatever kind of object is

108
00:07:05,180 --> 00:07:08,060
related to your actual model or table.

109
00:07:08,330 --> 00:07:15,420
And here let's make rufous Rufus will be five years old.

110
00:07:15,900 --> 00:07:24,330
Then we take that database session and then we can add in that puppy object and then we're going to

111
00:07:24,330 --> 00:07:32,310
save it by saying D-B session commit so it's not technically say until you call this commit.

112
00:07:32,320 --> 00:07:32,650
All right.

113
00:07:32,680 --> 00:07:33,940
Let me close this off.

114
00:07:34,880 --> 00:07:36,780
So you can see this in a little more detail.

115
00:07:36,800 --> 00:07:41,510
So that's creating Should it be anything new because we created two entries when we actually ran the

116
00:07:41,510 --> 00:07:42,700
database.

117
00:07:42,740 --> 00:07:50,620
Next we want to be able to read so reading is where we can actually introduce or filter options rember

118
00:07:50,650 --> 00:07:55,930
or m c the object relational mapper and there's lots of different options here and we're going to cover

119
00:07:55,930 --> 00:07:57,580
them in a lot more detail later on.

120
00:07:57,850 --> 00:08:01,650
But I'm going to show you some of the most basic ones we're going to be using throughout the course.

121
00:08:01,750 --> 00:08:04,610
So again this is for reading your database first.

122
00:08:04,660 --> 00:08:06,940
Imagine I wanted to grab all the puppies.

123
00:08:06,940 --> 00:08:10,450
So essentially I wanted to grab everything that was existing in my table.

124
00:08:10,450 --> 00:08:18,670
The way I could do that is I would say puppy dots queery the all.

125
00:08:18,860 --> 00:08:24,900
And this is going to return a list of the puppy objects in the table.

126
00:08:28,310 --> 00:08:39,850
And then I could say prints all puppies I can also select by the so if I wanted to select by either

127
00:08:39,860 --> 00:08:49,720
way I could do that they say puppy 1 is puppy queery get.

128
00:08:49,980 --> 00:08:57,260
And then I could say one there and then I could print for instance that puppy's name so puppy one name

129
00:08:57,810 --> 00:09:01,920
and that should be the very first puppet we added which if we go back to set up database it should have

130
00:09:01,920 --> 00:09:02,780
been Sam.

131
00:09:02,850 --> 00:09:05,130
So we should see Sammy be printed out here.

132
00:09:06,070 --> 00:09:07,410
So that's how we could select by the.

133
00:09:07,460 --> 00:09:11,760
And the next thing we can do here is filter as well when reading.

134
00:09:11,770 --> 00:09:14,260
So let's imagine I wanted to filter out by name.

135
00:09:14,260 --> 00:09:18,060
I only wanted to select the puppies where the name happened to be equal to Samie.

136
00:09:18,420 --> 00:09:26,590
What I could say is puppy or let's do Franki will say puppy Franki is equal to grab our puppy model

137
00:09:27,520 --> 00:09:33,400
and we'll say queery and then I can use the filter underscore by method.

138
00:09:33,700 --> 00:09:41,040
And here is where I provide a column name such as name equals or age equals start with name.

139
00:09:41,350 --> 00:09:43,970
And then I set it equal to whatever value I'm looking for.

140
00:09:44,050 --> 00:09:48,820
For instance let's say I'm looking for where it happens to be equal to Franki.

141
00:09:48,820 --> 00:09:58,210
This command this filter by command that will actually produce some sequel code for us and then if I

142
00:09:58,210 --> 00:10:04,660
want to actually grab the product or the result of that sequel code what I can do is say puppy Franki

143
00:10:05,860 --> 00:10:09,560
and then call all of this and that should print out a list.

144
00:10:09,760 --> 00:10:11,300
It's going to print that out.

145
00:10:11,530 --> 00:10:17,590
That's going to print out a list of all the puppies with the name happens to be with the Franki.

146
00:10:17,590 --> 00:10:20,150
In our case that should just be one singular puppy.

147
00:10:20,340 --> 00:10:26,110
And remember when we're printing up puppies it's going to be printed out in this form puppy name is

148
00:10:26,200 --> 00:10:27,370
age years old.

149
00:10:27,370 --> 00:10:35,250
So you should see a list here that looks something like like Frankie is three years old.

150
00:10:35,390 --> 00:10:38,000
So that should be the result of this line right here.

151
00:10:38,060 --> 00:10:41,350
Going to comment that that line.

152
00:10:41,620 --> 00:10:45,120
And then finally what we can do is we can start updating.

153
00:10:45,400 --> 00:10:47,100
And then after that we can show you how to delete.

154
00:10:47,380 --> 00:10:52,540
So if you want to update what we do we have to first grab our data so we'll grab the first puppy in

155
00:10:52,540 --> 00:11:02,320
our database or set that equal to puppy queery get one and let's change that puppy age so we'll see

156
00:11:02,320 --> 00:11:06,450
that first puppy is now going to be 10 years old.

157
00:11:07,270 --> 00:11:16,260
I will say D-B session and then we add in that same puppy object and then we call D-B session and we

158
00:11:16,260 --> 00:11:17,480
commit that change.

159
00:11:17,760 --> 00:11:18,790
So that's how you can update.

160
00:11:18,810 --> 00:11:23,140
You just grab based off either filter or buy or queery that get.

161
00:11:23,160 --> 00:11:27,480
Again we're going to spend a lot more time talking about or M and reading in general there's a lot of

162
00:11:27,540 --> 00:11:31,780
executer options and then we'll also talk about updating it a little more detail later on.

163
00:11:31,980 --> 00:11:37,080
But essentially you just grab that object add whatever attribute you want update it and then you can

164
00:11:37,110 --> 00:11:39,230
add it back in and commit those changes.

165
00:11:40,360 --> 00:11:42,860
Then for deleting it's kind of similar.

166
00:11:43,000 --> 00:11:49,900
We can grab another puppy Salt's grab the second puppy in the database and say puppy will see Cleary

167
00:11:49,940 --> 00:11:59,080
gets to that grabs a second Papu of ID to Elisei D-B session and then we call deletes on that puppy

168
00:11:59,200 --> 00:12:08,580
on that second pup and then we just commit those changes will say D-B session come at and then check

169
00:12:08,580 --> 00:12:09,310
for changes.

170
00:12:09,330 --> 00:12:12,480
So we'll just go ahead and print out all the puppies again.

171
00:12:12,600 --> 00:12:23,230
We'll say all puppies is equal to or puppy model grab all of them and then print out all the puppies

172
00:12:24,470 --> 00:12:25,780
OK so I'm going to save that.

173
00:12:26,100 --> 00:12:32,180
Let's run this and make sure it works and I it clear that and say Python crud up high.

174
00:12:32,340 --> 00:12:34,910
Well enter and it looks like a printed out.

175
00:12:34,910 --> 00:12:37,350
So let's make sure that it matches up what we expected.

176
00:12:37,760 --> 00:12:38,270
OK.

177
00:12:38,450 --> 00:12:40,830
So here we see Papy Sammy is 3 years old.

178
00:12:40,890 --> 00:12:43,300
Frank he's four years old publisher of his five years old.

179
00:12:43,340 --> 00:12:45,470
We take a look at the first thing we printed.

180
00:12:46,100 --> 00:12:50,560
All the way up here it's going to be prints all the puppies in our database.

181
00:12:50,570 --> 00:12:52,730
So make sense that we have Sammy and Frankie.

182
00:12:52,910 --> 00:12:56,820
Those puppies were created when we actually set up our database on at all.

183
00:12:56,930 --> 00:12:57,830
And then Rufus.

184
00:12:57,950 --> 00:13:00,860
Well that's the one we create an added at the very beginning.

185
00:13:00,980 --> 00:13:02,690
So now we have all three of them.

186
00:13:02,690 --> 00:13:08,930
That's the first print statement here on line 10 then selecting by id I want it to grab the ID of puppy

187
00:13:08,930 --> 00:13:09,600
one.

188
00:13:09,620 --> 00:13:12,330
That should be the first puppy that was put into the database.

189
00:13:12,350 --> 00:13:16,250
And if we check database again that was Sam over here.

190
00:13:16,250 --> 00:13:20,280
So when I print out Papy one name I should see Sammy which I do.

191
00:13:20,510 --> 00:13:26,810
Then for filters we can say puppy queery filter by name where it's Franki and then print out all the

192
00:13:26,810 --> 00:13:27,910
results of that.

193
00:13:27,920 --> 00:13:29,950
Notice how it actually gives us a list here.

194
00:13:29,960 --> 00:13:32,010
So it says puppy Franki is four years old.

195
00:13:32,060 --> 00:13:34,770
This itself is inside of a list.

196
00:13:34,790 --> 00:13:40,780
So if there were more puppies than named Frankie it would return back their name as well as her age.

197
00:13:41,030 --> 00:13:44,370
Then finally what we end up doing is we update Samis H.

198
00:13:44,570 --> 00:13:47,510
And then we actually delete the second pup which was Franki.

199
00:13:47,510 --> 00:13:52,160
So when we print out all the puppies I now see that Sammy is 10 years old instead of three years old

200
00:13:52,520 --> 00:13:55,870
due to my update to his age and I no longer see Frankie.

201
00:13:55,880 --> 00:13:58,090
Now I just have Rufus.

202
00:13:58,210 --> 00:14:01,320
Now if you want to play around more with crud that's totally OK.

203
00:14:01,450 --> 00:14:06,430
But something to note here is you're going to have issues if you try running this cruds script again

204
00:14:06,460 --> 00:14:08,740
or if you try running the script multiple times.

205
00:14:08,740 --> 00:14:15,680
So I want to show you what happens if I try running Python crud that pagan it's going to say unexpected

206
00:14:15,830 --> 00:14:18,180
mapping issues are going to get these errors.

207
00:14:18,350 --> 00:14:22,690
And that's because you started to ask for puppies that are no longer in your database.

208
00:14:22,760 --> 00:14:28,490
So if you start scrolling back up here they'll say for instance delete the second pup will you already

209
00:14:28,490 --> 00:14:32,290
believe the second pup when you ran your CRID script the first time around.

210
00:14:32,600 --> 00:14:37,220
So you can't run up the lead line more than once otherwise you're going to get an error.

211
00:14:37,430 --> 00:14:40,560
So notice here we're saying get ID 2 and then delete that.

212
00:14:40,610 --> 00:14:41,890
Well that puppy no longer exists.

213
00:14:41,900 --> 00:14:43,860
We deleted it off our database.

214
00:14:43,970 --> 00:14:49,180
So let's imagine that you want to keep playing around and proxy crud but unfortunately you deleted.

215
00:14:49,180 --> 00:14:51,670
Now you're starting to get errors and you would just like to restart.

216
00:14:51,740 --> 00:14:56,560
That happens to me all the time as I especially as I was practicing crud with Laskin sequel alchemy.

217
00:14:56,780 --> 00:15:02,240
The best way I figured out how to do this is especially in the very beginning at this stage is to just

218
00:15:02,300 --> 00:15:09,610
open your tab up here go to stated that sequel like and then go ahead and just delete the sequel database

219
00:15:09,610 --> 00:15:10,800
that you made.

220
00:15:11,050 --> 00:15:14,270
And then we just need to run Python setup database again.

221
00:15:14,290 --> 00:15:20,140
So say Python set up a database that adds in the first two puppies and then we have no problem rerunning

222
00:15:20,140 --> 00:15:21,680
credit pi.

223
00:15:21,730 --> 00:15:23,500
So we get the same results here.

224
00:15:23,800 --> 00:15:27,730
Again you only need to run that if you're kind of messing around and you're trying to delete puppies

225
00:15:27,730 --> 00:15:29,960
that you already deleted or those kind of things.

226
00:15:30,100 --> 00:15:30,300
OK.

227
00:15:30,310 --> 00:15:33,010
So that's a work around for that particular issue later on.

228
00:15:33,010 --> 00:15:37,900
We're not really going to be using these manual Crudup by scripts instead will kind of be incorporating

229
00:15:37,900 --> 00:15:43,060
these into selections on forms and other features in our flask applications.

230
00:15:43,060 --> 00:15:44,260
All right.

231
00:15:44,260 --> 00:15:46,810
If you have any questions feel free to post that Q forums.

232
00:15:46,810 --> 00:15:48,170
We'll see you in the next lecture.