WEBVTT

1
00:00:01.730 --> 00:00:04.399
ok so in the previous video we talked

2
00:00:04.399 --> 00:00:05.810
about the problem that place holders
ok so in the previous video we talked

3
00:00:05.810 --> 00:00:07.940
were designed to solve but it's time you
about the problem that place holders

4
00:00:07.940 --> 00:00:10.190
have a good look at how we use them
were designed to solve but it's time you

5
00:00:10.190 --> 00:00:12.920
now as you saw when i modified the code
have a good look at how we use them

6
00:00:12.920 --> 00:00:15.320
to use placeholders rather than string
now as you saw when i modified the code

7
00:00:15.320 --> 00:00:16.970
formatting they actually worked in a
to use placeholders rather than string

8
00:00:16.970 --> 00:00:19.519
very similar way we can see that
formatting they actually worked in a

9
00:00:19.519 --> 00:00:21.859
by comparing the code on line 9 to the
very similar way we can see that

10
00:00:21.859 --> 00:00:25.369
comment out code on line 8 instead of
by comparing the code on line 9 to the

11
00:00:25.369 --> 00:00:27.050
python replacement fields
comment out code on line 8 instead of

12
00:00:27.050 --> 00:00:28.369
that's the opening and closing curly
python replacement fields

13
00:00:28.369 --> 00:00:30.800
braces we just use a question mark to
that's the opening and closing curly

14
00:00:30.800 --> 00:00:33.230
indicate the places where values will be
braces we just use a question mark to

15
00:00:33.230 --> 00:00:36.559
placed then inour execute method we
indicate the places where values will be

16
00:00:36.559 --> 00:00:38.660
provide a tuple containing the values
placed then inour execute method we

17
00:00:38.660 --> 00:00:40.609
that will be used and you saw that on
provide a tuple containing the values

18
00:00:40.609 --> 00:00:44.510
line 13 now the tuple should have one
that will be used and you saw that on

19
00:00:44.510 --> 00:00:47.420
value for each ? in the string so when we
line 13 now the tuple should have one

20
00:00:47.420 --> 00:00:49.489
actually called the execute method on
value for each ? in the string so when we

21
00:00:49.489 --> 00:00:51.859
line 13 we actually passed the tuple
actually called the execute method on

22
00:00:51.859 --> 00:00:53.719
containing the new email address and the
line 13 we actually passed the tuple

23
00:00:53.719 --> 00:00:55.399
phone number now we have to use
containing the new email address and the

24
00:00:55.399 --> 00:00:57.079
parentheses around the tuple here
phone number now we have to use

25
00:00:57.079 --> 00:00:59.210
otherwise python would interpreted as a
parentheses around the tuple here

26
00:00:59.210 --> 00:01:02.000
method call with three arguments now
otherwise python would interpreted as a

27
00:01:02.000 --> 00:01:05.449
update string on line 9 is very similar
method call with three arguments now

28
00:01:05.449 --> 00:01:06.830
to the commented out version on line
update string on line 9 is very similar

29
00:01:06.830 --> 00:01:09.320
8 but again we'll just use ? ?
to the commented out version on line

30
00:01:09.320 --> 00:01:11.540
character instead of the left-to-right
8 but again we'll just use ? ?

31
00:01:11.540 --> 00:01:14.330
curly braces now one other thing to note
character instead of the left-to-right

32
00:01:14.330 --> 00:01:17.000
is that we have an enclosed the first ?
curly braces now one other thing to note

33
00:01:17.000 --> 00:01:19.760
in single quotes now the library's
is that we have an enclosed the first ?

34
00:01:19.760 --> 00:01:22.430
parameters substitution takes care of
in single quotes now the library's

35
00:01:22.430 --> 00:01:24.230
correctly quoting strings where
parameters substitution takes care of

36
00:01:24.230 --> 00:01:26.240
necessary which makes our life a lot
correctly quoting strings where

