1
00:00:05,380 --> 00:00:08,039
ok so in the previous video we talked

2
00:00:08,039 --> 00:00:09,460
about the problem that place holders

3
00:00:09,460 --> 00:00:11,590
were designed to solve but it's time you

4
00:00:11,590 --> 00:00:13,840
have a good look at how we use them

5
00:00:13,840 --> 00:00:16,570
now as you saw when i modified the code

6
00:00:16,570 --> 00:00:18,970
to use placeholders rather than string

7
00:00:18,970 --> 00:00:20,620
formatting they actually worked in a

8
00:00:20,620 --> 00:00:23,160
very similar way we can see that

9
00:00:23,160 --> 00:00:25,500
by comparing the code on line 9 to the

10
00:00:25,500 --> 00:00:29,010
comment out code on line 8 instead of

11
00:00:29,010 --> 00:00:30,700
python replacement fields

12
00:00:30,700 --> 00:00:32,009
that's the opening and closing curly

13
00:00:32,009 --> 00:00:34,450
braces we just use a question mark to

14
00:00:34,450 --> 00:00:36,880
indicate the places where values will be

15
00:00:36,880 --> 00:00:40,200
placed then inour execute method we

16
00:00:40,200 --> 00:00:42,310
provide a tuple containing the values

17
00:00:42,310 --> 00:00:44,250
that will be used and you saw that on

18
00:00:44,250 --> 00:00:48,160
line 13 now the tuple should have one

19
00:00:48,160 --> 00:00:51,070
value for each ? in the string so when we

20
00:00:51,070 --> 00:00:53,130
actually called the execute method on

21
00:00:53,130 --> 00:00:55,500
line 13 we actually passed the tuple

22
00:00:55,500 --> 00:00:57,360
containing the new email address and the

23
00:00:57,360 --> 00:00:59,040
phone number now we have to use

24
00:00:59,040 --> 00:01:00,720
parentheses around the tuple here

25
00:01:00,720 --> 00:01:02,860
otherwise python would interpreted as a

26
00:01:02,860 --> 00:01:05,650
method call with three arguments now

27
00:01:05,650 --> 00:01:09,090
update string on line 9 is very similar

28
00:01:09,090 --> 00:01:10,480
to the commented out version on line

29
00:01:10,480 --> 00:01:12,970
8 but again we'll just use ? ?

30
00:01:12,970 --> 00:01:15,190
character instead of the left-to-right

31
00:01:15,190 --> 00:01:17,980
curly braces now one other thing to note

32
00:01:17,980 --> 00:01:20,650
is that we have an enclosed the first ?

33
00:01:20,650 --> 00:01:23,410
in single quotes now the library's

34
00:01:23,410 --> 00:01:26,080
parameters substitution takes care of

35
00:01:26,080 --> 00:01:27,880
correctly quoting strings where

36
00:01:27,880 --> 00:01:29,890
necessary which makes our life a lot

37
00:01:29,890 --> 00:01:31,900
easier and that's really just about

38
00:01:31,900 --> 00:01:33,040
everything we need to know about

39
00:01:33,040 --> 00:01:35,410
placeholders and parameter substitution

40
00:01:35,410 --> 00:01:37,540
use a question mark when you want an

41
00:01:37,540 --> 00:01:40,210
actual value to be inserted and provide

42
00:01:40,210 --> 00:01:42,280
the values as a tuple when you call the

43
00:01:42,280 --> 00:01:44,830
execute method on a cursor or a connection

44
00:01:44,830 --> 00:01:47,140
and obviously there's quite a lot going

45
00:01:47,140 --> 00:01:48,970
on under the hood so to speak to

46
00:01:48,970 --> 00:01:51,100
sanitize the values and to make sure

47
00:01:51,100 --> 00:01:53,770
that it won't cause problems but from our

48
00:01:53,770 --> 00:01:55,450
point of view using placeholders is

49
00:01:55,450 --> 00:01:58,210
really very simple and all the python

50
00:01:58,210 --> 00:02:00,220
database library support placeholders

51
00:02:00,220 --> 00:02:02,950
and parameter substitution so you can

52
00:02:02,950 --> 00:02:04,420
take the same approach when using a

53
00:02:04,420 --> 00:02:06,730
different database mysql for example

54
00:02:06,730 --> 00:02:08,979
but the placeholder characters may be

55
00:02:08,979 --> 00:02:09,940
different

