1
00:00:00,926 --> 00:00:02,604
- [Instructor] Hi, and
welcome back to the course.

2
00:00:02,604 --> 00:00:04,116
In this video, we're
finally going to start

3
00:00:04,116 --> 00:00:06,699
saving our items to a database.

4
00:00:07,848 --> 00:00:11,433
The first thing to do is
to open up your app.py.

5
00:00:11,433 --> 00:00:14,425
What we're going to do is cut

6
00:00:14,425 --> 00:00:17,969
both item and item list resources.

7
00:00:17,969 --> 00:00:19,136
Just cut them.

8
00:00:21,016 --> 00:00:23,417
Then we're going to create a new file

9
00:00:23,417 --> 00:00:24,667
called item.py.

10
00:00:25,884 --> 00:00:26,920
Paste them in.

11
00:00:26,920 --> 00:00:27,914
Okay.

12
00:00:27,914 --> 00:00:29,371
That's the first thing.

13
00:00:29,371 --> 00:00:32,367
Also remember to remove
the items list from there

14
00:00:32,367 --> 00:00:34,349
because now we're going to start

15
00:00:34,349 --> 00:00:36,124
putting things in a database.

16
00:00:36,124 --> 00:00:39,629
We're no longer going to
use the in-memory database

17
00:00:39,629 --> 00:00:41,379
which is just a list.

18
00:00:43,470 --> 00:00:46,807
Now we're using the Item and ItemList

19
00:00:46,807 --> 00:00:49,813
classes here so we have to
make sure to import them.

20
00:00:49,813 --> 00:00:53,313
It's take from item import Item, ItemList.

21
00:00:55,261 --> 00:00:57,740
Remember item is the name of the file.

22
00:00:57,740 --> 00:01:00,970
In our case it's item.py so just use item.

23
00:01:00,970 --> 00:01:02,300
If you called it something else,

24
00:01:02,300 --> 00:01:03,467
then use that.

25
00:01:04,810 --> 00:01:08,752
Now because we no longer have
any resources defined in here,

26
00:01:08,752 --> 00:01:11,971
we don't need to import
Resource from flask_restful.

27
00:01:11,971 --> 00:01:15,741
Because we no longer have any
methods that require a JWT

28
00:01:15,741 --> 00:01:19,824
defined in here, we also
don't need jwt_required.

29
00:01:20,924 --> 00:01:22,630
We don't have a request parser

30
00:01:22,630 --> 00:01:24,254
so we don't need that.

31
00:01:24,254 --> 00:01:26,227
We're also not accessing
any particular requests

32
00:01:26,227 --> 00:01:28,727
so we also don't need request.

33
00:01:33,668 --> 00:01:36,263
The rest of things we do need.

34
00:01:36,263 --> 00:01:40,122
Now let's go into the item.py
file that we've created.

35
00:01:40,122 --> 00:01:43,945
We have to make sure to
import the necessary things.

36
00:01:43,945 --> 00:01:47,945
From flask_restful import
Resource and reqparse.

37
00:01:50,488 --> 00:01:52,238
From flask_jwt import

38
00:01:54,176 --> 00:01:55,259
jwt_required.

39
00:01:56,768 --> 00:01:58,435
Also, import sqlite3

40
00:02:00,022 --> 00:02:01,282
because we're going to be needing that

41
00:02:01,282 --> 00:02:03,720
to access the database.

42
00:02:03,720 --> 00:02:05,311
If I've forgotten anything,

43
00:02:05,311 --> 00:02:06,654
then apologies for that.

44
00:02:06,654 --> 00:02:09,539
It is possible that I've
forgotten something.

45
00:02:09,539 --> 00:02:13,412
I'm sure we'll find out when
we try this with Postman.

46
00:02:13,412 --> 00:02:16,409
What we're going to do in
this video is retrieve items

47
00:02:16,409 --> 00:02:17,909
from the database.

48
00:02:18,746 --> 00:02:22,047
The first thing is get this Get-method