37
00:01:26.240 --> 00:01:28.250
easier and that's really just about
necessary which makes our life a lot

38
00:01:28.250 --> 00:01:29.390
everything we need to know about
easier and that's really just about

39
00:01:29.390 --> 00:01:31.760
placeholders and parameter substitution
everything we need to know about

40
00:01:31.760 --> 00:01:33.890
use a question mark when you want an
placeholders and parameter substitution

41
00:01:33.890 --> 00:01:36.560
actual value to be inserted and provide
use a question mark when you want an

42
00:01:36.560 --> 00:01:38.630
the values as a tuple when you call the
actual value to be inserted and provide

43
00:01:38.630 --> 00:01:41.180
execute method on a cursor or a connection
the values as a tuple when you call the

44
00:01:41.180 --> 00:01:43.490
and obviously there's quite a lot going
execute method on a cursor or a connection

45
00:01:43.490 --> 00:01:45.320
on under the hood so to speak to
and obviously there's quite a lot going

46
00:01:45.320 --> 00:01:47.450
sanitize the values and to make sure
on under the hood so to speak to

47
00:01:47.450 --> 00:01:50.120
that it won't cause problems but from our
sanitize the values and to make sure

48
00:01:50.120 --> 00:01:51.800
point of view using placeholders is
that it won't cause problems but from our

49
00:01:51.800 --> 00:01:54.560
really very simple and all the python
point of view using placeholders is

50
00:01:54.560 --> 00:01:56.570
database library support placeholders
really very simple and all the python

51
00:01:56.570 --> 00:01:59.300
and parameter substitution so you can
database library support placeholders

52
00:01:59.300 --> 00:02:00.770
take the same approach when using a
and parameter substitution so you can

53
00:02:00.770 --> 00:02:03.080
different database mysql for example
take the same approach when using a

54
00:02:03.080 --> 00:02:05.330
but the placeholder characters may be
different database mysql for example

55
00:02:05.330 --> 00:02:06.290
different
but the placeholder characters may be

56
00:02:06.290 --> 00:02:08.000
the sqlite library use question marks
different

57
00:02:08.000 --> 00:02:10.909
but other database libraries they use things
the sqlite library use question marks

58
00:02:10.909 --> 00:02:12.760
like a percent s
but other database libraries they use things

59
00:02:12.760 --> 00:02:16.090
or colon one etc the documentation for
like a percent s

60
00:02:16.090 --> 00:02:18.099
the library will tell you what characters
or colon one etc the documentation for

61
00:02:18.099 --> 00:02:19.510
you need to use as a placeholder
the library will tell you what characters

62
00:02:19.510 --> 00:02:21.489
everything else will work in the same
you need to use as a placeholder

63
00:02:21.489 --> 00:02:24.459
way as it does withour sql databases
everything else will work in the same

64
00:02:24.459 --> 00:02:26.829
alright I'm going to finish this video
way as it does withour sql databases

65
00:02:26.829 --> 00:02:29.379
with a short challenge now the challenge
alright I'm going to finish this video

66
00:02:29.379 --> 00:02:31.659
itself is quite simple but if you find
with a short challenge now the challenge

67
00:02:31.659 --> 00:02:33.519
yourself struggling then what I
itself is quite simple but if you find

68
00:02:33.519 --> 00:02:35.260
suggest you go back and watch the two
yourself struggling then what I

69
00:02:35.260 --> 00:02:38.379
videos on tuples in section 7
suggest you go back and watch the two

70
00:02:38.379 --> 00:02:42.459
alright so here's the challenge so your
videos on tuples in section 7

71
00:02:42.459 --> 00:02:44.859
challenge is to modify the check DB
alright so here's the challenge so your

72
00:02:44.859 --> 00:02:47.680
program so that it asks the user to
challenge is to modify the check DB

73
00:02:47.680 --> 00:02:50.230
enter a name then uses a where clause in
program so that it asks the user to