56
00:02:09,940 --> 00:02:11,650
the sqlite library use question marks

57
00:02:11,650 --> 00:02:14,550
but other database libraries they use things

58
00:02:14,550 --> 00:02:16,410
like a percent s

59
00:02:16,410 --> 00:02:19,740
or colon one etc the documentation for

60
00:02:19,740 --> 00:02:21,740
the library will tell you what characters

61
00:02:21,740 --> 00:02:23,160
you need to use as a placeholder

62
00:02:23,160 --> 00:02:25,130
everything else will work in the same

63
00:02:25,130 --> 00:02:28,100
way as it does withour sql databases

64
00:02:28,100 --> 00:02:30,470
alright I'm going to finish this video

65
00:02:30,470 --> 00:02:33,020
with a short challenge now the challenge

66
00:02:33,020 --> 00:02:35,300
itself is quite simple but if you find

67
00:02:35,300 --> 00:02:37,160
yourself struggling then what I

68
00:02:37,160 --> 00:02:38,910
suggest you go back and watch the two

69
00:02:38,910 --> 00:02:42,020
videos on tuples in section 7

70
00:02:42,020 --> 00:02:46,100
alright so here's the challenge so your

71
00:02:46,100 --> 00:02:48,500
challenge is to modify the check DB

72
00:02:48,500 --> 00:02:51,330
program so that it asks the user to

73
00:02:51,330 --> 00:02:53,880
enter a name then uses a where clause in

74
00:02:53,880 --> 00:02:56,270
the sql statement to retrieve just

75
00:02:56,270 --> 00:02:59,100
the row for that one person now it seems

76
00:02:59,100 --> 00:03:00,800
simple but if you find yourself

77
00:03:00,800 --> 00:03:03,350
struggling with the Python syntax as i

78
00:03:03,350 --> 00:03:05,340
mentioned go back and watch the tuple

79
00:03:05,340 --> 00:03:08,630
videos in section 7 before you start

80
00:03:08,630 --> 00:03:10,710
also remember to change the table name

81
00:03:10,710 --> 00:03:13,620
in check db back to contacts because if

82
00:03:13,620 --> 00:03:15,710
you recall i change that to sqlite

83
00:03:15,710 --> 00:03:18,410
_master in the previous video so

84
00:03:18,410 --> 00:03:19,800
that's a challenge so how you go

85
00:03:19,800 --> 00:03:21,770
pause the video and I'll see you when

86
00:03:21,770 --> 00:03:25,940
you get back ok so how did you get on

87
00:03:25,940 --> 00:03:27,560
with that hopefully managed to solve it

88
00:03:27,560 --> 00:03:29,960
now here's my solution to the challenge

89
00:03:29,960 --> 00:03:33,090
alright so let's start by adding a name

90
00:03:33,090 --> 00:03:34,710
for the user on-line 5 so come back

91
00:03:34,710 --> 00:03:42,840
up here put name...

92
00:03:42,840 --> 00:03:47,690
...

93
00:03:47,690 --> 00:03:50,910
...we're going to

94
00:03:50,910 --> 00:03:52,800
change the sqlite master that was

95
00:03:52,800 --> 00:03:54,740
changing the previous video that will be

96
00:03:54,740 --> 00:04:01,080
our contacts again...

97
00:04:01,080 --> 00:04:11,160
...

98
00:04:11,160 --> 00:04:15,960
...

99
00:04:15,960 --> 00:04:18,620
....so I've modified

100
00:04:18,620 --> 00:04:21,209
the query to add a where clause and use

101
00:04:21,209 --> 00:04:22,740
the placeholder for the name we want to

102
00:04:22,740 --> 00:04:24,800
find now because we're using a

103
00:04:24,800 --> 00:04:26,850
placeholder we need to provide to the

104
00:04:26,850 --> 00:04:28,860
parameter to substitute

105
00:04:28,860 --> 00:04:30,030
that's the bit that may have caused you

106
00:04:30,030 --> 00:04:32,180
some problems in fact that's actually

107
00:04:32,180 --> 00:04:33,680
the reason I set this challenge

108
00:04:33,680 --> 00:04:36,590
providing variable number of numbers of

109
00:04:36,590 --> 00:04:38,870
arguments to functions using a tuple is

110
00:04:38,870 --> 00:04:41,180
very common and it's probably something

