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

2
00:00:02,377 --> 00:00:06,339
In this video, we're going
to allow users to sign up.

3
00:00:06,339 --> 00:00:07,898
Now the first thing I'm going to change

4
00:00:07,898 --> 00:00:10,336
is this test.py file.

5
00:00:10,336 --> 00:00:11,912
Remember in the last video I told you

6
00:00:11,912 --> 00:00:16,079
that we had to run test.py
from outside the folder,

7
00:00:16,980 --> 00:00:18,948
so that data.db got created there,

8
00:00:18,948 --> 00:00:23,432
and then we had to run the
app inside the main folder,

9
00:00:23,432 --> 00:00:24,686
as opposed to inside code.

10
00:00:24,686 --> 00:00:26,358
And it was getting a bit confusing.

11
00:00:26,358 --> 00:00:30,623
So instead of having
a sort of test.py file

12
00:00:30,623 --> 00:00:34,790
that creates the table, which
doesn't really make sense.

13
00:00:35,648 --> 00:00:38,939
Instead, let's have a new file,

14
00:00:38,939 --> 00:00:42,387
which is in charge of
creating the required tables.

15
00:00:42,387 --> 00:00:45,522
I'm going to call this create_tables.py,

16
00:00:45,522 --> 00:00:47,751
and that's going to be inside the code,

17
00:00:47,751 --> 00:00:50,584
so beside app, security, and user.

18
00:00:52,105 --> 00:00:54,373
And in here, all we're
going to do is make sure

19
00:00:54,373 --> 00:00:57,682
that we create the appropriate tables.

20
00:00:57,682 --> 00:01:00,024
In this video, this is
going to be the users table,

21
00:01:00,024 --> 00:01:03,857
but it might also be the
items table later on.

22
00:01:05,031 --> 00:01:09,419
So let's import sqlite3, and
then open up the connection,

23
00:01:09,419 --> 00:01:13,224
which is sqlite3.connect('data.db')

24
00:01:13,224 --> 00:01:15,549
By the way, this data.db
can be whatever you want.

25
00:01:15,549 --> 00:01:17,561
It doesn't have to be data.db.

26
00:01:17,561 --> 00:01:20,978
It can be mydatabase.db or anything else.

27
00:01:22,045 --> 00:01:26,503
Then the cursor, which is
going to be connection.cursor()

28
00:01:26,503 --> 00:01:29,503
And finally, the create_table query,

29
00:01:30,648 --> 00:01:34,984
which is going to be
CREATE TABLE IF NOT EXISTS,

30
00:01:34,984 --> 00:01:36,878
so it doesn't exist already,

31
00:01:36,878 --> 00:01:40,013
the name of the table,
users, and then the columns.

32
00:01:40,013 --> 00:01:43,346
So id int, username text, password text.

33
00:01:48,868 --> 00:01:50,618
Then make sure to run

34
00:01:51,576 --> 00:01:53,996
this by executing it.

35
00:01:53,996 --> 00:01:56,079
And finally we can commit

36
00:01:58,420 --> 00:01:59,253
and close.

37
00:02:02,660 --> 00:02:06,387
Okay, so now we've got the
create_tables script here,

38
00:02:06,387 --> 00:02:09,905
so we can run it, and that
will create a data.db,

39
00:02:09,905 --> 00:02:12,261
and in it, it will create the table.

40
00:02:12,261 --> 00:02:13,761
Now key thing:

41
00:02:15,495 --> 00:02:18,495
the id is going to start by being 1,

42
00:02:21,181 --> 00:02:23,001
then it's going to be 2,
then it's going to be 3,

43
00:02:23,001 --> 00:02:25,108
then 4, and so on.

44
00:02:25,108 --> 00:02:27,275
When we create a new user,

45
00:02:28,808 --> 00:02:30,724
it doesn't really make sense

46
00:02:30,724 --> 00:02:34,207
that we also have to specify its id,

47
00:02:34,207 --> 00:02:36,419
because it's always going to be one more

48
00:02:36,419 --> 00:02:39,632
than the number of users
we've got in the database.

49
00:02:39,632 --> 00:02:41,748
Fortunately, sql allows us

50
00:02:41,748 --> 00:02:45,283
to define auto-incrementing columns,

51
00:02:45,283 --> 00:02:47,904
which means that when we insert a row,

52
00:02:47,904 --> 00:02:50,795
it will automatically be id 1,

53
00:02:50,795 --> 00:02:54,408
then when we insert another
one it will be id 2, and so on.