74
00:02:50.230 --> 00:02:52.629
the sql statement to retrieve just
enter a name then uses a where clause in

75
00:02:52.629 --> 00:02:55.450
the row for that one person now it seems
the sql statement to retrieve just

76
00:02:55.450 --> 00:02:57.159
simple but if you find yourself
the row for that one person now it seems

77
00:02:57.159 --> 00:02:59.709
struggling with the Python syntax as i
simple but if you find yourself

78
00:02:59.709 --> 00:03:01.690
mentioned go back and watch the tuple
struggling with the Python syntax as i

79
00:03:01.690 --> 00:03:04.989
videos in section 7 before you start
mentioned go back and watch the tuple

80
00:03:04.989 --> 00:03:07.060
also remember to change the table name
videos in section 7 before you start

81
00:03:07.060 --> 00:03:09.970
in check db back to contacts because if
also remember to change the table name

82
00:03:09.970 --> 00:03:12.069
you recall i change that to sqlite
in check db back to contacts because if

83
00:03:12.069 --> 00:03:14.769
_master in the previous video so
you recall i change that to sqlite

84
00:03:14.769 --> 00:03:16.150
that's a challenge so how you go
_master in the previous video so

85
00:03:16.150 --> 00:03:18.129
pause the video and I'll see you when
that's a challenge so how you go

86
00:03:18.129 --> 00:03:22.299
you get back ok so how did you get on
pause the video and I'll see you when

87
00:03:22.299 --> 00:03:23.919
with that hopefully managed to solve it
you get back ok so how did you get on

88
00:03:23.919 --> 00:03:26.319
now here's my solution to the challenge
with that hopefully managed to solve it

89
00:03:26.319 --> 00:03:29.440
alright so let's start by adding a name
now here's my solution to the challenge

90
00:03:29.440 --> 00:03:31.060
for the user on-line 5 so come back
alright so let's start by adding a name

91
00:03:31.060 --> 00:03:39.190
up here put name...
for the user on-line 5 so come back

92
00:03:39.190 --> 00:03:44.049
...
up here put name...

93
00:03:44.049 --> 00:03:47.260
...we're going to
...

94
00:03:47.260 --> 00:03:49.150
change the sqlite master that was
...we're going to

95
00:03:49.150 --> 00:03:51.099
changing the previous video that will be
change the sqlite master that was

96
00:03:51.099 --> 00:03:57.430
our contacts again...
changing the previous video that will be

97
00:03:57.430 --> 00:04:07.510
...
our contacts again...

98
00:04:07.510 --> 00:04:12.310
...
...

99
00:04:12.310 --> 00:04:14.979
....so I've modified
...

100
00:04:14.979 --> 00:04:17.560
the query to add a where clause and use
....so I've modified

101
00:04:17.560 --> 00:04:19.090
the placeholder for the name we want to
the query to add a where clause and use

102
00:04:19.090 --> 00:04:21.159
find now because we're using a
the placeholder for the name we want to

103
00:04:21.159 --> 00:04:23.200
placeholder we need to provide to the
find now because we're using a

104
00:04:23.200 --> 00:04:25.210
parameter to substitute
placeholder we need to provide to the

105
00:04:25.210 --> 00:04:26.380
that's the bit that may have caused you
parameter to substitute

106
00:04:26.380 --> 00:04:28.539
some problems in fact that's actually
that's the bit that may have caused you

107
00:04:28.539 --> 00:04:30.039
the reason I set this challenge
some problems in fact that's actually

108
00:04:30.039 --> 00:04:32.949
providing variable number of numbers of
the reason I set this challenge

109
00:04:32.949 --> 00:04:35.229
arguments to functions using a tuple is
providing variable number of numbers of

110
00:04:35.229 --> 00:04:37.539
very common and it's probably something
arguments to functions using a tuple is