111
00:04:41,180 --> 00:04:43,740
you find easy you'll find easy where

112
00:04:43,740 --> 00:04:46,050
there's more than one value where people

113
00:04:46,050 --> 00:04:48,450
struggle is passing just a single value

114
00:04:48,450 --> 00:04:50,310
to a tuple like we've done here

115
00:04:50,310 --> 00:04:53,960
obviously I have separated the name

116
00:04:53,960 --> 00:04:56,730
variable in parentheses but there's also

117
00:04:56,730 --> 00:04:58,710
a comma after that before the ending

118
00:04:58,710 --> 00:05:01,400
right parenthesis so when you're telling

119
00:05:01,400 --> 00:05:03,030
python that you want a tuple containing

120
00:05:03,030 --> 00:05:05,120
a single value you have to put a comma

121
00:05:05,120 --> 00:05:07,950
like I've done after the value and because

122
00:05:07,950 --> 00:05:09,420
the tuple was being passed as an argument

123
00:05:09,420 --> 00:05:11,010
to the function call

124
00:05:11,010 --> 00:05:12,510
we also need to enclose that in

125
00:05:12,510 --> 00:05:15,090
parentheses so we actually run this now

126
00:05:15,090 --> 00:05:17,810
just to confirm that it works enter a name

127
00:05:17,810 --> 00:05:20,760
to search for type my name Tim press

128
00:05:20,760 --> 00:05:24,620
enter and we get my detail showing now

129
00:05:24,620 --> 00:05:26,700
the equals check in a where clause is

130
00:05:26,700 --> 00:05:29,340
case sensitive so you may have use like

131
00:05:29,340 --> 00:05:31,740
instead so firstly just to confirm that

132
00:05:31,740 --> 00:05:33,840
it is case-sensitive will use a

133
00:05:33,840 --> 00:05:37,980
lowercase tim like so we get nothing this

134
00:05:37,980 --> 00:05:39,530
time because the record save with the

135
00:05:39,530 --> 00:05:41,900
capital T if we change this line here

136
00:05:41,900 --> 00:05:44,360
and I'll commented out duplicate it and

137
00:05:44,360 --> 00:05:47,640
commented out to change instead of

138
00:05:47,640 --> 00:05:49,860
putting the where clause well we still need

139
00:05:49,860 --> 00:05:51,030
we're clause but instead of putting the

140
00:05:51,030 --> 00:05:55,140
equals we put like and if we run that

141
00:05:55,140 --> 00:05:58,050
you should be able to do a search

142
00:05:58,050 --> 00:06:00,590
for lower case tim and it still matches

143
00:06:00,590 --> 00:06:01,530
that even though it started with

144
00:06:01,530 --> 00:06:07,080
uppercase and just running it again with

145
00:06:07,080 --> 00:06:08,700
the upper case either case it's actually

146
00:06:08,700 --> 00:06:12,360
working nicely now you may have just

147
00:06:12,360 --> 00:06:14,970
tried passing the value name like this

148
00:06:14,970 --> 00:06:20,970
when you try and do that and run it

149
00:06:20,970 --> 00:06:22,280
actually got a really weird error

150
00:06:22,280 --> 00:06:29,300
incorrect number of binding supplied the

151
00:06:29,300 --> 00:06:31,050
current statement uses one and there are

152
00:06:31,050 --> 00:06:32,640
three supplied which is a little bit

153
00:06:32,640 --> 00:06:35,780
bizarre looking at the code it's

154
00:06:35,780 --> 00:06:37,770
actually hard to see how three values

155
00:06:37,770 --> 00:06:39,860
are being supplied to anything on line

156
00:06:39,860 --> 00:06:40,860
eight

157
00:06:40,860 --> 00:06:42,650
but might actually become clear if we

158
00:06:42,650 --> 00:06:44,550
run the program again and try searching

159
00:06:44,550 --> 00:06:51,150
for brian this times so lets do that now

160
00:06:51,150 --> 00:06:52,620
it's saying the current statement uses one

161
00:06:52,620 --> 00:06:54,930
and there are five supplied but not

162
00:06:54,930 --> 00:06:56,990
specifying that name is a tuple

163
00:06:56,990 --> 00:06:58,890
we're actually confusing the sqlite

164
00:06:58,890 --> 00:07:00,810
library the number is showing in the

165
00:07:00,810 --> 00:07:02,610
error message is a number of characters