54
00:02:54,408 --> 00:02:56,786
However, there is a specific thing

55
00:02:56,786 --> 00:02:59,869
that we have to write here, which is,

56
00:03:00,782 --> 00:03:04,032
instead of int, we have to use INTEGER,

57
00:03:05,502 --> 00:03:09,411
and then we also have to say PRIMARY KEY.

58
00:03:09,411 --> 00:03:11,328
Now normally in sqlite,

59
00:03:12,346 --> 00:03:16,013
it doesn't matter if
you use int or INTEGER,

60
00:03:17,231 --> 00:03:21,402
but when you want to create
auto-incrementing columns,

61
00:03:21,402 --> 00:03:22,438
it does.

62
00:03:22,438 --> 00:03:24,885
You have to use the whole word INTEGER,

63
00:03:24,885 --> 00:03:26,885
as opposed to int, okay?

64
00:03:28,054 --> 00:03:29,787
So that's what we need, really.

65
00:03:29,787 --> 00:03:30,841
That's everything.

66
00:03:30,841 --> 00:03:33,096
When we create tables now,

67
00:03:33,096 --> 00:03:35,011
we're going to be creating a new table

68
00:03:35,011 --> 00:03:37,693
with an auto-incrementing id,

69
00:03:37,693 --> 00:03:40,210
which means that when we add a new user,

70
00:03:40,210 --> 00:03:42,961
we only have to specify the
username and the password,

71
00:03:42,961 --> 00:03:47,785
because the id would be
assigned automatically, okay?

72
00:03:47,785 --> 00:03:51,452
Okay, so now let's go
back to our user file.

73
00:03:53,045 --> 00:03:54,299
And what we're going to do

74
00:03:54,299 --> 00:03:57,834
is we're going to allow
our users to sign up.

75
00:03:57,834 --> 00:03:58,913
How do we do this?

76
00:03:58,913 --> 00:04:01,496
Well, we've got our class User,

77
00:04:02,396 --> 00:04:06,146
which gives us some
power over finding users.

78
00:04:08,344 --> 00:04:11,879
This User class must not be the same

79
00:04:11,879 --> 00:04:16,729
as the resource that we're
going to use to sign up.

80
00:04:16,729 --> 00:04:18,497
Now what I'm going to create

81
00:04:18,497 --> 00:04:20,795
is a new class called UserRegister,

82
00:04:20,795 --> 00:04:22,659
and this is going to be a Resource,

83
00:04:22,659 --> 00:04:25,637
and just like the Item
and the ItemList was.

84
00:04:25,637 --> 00:04:26,708
And it's going to be a Resource

85
00:04:26,708 --> 00:04:31,427
just so we can add it to
the API using flask_restful,

86
00:04:31,427 --> 00:04:33,099
so it's just going to
make things a bit simpler.

87
00:04:33,099 --> 00:04:37,266
We could just create a flask
endpoint to register users.

88
00:04:38,167 --> 00:04:40,265
We could do that instead
of creating a Resource,

89
00:04:40,265 --> 00:04:43,087
but it doesn't really
matter which way we do it.

90
00:04:43,087 --> 00:04:46,909
Okay, and this does mean
that from flask_restful,

91
00:04:46,909 --> 00:04:50,159
we're going to need to import Resource.

92
00:04:52,909 --> 00:04:55,408
Now the good thing about
using flask_restful,

93
00:04:55,408 --> 00:04:57,889
and making this user register a Resource,

94
00:04:57,889 --> 00:05:00,806
is that we only need to do post.

95
00:05:00,806 --> 00:05:02,339
Create the post method,

96
00:05:02,339 --> 00:05:04,681
and then this is what's
going to get called

97
00:05:04,681 --> 00:05:08,264
when we post some data
to the UserRegister.

98
00:05:09,174 --> 00:05:10,593
Let's go over to the app

99
00:05:10,593 --> 00:05:14,912
and make sure that we import
user from at the top here.

100
00:05:14,912 --> 00:05:18,282
So we're going to say from
user import UserRegister,

101
00:05:18,282 --> 00:05:20,424
apologies, make sure
to import UserRegister,

102
00:05:20,424 --> 00:05:22,444
which is our Resource.

103
00:05:22,444 --> 00:05:24,247
And then go to the very bottom

104
00:05:24,247 --> 00:05:28,414
and do api.add_resource(UserRegister),

105
00:05:30,429 --> 00:05:32,205
and here we can give it something like

106
00:05:32,205 --> 00:05:35,706
/register as an endpoint.