49
00:02:22,047 --> 00:02:25,115
and delete everything in it.

50
00:02:25,115 --> 00:02:28,507
You know the drill with
interacting with a database.

51
00:02:28,507 --> 00:02:32,674
The first thing we have to
do is to set up a connection,

52
00:02:33,558 --> 00:02:36,675
data.db, and then create a cursor

53
00:02:36,675 --> 00:02:38,925
which is connection.cursor.

54
00:02:40,283 --> 00:02:42,019
The query that we're going to be using

55
00:02:42,019 --> 00:02:44,614
in this particular instance is

56
00:02:44,614 --> 00:02:46,281
Select * From items.

57
00:02:48,654 --> 00:02:50,616
Then we're going to
perform filtering as well

58
00:02:50,616 --> 00:02:52,575
because we don't want to
select all of the items,

59
00:02:52,575 --> 00:02:56,812
we want to select an
item where the items name

60
00:02:56,812 --> 00:02:59,710
matches a particular row so

61
00:02:59,710 --> 00:03:01,210
Where name=?.

62
00:03:04,147 --> 00:03:06,980
Then we're going to store
the results in a variable

63
00:03:06,980 --> 00:03:09,104
called result, but you
can call your variable

64
00:03:09,104 --> 00:03:13,432
whatever you want of
course, cursor.execute

65
00:03:13,432 --> 00:03:15,432
the query with the name.

66
00:03:16,418 --> 00:03:17,954
The name is the parameter.

67
00:03:17,954 --> 00:03:19,742
Remember that has to be in a tuple,

68
00:03:19,742 --> 00:03:21,600
so we have to put the comma at the end

69
00:03:21,600 --> 00:03:24,619
because we're only passing a single

70
00:03:24,619 --> 00:03:25,619
value tuple.

71
00:03:27,365 --> 00:03:30,088
Now the name should be unique

72
00:03:30,088 --> 00:03:31,338
so we know that

73
00:03:32,517 --> 00:03:35,107
the result.fetchone is going to give us

74
00:03:35,107 --> 00:03:37,857
the only row with a specific name

75
00:03:38,694 --> 00:03:41,599
because we're doing Where name= something.

76
00:03:41,599 --> 00:03:45,855
There should be only one
row or no rows coming back.

77
00:03:45,855 --> 00:03:46,792
We don't have to worry about

78
00:03:46,792 --> 00:03:49,508
having multiple rows coming back.

79
00:03:49,508 --> 00:03:51,392
Now that we've got the row out,

80
00:03:51,392 --> 00:03:53,160
we can do a connection.close

81
00:03:53,160 --> 00:03:56,217
because we no longer
need the connection open.

82
00:03:56,217 --> 00:03:57,223
Then what we're going to do is

83
00:03:57,223 --> 00:04:01,054
if the row exists, if the row is not none,

84
00:04:01,054 --> 00:04:03,387
we're going to return a JSON

85
00:04:04,773 --> 00:04:07,356
which is item is a set of name,

86
00:04:08,454 --> 00:04:09,856
row zero,

87
00:04:09,856 --> 00:04:10,689
and price,

88
00:04:12,015 --> 00:04:12,848
row one.

89
00:04:14,679 --> 00:04:17,442
If the row didn't exist,

90
00:04:17,442 --> 00:04:20,652
then we're going to return a message

91
00:04:20,652 --> 00:04:24,569
saying "Item not found"
or something like that.

92
00:04:25,530 --> 00:04:29,331
Now we don't need this
Else-statement there at all

93
00:04:29,331 --> 00:04:30,622
because if the row is not none,

94
00:04:30,622 --> 00:04:32,654
we're going to return this.

95
00:04:32,654 --> 00:04:34,937
Then we're just going to exit the method.

96
00:04:34,937 --> 00:04:37,021
If this return doesn't happen,

97
00:04:37,021 --> 00:04:38,769
then we're going to run this one.

98
00:04:38,769 --> 00:04:43,623
The Else-statement is kind
of logically implicit here