166
00:07:02,610 --> 00:07:05,090
in our name string 3 for tim and five for

167
00:07:05,090 --> 00:07:07,400
Brian now that's not really important

168
00:07:07,400 --> 00:07:09,210
i'm just pointing out sometimes that

169
00:07:09,210 --> 00:07:11,240
error messages really aren't as helpful as

170
00:07:11,240 --> 00:07:13,340
they could be this to make very good

171
00:07:13,340 --> 00:07:15,030
subject for a google search thought to

172
00:07:15,030 --> 00:07:16,110
find out more

173
00:07:16,110 --> 00:07:18,300
so if you get stuck placed the entire line

174
00:07:18,300 --> 00:07:19,770
of the error message into your search

175
00:07:19,770 --> 00:07:20,610
engine

176
00:07:20,610 --> 00:07:22,470
alright so that's the first attempt and

177
00:07:22,470 --> 00:07:23,870
having decided we should be passing a

178
00:07:23,870 --> 00:07:26,280
tuple we put name in parentheses so if

179
00:07:26,280 --> 00:07:28,920
we go back and put the parentheses back

180
00:07:28,920 --> 00:07:31,620
in around name variable noting that i

181
00:07:31,620 --> 00:07:33,720
haven't got a semi a comma at

182
00:07:33,720 --> 00:07:40,490
the end though when we run this we still

183
00:07:40,490 --> 00:07:41,870
get the same error three in this case

184
00:07:41,870 --> 00:07:43,680
because i choose time and we also got a

185
00:07:43,680 --> 00:07:45,150
warning here remove redundant

186
00:07:45,150 --> 00:07:49,280
parentheses so remember when it's not

187
00:07:49,280 --> 00:07:51,240
the closing parenthesis that define a

188
00:07:51,240 --> 00:07:53,280
tuple it's a fact that more than one

189
00:07:53,280 --> 00:07:55,620
value is specified so we do need

190
00:07:55,620 --> 00:07:57,450
parentheses here even though at the

191
00:07:57,450 --> 00:07:59,430
moment intellij saying we need to

192
00:07:59,430 --> 00:08:01,950
remove our redundant parentheses but we're

193
00:08:01,950 --> 00:08:03,900
passing the tuple to a method and

194
00:08:03,900 --> 00:08:05,360
commas are actually used to

195
00:08:05,360 --> 00:08:07,170
separate individual arguments in a

196
00:08:07,170 --> 00:08:09,390
method call but the parentheses

197
00:08:09,390 --> 00:08:12,650
themselves don't turn this with the

198
00:08:12,650 --> 00:08:14,640
parentheses around name into a tuple

199
00:08:14,640 --> 00:08:17,280
to do that we do need to add the comma

200
00:08:17,280 --> 00:08:19,770
at the end of name before the closing

201
00:08:19,770 --> 00:08:22,550
right parenthesis and notice the warning

202
00:08:22,550 --> 00:08:25,140
disappears at the same time just to

203
00:08:25,140 --> 00:08:30,360
confirm will run this again brian now

204
00:08:30,360 --> 00:08:32,760
successfully works so if you struggled

205
00:08:32,760 --> 00:08:34,280
with that and couldn't get it to work

206
00:08:34,280 --> 00:08:36,539
don't worry you know how to provide a

207
00:08:36,539 --> 00:08:39,510
tuple that contains a single value when

208
00:08:39,510 --> 00:08:40,970
calling methods and functions and that's

209
00:08:40,970 --> 00:08:42,230
something that you'll probably be using

210
00:08:42,230 --> 00:08:44,670
fairly often and it is something that

211
00:08:44,670 --> 00:08:47,300
people do struggle with substituting

212
00:08:47,300 --> 00:08:48,720
a single statement in sql statement

213
00:08:48,720 --> 00:08:50,910
is common so I have included this

214
00:08:50,910 --> 00:08:52,850
challenge to make sure you knew how do it before

215
00:08:52,850 --> 00:08:53,830
we move on

216
00:08:53,830 --> 00:08:55,170
alright so that's the end of this video

217
00:08:55,170 --> 00:08:57,100
in the next video we're going to leave

218
00:08:57,100 --> 00:08:59,230
databases briefly and have a look at

219
00:08:59,230 --> 00:09:01,900
handling exceptions in python see you in

220
00:09:01,900 --> 00:09:07,410
the next video