107
00:05:35,706 --> 00:05:36,638
Now what this means

108
00:05:36,638 --> 00:05:40,669
is that when we execute a
post request to /register,

109
00:05:40,669 --> 00:05:42,794
that's going to call the UserRegister,

110
00:05:42,794 --> 00:05:46,794
and that's going to call
this post method, okay?

111
00:05:48,018 --> 00:05:50,265
So what does the post method have to do?

112
00:05:50,265 --> 00:05:54,348
Well, the first thing it
has to do is to connect.

113
00:05:55,781 --> 00:05:59,948
So connection is
sqlite3.connect('data.db')

114
00:06:02,171 --> 00:06:03,573
and then create a cursor,

115
00:06:03,573 --> 00:06:06,003
which is going to be connection.cursor(),

116
00:06:06,003 --> 00:06:08,293
and you know all the drill with this.

117
00:06:08,293 --> 00:06:12,460
Now the query is going to
be INSERT INTO users VALUES,

118
00:06:14,336 --> 00:06:16,582
and this is important as well,

119
00:06:16,582 --> 00:06:18,864
and here we have to
insert specific values:

120
00:06:18,864 --> 00:06:21,781
id, username, and password.

121
00:06:21,781 --> 00:06:25,948
The id is auto-incrementing,
so we have to insert NULL,

122
00:06:27,510 --> 00:06:28,906
and then question mark, question mark

123
00:06:28,906 --> 00:06:31,406
for the username and password.

124
00:06:32,520 --> 00:06:35,254
Cursor.execute, the query,

125
00:06:35,254 --> 00:06:39,381
and now we need the username and password,

126
00:06:39,381 --> 00:06:41,445
which we are going to get

127
00:06:41,445 --> 00:06:42,862
data['username'],

128
00:06:45,372 --> 00:06:46,789
data['password'].

129
00:06:47,862 --> 00:06:50,100
We're going to get data in a moment.

130
00:06:50,100 --> 00:06:53,683
Finally, we're just
going to commit, close,

131
00:06:54,727 --> 00:06:57,809
and return a wee message saying,

132
00:06:57,809 --> 00:07:00,142
"User created successfully."

133
00:07:03,190 --> 00:07:06,142
Okay, so the only thing
that's missing is the data.

134
00:07:06,142 --> 00:07:08,780
But you can do this yourselves first,

135
00:07:08,780 --> 00:07:11,227
so I'd recommend that you pause the video,

136
00:07:11,227 --> 00:07:15,032
create a request parser as
we did in the last section,

137
00:07:15,032 --> 00:07:17,888
for the items, create a request parser

138
00:07:17,888 --> 00:07:20,422
that accepts a username and a password,

139
00:07:20,422 --> 00:07:23,688
parse the JSON data coming
into this post request,

140
00:07:23,688 --> 00:07:26,169
and call it data so that we can use it

141
00:07:26,169 --> 00:07:28,442
in these parameters here.

142
00:07:28,442 --> 00:07:30,871
It's a bit of a more
complicated sort of assignment,

143
00:07:30,871 --> 00:07:32,674
but I would recommend
that you give it a go.

144
00:07:32,674 --> 00:07:34,467
So pause the video now and try that,

145
00:07:34,467 --> 00:07:37,800
and then come back and we'll do it here.

146
00:07:40,058 --> 00:07:42,291
Okay, hopefully you got that.

147
00:07:42,291 --> 00:07:45,626
The first thing to do
is to import the parser

148
00:07:45,626 --> 00:07:48,876
from flask_restful, so that's reqparse.

149
00:07:51,338 --> 00:07:53,872
And then we are going to create a parser

150
00:07:53,872 --> 00:07:56,615
in the UserRegister class.

151
00:07:56,615 --> 00:08:00,782
The parser is going to be
reqparse.RequestParser(),

152
00:08:02,118 --> 00:08:04,033
and then we're going to add two arguments,

153
00:08:04,033 --> 00:08:05,740
username, and password.

154
00:08:05,740 --> 00:08:07,407
Parser.add_argument,

155
00:08:08,496 --> 00:08:09,579
the username,

156
00:08:13,328 --> 00:08:16,855
and the type is going
to be str for string,

157
00:08:16,855 --> 00:08:19,990
required is going to be
True, because it is required,

158
00:08:19,990 --> 00:08:22,793
and the help message is going to be

159
00:08:22,793 --> 00:08:25,210
"This field cannot be blank."

160
00:08:26,843 --> 00:08:30,486
And then we're going to copy
that, place it in there.