111
00:04:37.539 --> 00:04:40.090
you find easy you'll find easy where
very common and it's probably something

112
00:04:40.090 --> 00:04:42.400
there's more than one value where people
you find easy you'll find easy where

113
00:04:42.400 --> 00:04:44.800
struggle is passing just a single value
there's more than one value where people

114
00:04:44.800 --> 00:04:46.660
to a tuple like we've done here
struggle is passing just a single value

115
00:04:46.660 --> 00:04:50.319
obviously I have separated the name
to a tuple like we've done here

116
00:04:50.319 --> 00:04:53.080
variable in parentheses but there's also
obviously I have separated the name

117
00:04:53.080 --> 00:04:55.060
a comma after that before the ending
variable in parentheses but there's also

118
00:04:55.060 --> 00:04:57.759
right parenthesis so when you're telling
a comma after that before the ending

119
00:04:57.759 --> 00:04:59.380
python that you want a tuple containing
right parenthesis so when you're telling

120
00:04:59.380 --> 00:05:01.479
a single value you have to put a comma
python that you want a tuple containing

121
00:05:01.479 --> 00:05:04.300
like I've done after the value and because
a single value you have to put a comma

122
00:05:04.300 --> 00:05:05.770
the tuple was being passed as an argument
like I've done after the value and because

123
00:05:05.770 --> 00:05:07.360
to the function call
the tuple was being passed as an argument

124
00:05:07.360 --> 00:05:08.860
we also need to enclose that in
to the function call

125
00:05:08.860 --> 00:05:11.440
parentheses so we actually run this now
we also need to enclose that in

126
00:05:11.440 --> 00:05:14.169
just to confirm that it works enter a name
parentheses so we actually run this now

127
00:05:14.169 --> 00:05:17.110
to search for type my name Tim press
just to confirm that it works enter a name

128
00:05:17.110 --> 00:05:20.979
enter and we get my detail showing now
to search for type my name Tim press

129
00:05:20.979 --> 00:05:23.050
the equals check in a where clause is
enter and we get my detail showing now

130
00:05:23.050 --> 00:05:25.690
case sensitive so you may have use like
the equals check in a where clause is

131
00:05:25.690 --> 00:05:28.090
instead so firstly just to confirm that
case sensitive so you may have use like

132
00:05:28.090 --> 00:05:30.190
it is case-sensitive will use a
instead so firstly just to confirm that

133
00:05:30.190 --> 00:05:34.330
lowercase tim like so we get nothing this
it is case-sensitive will use a

134
00:05:34.330 --> 00:05:35.889
time because the record save with the
lowercase tim like so we get nothing this

135
00:05:35.889 --> 00:05:38.259
capital T if we change this line here
time because the record save with the

136
00:05:38.259 --> 00:05:40.719
and I'll commented out duplicate it and
capital T if we change this line here

137
00:05:40.719 --> 00:05:43.990
commented out to change instead of
and I'll commented out duplicate it and

138
00:05:43.990 --> 00:05:46.210
putting the where clause well we still need
commented out to change instead of

139
00:05:46.210 --> 00:05:47.380
we're clause but instead of putting the
putting the where clause well we still need

140
00:05:47.380 --> 00:05:51.490
equals we put like and if we run that
we're clause but instead of putting the

141
00:05:51.490 --> 00:05:54.400
you should be able to do a search
equals we put like and if we run that

142
00:05:54.400 --> 00:05:56.949
for lower case tim and it still matches
you should be able to do a search

143
00:05:56.949 --> 00:05:57.880
that even though it started with
for lower case tim and it still matches

144
00:05:57.880 --> 00:06:03.430
uppercase and just running it again with
that even though it started with

145
00:06:03.430 --> 00:06:05.050
the upper case either case it's actually
uppercase and just running it again with

146
00:06:05.050 --> 00:06:08.710
working nicely now you may have just
the upper case either case it's actually