99
00:04:43,623 --> 00:04:44,730
which is nice.

100
00:04:44,730 --> 00:04:45,728
It saves us some code.

101
00:04:45,728 --> 00:04:49,478
It makes things a bit
easier to read as well.

102
00:04:50,502 --> 00:04:54,669
This is the way to retrieve
the item from the database.

103
00:04:56,238 --> 00:04:58,959
We should test this out using Postman.

104
00:04:58,959 --> 00:05:01,135
Of course, the first
thing that we have to do

105
00:05:01,135 --> 00:05:04,090
is to make sure that we have
some data in the database

106
00:05:04,090 --> 00:05:06,589
that we can retrieve because right now

107
00:05:06,589 --> 00:05:07,677
there won't be any.

108
00:05:07,677 --> 00:05:10,651
Let's go to our create_tables script.

109
00:05:10,651 --> 00:05:13,568
We are going to create a new table.

110
00:05:14,592 --> 00:05:16,422
I'm just going to copy
that and make sure to

111
00:05:16,422 --> 00:05:19,172
Create Table If Not Exists items.

112
00:05:21,699 --> 00:05:23,449
Then have the correct

113
00:05:25,833 --> 00:05:26,666
columns here.

114
00:05:26,666 --> 00:05:28,721
The first one is name.

115
00:05:28,721 --> 00:05:29,888
That's our text.

116
00:05:29,888 --> 00:05:31,940
The second one is price.

117
00:05:31,940 --> 00:05:34,288
That is going to be a real.

118
00:05:34,288 --> 00:05:35,371
Real is just

119
00:05:36,532 --> 00:05:37,844
a number with a decimal point

120
00:05:37,844 --> 00:05:40,011
such as 10.99 for example.

121
00:05:41,416 --> 00:05:45,178
Then I'm also going to do cursor.execute,

122
00:05:45,178 --> 00:05:47,345
Insert Into items, values,

123
00:05:48,313 --> 00:05:49,438
test,

124
00:05:49,438 --> 00:05:50,271
and 10.99.

125
00:05:51,848 --> 00:05:53,971
What this is going to do
is it's going to insert

126
00:05:53,971 --> 00:05:57,710
into the items table this
following set of values

127
00:05:57,710 --> 00:05:59,823
which is test for the name

128
00:05:59,823 --> 00:06:02,606
and 10.99 for the price.

129
00:06:02,606 --> 00:06:04,902
Finally, we're going to
commit and close as usual.

130
00:06:04,902 --> 00:06:07,076
What we'll end up with is two tables

131
00:06:07,076 --> 00:06:09,659
and one row in the items table.

132
00:06:10,620 --> 00:06:12,870
Make sure to delete data.db

133
00:06:15,527 --> 00:06:16,910
because we're going to recreate it

134
00:06:16,910 --> 00:06:19,409
using these two tables now.

135
00:06:19,409 --> 00:06:20,993
Let's go to the terminal.

136
00:06:20,993 --> 00:06:23,411
I'm going to clear that out and run

137
00:06:23,411 --> 00:06:26,104
from within the Code folder

138
00:06:26,104 --> 00:06:27,710
the create_table script.

139
00:06:27,710 --> 00:06:30,043
Then you can run the app.py.

140
00:06:31,646 --> 00:06:33,724
Then go over to Postman.

141
00:06:33,724 --> 00:06:35,703
We're going to go over
the entire thing again.

142
00:06:35,703 --> 00:06:38,600
I'm going to close these tabs
that are not not required.

143
00:06:38,600 --> 00:06:42,975
Then we're going to do
register first of all.

144
00:06:42,975 --> 00:06:45,065
Remember to register with a username

145
00:06:45,065 --> 00:06:47,698
and password that you know.

146
00:06:47,698 --> 00:06:51,097
That's invalid credentials
because this register method

147
00:06:51,097 --> 00:06:53,021
should be register and it is auth.

148
00:06:53,021 --> 00:06:55,671
Make sure to save things
as you change them.