161
00:08:30,486 --> 00:08:33,233
Might call it password.

162
00:08:33,233 --> 00:08:37,400
Now this parser will parse
through the JSON of the request,

163
00:08:38,310 --> 00:08:40,809
and make sure the username
and password are there.

164
00:08:40,809 --> 00:08:43,195
But we, of course, have to use the parser,

165
00:08:43,195 --> 00:08:47,362
so let's say data =
UserRegister.parser.parse_args()

166
00:08:54,320 --> 00:08:55,487
And that's it.

167
00:08:56,930 --> 00:08:59,247
Now what we're doing is to recap.

168
00:08:59,247 --> 00:09:02,346
Parse the arguments using
the UserRegister parser,

169
00:09:02,346 --> 00:09:05,368
which is going to expect
a username and a password.

170
00:09:05,368 --> 00:09:06,691
And then we're going to do

171
00:09:06,691 --> 00:09:08,555
the usual connect to the database,

172
00:09:08,555 --> 00:09:10,288
create a cursor,

173
00:09:10,288 --> 00:09:14,455
insert the values into the
table with NULL as the id,

174
00:09:15,921 --> 00:09:17,506
so it auto-increments,

175
00:09:17,506 --> 00:09:20,693
it goes first 1, then 2,
then 3, then 4, and so on,

176
00:09:20,693 --> 00:09:21,738
and then two question marks,

177
00:09:21,738 --> 00:09:25,143
so one for the username,
and one for the password,

178
00:09:25,143 --> 00:09:27,102
remembering that username and password

179
00:09:27,102 --> 00:09:28,935
have to be in a tuple,

180
00:09:29,897 --> 00:09:33,571
so we've got a tuple here of
the username and password.

181
00:09:33,571 --> 00:09:35,774
We don't need a comma
at the end of the tuple.

182
00:09:35,774 --> 00:09:38,857
We don't need a comma, but
we can put it in if you want,

183
00:09:38,857 --> 00:09:41,173
if that makes you feel
better that this is a tuple,

184
00:09:41,173 --> 00:09:43,019
you can put the comma there.

185
00:09:43,019 --> 00:09:44,874
It's not required.

186
00:09:44,874 --> 00:09:47,852
After running that query,
we're going to commmit,

187
00:09:47,852 --> 00:09:49,846
so that it gets saved to the disc,

188
00:09:49,846 --> 00:09:51,927
and we're going to close the connection,

189
00:09:51,927 --> 00:09:53,703
and finally we're going
to return a message

190
00:09:53,703 --> 00:09:56,080
saying the user was created successfully

191
00:09:56,080 --> 00:09:58,788
with a return response code of 201

192
00:09:58,788 --> 00:10:03,001
which, as you know, is
created, that's what it means.

193
00:10:03,001 --> 00:10:05,667
So now let's save that.

194
00:10:05,667 --> 00:10:09,211
Make sure to delete data.db
outside the project.

195
00:10:09,211 --> 00:10:11,187
This is not a required step,
but it kind of makes sense,

196
00:10:11,187 --> 00:10:13,338
because that's no longer in use.

197
00:10:13,338 --> 00:10:15,071
Let's go back to our terminal.

198
00:10:15,071 --> 00:10:16,725
I'm going to clear everything

199
00:10:16,725 --> 00:10:19,098
to make sure that we're
not doing anything silly.

200
00:10:19,098 --> 00:10:22,348
And then we are in the section5 folder,

201
00:10:23,948 --> 00:10:27,405
so we have test.py, code, and venv.

202
00:10:27,405 --> 00:10:29,329
We want to go inside code.

203
00:10:29,329 --> 00:10:30,246
So cd code.

204
00:10:32,350 --> 00:10:36,112
And then we're going to
run create_tables.py,

205
00:10:36,112 --> 00:10:39,090
which does nothing, but
it does create the table.

206
00:10:39,090 --> 00:10:42,669
And then we're going to run app.py, okay?

207
00:10:42,669 --> 00:10:45,234
Then let's go back to Postman,

208
00:10:45,234 --> 00:10:48,226
and now if we type /auth,

209
00:10:48,226 --> 00:10:50,876
we're going to get invalid credentials.

210
00:10:50,876 --> 00:10:52,142
That user doesn't exist,

211
00:10:52,142 --> 00:10:56,443
because we're trying to
log in with jose asdf,

212
00:10:56,443 --> 00:10:58,124
and that doesn't exist.

213
00:10:58,124 --> 00:11:00,571
So I'm going to close
the other tabs first.