147
00:06:08.710 --> 00:06:11.320
tried passing the value name like this
working nicely now you may have just

148
00:06:11.320 --> 00:06:17.320
when you try and do that and run it
tried passing the value name like this

149
00:06:17.320 --> 00:06:18.639
actually got a really weird error
when you try and do that and run it

150
00:06:18.639 --> 00:06:25.659
incorrect number of binding supplied the
actually got a really weird error

151
00:06:25.659 --> 00:06:27.400
current statement uses one and there are
incorrect number of binding supplied the

152
00:06:27.400 --> 00:06:28.990
three supplied which is a little bit
current statement uses one and there are

153
00:06:28.990 --> 00:06:32.139
bizarre looking at the code it's
three supplied which is a little bit

154
00:06:32.139 --> 00:06:34.120
actually hard to see how three values
bizarre looking at the code it's

155
00:06:34.120 --> 00:06:36.219
are being supplied to anything on line
actually hard to see how three values

156
00:06:36.219 --> 00:06:37.210
eight
are being supplied to anything on line

157
00:06:37.210 --> 00:06:39.009
but might actually become clear if we
eight

158
00:06:39.009 --> 00:06:40.900
run the program again and try searching
but might actually become clear if we

159
00:06:40.900 --> 00:06:47.500
for brian this times so lets do that now
run the program again and try searching

160
00:06:47.500 --> 00:06:48.970
it's saying the current statement uses one
for brian this times so lets do that now

161
00:06:48.970 --> 00:06:51.280
and there are five supplied but not
it's saying the current statement uses one

162
00:06:51.280 --> 00:06:53.349
specifying that name is a tuple
and there are five supplied but not

163
00:06:53.349 --> 00:06:55.240
we're actually confusing the sqlite
specifying that name is a tuple

164
00:06:55.240 --> 00:06:57.160
library the number is showing in the
we're actually confusing the sqlite

165
00:06:57.160 --> 00:06:58.960
error message is a number of characters
library the number is showing in the

166
00:06:58.960 --> 00:07:01.449
in our name string 3 for tim and five for
error message is a number of characters

167
00:07:01.449 --> 00:07:03.759
Brian now that's not really important
in our name string 3 for tim and five for

168
00:07:03.759 --> 00:07:05.560
i'm just pointing out sometimes that
Brian now that's not really important

169
00:07:05.560 --> 00:07:07.599
error messages really aren't as helpful as
i'm just pointing out sometimes that

170
00:07:07.599 --> 00:07:09.699
they could be this to make very good
error messages really aren't as helpful as

171
00:07:09.699 --> 00:07:11.380
subject for a google search thought to
they could be this to make very good

172
00:07:11.380 --> 00:07:12.460
find out more
subject for a google search thought to

173
00:07:12.460 --> 00:07:14.650
so if you get stuck placed the entire line
find out more

174
00:07:14.650 --> 00:07:16.120
of the error message into your search
so if you get stuck placed the entire line

175
00:07:16.120 --> 00:07:16.960
engine
of the error message into your search

176
00:07:16.960 --> 00:07:18.820
alright so that's the first attempt and
engine

177
00:07:18.820 --> 00:07:20.229
having decided we should be passing a
alright so that's the first attempt and

178
00:07:20.229 --> 00:07:22.630
tuple we put name in parentheses so if
having decided we should be passing a

179
00:07:22.630 --> 00:07:25.270
we go back and put the parentheses back
tuple we put name in parentheses so if

180
00:07:25.270 --> 00:07:27.970
in around name variable noting that i
we go back and put the parentheses back

181
00:07:27.970 --> 00:07:30.070
haven't got a semi a comma at
in around name variable noting that i

182
00:07:30.070 --> 00:07:36.849
the end though when we run this we still
haven't got a semi a comma at

183
00:07:36.849 --> 00:07:38.229
get the same error three in this case
the end though when we run this we still