149
00:06:55,671 --> 00:06:57,231
I'm going to register now.

150
00:06:57,231 --> 00:06:58,909
User created successfully

151
00:06:58,909 --> 00:07:00,826
with "jose" and "asdf."

152
00:07:01,803 --> 00:07:04,244
All we've done here is made a Post request

153
00:07:04,244 --> 00:07:06,327
to the register endpoint.

154
00:07:07,422 --> 00:07:11,374
Then in the auth, we're
going to make sure to use

155
00:07:11,374 --> 00:07:13,833
the same username and password.

156
00:07:13,833 --> 00:07:16,292
Now we're calling the auth endpoint.

157
00:07:16,292 --> 00:07:17,601
I'm going to send that.

158
00:07:17,601 --> 00:07:20,019
What I get back is as you would expect,

159
00:07:20,019 --> 00:07:20,944
the auth token.

160
00:07:20,944 --> 00:07:22,407
Make sure to copy that.

161
00:07:22,407 --> 00:07:25,163
Do not include the quotation marks.

162
00:07:25,163 --> 00:07:26,809
Then we're going to Get an item.

163
00:07:26,809 --> 00:07:29,296
The item we're going to get is test

164
00:07:29,296 --> 00:07:31,613
because that is the test item

165
00:07:31,613 --> 00:07:36,082
that we've inserted in
our create_tables script.

166
00:07:36,082 --> 00:07:38,252
In the Headers, make sure to have your

167
00:07:38,252 --> 00:07:41,030
new Authorization token.

168
00:07:41,030 --> 00:07:43,563
Remember that the
Authorization header has to be

169
00:07:43,563 --> 00:07:46,146
JWT, space, and then the token.

170
00:07:48,582 --> 00:07:49,517
There we have it.

171
00:07:49,517 --> 00:07:52,338
We have test with price 10.99

172
00:07:52,338 --> 00:07:55,838
which is exactly what we inserted in here.

173
00:07:57,501 --> 00:07:58,407
Going back to item,

174
00:07:58,407 --> 00:08:00,305
let's just recap the code quickly.

175
00:08:00,305 --> 00:08:03,303
What we've done is the usual

176
00:08:03,303 --> 00:08:05,051
to select an item from the database

177
00:08:05,051 --> 00:08:08,444
where the name matches a specific item.

178
00:08:08,444 --> 00:08:10,745
Then if there was a row,

179
00:08:10,745 --> 00:08:13,057
then we are returning the JSON,

180
00:08:13,057 --> 00:08:15,748
item is a name and a price,

181
00:08:15,748 --> 00:08:19,064
and if not, we're going to
return "Item not found."

182
00:08:19,064 --> 00:08:22,912
Remember the Get-method retrieves the item

183
00:08:22,912 --> 00:08:23,745
from this

184
00:08:25,681 --> 00:08:28,077
last bit of the URL.

185
00:08:28,077 --> 00:08:29,736
Let's try test2.

186
00:08:29,736 --> 00:08:32,215
As you can see, we get
"Item not found" back

187
00:08:32,215 --> 00:08:34,996
because there isn't a row with this name.

188
00:08:34,996 --> 00:08:37,855
It's important that you not
only try things that work

189
00:08:37,855 --> 00:08:40,647
but also try the things that
you know shouldn't work.

190
00:08:40,647 --> 00:08:42,130
That's also important,

191
00:08:42,130 --> 00:08:45,136
in this case, "Item not found."

192
00:08:45,136 --> 00:08:47,513
Okay, that's everything for this video.

193
00:08:47,513 --> 00:08:50,521
We are now able to retrieve
items from a database.

194
00:08:50,521 --> 00:08:52,958
This has limited use until we can also

195
00:08:52,958 --> 00:08:54,962
insert items into the database.

196
00:08:54,962 --> 00:08:56,960
That's what we're going
to do in the next video.

197
00:08:56,960 --> 00:08:58,152
Thanks for joining me.

198
00:08:58,152 --> 00:08:59,590
I'll see you there.