1 00:00:05,280 --> 00:00:07,250 Okay so lets have a look at that short cut 2 00:00:07,250 --> 00:00:09,610 that I mentioned in the previous video. 3 00:00:09,610 --> 00:00:12,830 So we're gonna go ahead and create a new python file. 4 00:00:12,830 --> 00:00:16,540 So I'm going to right click, new, select python file. 5 00:00:16,540 --> 00:00:19,040 We'll call this one contacts2. 6 00:00:21,220 --> 00:00:23,830 Then let's go back to the orignal contacts profile 7 00:00:23,830 --> 00:00:25,640 that we created in the previous video, 8 00:00:25,640 --> 00:00:27,200 and just copy the first three lines 9 00:00:27,200 --> 00:00:29,450 including the second line which is blank. 10 00:00:29,450 --> 00:00:30,710 Paste that in there. 11 00:00:30,710 --> 00:00:32,830 Then I'm gonna close down this rom window. 12 00:00:32,830 --> 00:00:34,480 And let's double click that 13 00:00:34,480 --> 00:00:36,470 so we've got a full screen there. 14 00:00:36,470 --> 00:00:38,680 Alright, so at the moment we've got 15 00:00:38,680 --> 00:00:42,500 the import and we're opening the contacts.sqlite. 16 00:00:42,500 --> 00:00:44,540 Now sometimes cursors are useful, 17 00:00:44,540 --> 00:00:46,940 and again we used a cursor in the previous video, 18 00:00:46,940 --> 00:00:49,370 and we will be using those a little later. 19 00:00:49,370 --> 00:00:52,060 But in the scenario where you just want to iterate 20 00:00:52,060 --> 00:00:54,030 over the resulting data set, 21 00:00:54,030 --> 00:00:57,720 python allows you to execute queries using the connexion 22 00:00:57,720 --> 00:01:00,410 without having to create a cursor. 23 00:01:00,410 --> 00:01:02,080 So let's look at how to do that. 24 00:01:02,080 --> 00:01:04,650 So we've still got our connexion. 25 00:01:04,650 --> 00:01:08,810 On line five I've gotta type, for row in db.execute 26 00:01:11,360 --> 00:01:14,440 and select, and that is how 27 00:01:14,440 --> 00:01:16,920 now that intellij is helping us 28 00:01:16,920 --> 00:01:18,930 with these commands, so I can just press enter there, 29 00:01:18,930 --> 00:01:21,700 select, star, type in F, 30 00:01:21,700 --> 00:01:23,770 and then it comes up with from for us automatically. 31 00:01:23,770 --> 00:01:24,880 Press enter there, 32 00:01:24,880 --> 00:01:29,050 and then the actual table name which on it was contacts. 33 00:01:30,370 --> 00:01:31,540 And print row. 34 00:01:33,270 --> 00:01:35,240 So you can see we haven't used a cursor there. 35 00:01:35,240 --> 00:01:38,290 Then lastly I'll just close the data base off. 36 00:01:38,290 --> 00:01:41,000 Right so, that's an alternative. 37 00:01:41,000 --> 00:01:43,730 And when you go to run this remember to right click it first 38 00:01:43,730 --> 00:01:45,530 and choose run contacts2, 39 00:01:45,530 --> 00:01:47,730 so that you're running this python script 40 00:01:47,730 --> 00:01:49,010 and not the original one. 41 00:01:49,010 --> 00:01:51,450 So if I run this, you're probably expecting 42 00:01:51,450 --> 00:01:53,510 to see the two rows printed out. 43 00:01:53,510 --> 00:01:56,240 So if we actually run it now we'll see the two rows 44 00:01:56,240 --> 00:01:58,860 and in fact, they're not printed. 45 00:01:58,860 --> 00:02:01,010 So at this point you might be checking a code, 46 00:02:01,010 --> 00:02:02,740 or looking at the screen and wondering, 47 00:02:02,740 --> 00:02:04,210 you know trying to work out what's going on 48 00:02:04,210 --> 00:02:05,820 and why nothing's printed. 49 00:02:05,820 --> 00:02:08,009 And you might even wanna launch a terminal session 50 00:02:08,009 --> 00:02:10,220 or command prompt and check the data base 51 00:02:10,220 --> 00:02:11,960 with the command line shell, 52 00:02:11,960 --> 00:02:14,740 but I won't bother because there's nothing in it. 53 00:02:14,740 --> 00:02:18,220 Now, reason is that when you make changes to a table, 54 00:02:18,220 --> 00:02:21,130 using insert, update or delete statements, 55 00:02:21,130 --> 00:02:22,960 nothing's made permanent, 56 00:02:22,960 --> 00:02:25,170 until you commit the changes. 57 00:02:25,170 --> 00:02:28,670 Now, some commands were forced the data to be committed, 58 00:02:28,670 --> 00:02:31,330 and we're looking at things like bulk update 59 00:02:31,330 --> 00:02:33,430 and sequel scripts a bit later, 60 00:02:33,430 --> 00:02:37,130 but it's not a good idea generally to rely on that behaviour. 61 00:02:37,130 --> 00:02:38,920 So the problem we've got is nothing to do 62 00:02:38,920 --> 00:02:40,650 with the code in this programme, 63 00:02:40,650 --> 00:02:42,700 it's because we didn't commit the changes 64 00:02:42,700 --> 00:02:44,070 in our previous script. 65 00:02:44,070 --> 00:02:46,970 So our insert statements were rolled back 66 00:02:46,970 --> 00:02:49,210 when we closed the data base. 67 00:02:49,210 --> 00:02:51,360 And looking at that other code, 68 00:02:51,360 --> 00:02:52,630 if you think about it 69 00:02:52,630 --> 00:02:56,270 it does make sense because when we run this programme, 70 00:02:56,270 --> 00:02:57,110 we run it now, 71 00:02:57,110 --> 00:03:00,490 this is running the contacts and not the contacts2, 72 00:03:00,490 --> 00:03:02,920 it's showing two records and every time I run it, 73 00:03:02,920 --> 00:03:04,930 it's only showing the two records. 74 00:03:04,930 --> 00:03:06,850 Well it's actually showing none as the third record, 75 00:03:06,850 --> 00:03:09,160 but the point is we've got these insert statements 76 00:03:09,160 --> 00:03:10,720 on line five and six, 77 00:03:10,720 --> 00:03:13,360 so if it was inserting records every time we run it, 78 00:03:13,360 --> 00:03:15,390 we would have many more records 79 00:03:15,390 --> 00:03:16,680 than actually what we've got showing, 80 00:03:16,680 --> 00:03:18,690 which is only those two. 81 00:03:18,690 --> 00:03:21,580 So, the reason for this is that sqlite wraps things 82 00:03:21,580 --> 00:03:24,910 like inserts and deletions in a transaction. 83 00:03:24,910 --> 00:03:28,610 Which is something that is very common in data base systems. 84 00:03:28,610 --> 00:03:31,900 The idea is that the entire transaction can be rolled back 85 00:03:31,900 --> 00:03:33,980 if something goes wrong. 86 00:03:33,980 --> 00:03:36,560 Now that's actually an incredibly useful feature. 87 00:03:36,560 --> 00:03:38,970 So in a banking application for example, 88 00:03:38,970 --> 00:03:40,560 you don't want to debit one account 89 00:03:40,560 --> 00:03:43,580 unless the paying account is also credited. 90 00:03:43,580 --> 00:03:46,070 So if something goes wrong between the two updates, 91 00:03:46,070 --> 00:03:48,350 money will go into the account being debited, 92 00:03:48,350 --> 00:03:50,480 but wouldn't be taken out of the other account, 93 00:03:50,480 --> 00:03:52,310 and that's good for the holder of the other account 94 00:03:52,310 --> 00:03:54,660 but not very good for the bank. 95 00:03:54,660 --> 00:03:57,220 So typically, the two updates will be performed 96 00:03:57,220 --> 00:03:58,680 in a transaction. 97 00:03:58,680 --> 00:04:02,630 Now the transaction is only committed if nothing goes wrong. 98 00:04:02,630 --> 00:04:05,940 Now if there's an error in either update in this scenario, 99 00:04:05,940 --> 00:04:08,390 the entire transaction would be rolled back. 100 00:04:08,390 --> 00:04:10,010 So if the bank debited an account 101 00:04:10,010 --> 00:04:12,390 and failed to take the money from the other account, 102 00:04:12,390 --> 00:04:15,120 the bank would owe the money that transferred. 103 00:04:15,120 --> 00:04:16,760 By the way if you're thinking I've got the terms 104 00:04:16,760 --> 00:04:19,000 debit and credit the wrong way round, 105 00:04:19,000 --> 00:04:20,899 your bank statements are produced 106 00:04:20,899 --> 00:04:22,940 from the point of view of the bank. 107 00:04:22,940 --> 00:04:25,230 So if your account's in credit, 108 00:04:25,230 --> 00:04:28,160 that means the bank effectively owes you money. 109 00:04:28,160 --> 00:04:31,940 And when you pay money in, the bank debits your account 110 00:04:31,940 --> 00:04:35,010 and credits theirs with the corresponding amount. 111 00:04:35,010 --> 00:04:36,720 So from their point of view, 112 00:04:36,720 --> 00:04:38,760 their account with you is in credit, 113 00:04:38,760 --> 00:04:40,620 and they owe you money. 114 00:04:40,620 --> 00:04:42,720 As a programmer you may well have to work on 115 00:04:42,720 --> 00:04:44,290 financial systems so, 116 00:04:44,290 --> 00:04:47,070 bear in mind that the way we learn to use debit and credit, 117 00:04:47,070 --> 00:04:48,320 from having a bank account 118 00:04:48,320 --> 00:04:51,530 is actually the other way round in accounting. 119 00:04:51,530 --> 00:04:53,430 Alright, back to python. 120 00:04:53,430 --> 00:04:56,390 So there's nothing in our contacts table, 121 00:04:56,390 --> 00:04:58,180 because we didn't commit the inserts 122 00:04:58,180 --> 00:05:00,280 in the previous programme which I've now got on screen, 123 00:05:00,280 --> 00:05:02,060 our contacts.py. 124 00:05:02,060 --> 00:05:05,280 So let's actually add a line to that script now, 125 00:05:05,280 --> 00:05:08,850 to commit the changes before we close the data base. 126 00:05:08,850 --> 00:05:09,920 So where we do that is, 127 00:05:09,920 --> 00:05:13,340 in between the cursor.close and the db.close, 128 00:05:13,340 --> 00:05:17,410 so we type in db.commit, obviously parenthesis 129 00:05:17,410 --> 00:05:19,250 because it's a function. 130 00:05:19,250 --> 00:05:21,030 Alright so now if I right click it, 131 00:05:21,030 --> 00:05:23,840 making sure I'm running contacts and not contacts2, 132 00:05:23,840 --> 00:05:25,310 and run it. 133 00:05:25,310 --> 00:05:26,990 We still get the same output, 134 00:05:26,990 --> 00:05:29,400 but this time the data has actually been committed 135 00:05:29,400 --> 00:05:31,010 to the table. 136 00:05:31,010 --> 00:05:33,500 So there's two important things we've seen here. 137 00:05:33,500 --> 00:05:36,570 The first is that it's crucial to commit any changes 138 00:05:36,570 --> 00:05:38,580 that you make to the data base, 139 00:05:38,580 --> 00:05:41,020 if you want those changes to be persistent. 140 00:05:41,020 --> 00:05:42,960 If you fail to commit the changes, 141 00:05:42,960 --> 00:05:45,660 they'll be lost when you close the connexion. 142 00:05:45,660 --> 00:05:47,310 Now you will forget at some point, 143 00:05:47,310 --> 00:05:48,970 but at least now you won't spend hours, 144 00:05:48,970 --> 00:05:52,420 or hopefully won't spend hours trying to debug your code. 145 00:05:52,420 --> 00:05:54,360 You'll actually realise straight away 146 00:05:54,360 --> 00:05:56,180 that you didn't commit the changes 147 00:05:56,180 --> 00:05:58,050 and you can add the commit to your code, 148 00:05:58,050 --> 00:06:00,730 run your test again and then move on. 149 00:06:00,730 --> 00:06:04,120 Alright the second thing, is that shortcut I mentioned. 150 00:06:04,120 --> 00:06:06,100 Very often we're not interested in a cursor, 151 00:06:06,100 --> 00:06:09,070 and I'll just go back to contacts2 pyscript. 152 00:06:09,070 --> 00:06:11,120 Now cursors can be very useful, 153 00:06:11,120 --> 00:06:12,660 but if you just wanna run a query 154 00:06:12,660 --> 00:06:14,100 and loop through the results, 155 00:06:14,100 --> 00:06:16,390 then you can execute the query using the connexion 156 00:06:16,390 --> 00:06:20,610 as I showed, without explicitly creating the cursor. 157 00:06:20,610 --> 00:06:23,890 So the connexions execute method does return a cursor 158 00:06:23,890 --> 00:06:25,870 when you execute a select statement, 159 00:06:25,870 --> 00:06:29,420 but you don't have to worry about explicitly defining one. 160 00:06:29,420 --> 00:06:32,540 And we can actually unpack the cursors tuples, 161 00:06:32,540 --> 00:06:35,050 just like we did in the previous programme. 162 00:06:35,050 --> 00:06:37,910 Right firstly let's just run this in this scenario first. 163 00:06:37,910 --> 00:06:39,780 So I'm running contacts2 now and making sure 164 00:06:39,780 --> 00:06:42,080 that contacts2 is selected here. 165 00:06:42,080 --> 00:06:43,670 And we're now getting some output, 166 00:06:43,670 --> 00:06:45,190 and that's because in the other script 167 00:06:45,190 --> 00:06:46,970 we've committed the change. 168 00:06:46,970 --> 00:06:48,860 So that's one way of doing it but as I mentioned 169 00:06:48,860 --> 00:06:51,530 we can also unpack the cursors tuple. 170 00:06:51,530 --> 00:06:54,020 So we can do something like this instead. 171 00:06:54,020 --> 00:06:57,150 So for name in, we'll change this here to, 172 00:06:57,150 --> 00:06:58,630 for, 173 00:06:58,630 --> 00:07:00,090 name, 174 00:07:00,090 --> 00:07:01,530 phone, 175 00:07:01,530 --> 00:07:03,610 email in db.execute 176 00:07:03,610 --> 00:07:05,620 and that remains the same, the rest of the line, 177 00:07:05,620 --> 00:07:08,530 and instead we'll have print name, 178 00:07:08,530 --> 00:07:09,530 print phone, 179 00:07:10,890 --> 00:07:11,890 print email, 180 00:07:13,340 --> 00:07:15,660 and let's add a separator as well so print, 181 00:07:15,660 --> 00:07:17,160 little typo there, 182 00:07:18,550 --> 00:07:22,400 and we'll print as mentioned a separator, 183 00:07:22,400 --> 00:07:24,900 times 20, and we can run that. 184 00:07:26,940 --> 00:07:30,790 And you can see we've got the two unpacked tuples. 185 00:07:30,790 --> 00:07:33,610 And note here that I haven't called db.commit, 186 00:07:33,610 --> 00:07:35,510 and the reason for that is we haven't actually written 187 00:07:35,510 --> 00:07:38,410 anything to the data base with this file, 188 00:07:38,410 --> 00:07:41,490 with this python script, so there's no changes to commit. 189 00:07:41,490 --> 00:07:43,550 Now we may have only saved two lines of code 190 00:07:43,550 --> 00:07:45,190 over the previous programme, 191 00:07:45,190 --> 00:07:47,340 but every line on a code you don't write 192 00:07:47,340 --> 00:07:49,500 is one less code you have to debug. 193 00:07:49,500 --> 00:07:52,500 So this short cut I think is very useful. 194 00:07:52,500 --> 00:07:55,080 More importantly, because it's available 195 00:07:55,080 --> 00:07:57,970 you'll almost certainly come across it in production code 196 00:07:57,970 --> 00:08:00,060 that you may have to maintain. 197 00:08:00,060 --> 00:08:01,990 So you may decide that you'll always create a cursor 198 00:08:01,990 --> 00:08:04,360 because it's clearer, what's going on, 199 00:08:04,360 --> 00:08:05,950 but learning to programme isn't just about 200 00:08:05,950 --> 00:08:07,370 learning to write code, 201 00:08:07,370 --> 00:08:09,610 but also about learning to read it. 202 00:08:09,610 --> 00:08:11,510 Now it's often been said that code is read 203 00:08:11,510 --> 00:08:13,680 ten times as often as it's written. 204 00:08:13,680 --> 00:08:15,610 Now I'm not gonna comment on statistics like that, 205 00:08:15,610 --> 00:08:17,740 but I can confirm that I've read far more code 206 00:08:17,740 --> 00:08:18,910 than I've written, 207 00:08:18,910 --> 00:08:21,090 and a lot of the has been my own code. 208 00:08:21,090 --> 00:08:22,740 So in other words, I've written it once 209 00:08:22,740 --> 00:08:24,620 then read it many times afterwards, 210 00:08:24,620 --> 00:08:27,890 when maintaining the programmes and adding new features. 211 00:08:27,890 --> 00:08:30,680 Okay, so we can execute queries using the connexion 212 00:08:30,680 --> 00:08:31,780 as you saw here, 213 00:08:31,780 --> 00:08:34,490 rather than explicitly creating a cursor, 214 00:08:34,490 --> 00:08:35,909 and we saw that right at the start 215 00:08:35,909 --> 00:08:39,320 when we created a table and inserted new records into it. 216 00:08:39,320 --> 00:08:41,380 Now we could have created a cursor to execute 217 00:08:41,380 --> 00:08:43,640 the create table state with incidentally, 218 00:08:43,640 --> 00:08:46,410 but it probably doesn't make sense to do that. 219 00:08:46,410 --> 00:08:48,860 For certain update statements it can make sense 220 00:08:48,860 --> 00:08:49,940 to use a cursor, 221 00:08:49,940 --> 00:08:51,710 even though we won't be getting back any rows 222 00:08:51,710 --> 00:08:53,220 from the data base. 223 00:08:53,220 --> 00:08:55,950 And that's because cursors have a row count property, 224 00:08:55,950 --> 00:08:59,220 that we can use to check how many rows were affected 225 00:08:59,220 --> 00:09:01,680 by the previous sequel statement. 226 00:09:01,680 --> 00:09:03,300 So let's actually see how that works, 227 00:09:03,300 --> 00:09:07,790 by performing an update in this contacts2 pyscript. 228 00:09:07,790 --> 00:09:10,120 So what we're gonna do is make a change here now, 229 00:09:10,120 --> 00:09:11,560 and I'm gonna add an update, 230 00:09:11,560 --> 00:09:14,490 I'm gonna add this starting on line five, 231 00:09:14,490 --> 00:09:15,330 update 232 00:09:16,270 --> 00:09:17,330 underscore 233 00:09:17,330 --> 00:09:18,160 s-q-l 234 00:09:18,160 --> 00:09:19,520 equals 235 00:09:19,520 --> 00:09:20,350 update 236 00:09:21,960 --> 00:09:22,800 contacts 237 00:09:23,820 --> 00:09:24,780 set 238 00:09:24,780 --> 00:09:25,970 email 239 00:09:25,970 --> 00:09:26,950 equals, 240 00:09:26,950 --> 00:09:28,280 and a single quote because we've already got 241 00:09:28,280 --> 00:09:29,990 a double quoted string, 242 00:09:29,990 --> 00:09:31,490 update@update.com, 243 00:09:33,820 --> 00:09:35,100 single quote, 244 00:09:35,100 --> 00:09:36,350 where, contacts 245 00:09:37,980 --> 00:09:39,320 dot phone 246 00:09:39,320 --> 00:09:40,320 equals 1234. 247 00:09:41,270 --> 00:09:43,120 So hopefully you recognise the standard sequel code 248 00:09:43,120 --> 00:09:44,930 we're using now. 249 00:09:44,930 --> 00:09:46,630 Right then we want update 250 00:09:46,630 --> 00:09:49,960 underscore cursor is equal to db.cursor, 251 00:09:52,250 --> 00:09:53,670 and update_cursor 252 00:09:54,570 --> 00:09:55,570 dot execute, 253 00:09:56,990 --> 00:09:57,910 update sql, 254 00:09:59,170 --> 00:10:03,060 which is the sequel code that we want to execute. 255 00:10:03,060 --> 00:10:05,470 And then we'll print out print, 256 00:10:05,470 --> 00:10:07,460 double quote, curly braces 257 00:10:07,460 --> 00:10:10,880 left and right curly braces, rows updated 258 00:10:13,030 --> 00:10:14,290 dot format, 259 00:10:14,290 --> 00:10:15,790 then update_cursor 260 00:10:16,810 --> 00:10:18,020 dot 261 00:10:18,020 --> 00:10:18,850 rowcount, 262 00:10:21,390 --> 00:10:24,310 and then we wanna close the connexion so update_cursor, 263 00:10:24,310 --> 00:10:25,980 or the cursor I should say, I said connexion, 264 00:10:25,980 --> 00:10:27,640 update_cursor.close, 265 00:10:28,970 --> 00:10:31,620 and then we'll leave the rest of the code there as it was. 266 00:10:31,620 --> 00:10:33,550 So here I've stored the sequel statement 267 00:10:33,550 --> 00:10:36,150 in the string variable update underscore sequel, 268 00:10:36,150 --> 00:10:39,150 then created a cursor and used it to execute 269 00:10:39,150 --> 00:10:40,700 the sequel statement. 270 00:10:40,700 --> 00:10:42,390 Now because we've used a cursor, 271 00:10:42,390 --> 00:10:44,050 we can check the row count property 272 00:10:44,050 --> 00:10:46,270 to see how many rows were updated. 273 00:10:46,270 --> 00:10:48,520 So if we actually run this, 274 00:10:50,680 --> 00:10:53,740 we can see in the output one row's updated, 275 00:10:53,740 --> 00:10:55,970 and we can also see the record that was updated 276 00:10:55,970 --> 00:10:58,410 with the phone number of 1234, 277 00:10:58,410 --> 00:11:02,150 has now got a new email address, update@update.com. 278 00:11:02,150 --> 00:11:03,390 And just to confirm that, 279 00:11:03,390 --> 00:11:05,330 if I change the condition over here 280 00:11:05,330 --> 00:11:08,810 so that the phone number doesn't match any records on file, 281 00:11:08,810 --> 00:11:10,510 we should get no rows updated so, 282 00:11:10,510 --> 00:11:11,340 4567, 283 00:11:12,270 --> 00:11:13,520 run this again, 284 00:11:15,430 --> 00:11:17,900 zero rows updated as you can see. 285 00:11:17,900 --> 00:11:21,900 Alright so dropping the where clause altogether, 286 00:11:24,420 --> 00:11:27,110 just delete that actually, 287 00:11:27,110 --> 00:11:30,890 and running that should result in all rows being updated. 288 00:11:30,890 --> 00:11:32,590 Right so if I run that, 289 00:11:32,590 --> 00:11:34,390 or where they actually updated? 290 00:11:34,390 --> 00:11:35,370 Now this is actually a challenge 291 00:11:35,370 --> 00:11:37,790 that I'm setting for you now. 292 00:11:48,900 --> 00:11:50,300 Alright so that's the challenge again, 293 00:11:50,300 --> 00:11:51,940 create a new python file here, 294 00:11:51,940 --> 00:11:53,980 write the code to print out all the records 295 00:11:53,980 --> 00:11:56,550 in the contacts.sqlite data base, 296 00:11:56,550 --> 00:11:59,210 check the result and explain why 297 00:11:59,210 --> 00:12:03,010 the records aren't as we'd expect them to be. 298 00:12:03,010 --> 00:12:07,350 Pause the video, and I'll see you when you get back. 299 00:12:07,350 --> 00:12:08,460 Alright so how did you get on? 300 00:12:08,460 --> 00:12:11,470 Let's actually go through now with a solution 301 00:12:11,470 --> 00:12:12,340 to this challenge. 302 00:12:12,340 --> 00:12:14,750 I'm going to start by creating a new file, 303 00:12:14,750 --> 00:12:15,970 new python file, 304 00:12:15,970 --> 00:12:18,510 and we'll call this one 305 00:12:18,510 --> 00:12:19,340 checkdb. 306 00:12:23,520 --> 00:12:26,610 So I'll start with an import sqlite3. 307 00:12:28,880 --> 00:12:31,960 I'll do a conn = sqlite3.connect 308 00:12:34,380 --> 00:12:36,050 and contacts.sqlite, 309 00:12:36,910 --> 00:12:38,350 as we've done before. 310 00:12:38,350 --> 00:12:41,600 Then we put for, row, in, conn.execute, 311 00:12:43,400 --> 00:12:45,570 select star from contacts, 312 00:12:47,870 --> 00:12:48,700 print row, 313 00:12:50,960 --> 00:12:52,040 conn 314 00:12:52,040 --> 00:12:52,880 dot close. 315 00:12:53,870 --> 00:12:55,580 Now your solution might be different to mine 316 00:12:55,580 --> 00:12:56,840 you may have used a cursor, 317 00:12:56,840 --> 00:12:59,270 or unpacked the rows tuples to print out the results, 318 00:12:59,270 --> 00:13:00,310 for example, 319 00:13:00,310 --> 00:13:02,640 but when I right click this, 320 00:13:03,790 --> 00:13:07,560 and run it making sure I'm running checkdb, 321 00:13:07,560 --> 00:13:11,070 notice how we've got the old records back again. 322 00:13:11,070 --> 00:13:13,100 So each row's got a different email address, 323 00:13:13,100 --> 00:13:17,070 even though we ran the update in the contacts2 script, 324 00:13:17,070 --> 00:13:20,490 that was meant to actually have updated all the records 325 00:13:20,490 --> 00:13:21,700 in the data base. 326 00:13:21,700 --> 00:13:23,670 So in other words our previous update hasn't altered 327 00:13:23,670 --> 00:13:25,600 the rows in the table. 328 00:13:25,600 --> 00:13:27,700 Now the reason that the rows haven't been updated, 329 00:13:27,700 --> 00:13:30,650 is that there was no commit in the contacts2 programme, 330 00:13:30,650 --> 00:13:33,330 so when we closed the connexion the update was rolled back, 331 00:13:33,330 --> 00:13:35,030 and the table's unchanged. 332 00:13:35,030 --> 00:13:37,540 Now it's very easy to do that as I said, 333 00:13:37,540 --> 00:13:40,340 and it was especially easy in contacts2, 334 00:13:40,340 --> 00:13:42,060 and that's because we started off not needing 335 00:13:42,060 --> 00:13:43,270 to commit anything, 336 00:13:43,270 --> 00:13:45,940 when the programme only queried the data base. 337 00:13:45,940 --> 00:13:48,180 Now you could automatically call commit before close 338 00:13:48,180 --> 00:13:49,410 at the end of your programmes, 339 00:13:49,410 --> 00:13:52,000 but I wouldn't necessarily recommend that, 340 00:13:52,000 --> 00:13:54,000 and that's because the time to commit the updates 341 00:13:54,000 --> 00:13:55,960 is once you're happy with the changes, 342 00:13:55,960 --> 00:13:59,080 rather than leading it until you close the connexion. 343 00:13:59,080 --> 00:14:01,080 So go back to contacts2, 344 00:14:02,120 --> 00:14:04,020 we really should commit the changes after 345 00:14:04,020 --> 00:14:05,720 performing the update. 346 00:14:05,720 --> 00:14:08,600 Now something else I recommend is to use the cursor 347 00:14:08,600 --> 00:14:11,280 to commit the changes, rather than the connexion. 348 00:14:11,280 --> 00:14:14,510 Now cursor objects themselves don't have a commit method, 349 00:14:14,510 --> 00:14:16,740 but they do have a connexion property 350 00:14:16,740 --> 00:14:19,610 that we can use to get a reference to the connexion, 351 00:14:19,610 --> 00:14:22,030 that the cursor's using. 352 00:14:22,030 --> 00:14:24,220 So what we wanna do here is after the 353 00:14:24,220 --> 00:14:26,950 print that says how many rows are updated, 354 00:14:26,950 --> 00:14:28,580 this before the close, 355 00:14:28,580 --> 00:14:30,840 we should actually put update 356 00:14:30,840 --> 00:14:32,510 underscore cursor 357 00:14:32,510 --> 00:14:34,300 dot connexion 358 00:14:34,300 --> 00:14:35,430 dot 359 00:14:35,430 --> 00:14:36,270 commit. 360 00:14:37,170 --> 00:14:38,600 Like so. 361 00:14:38,600 --> 00:14:39,970 So you might be asking at this point, 362 00:14:39,970 --> 00:14:43,020 why is this better than calling db.commit? 363 00:14:43,020 --> 00:14:45,720 Well in this small programme you really could use either. 364 00:14:45,720 --> 00:14:47,950 The cursor connexions is db, 365 00:14:47,950 --> 00:14:49,630 so calling commit on either one 366 00:14:49,630 --> 00:14:51,700 will do exactly the same thing, 367 00:14:51,700 --> 00:14:54,920 and we can see all the code on the screen at the same time, 368 00:14:54,920 --> 00:14:57,110 but as your programmes get more complex, 369 00:14:57,110 --> 00:15:00,040 you'll start creating functions or classes 370 00:15:00,040 --> 00:15:02,470 to deal with things like updates and inserts, 371 00:15:02,470 --> 00:15:04,610 and when you start doing that, 372 00:15:04,610 --> 00:15:07,640 your functions may have a cursor object to work with 373 00:15:07,640 --> 00:15:10,840 but won't necessarily know about the programmes connexion. 374 00:15:10,840 --> 00:15:12,870 There's no need for them to know about the connexion, 375 00:15:12,870 --> 00:15:16,960 so it's tidier to use whatever connexion your cursor has. 376 00:15:16,960 --> 00:15:19,450 Now you might also create more than one connexion 377 00:15:19,450 --> 00:15:20,550 to the data base, 378 00:15:20,550 --> 00:15:23,030 and calling the commit method on the wrong connexion 379 00:15:23,030 --> 00:15:24,620 will introduce subtle bugs 380 00:15:24,620 --> 00:15:26,980 that may be really hard to find. 381 00:15:26,980 --> 00:15:28,420 The commit method doesn't give an error 382 00:15:28,420 --> 00:15:29,760 if there's nothing to commit, 383 00:15:29,760 --> 00:15:31,640 so calling it on the wrong connexion 384 00:15:31,640 --> 00:15:33,920 will be a really hard bug to spot. 385 00:15:33,920 --> 00:15:36,360 So for that reason I suggest using the cursors 386 00:15:36,360 --> 00:15:39,620 connexion property, to get a reference to the connexion 387 00:15:39,620 --> 00:15:42,770 that the cursor's using, and then call commit on that. 388 00:15:42,770 --> 00:15:44,630 So just to be clear we are dealing with 389 00:15:44,630 --> 00:15:46,820 exactly the same connexion here, 390 00:15:46,820 --> 00:15:49,670 so let's actually look at our complete code again. 391 00:15:49,670 --> 00:15:51,950 So you can see on line three, 392 00:15:51,950 --> 00:15:53,660 we create a connexion to the data base 393 00:15:53,660 --> 00:15:55,630 and we store it in db. 394 00:15:55,630 --> 00:16:00,070 We then use that same db, that connexion on line six 395 00:16:00,070 --> 00:16:01,930 to create the cursor, 396 00:16:01,930 --> 00:16:05,950 so the cursor is using the connexion stored in db. 397 00:16:05,950 --> 00:16:07,910 Now on line 10, 398 00:16:07,910 --> 00:16:11,720 when we refer to update_cursor.connection, 399 00:16:11,720 --> 00:16:13,270 that's exactly the same connexion 400 00:16:13,270 --> 00:16:15,870 that we stored in the variable db, 401 00:16:15,870 --> 00:16:18,760 and we can actually check that in fact, 402 00:16:18,760 --> 00:16:22,320 we could actually do that after the print here. 403 00:16:22,320 --> 00:16:24,740 We type in print, then print, 404 00:16:25,840 --> 00:16:27,920 are connexions the same, 405 00:16:31,750 --> 00:16:33,000 and dot format, 406 00:16:34,430 --> 00:16:35,730 update 407 00:16:35,730 --> 00:16:36,600 cursor 408 00:16:36,600 --> 00:16:38,270 dot connexion 409 00:16:38,270 --> 00:16:39,520 is equal to db. 410 00:16:41,980 --> 00:16:43,620 Then we do another print there. 411 00:16:43,620 --> 00:16:45,950 Let's just try running that. 412 00:16:47,500 --> 00:16:49,880 You can see, are connexions the same true. 413 00:16:49,880 --> 00:16:52,470 So if you make changes to your data base using a cursor, 414 00:16:52,470 --> 00:16:54,860 I really do suggest you call the commit method 415 00:16:54,860 --> 00:16:57,390 by using the cursors connexion property. 416 00:16:57,390 --> 00:16:58,990 That way your code won't have to care about 417 00:16:58,990 --> 00:17:00,290 which connexion to use, 418 00:17:00,290 --> 00:17:01,870 and you'll always get the correct one 419 00:17:01,870 --> 00:17:04,730 because it's using the cursors connexion property. 420 00:17:04,730 --> 00:17:07,430 Now of course, if you didn't use a cursor 421 00:17:07,430 --> 00:17:08,800 to perform the update, 422 00:17:08,800 --> 00:17:10,750 so if you've used the connexions execute method 423 00:17:10,750 --> 00:17:13,540 like we did in the previous contacts.py programme 424 00:17:13,540 --> 00:17:15,220 then you'd have to actually call commit 425 00:17:15,220 --> 00:17:17,060 using the connexion. 426 00:17:17,060 --> 00:17:19,480 Right so let's just run this. 427 00:17:20,800 --> 00:17:24,970 And if you go back to our checkdb again, and run that, 428 00:17:25,930 --> 00:17:27,490 we can verify that the records this time 429 00:17:27,490 --> 00:17:29,620 have actually been updated. 430 00:17:29,620 --> 00:17:30,700 Alright so there's a couple more things 431 00:17:30,700 --> 00:17:32,680 I wanna cover before we move on. 432 00:17:32,680 --> 00:17:34,910 One thing we need to look at is the email address 433 00:17:34,910 --> 00:17:39,490 that we've hard coded in the update sequel string. 434 00:17:39,490 --> 00:17:40,320 Over here. 435 00:17:41,210 --> 00:17:43,060 Now there's actually a better way of doing that. 436 00:17:43,060 --> 00:17:44,520 So we're gonna look at place holders 437 00:17:44,520 --> 00:17:47,640 and parameter substitution to handle things like this, 438 00:17:47,640 --> 00:17:49,840 and I've also talked about commit, 439 00:17:49,840 --> 00:17:52,290 but I haven't shown you what to do 440 00:17:52,290 --> 00:17:54,910 if you decide you shouldn't commit the transaction. 441 00:17:54,910 --> 00:17:56,560 So we're gonna be talking about 442 00:17:56,560 --> 00:17:59,020 and seeing how to roll back an update. 443 00:17:59,020 --> 00:18:00,790 So I'm gonna stop the video here, 444 00:18:00,790 --> 00:18:02,120 and in the next video we're gonna use 445 00:18:02,120 --> 00:18:05,270 parameter substitution in our sequel statements. 446 00:18:05,270 --> 00:18:07,530 After that we're going to leave databases briefly 447 00:18:07,530 --> 00:18:09,750 and have a look at exceptions in python. 448 00:18:09,750 --> 00:18:11,030 Once we know how to use exceptions 449 00:18:11,030 --> 00:18:13,120 to detect that something's gone wrong, 450 00:18:13,120 --> 00:18:16,300 we can see how to roll back our transactions. 451 00:18:16,300 --> 00:18:19,280 So I'll see you as always, in the next video.