214
00:11:00,571 --> 00:11:02,826
So we're going to need
to create a new endpoint.

215
00:11:02,826 --> 00:11:06,048
Let's duplicate the /auth endpoint.

216
00:11:06,048 --> 00:11:08,434
Make sure to edit it and
give it a decent name,

217
00:11:08,434 --> 00:11:09,670
which should be /register,

218
00:11:09,670 --> 00:11:14,120
because that's the endpoint
we've used in our Resource,

219
00:11:14,120 --> 00:11:18,287
and change it here to
be /register as well.

220
00:11:20,209 --> 00:11:22,280
Now we can send that in,

221
00:11:22,280 --> 00:11:24,918
and we see the user was
created successfully.

222
00:11:24,918 --> 00:11:28,654
And now we should be able to go to /auth

223
00:11:28,654 --> 00:11:31,580
and log in correctly, which we do.

224
00:11:31,580 --> 00:11:35,280
And now we get this access_token
back, so we can copy that.

225
00:11:35,280 --> 00:11:37,113
Go in to post an item.

226
00:11:39,599 --> 00:11:41,907
I'm going to get a table,
and that gets created fine,

227
00:11:41,907 --> 00:11:44,824
and then we're going to request it,

228
00:11:46,034 --> 00:11:47,897
and it tells me the request token that,

229
00:11:47,897 --> 00:11:49,848
"Request does not
contain an access token,"

230
00:11:49,848 --> 00:11:52,425
which we're going to put in here.

231
00:11:52,425 --> 00:11:56,342
Authorization, JWT, space,
paste everything in,

232
00:11:58,111 --> 00:11:59,748
and now we get a table back.

233
00:11:59,748 --> 00:12:01,377
So everything works.

234
00:12:01,377 --> 00:12:04,686
Once again, what we've done is
we've registered a new user,

235
00:12:04,686 --> 00:12:07,707
then we've authorised with
it, we've created a new item,

236
00:12:07,707 --> 00:12:09,091
and then we've retrieved the item

237
00:12:09,091 --> 00:12:10,493
because we know this endpoint

238
00:12:10,493 --> 00:12:13,576
has the JWT required decorator, okay?

239
00:12:16,972 --> 00:12:18,304
So to recap the code,

240
00:12:18,304 --> 00:12:22,370
what we've done is first got
the data from the JSON payload,

241
00:12:22,370 --> 00:12:24,173
then we've connected, created a cursor,

242
00:12:24,173 --> 00:12:27,308
inserted the values in, and
then committed and closed,

243
00:12:27,308 --> 00:12:29,928
and returned a correct message,

244
00:12:29,928 --> 00:12:32,663
and that is in the post method.

245
00:12:32,663 --> 00:12:34,552
So when we go back to our app,

246
00:12:34,552 --> 00:12:37,095
we see that the
UserRegister has been added

247
00:12:37,095 --> 00:12:40,578
as a Resource under
the /register endpoint,

248
00:12:40,578 --> 00:12:44,191
which is the one we were
calling through Postman.

249
00:12:44,191 --> 00:12:49,076
And then when we did the
get method for our item,

250
00:12:49,076 --> 00:12:51,088
we see that that needs a JWT,

251
00:12:51,088 --> 00:12:54,684
which we were passing
in after authorising.

252
00:12:54,684 --> 00:12:56,582
I would recommend you take a few minutes

253
00:12:56,582 --> 00:12:58,803
to connect all the dots in your head.

254
00:12:58,803 --> 00:13:00,736
Maybe draw a wee diagram

255
00:13:00,736 --> 00:13:02,912
of how things are
connecting to one another,

256
00:13:02,912 --> 00:13:04,785
and just to make sure that everything

257
00:13:04,785 --> 00:13:06,683
is sort of consolidated in your head,

258
00:13:06,683 --> 00:13:10,383
because it's only going
to get more complicated,

259
00:13:10,383 --> 00:13:12,534
and I wouldn't want you to feel confused.

260
00:13:12,534 --> 00:13:16,701
So now is a perfect time to
give yourself a bit of a break

261
00:13:17,820 --> 00:13:20,867
and ask any questions in the
course Q&A, if you've got any,

262
00:13:20,867 --> 00:13:23,723
regarding how this works
or how things connect,

263
00:13:23,723 --> 00:13:27,154
because I'm always
available to help you out.

264
00:13:27,154 --> 00:13:28,617
But once you feel like you're ready,

265
00:13:28,617 --> 00:13:31,650
let's move on to the next
video, and I'll see you there.