184
00:07:38.229 --> 00:07:40.030
because i choose time and we also got a
get the same error three in this case

185
00:07:40.030 --> 00:07:41.500
warning here remove redundant
because i choose time and we also got a

186
00:07:41.500 --> 00:07:45.639
parentheses so remember when it's not
warning here remove redundant

187
00:07:45.639 --> 00:07:47.590
the closing parenthesis that define a
parentheses so remember when it's not

188
00:07:47.590 --> 00:07:49.630
tuple it's a fact that more than one
the closing parenthesis that define a

189
00:07:49.630 --> 00:07:51.970
value is specified so we do need
tuple it's a fact that more than one

190
00:07:51.970 --> 00:07:53.800
parentheses here even though at the
value is specified so we do need

191
00:07:53.800 --> 00:07:55.780
moment intellij saying we need to
parentheses here even though at the

192
00:07:55.780 --> 00:07:58.300
remove our redundant parentheses but we're
moment intellij saying we need to

193
00:07:58.300 --> 00:08:00.250
passing the tuple to a method and
remove our redundant parentheses but we're

194
00:08:00.250 --> 00:08:01.719
commas are actually used to
passing the tuple to a method and

195
00:08:01.719 --> 00:08:03.520
separate individual arguments in a
commas are actually used to

196
00:08:03.520 --> 00:08:05.740
method call but the parentheses
separate individual arguments in a

197
00:08:05.740 --> 00:08:09.009
themselves don't turn this with the
method call but the parentheses

198
00:08:09.009 --> 00:08:10.990
parentheses around name into a tuple
themselves don't turn this with the

199
00:08:10.990 --> 00:08:13.630
to do that we do need to add the comma
parentheses around name into a tuple

200
00:08:13.630 --> 00:08:16.120
at the end of name before the closing
to do that we do need to add the comma

201
00:08:16.120 --> 00:08:18.909
right parenthesis and notice the warning
at the end of name before the closing

202
00:08:18.909 --> 00:08:21.490
disappears at the same time just to
right parenthesis and notice the warning

203
00:08:21.490 --> 00:08:26.710
confirm will run this again brian now
disappears at the same time just to

204
00:08:26.710 --> 00:08:29.110
successfully works so if you struggled
confirm will run this again brian now

205
00:08:29.110 --> 00:08:30.639
with that and couldn't get it to work
successfully works so if you struggled

206
00:08:30.639 --> 00:08:32.890
don't worry you know how to provide a
with that and couldn't get it to work

207
00:08:32.890 --> 00:08:35.860
tuple that contains a single value when
don't worry you know how to provide a

208
00:08:35.860 --> 00:08:37.329
calling methods and functions and that's
tuple that contains a single value when

209
00:08:37.329 --> 00:08:38.589
something that you'll probably be using
calling methods and functions and that's

210
00:08:38.589 --> 00:08:41.020
fairly often and it is something that
something that you'll probably be using

211
00:08:41.020 --> 00:08:43.659
people do struggle with substituting
fairly often and it is something that

212
00:08:43.659 --> 00:08:45.070
a single statement in sql statement
people do struggle with substituting

213
00:08:45.070 --> 00:08:47.260
is common so I have included this
a single statement in sql statement

214
00:08:47.260 --> 00:08:49.209
challenge to make sure you knew how do it before
is common so I have included this

215
00:08:49.209 --> 00:08:50.180
we move on
challenge to make sure you knew how do it before

216
00:08:50.180 --> 00:08:51.529
alright so that's the end of this video
we move on

217
00:08:51.529 --> 00:08:53.450
in the next video we're going to leave
alright so that's the end of this video

218
00:08:53.450 --> 00:08:55.580
databases briefly and have a look at
in the next video we're going to leave

219
00:08:55.580 --> 00:08:58.250
handling exceptions in python see you in
databases briefly and have a look at

220
00:08:58.250 --> 00:09:03.760
the next video
handling exceptions in python see you in