1 00:00:04,840 --> 00:00:05,670 All right so it's time 2 00:00:05,670 --> 00:00:07,680 to start breaking some things. 3 00:00:07,680 --> 00:00:09,050 What we're gonna do first is start 4 00:00:09,050 --> 00:00:13,000 by deleting the accounts.sqlite in the project pane. 5 00:00:13,000 --> 00:00:14,480 On Windows, you'll get an error 6 00:00:14,480 --> 00:00:16,620 if you've got any tables open in the database viewer, 7 00:00:16,620 --> 00:00:19,700 but it's a good idea to close them on a MAC and Linux too. 8 00:00:19,700 --> 00:00:22,420 Let's just close those two down. 9 00:00:22,420 --> 00:00:24,330 Delete accounts.sqlite. 10 00:00:29,540 --> 00:00:31,490 We're just gonna run the rollback.pi again 11 00:00:31,490 --> 00:00:34,900 just to make sure that both tables contain 12 00:00:34,900 --> 00:00:36,800 predictable data after all the changes 13 00:00:36,800 --> 00:00:38,660 that we've been making. 14 00:00:38,660 --> 00:00:40,520 All right, you can see that's been created again there 15 00:00:40,520 --> 00:00:41,780 in the project pane. 16 00:00:41,780 --> 00:00:44,300 The history table has a composite key 17 00:00:44,300 --> 00:00:46,660 made up of the time and account columns. 18 00:00:46,660 --> 00:00:49,870 The combination of these two columns must be unique. 19 00:00:49,870 --> 00:00:51,450 An easy way to simulate an error here 20 00:00:51,450 --> 00:00:53,390 is to try and save a transaction 21 00:00:53,390 --> 00:00:55,690 with the same time and account 22 00:00:55,690 --> 00:00:57,700 as one that already exists. 23 00:00:57,700 --> 00:00:59,990 That's actually easily done and the way to do that 24 00:00:59,990 --> 00:01:03,400 would be to modify our current time method 25 00:01:03,400 --> 00:01:06,560 to return the same number each time. 26 00:01:06,560 --> 00:01:10,700 Let's start by deleting these two commented out lines. 27 00:01:10,700 --> 00:01:14,620 We'll comment out also this first line 28 00:01:14,620 --> 00:01:17,990 then what we'll do is we'll just return one. 29 00:01:17,990 --> 00:01:19,330 We're gonna run the programme again 30 00:01:19,330 --> 00:01:20,780 without clearing out any values. 31 00:01:20,780 --> 00:01:23,580 Now the current time's returning one in every case. 32 00:01:23,580 --> 00:01:27,180 If we run it, we actually get an error. 33 00:01:27,180 --> 00:01:29,030 When we try to get a new history row 34 00:01:29,030 --> 00:01:30,830 with the same time and account 35 00:01:30,830 --> 00:01:32,390 as a row that already exists, 36 00:01:32,390 --> 00:01:35,450 we get a Sql3 integrity error 37 00:01:35,450 --> 00:01:37,560 because the primary keys are composite made up of 38 00:01:37,560 --> 00:01:40,060 time and account and as we've established, 39 00:01:40,060 --> 00:01:42,660 primary keys must be unique. 40 00:01:42,660 --> 00:01:44,210 We have a look there, and we'll have a look at our data 41 00:01:44,210 --> 00:01:47,940 so we're going to open up to the tables again. 42 00:01:47,940 --> 00:01:50,130 Accounts and then History. 43 00:01:50,130 --> 00:01:51,960 We'll have a look and see what's happened here. 44 00:01:51,960 --> 00:01:53,980 First thing under accounts, 45 00:01:53,980 --> 00:01:55,690 that's probably not as bad as it could be. 46 00:01:55,690 --> 00:01:58,140 John's balance is now 2010 47 00:01:58,140 --> 00:01:59,580 and there's a corresponding entry 48 00:01:59,580 --> 00:02:00,910 if we have a look in history 49 00:02:00,910 --> 00:02:03,840 which should be of 1010 as you can see there. 50 00:02:03,840 --> 00:02:07,380 The database still tallys so that's a good thing. 51 00:02:07,380 --> 00:02:10,500 The reason for that is because by default, 52 00:02:10,500 --> 00:02:12,910 nothing gets persisted to the database 53 00:02:12,910 --> 00:02:14,990 until it's committed and the crash happened 54 00:02:14,990 --> 00:02:16,910 before the commit line. 55 00:02:16,910 --> 00:02:20,360 Before the commit line call which I think is on line 41. 56 00:02:20,360 --> 00:02:21,680 This one here. 57 00:02:21,680 --> 00:02:23,880 The crash happened before that. 58 00:02:23,880 --> 00:02:26,230 The new balance for the first deposit of 10 59 00:02:26,230 --> 00:02:28,600 wasn't actually written to the database. 60 00:02:28,600 --> 00:02:31,140 Of course our programme did crash 61 00:02:31,140 --> 00:02:32,510 and that's not a good thing. 62 00:02:32,510 --> 00:02:34,710 Let's see if we can avoid a crash. 63 00:02:34,710 --> 00:02:38,010 You're gonna wrap the database updates in a try block. 64 00:02:38,010 --> 00:02:41,240 We're gonna start here just before the db.execute. 65 00:02:41,240 --> 00:02:44,490 Try and we want to add those two lines. 66 00:02:45,960 --> 00:02:47,300 Indent them. 67 00:02:47,300 --> 00:02:49,630 Then we want to type except. 68 00:02:50,810 --> 00:02:53,480 It's going to be squlite3.Error. 69 00:02:54,870 --> 00:02:56,300 If we do get an error, 70 00:02:56,300 --> 00:02:58,630 we want to do a db.rollback. 71 00:03:00,700 --> 00:03:03,600 Otherwise, we're gonna put finally 72 00:03:03,600 --> 00:03:04,430 db.commit. 73 00:03:05,360 --> 00:03:06,490 Just space it out a little bit 74 00:03:06,490 --> 00:03:08,220 so we can see it a little bit easier. 75 00:03:08,220 --> 00:03:10,270 What we're doing here is we're protecting 76 00:03:10,270 --> 00:03:13,130 our database updates with a try block. 77 00:03:13,130 --> 00:03:14,870 If there's an error, we're using 78 00:03:14,870 --> 00:03:16,810 the connexions rollback method. 79 00:03:16,810 --> 00:03:18,570 This one here on line 44 80 00:03:18,570 --> 00:03:20,770 to rollback any updates that were pending 81 00:03:20,770 --> 00:03:22,220 or that are pending. 82 00:03:22,220 --> 00:03:23,980 Here we're getting an exception 83 00:03:23,980 --> 00:03:25,880 when we run this code, or we will be 84 00:03:25,880 --> 00:03:28,170 when we try to update the history table. 85 00:03:28,170 --> 00:03:31,550 What this ensures is that the changes to the accounts table 86 00:03:31,550 --> 00:03:34,050 which had already taken place on the previous slide, 87 00:03:34,050 --> 00:03:36,340 this update here on line 41, 88 00:03:36,340 --> 00:03:38,560 we ensure that they don't take place either. 89 00:03:38,560 --> 00:03:39,940 If there's no error, 90 00:03:39,940 --> 00:03:44,530 the finally block executes the db.commit is executed 91 00:03:44,530 --> 00:03:48,340 and both updates the accounts table and the history table 92 00:03:48,340 --> 00:03:49,540 are updated. 93 00:03:49,540 --> 00:03:51,560 I'm using the word updated to mean any change, 94 00:03:51,560 --> 00:03:53,420 even though one of the changes or the updates 95 00:03:53,420 --> 00:03:54,990 is an insert. 96 00:03:54,990 --> 00:03:57,030 Let's actually see how this behaves. 97 00:03:57,030 --> 00:03:59,200 I'm going to run it again. 98 00:04:01,580 --> 00:04:02,990 This time we haven't got an error. 99 00:04:02,990 --> 00:04:04,440 That's a good thing. 100 00:04:04,440 --> 00:04:06,930 The try is has obviously caught the error. 101 00:04:06,930 --> 00:04:11,010 If we have a look at the history table again, we refresh. 102 00:04:11,010 --> 00:04:13,440 On accounts we do a refresh as well. 103 00:04:13,440 --> 00:04:15,130 You can see nothing's been updated this time 104 00:04:15,130 --> 00:04:17,560 which is what we wanted because we've got an error 105 00:04:17,560 --> 00:04:20,120 with that transaction record in history 106 00:04:20,120 --> 00:04:22,029 because of the way that we modified 107 00:04:22,029 --> 00:04:25,050 our current time method to always return one. 108 00:04:25,050 --> 00:04:27,920 That's how you go about rolling back transactions 109 00:04:27,920 --> 00:04:29,850 when you need to make sure that a series of updates 110 00:04:29,850 --> 00:04:32,930 all take place and that the database itself 111 00:04:32,930 --> 00:04:36,430 isn't updated if one or more of the updates fails. 112 00:04:36,430 --> 00:04:38,490 There's still a problem here though. 113 00:04:38,490 --> 00:04:40,980 Also I can almost hear you asking 114 00:04:40,980 --> 00:04:43,240 why do we have to roll back the transactions 115 00:04:43,240 --> 00:04:45,880 if commit isn't being called. 116 00:04:45,880 --> 00:04:48,250 I'll deal with the problem first. 117 00:04:48,250 --> 00:04:51,510 Looking at the accounts table, 118 00:04:51,510 --> 00:04:53,670 notice John's balance. 119 00:04:53,670 --> 00:04:55,570 However when I come down here and have a look at his balance 120 00:04:55,570 --> 00:04:57,720 it's showing $30.10. 121 00:04:57,720 --> 00:05:01,120 We've got 2010 in the accounts table 122 00:05:01,120 --> 00:05:04,200 and $30.10 showing in the run window. 123 00:05:05,100 --> 00:05:07,220 Looking at our code, 124 00:05:07,220 --> 00:05:08,900 you can see one of the problems that we've got here 125 00:05:08,900 --> 00:05:11,780 is that the balance attribute, 126 00:05:11,780 --> 00:05:13,570 it's the last line here on line 48, 127 00:05:13,570 --> 00:05:15,000 that's updated whether the data 128 00:05:15,000 --> 00:05:17,500 is saved successfully or not. 129 00:05:17,500 --> 00:05:20,230 We can't actually put that line into the finally block 130 00:05:20,230 --> 00:05:22,000 because finally executes whether 131 00:05:22,000 --> 00:05:23,690 there's an exception or not. 132 00:05:23,690 --> 00:05:27,100 This is a great time to use the else clause. 133 00:05:27,100 --> 00:05:28,190 What we really should do there 134 00:05:28,190 --> 00:05:30,460 is change this to put except 135 00:05:30,460 --> 00:05:32,870 and after the rollback, 136 00:05:32,870 --> 00:05:36,780 you do an else and that's where we put this code, 137 00:05:36,780 --> 00:05:38,440 the updated balance. 138 00:05:43,940 --> 00:05:47,200 Else has to go after all exception clauses 139 00:05:47,200 --> 00:05:50,260 but it has to go before the finally clause. 140 00:05:50,260 --> 00:05:53,650 Let's just try running that again. 141 00:05:53,650 --> 00:05:56,150 Hopefully we've got it working correctly now. 142 00:05:56,150 --> 00:05:59,260 We've now got the correct balance showing there, $20.10. 143 00:05:59,260 --> 00:06:02,750 Of course that equates to the accounts balance here 144 00:06:02,750 --> 00:06:05,300 of 2010 which of course is an integer. 145 00:06:05,300 --> 00:06:07,600 Dividing by 100, we get the $20.10 here. 146 00:06:07,600 --> 00:06:11,760 That's now showing itself correctly which is good. 147 00:06:11,760 --> 00:06:13,600 In fact, looking at this code, 148 00:06:13,600 --> 00:06:18,160 really the db.commit should also go in the else block. 149 00:06:18,160 --> 00:06:20,770 We got away with putting it in the finally block 150 00:06:20,770 --> 00:06:23,450 because the transaction will have already been rolled back 151 00:06:23,450 --> 00:06:25,160 when there's an error and calling commit 152 00:06:25,160 --> 00:06:26,660 when there's no transactions to commit 153 00:06:26,660 --> 00:06:29,150 doesn't actually do anything. 154 00:06:29,150 --> 00:06:31,480 I started off writing it with a finally block 155 00:06:31,480 --> 00:06:34,840 because try and finally are often talked about together 156 00:06:34,840 --> 00:06:38,150 and you'll hear programmers referring to try finally blocks. 157 00:06:38,150 --> 00:06:39,390 Doing it that way is something 158 00:06:39,390 --> 00:06:41,250 that people will often do at first, 159 00:06:41,250 --> 00:06:42,880 especially if they're used to other languages 160 00:06:42,880 --> 00:06:43,960 such as Java. 161 00:06:44,920 --> 00:06:47,610 If I don't do that, another bad habit if you like 162 00:06:47,610 --> 00:06:51,110 is to update the balance attribute inside the try block. 163 00:06:51,110 --> 00:06:53,200 Something like this. 164 00:06:53,200 --> 00:06:56,300 I'll just copy this temporarily. 165 00:06:56,300 --> 00:06:57,490 Something putting it in there. 166 00:06:57,490 --> 00:07:00,710 That would be some code you'd probably see. 167 00:07:00,710 --> 00:07:04,080 That would work fine, but if there's an exception raised 168 00:07:04,080 --> 00:07:06,760 by either of the database update statements, 169 00:07:06,760 --> 00:07:09,730 the code to update the balance won't be reached. 170 00:07:09,730 --> 00:07:11,340 It's not wrong as such, 171 00:07:11,340 --> 00:07:14,160 but really you should limit the code inside a try block 172 00:07:14,160 --> 00:07:17,410 to just the code you want to protect. 173 00:07:17,410 --> 00:07:20,700 This is an excellent demonstration of why Python 174 00:07:20,700 --> 00:07:22,510 provides an else clause. 175 00:07:22,510 --> 00:07:24,960 If the code to update the balance crashes, 176 00:07:24,960 --> 00:07:27,100 the database updates will be rolled back. 177 00:07:27,100 --> 00:07:29,520 Even though they worked, they would leave the database 178 00:07:29,520 --> 00:07:32,050 in a perfectly consistent state. 179 00:07:32,050 --> 00:07:34,080 I know that the assignment to the balance attribute 180 00:07:34,080 --> 00:07:35,550 didn't crash the programme, 181 00:07:35,550 --> 00:07:37,730 but it could be a function to call or some other code 182 00:07:37,730 --> 00:07:40,080 that might crash and consequently it really does 183 00:07:40,080 --> 00:07:41,970 belong in the else clause. 184 00:07:41,970 --> 00:07:44,770 I'm gonna undo that change. 185 00:07:44,770 --> 00:07:47,400 We'll now get rid of the finally clause 186 00:07:47,400 --> 00:07:51,340 and perform the commit in the else instead. 187 00:07:51,340 --> 00:07:52,690 Like so. 188 00:07:52,690 --> 00:07:54,000 In fact, to be consistent we should really 189 00:07:54,000 --> 00:07:55,380 swap those lines around. 190 00:07:55,380 --> 00:07:57,960 We should put the commit before 191 00:07:58,820 --> 00:08:01,240 the attribute balance update. 192 00:08:01,240 --> 00:08:02,700 That's because if commit fails, 193 00:08:02,700 --> 00:08:05,480 we don't want to store the new location in the attribute. 194 00:08:05,480 --> 00:08:07,850 That brings us to the question now, 195 00:08:07,850 --> 00:08:09,690 why do we have to roll back the transactions 196 00:08:09,690 --> 00:08:12,100 if commit isn't being called? 197 00:08:12,100 --> 00:08:14,370 Let's just update accounts table, 198 00:08:14,370 --> 00:08:16,340 or refresh it rather. 199 00:08:16,340 --> 00:08:20,080 Make a note of John's balance here which is 2010. 200 00:08:20,080 --> 00:08:23,270 I'm gonna delete the last row, TerryG 201 00:08:23,270 --> 00:08:26,070 and update the database. 202 00:08:26,070 --> 00:08:29,730 I'm gonna go back and run our programme again. 203 00:08:31,940 --> 00:08:34,240 Come back and have a look at accounts. 204 00:08:34,240 --> 00:08:37,030 We should be able to see that TerryG's record has been added 205 00:08:37,030 --> 00:08:38,630 and you can see that it was there. 206 00:08:38,630 --> 00:08:40,770 Let's see how that works if we comment out 207 00:08:40,770 --> 00:08:42,850 the rollback method call. 208 00:08:43,750 --> 00:08:46,870 I'm just going to comment that out. 209 00:08:46,870 --> 00:08:50,470 We'll need something else in the except clause 210 00:08:50,470 --> 00:08:51,880 so I'm just going to add a pass for now 211 00:08:51,880 --> 00:08:54,390 just to keep the compiler happy. 212 00:08:54,390 --> 00:08:56,060 If I run this again, 213 00:08:57,270 --> 00:09:00,600 come back to accounts and do a refresh. 214 00:09:00,600 --> 00:09:04,270 I'm going to delete the TerryG record again, 215 00:09:05,330 --> 00:09:07,630 update it, back to rollback.pi. 216 00:09:07,630 --> 00:09:09,460 Going to run it again. 217 00:09:10,390 --> 00:09:13,990 Come back to accounts and refresh. 218 00:09:13,990 --> 00:09:15,630 You can see what's happened here. 219 00:09:15,630 --> 00:09:17,800 We've actually got an incorrect balance now. 220 00:09:17,800 --> 00:09:19,860 The balance is now showing as 1980 221 00:09:19,860 --> 00:09:22,040 which is 30 less than it should be. 222 00:09:22,040 --> 00:09:24,800 Obviously we do need to roll back those transactions 223 00:09:24,800 --> 00:09:26,900 to make sure that doesn't happen. 224 00:09:26,900 --> 00:09:29,130 Why? 225 00:09:29,130 --> 00:09:30,340 Why, why, why? 226 00:09:30,340 --> 00:09:32,490 You've probably noticed that I don't ask questions like this 227 00:09:32,490 --> 00:09:34,420 unless I already know the answer. 228 00:09:34,420 --> 00:09:36,140 This time I'm going to break with tradition 229 00:09:36,140 --> 00:09:39,430 and not tell you, not just yet anyway. 230 00:09:39,430 --> 00:09:42,430 It's time right now for a challenge. 231 00:09:49,320 --> 00:09:51,410 What happened and why is the balance showing 232 00:09:51,410 --> 00:09:53,920 as 30 less than it should be? 233 00:09:53,920 --> 00:09:55,540 A good approach to solving this challenge 234 00:09:55,540 --> 00:09:57,170 is to read through the code 235 00:09:57,170 --> 00:09:59,440 and take the place of the computer. 236 00:09:59,440 --> 00:10:02,130 Quote unquote, execute each statement, 237 00:10:02,130 --> 00:10:05,100 and work out what's happening at each point. 238 00:10:05,100 --> 00:10:07,800 These days, there are debuggers that will do it for you. 239 00:10:07,800 --> 00:10:10,770 Each line is executed, and you can examine the contents 240 00:10:10,770 --> 00:10:13,000 of all the variables to check the state 241 00:10:13,000 --> 00:10:15,330 of the programme at each step. 242 00:10:17,460 --> 00:10:18,990 Debuggers can be very useful 243 00:10:18,990 --> 00:10:21,140 for tracking down really obscure bugs, 244 00:10:21,140 --> 00:10:24,020 but using them can be very time consuming. 245 00:10:24,020 --> 00:10:25,210 They can come in very handy 246 00:10:25,210 --> 00:10:26,760 for things like loops that go round 247 00:10:26,760 --> 00:10:28,410 hundreds or thousands of times, 248 00:10:28,410 --> 00:10:30,850 because doing that manually is just impractical. 249 00:10:30,850 --> 00:10:34,060 Like most tools, you'll make far better use of them 250 00:10:34,060 --> 00:10:35,840 if you understand the manual process 251 00:10:35,840 --> 00:10:37,720 that's being automated. 252 00:10:37,720 --> 00:10:40,060 Reading code and understanding what's happening at each step 253 00:10:40,060 --> 00:10:41,440 is invaluable. 254 00:10:41,440 --> 00:10:43,940 The more you do it, frankly the better and faster 255 00:10:43,940 --> 00:10:45,100 you get at it. 256 00:10:48,210 --> 00:10:50,720 Give it a go and remember that our insert statement 257 00:10:50,720 --> 00:10:52,800 will always raise an exception 258 00:10:52,800 --> 00:10:55,850 because the primary key already exists. 259 00:10:55,850 --> 00:10:57,040 If you run the programme again, 260 00:10:57,040 --> 00:10:59,320 remember to delete TerryG's record 261 00:10:59,320 --> 00:11:02,110 in the accounts table before running the programme each time. 262 00:11:02,110 --> 00:11:04,890 The problem doesn't occur if all the records 263 00:11:04,890 --> 00:11:07,170 already exist in the database. 264 00:11:07,170 --> 00:11:08,410 See if you can solve this challenge 265 00:11:08,410 --> 00:11:10,810 and figure out why the account balance is 30 less 266 00:11:10,810 --> 00:11:12,000 than what it should be. 267 00:11:12,000 --> 00:11:16,770 Pause the video, I'll see you when you get back. 268 00:11:16,770 --> 00:11:17,670 How did you get on? 269 00:11:17,670 --> 00:11:19,700 Did you manage to figure that out? 270 00:11:19,700 --> 00:11:21,050 Firstly, you should hopefully have noticed 271 00:11:21,050 --> 00:11:25,220 that John's balance attribute never actually gets updated. 272 00:11:26,470 --> 00:11:28,080 That's because the else clause 273 00:11:28,080 --> 00:11:30,200 doesn't get a chance to execute. 274 00:11:30,200 --> 00:11:34,780 In other words, every time the save update method is called, 275 00:11:34,780 --> 00:11:36,950 the balance is always the same. 276 00:11:36,950 --> 00:11:39,450 The next thing to note is that the command 277 00:11:39,450 --> 00:11:42,860 to update the balance column of the accounts table 278 00:11:42,860 --> 00:11:46,930 executes each time we make a deposit or withdrawal. 279 00:11:46,930 --> 00:11:50,900 Basically Terry, Graham, Eric and Michael's account balances 280 00:11:50,900 --> 00:11:55,140 are also retrieved into accounts class instances. 281 00:11:55,140 --> 00:11:59,300 The balance attribute starts off at 2010 and doesn't change. 282 00:12:00,210 --> 00:12:03,580 When this first deposit here on line 82 is attempted, 283 00:12:03,580 --> 00:12:06,330 the new balance becomes 3020 284 00:12:06,330 --> 00:12:09,030 and this is written to the accounts table. 285 00:12:09,030 --> 00:12:10,200 The update isn't committed, 286 00:12:10,200 --> 00:12:12,640 but the transaction is rolled back 287 00:12:12,640 --> 00:12:14,790 so that update is still pending. 288 00:12:14,790 --> 00:12:16,880 The code raises an exception 289 00:12:16,880 --> 00:12:19,330 so we quit the save and the update method 290 00:12:19,330 --> 00:12:22,660 and move on to the deposit of 10 cents on line 83. 291 00:12:22,660 --> 00:12:24,250 The balance attribute at this point 292 00:12:24,250 --> 00:12:28,940 is still 2010 so the new balance becomes 2010 plus 10 293 00:12:28,940 --> 00:12:32,010 which is 2020 and that's written to the accounts table, 294 00:12:32,010 --> 00:12:33,630 but not committed. 295 00:12:33,630 --> 00:12:36,000 At this point, there's now two pending updates 296 00:12:36,000 --> 00:12:37,510 in the accounts table. 297 00:12:37,510 --> 00:12:40,710 When the code continues execution on line 84, 298 00:12:40,710 --> 00:12:42,890 it attempts the second deposit of 10 cents, 299 00:12:42,890 --> 00:12:44,560 it has the same result. 300 00:12:44,560 --> 00:12:46,260 There's now three pending updates, 301 00:12:46,260 --> 00:12:48,810 each one will replace the balance columns value 302 00:12:48,810 --> 00:12:51,790 that would have been stored by the previous update. 303 00:12:51,790 --> 00:12:54,750 Next the code continues onto line 85, 304 00:12:54,750 --> 00:12:58,210 attempts to withdrawal 30 cents from the balance of 2010. 305 00:12:58,210 --> 00:13:02,210 That also fails, but now there's a fourth pending update 306 00:13:02,210 --> 00:13:03,820 of the balance column. 307 00:13:03,820 --> 00:13:07,270 Then the next few lines are largely irrelevant. 308 00:13:07,270 --> 00:13:10,140 We're attempting, each one retrieves the details 309 00:13:10,140 --> 00:13:12,310 from the database so we've got 310 00:13:12,310 --> 00:13:15,560 Terry, Graham, Eric, and Michael's code 311 00:13:16,780 --> 00:13:17,980 that is retrieving the data 312 00:13:17,980 --> 00:13:19,680 from the account class instances. 313 00:13:19,680 --> 00:13:21,830 They have no effect on the state of the database 314 00:13:21,830 --> 00:13:24,160 nor on John's account instance 315 00:13:24,160 --> 00:13:25,860 so we can safely ignore them. 316 00:13:25,860 --> 00:13:27,470 That brings us now to TerryG, 317 00:13:27,470 --> 00:13:29,860 remembering that we deleted that entry 318 00:13:29,860 --> 00:13:31,650 from the accounts table. 319 00:13:31,650 --> 00:13:32,910 The code in the inept method 320 00:13:32,910 --> 00:13:35,400 is consequently called for this particular entry. 321 00:13:35,400 --> 00:13:38,060 It inserts a new row into the accounts table 322 00:13:38,060 --> 00:13:39,910 and, this is the important bit, 323 00:13:39,910 --> 00:13:42,470 commits all pending transactions 324 00:13:42,470 --> 00:13:46,860 by calling the cursor.connection.commit method. 325 00:13:46,860 --> 00:13:49,150 As well as storing TerryG's details, 326 00:13:49,150 --> 00:13:51,570 our four pending updates at that point 327 00:13:51,570 --> 00:13:52,910 are also committed. 328 00:13:52,910 --> 00:13:55,070 Each value is saved in the accounts table 329 00:13:55,070 --> 00:13:58,500 and is then overwritten by the next pending transaction. 330 00:13:58,500 --> 00:14:00,160 Consequently we end up with a balance 331 00:14:00,160 --> 00:14:03,330 from the 30 cent withdrawal being the final one stored. 332 00:14:03,330 --> 00:14:06,550 That's why the balance is 30 less than it should be. 333 00:14:06,550 --> 00:14:07,580 It may not have been obvious, 334 00:14:07,580 --> 00:14:10,570 but unless you roll back a database transaction, 335 00:14:10,570 --> 00:14:13,860 it's still in there waiting to be committed. 336 00:14:13,860 --> 00:14:15,680 Unless you knew exactly where to look, 337 00:14:15,680 --> 00:14:17,550 using a debugger to detect that problem 338 00:14:17,550 --> 00:14:19,950 would have been far from easy. 339 00:14:19,950 --> 00:14:21,830 That's because the database wasn't updated 340 00:14:21,830 --> 00:14:23,360 until right at the end 341 00:14:23,360 --> 00:14:26,050 so checking the contents of the accounts table 342 00:14:26,050 --> 00:14:29,080 each time around wouldn't have revealed anything useful. 343 00:14:29,080 --> 00:14:31,720 I definitely recommend developing the ability 344 00:14:31,720 --> 00:14:33,710 to step through code manually. 345 00:14:33,710 --> 00:14:35,930 You can make that easier by adding print statements 346 00:14:35,930 --> 00:14:37,970 to print out the values of variables 347 00:14:37,970 --> 00:14:39,770 at various points in the code, 348 00:14:39,770 --> 00:14:42,710 and you'll find detecting and fixing bugs much easier 349 00:14:42,710 --> 00:14:45,630 when you can read code and step through it like this. 350 00:14:45,630 --> 00:14:47,450 It does take time to get the hang of it, 351 00:14:47,450 --> 00:14:51,210 but the more you practise, the better you get at it. 352 00:14:51,210 --> 00:14:54,000 Strange as it may sound, manually stepping through the code 353 00:14:54,000 --> 00:14:57,770 is actually often far quicker than using a debugger. 354 00:14:57,770 --> 00:14:59,430 Before I finish this video, 355 00:14:59,430 --> 00:15:02,050 we better put that rollback back in. 356 00:15:02,050 --> 00:15:03,320 I'm gonna come back up here 357 00:15:03,320 --> 00:15:04,680 to our save method, 358 00:15:04,680 --> 00:15:07,270 our save and else or update method 359 00:15:07,270 --> 00:15:10,610 and we're just going to uncomment the rollback 360 00:15:10,610 --> 00:15:13,680 and delete the pass we no longer need. 361 00:15:13,680 --> 00:15:15,160 Of course the other thing we want to do 362 00:15:15,160 --> 00:15:18,160 is make sure we remove the code here 363 00:15:19,180 --> 00:15:20,810 so that our current time method 364 00:15:20,810 --> 00:15:24,060 is actually returning a valid UTC time. 365 00:15:25,200 --> 00:15:29,460 Let's go back and delete the TerryG record again. 366 00:15:29,460 --> 00:15:30,290 Update it. 367 00:15:32,480 --> 00:15:34,650 Run our programme again. 368 00:15:34,650 --> 00:15:38,510 Refresh and we've got it doing the right thing. 369 00:15:38,510 --> 00:15:39,930 Obviously it's a little bit weird there, 370 00:15:39,930 --> 00:15:41,860 but it did actually correctly add 1000 371 00:15:41,860 --> 00:15:43,810 to the 2980 existing balance. 372 00:15:43,810 --> 00:15:45,550 If you really wanted to be sure about this, 373 00:15:45,550 --> 00:15:48,360 we could just go back to ensure it's working 374 00:15:48,360 --> 00:15:50,250 as it's been working previously. 375 00:15:50,250 --> 00:15:51,530 Delete all the entries. 376 00:15:51,530 --> 00:15:53,580 Same for the history. 377 00:15:53,580 --> 00:15:56,580 Delete all those, commit the change. 378 00:15:58,610 --> 00:16:00,190 Run the code again. 379 00:16:02,280 --> 00:16:04,300 Come back to accounts, refresh. 380 00:16:04,300 --> 00:16:05,980 It's looking like it was. 381 00:16:05,980 --> 00:16:07,070 Do a refresh. 382 00:16:08,310 --> 00:16:10,140 I didn't delete those. 383 00:16:11,220 --> 00:16:12,960 Let's do the right thing this time. 384 00:16:12,960 --> 00:16:14,270 Delete them this time 385 00:16:14,270 --> 00:16:17,520 and just to be sure I'll delete the accounts 386 00:16:17,520 --> 00:16:18,860 and our table entries. 387 00:16:18,860 --> 00:16:20,780 We updated both of those. 388 00:16:20,780 --> 00:16:22,230 This is what I was trying to do last time. 389 00:16:22,230 --> 00:16:23,070 Run it. 390 00:16:24,850 --> 00:16:26,010 History, refresh. 391 00:16:26,010 --> 00:16:28,360 We're now going to have four entries which is correct. 392 00:16:28,360 --> 00:16:31,820 If you refresh that, we've got our correct balances. 393 00:16:31,820 --> 00:16:32,660 All right? 394 00:16:32,660 --> 00:16:33,490 That's that. 395 00:16:33,490 --> 00:16:34,320 I hope you got a lot out of that. 396 00:16:34,320 --> 00:16:35,780 I'll see you in the next video.