WEBVTT 1 00:00:01.870 --> 00:00:02.703 All right so it's time 2 00:00:02.703 --> 00:00:04.713 to start breaking some things. 3 00:00:04.713 --> 00:00:06.087 What we're gonna do first is start 4 00:00:06.087 --> 00:00:10.035 by deleting the accounts.sqlite in the project pane. 5 00:00:10.035 --> 00:00:11.513 On Windows, you'll get an error 6 00:00:11.513 --> 00:00:13.650 if you've got any tables open in the database viewer, 7 00:00:13.650 --> 00:00:16.737 but it's a good idea to close them on a MAC and Linux too. 8 00:00:16.737 --> 00:00:19.450 Let's just close those two down. 9 00:00:19.450 --> 00:00:21.367 Delete accounts.sqlite. 10 00:00:26.575 --> 00:00:28.521 We're just gonna run the rollback.pi again 11 00:00:28.521 --> 00:00:31.937 just to make sure that both tables contain 12 00:00:31.937 --> 00:00:33.837 predictable data after all the changes 13 00:00:33.837 --> 00:00:35.695 that we've been making. 14 00:00:35.695 --> 00:00:37.550 All right, you can see that's been created again there 15 00:00:37.550 --> 00:00:38.811 in the project pane. 16 00:00:38.811 --> 00:00:41.334 The history table has a composite key 17 00:00:41.334 --> 00:00:43.694 made up of the time and account columns. 18 00:00:43.694 --> 00:00:46.903 The combination of these two columns must be unique. 19 00:00:46.903 --> 00:00:48.482 An easy way to simulate an error here 20 00:00:48.482 --> 00:00:50.422 is to try and save a transaction 21 00:00:50.422 --> 00:00:52.722 with the same time and account 22 00:00:52.722 --> 00:00:54.735 as one that already exists. 23 00:00:54.735 --> 00:00:57.022 That's actually easily done and the way to do that 24 00:00:57.022 --> 00:01:00.432 would be to modify our current time method 25 00:01:00.432 --> 00:01:03.591 to return the same number each time. 26 00:01:03.591 --> 00:01:07.739 Let's start by deleting these two commented out lines. 27 00:01:07.739 --> 00:01:11.651 We'll comment out also this first line 28 00:01:11.651 --> 00:01:15.028 then what we'll do is we'll just return one. 29 00:01:15.028 --> 00:01:16.363 We're gonna run the programme again 30 00:01:16.363 --> 00:01:17.816 without clearing out any values. 31 00:01:17.816 --> 00:01:20.616 Now the current time's returning one in every case. 32 00:01:20.616 --> 00:01:24.216 If we run it, we actually get an error. 33 00:01:24.216 --> 00:01:26.066 When we try to get a new history row 34 00:01:26.066 --> 00:01:27.866 with the same time and account 35 00:01:27.866 --> 00:01:29.423 as a row that already exists, 36 00:01:29.423 --> 00:01:32.488 we get a Sql3 integrity error 37 00:01:32.488 --> 00:01:34.599 because the primary keys are composite made up of 38 00:01:34.599 --> 00:01:37.099 time and account and as we've established, 39 00:01:37.099 --> 00:01:39.699 primary keys must be unique. 40 00:01:39.699 --> 00:01:41.249 We have a look there, and we'll have a look at our data 41 00:01:41.249 --> 00:01:44.971 so we're going to open up to the tables again. 42 00:01:44.971 --> 00:01:47.162 Accounts and then History. 43 00:01:47.162 --> 00:01:48.999 We'll have a look and see what's happened here. 44 00:01:48.999 --> 00:01:51.012 First thing under accounts, 45 00:01:51.012 --> 00:01:52.720 that's probably not as bad as it could be. 46 00:01:52.720 --> 00:01:55.172 John's balance is now 2010 47 00:01:55.172 --> 00:01:56.612 and there's a corresponding entry 48 00:01:56.612 --> 00:01:57.949 if we have a look in history 49 00:01:57.949 --> 00:02:00.872 which should be of 1010 as you can see there. 50 00:02:00.872 --> 00:02:04.412 The database still tallys so that's a good thing. 51 00:02:04.412 --> 00:02:07.533 The reason for that is because by default, 52 00:02:07.533 --> 00:02:09.947 nothing gets persisted to the database 53 00:02:09.947 --> 00:02:12.022 until it's committed and the crash happened 54 00:02:12.022 --> 00:02:13.949 before the commit line. 55 00:02:13.949 --> 00:02:17.399 Before the commit line call which I think is on line 41. 56 00:02:17.399 --> 00:02:18.712 This one here. 57 00:02:18.712 --> 00:02:20.912 The crash happened before that. 58 00:02:20.912 --> 00:02:23.262 The new balance for the first deposit of 10 59 00:02:23.262 --> 00:02:25.636 wasn't actually written to the database. 60 00:02:25.636 --> 00:02:28.174 Of course our programme did crash 61 00:02:28.174 --> 00:02:29.549 and that's not a good thing. 62 00:02:29.549 --> 00:02:31.749 Let's see if we can avoid a crash. 63 00:02:31.749 --> 00:02:35.049 You're gonna wrap the database updates in a try block. 64 00:02:35.049 --> 00:02:38.272 We're gonna start here just before the db.execute. 65 00:02:38.272 --> 00:02:41.522 Try and we want to add those two lines. 66 00:02:42.995 --> 00:02:44.333 Indent them. 67 00:02:44.333 --> 00:02:46.666 Then we want to type except. 68 00:02:47.849 --> 00:02:50.516 It's going to be squlite3.Error. 69 00:02:51.908 --> 00:02:53.336 If we do get an error, 70 00:02:53.336 --> 00:02:55.669 we want to do a db.rollback. 71 00:02:57.730 --> 00:03:00.635 Otherwise, we're gonna put finally 72 00:03:00.635 --> 00:03:01.468 db.commit. 73 00:03:02.399 --> 00:03:03.523 Just space it out a little bit 74 00:03:03.523 --> 00:03:05.255 so we can see it a little bit easier. 75 00:03:05.255 --> 00:03:07.302 What we're doing here is we're protecting 76 00:03:07.302 --> 00:03:10.168 our database updates with a try block. 77 00:03:10.168 --> 00:03:11.905 If there's an error, we're using 78 00:03:11.905 --> 00:03:13.843 the connexions rollback method. 79 00:03:13.843 --> 00:03:15.605 This one here on line 44 80 00:03:15.605 --> 00:03:17.805 to rollback any updates that were pending 81 00:03:17.805 --> 00:03:19.255 or that are pending. 82 00:03:19.255 --> 00:03:21.018 Here we're getting an exception 83 00:03:21.018 --> 00:03:22.917 when we run this code, or we will be 84 00:03:22.917 --> 00:03:25.205 when we try to update the history table. 85 00:03:25.205 --> 00:03:28.581 What this ensures is that the changes to the accounts table 86 00:03:28.581 --> 00:03:31.081 which had already taken place on the previous slide, 87 00:03:31.081 --> 00:03:33.378 this update here on line 41, 88 00:03:33.378 --> 00:03:35.591 we ensure that they don't take place either. 89 00:03:35.591 --> 00:03:36.975 If there's no error, 90 00:03:36.975 --> 00:03:41.565 the finally block executes the db.commit is executed 91 00:03:41.565 --> 00:03:45.378 and both updates the accounts table and the history table 92 00:03:45.378 --> 00:03:46.578 are updated. 93 00:03:46.578 --> 00:03:48.591 I'm using the word updated to mean any change, 94 00:03:48.591 --> 00:03:50.455 even though one of the changes or the updates 95 00:03:50.455 --> 00:03:52.026 is an insert. 96 00:03:52.026 --> 00:03:54.065 Let's actually see how this behaves. 97 00:03:54.065 --> 00:03:56.232 I'm going to run it again. 98 00:03:58.615 --> 00:04:00.027 This time we haven't got an error. 99 00:04:00.027 --> 00:04:01.478 That's a good thing. 100 00:04:01.478 --> 00:04:03.965 The try is has obviously caught the error. 101 00:04:03.965 --> 00:04:08.041 If we have a look at the history table again, we refresh. 102 00:04:08.041 --> 00:04:10.478 On accounts we do a refresh as well. 103 00:04:10.478 --> 00:04:12.165 You can see nothing's been updated this time 104 00:04:12.165 --> 00:04:14.591 which is what we wanted because we've got an error 105 00:04:14.591 --> 00:04:17.155 with that transaction record in history 106 00:04:17.155 --> 00:04:19.066 because of the way that we modified 107 00:04:19.066 --> 00:04:22.087 our current time method to always return one. 108 00:04:22.087 --> 00:04:24.952 That's how you go about rolling back transactions 109 00:04:24.952 --> 00:04:26.888 when you need to make sure that a series of updates 110 00:04:26.888 --> 00:04:29.962 all take place and that the database itself 111 00:04:29.962 --> 00:04:33.462 isn't updated if one or more of the updates fails. 112 00:04:33.462 --> 00:04:35.525 There's still a problem here though. 113 00:04:35.525 --> 00:04:38.012 Also I can almost hear you asking 114 00:04:38.012 --> 00:04:40.274 why do we have to roll back the transactions 115 00:04:40.274 --> 00:04:42.912 if commit isn't being called. 116 00:04:42.912 --> 00:04:45.288 I'll deal with the problem first. 117 00:04:45.288 --> 00:04:48.540 Looking at the accounts table, 118 00:04:48.540 --> 00:04:50.702 notice John's balance. 119 00:04:50.702 --> 00:04:52.609 However when I come down here and have a look at his balance 120 00:04:52.609 --> 00:04:54.752 it's showing $30.10. 121 00:04:54.752 --> 00:04:58.150 We've got 2010 in the accounts table 122 00:04:58.150 --> 00:05:01.233 and $30.10 showing in the run window. 123 00:05:02.138 --> 00:05:04.251 Looking at our code, 124 00:05:04.251 --> 00:05:05.936 you can see one of the problems that we've got here 125 00:05:05.936 --> 00:05:08.812 is that the balance attribute, 126 00:05:08.812 --> 00:05:10.601 it's the last line here on line 48, 127 00:05:10.601 --> 00:05:12.038 that's updated whether the data 128 00:05:12.038 --> 00:05:14.538 is saved successfully or not. 129 00:05:14.538 --> 00:05:17.262 We can't actually put that line into the finally block 130 00:05:17.262 --> 00:05:19.038 because finally executes whether 131 00:05:19.038 --> 00:05:20.722 there's an exception or not. 132 00:05:20.722 --> 00:05:24.135 This is a great time to use the else clause. 133 00:05:24.135 --> 00:05:25.225 What we really should do there 134 00:05:25.225 --> 00:05:27.495 is change this to put except 135 00:05:27.495 --> 00:05:29.900 and after the rollback, 136 00:05:29.900 --> 00:05:33.811 you do an else and that's where we put this code, 137 00:05:33.811 --> 00:05:35.478 the updated balance. 138 00:05:40.975 --> 00:05:44.235 Else has to go after all exception clauses 139 00:05:44.235 --> 00:05:47.295 but it has to go before the finally clause. 140 00:05:47.295 --> 00:05:50.685 Let's just try running that again. 141 00:05:50.685 --> 00:05:53.185 Hopefully we've got it working correctly now. 142 00:05:53.185 --> 00:05:56.298 We've now got the correct balance showing there, $20.10. 143 00:05:56.298 --> 00:05:59.783 Of course that equates to the accounts balance here 144 00:05:59.783 --> 00:06:02.330 of 2010 which of course is an integer. 145 00:06:02.330 --> 00:06:04.632 Dividing by 100, we get the $20.10 here. 146 00:06:04.632 --> 00:06:08.794 That's now showing itself correctly which is good. 147 00:06:08.794 --> 00:06:10.633 In fact, looking at this code, 148 00:06:10.633 --> 00:06:15.196 really the db.commit should also go in the else block. 149 00:06:15.196 --> 00:06:17.808 We got away with putting it in the finally block 150 00:06:17.808 --> 00:06:20.483 because the transaction will have already been rolled back 151 00:06:20.483 --> 00:06:22.196 when there's an error and calling commit 152 00:06:22.196 --> 00:06:23.693 when there's no transactions to commit 153 00:06:23.693 --> 00:06:26.180 doesn't actually do anything. 154 00:06:26.180 --> 00:06:28.515 I started off writing it with a finally block 155 00:06:28.515 --> 00:06:31.870 because try and finally are often talked about together 156 00:06:31.870 --> 00:06:35.188 and you'll hear programmers referring to try finally blocks. 157 00:06:35.188 --> 00:06:36.425 Doing it that way is something 158 00:06:36.425 --> 00:06:38.287 that people will often do at first, 159 00:06:38.287 --> 00:06:39.910 especially if they're used to other languages 160 00:06:39.910 --> 00:06:40.993 such as Java. 161 00:06:41.958 --> 00:06:44.648 If I don't do that, another bad habit if you like 162 00:06:44.648 --> 00:06:48.148 is to update the balance attribute inside the try block. 163 00:06:48.148 --> 00:06:50.238 Something like this. 164 00:06:50.238 --> 00:06:53.338 I'll just copy this temporarily. 165 00:06:53.338 --> 00:06:54.525 Something putting it in there. 166 00:06:54.525 --> 00:06:57.748 That would be some code you'd probably see. 167 00:06:57.748 --> 00:07:01.112 That would work fine, but if there's an exception raised 168 00:07:01.112 --> 00:07:03.798 by either of the database update statements, 169 00:07:03.798 --> 00:07:06.761 the code to update the balance won't be reached. 170 00:07:06.761 --> 00:07:08.375 It's not wrong as such, 171 00:07:08.375 --> 00:07:11.198 but really you should limit the code inside a try block 172 00:07:11.198 --> 00:07:14.446 to just the code you want to protect. 173 00:07:14.446 --> 00:07:17.734 This is an excellent demonstration of why Python 174 00:07:17.734 --> 00:07:19.547 provides an else clause. 175 00:07:19.547 --> 00:07:21.997 If the code to update the balance crashes, 176 00:07:21.997 --> 00:07:24.137 the database updates will be rolled back. 177 00:07:24.137 --> 00:07:26.550 Even though they worked, they would leave the database 178 00:07:26.550 --> 00:07:29.087 in a perfectly consistent state. 179 00:07:29.087 --> 00:07:31.114 I know that the assignment to the balance attribute 180 00:07:31.114 --> 00:07:32.586 didn't crash the programme, 181 00:07:32.586 --> 00:07:34.764 but it could be a function to call or some other code 182 00:07:34.764 --> 00:07:37.114 that might crash and consequently it really does 183 00:07:37.114 --> 00:07:39.000 belong in the else clause. 184 00:07:39.000 --> 00:07:41.800 I'm gonna undo that change. 185 00:07:41.800 --> 00:07:44.437 We'll now get rid of the finally clause 186 00:07:44.437 --> 00:07:48.377 and perform the commit in the else instead. 187 00:07:48.377 --> 00:07:49.727 Like so. 188 00:07:49.727 --> 00:07:51.036 In fact, to be consistent we should really 189 00:07:51.036 --> 00:07:52.412 swap those lines around. 190 00:07:52.412 --> 00:07:54.995 We should put the commit before 191 00:07:55.850 --> 00:07:58.275 the attribute balance update. 192 00:07:58.275 --> 00:07:59.737 That's because if commit fails, 193 00:07:59.737 --> 00:08:02.514 we don't want to store the new location in the attribute. 194 00:08:02.514 --> 00:08:04.887 That brings us to the question now, 195 00:08:04.887 --> 00:08:06.727 why do we have to roll back the transactions 196 00:08:06.727 --> 00:08:09.137 if commit isn't being called? 197 00:08:09.137 --> 00:08:11.400 Let's just update accounts table, 198 00:08:11.400 --> 00:08:13.374 or refresh it rather. 199 00:08:13.374 --> 00:08:17.114 Make a note of John's balance here which is 2010. 200 00:08:17.114 --> 00:08:20.300 I'm gonna delete the last row, TerryG 201 00:08:20.300 --> 00:08:23.100 and update the database. 202 00:08:23.100 --> 00:08:26.767 I'm gonna go back and run our programme again. 203 00:08:28.974 --> 00:08:31.274 Come back and have a look at accounts. 204 00:08:31.274 --> 00:08:34.064 We should be able to see that TerryG's record has been added 205 00:08:34.064 --> 00:08:35.660 and you can see that it was there. 206 00:08:35.660 --> 00:08:37.800 Let's see how that works if we comment out 207 00:08:37.800 --> 00:08:39.883 the rollback method call. 208 00:08:40.785 --> 00:08:43.900 I'm just going to comment that out. 209 00:08:43.900 --> 00:08:47.500 We'll need something else in the except clause 210 00:08:47.500 --> 00:08:48.914 so I'm just going to add a pass for now 211 00:08:48.914 --> 00:08:51.427 just to keep the compiler happy. 212 00:08:51.427 --> 00:08:53.094 If I run this again, 213 00:08:54.300 --> 00:08:57.637 come back to accounts and do a refresh. 214 00:08:57.637 --> 00:09:01.304 I'm going to delete the TerryG record again, 215 00:09:02.364 --> 00:09:04.664 update it, back to rollback.pi. 216 00:09:04.664 --> 00:09:06.497 Going to run it again. 217 00:09:07.424 --> 00:09:11.024 Come back to accounts and refresh. 218 00:09:11.024 --> 00:09:12.662 You can see what's happened here. 219 00:09:12.662 --> 00:09:14.835 We've actually got an incorrect balance now. 220 00:09:14.835 --> 00:09:16.899 The balance is now showing as 1980 221 00:09:16.899 --> 00:09:19.074 which is 30 less than it should be. 222 00:09:19.074 --> 00:09:21.837 Obviously we do need to roll back those transactions 223 00:09:21.837 --> 00:09:23.937 to make sure that doesn't happen. 224 00:09:23.937 --> 00:09:25.336 Why? 225 00:09:25.336 --> 00:09:26.169 Why? 226 00:09:26.169 --> 00:09:27.374 Why, why, why? 227 00:09:27.374 --> 00:09:29.524 You've probably noticed that I don't ask questions like this 228 00:09:29.524 --> 00:09:31.450 unless I already know the answer. 229 00:09:31.450 --> 00:09:33.172 This time I'm going to break with tradition 230 00:09:33.172 --> 00:09:36.466 and not tell you, not just yet anyway. 231 00:09:36.466 --> 00:09:39.466 It's time right now for a challenge. 232 00:09:46.355 --> 00:09:48.444 What happened and why is the balance showing 233 00:09:48.444 --> 00:09:50.957 as 30 less than it should be? 234 00:09:50.957 --> 00:09:52.570 A good approach to solving this challenge 235 00:09:52.570 --> 00:09:54.207 is to read through the code 236 00:09:54.207 --> 00:09:56.470 and take the place of the computer. 237 00:09:56.470 --> 00:09:59.169 Quote unquote, execute each statement, 238 00:09:59.169 --> 00:10:02.134 and work out what's happening at each point. 239 00:10:02.134 --> 00:10:04.834 These days, there are debuggers that will do it for you. 240 00:10:04.834 --> 00:10:07.807 Each line is executed, and you can examine the contents 241 00:10:07.807 --> 00:10:10.034 of all the variables to check the state 242 00:10:10.034 --> 00:10:12.367 of the programme at each step. 243 00:10:14.494 --> 00:10:16.020 Debuggers can be very useful 244 00:10:16.020 --> 00:10:18.170 for tracking down really obscure bugs, 245 00:10:18.170 --> 00:10:21.055 but using them can be very time consuming. 246 00:10:21.055 --> 00:10:22.244 They can come in very handy 247 00:10:22.244 --> 00:10:23.794 for things like loops that go round 248 00:10:23.794 --> 00:10:25.444 hundreds or thousands of times, 249 00:10:25.444 --> 00:10:27.884 because doing that manually is just impractical. 250 00:10:27.884 --> 00:10:31.094 Like most tools, you'll make far better use of them 251 00:10:31.094 --> 00:10:32.870 if you understand the manual process 252 00:10:32.870 --> 00:10:34.756 that's being automated. 253 00:10:34.756 --> 00:10:37.091 Reading code and understanding what's happening at each step 254 00:10:37.091 --> 00:10:38.470 is invaluable. 255 00:10:38.470 --> 00:10:40.970 The more you do it, frankly the better and faster 256 00:10:40.970 --> 00:10:42.137 you get at it. 257 00:10:45.244 --> 00:10:47.757 Give it a go and remember that our insert statement 258 00:10:47.757 --> 00:10:49.833 will always raise an exception 259 00:10:49.833 --> 00:10:52.884 because the primary key already exists. 260 00:10:52.884 --> 00:10:54.070 If you run the programme again, 261 00:10:54.070 --> 00:10:56.357 remember to delete TerryG's record 262 00:10:56.357 --> 00:10:59.144 in the accounts table before running the programme each time. 263 00:10:59.144 --> 00:11:01.920 The problem doesn't occur if all the records 264 00:11:01.920 --> 00:11:04.207 already exist in the database. 265 00:11:04.207 --> 00:11:05.444 See if you can solve this challenge 266 00:11:05.444 --> 00:11:07.840 and figure out why the account balance is 30 less 267 00:11:07.840 --> 00:11:09.034 than what it should be. 268 00:11:09.034 --> 00:11:13.804 Pause the video, I'll see you when you get back. 269 00:11:13.804 --> 00:11:14.707 How did you get on? 270 00:11:14.707 --> 00:11:16.734 Did you manage to figure that out? 271 00:11:16.734 --> 00:11:18.084 Firstly, you should hopefully have noticed 272 00:11:18.084 --> 00:11:22.251 that John's balance attribute never actually gets updated. 273 00:11:23.509 --> 00:11:25.110 That's because the else clause 274 00:11:25.110 --> 00:11:27.234 doesn't get a chance to execute. 275 00:11:27.234 --> 00:11:31.810 In other words, every time the save update method is called, 276 00:11:31.810 --> 00:11:33.983 the balance is always the same. 277 00:11:33.983 --> 00:11:36.482 The next thing to note is that the command 278 00:11:36.482 --> 00:11:39.896 to update the balance column of the accounts table 279 00:11:39.896 --> 00:11:43.960 executes each time we make a deposit or withdrawal. 280 00:11:43.960 --> 00:11:47.934 Basically Terry, Graham, Eric and Michael's account balances 281 00:11:47.934 --> 00:11:52.170 are also retrieved into accounts class instances. 282 00:11:52.170 --> 00:11:56.337 The balance attribute starts off at 2010 and doesn't change. 283 00:11:57.247 --> 00:12:00.611 When this first deposit here on line 82 is attempted, 284 00:12:00.611 --> 00:12:03.360 the new balance becomes 3020 285 00:12:03.360 --> 00:12:06.060 and this is written to the accounts table. 286 00:12:06.060 --> 00:12:07.231 The update isn't committed, 287 00:12:07.231 --> 00:12:09.670 but the transaction is rolled back 288 00:12:09.670 --> 00:12:11.820 so that update is still pending. 289 00:12:11.820 --> 00:12:13.910 The code raises an exception 290 00:12:13.910 --> 00:12:16.360 so we quit the save and the update method 291 00:12:16.360 --> 00:12:19.697 and move on to the deposit of 10 cents on line 83. 292 00:12:19.697 --> 00:12:21.284 The balance attribute at this point 293 00:12:21.284 --> 00:12:25.970 is still 2010 so the new balance becomes 2010 plus 10 294 00:12:25.970 --> 00:12:29.047 which is 2020 and that's written to the accounts table, 295 00:12:29.047 --> 00:12:30.669 but not committed. 296 00:12:30.669 --> 00:12:33.033 At this point, there's now two pending updates 297 00:12:33.033 --> 00:12:34.547 in the accounts table. 298 00:12:34.547 --> 00:12:37.747 When the code continues execution on line 84, 299 00:12:37.747 --> 00:12:39.920 it attempts the second deposit of 10 cents, 300 00:12:39.920 --> 00:12:41.592 it has the same result. 301 00:12:41.592 --> 00:12:43.294 There's now three pending updates, 302 00:12:43.294 --> 00:12:45.844 each one will replace the balance columns value 303 00:12:45.844 --> 00:12:48.820 that would have been stored by the previous update. 304 00:12:48.820 --> 00:12:51.780 Next the code continues onto line 85, 305 00:12:51.780 --> 00:12:55.244 attempts to withdrawal 30 cents from the balance of 2010. 306 00:12:55.244 --> 00:12:59.244 That also fails, but now there's a fourth pending update 307 00:12:59.244 --> 00:13:00.857 of the balance column. 308 00:13:00.857 --> 00:13:04.309 Then the next few lines are largely irrelevant. 309 00:13:04.309 --> 00:13:07.178 We're attempting, each one retrieves the details 310 00:13:07.178 --> 00:13:09.340 from the database so we've got 311 00:13:09.340 --> 00:13:12.590 Terry, Graham, Eric, and Michael's code 312 00:13:13.819 --> 00:13:15.017 that is retrieving the data 313 00:13:15.017 --> 00:13:16.717 from the account class instances. 314 00:13:16.717 --> 00:13:18.869 They have no effect on the state of the database 315 00:13:18.869 --> 00:13:21.193 nor on John's account instance 316 00:13:21.193 --> 00:13:22.891 so we can safely ignore them. 317 00:13:22.891 --> 00:13:24.506 That brings us now to TerryG, 318 00:13:24.506 --> 00:13:26.896 remembering that we deleted that entry 319 00:13:26.896 --> 00:13:28.683 from the accounts table. 320 00:13:28.683 --> 00:13:29.944 The code in the inept method 321 00:13:29.944 --> 00:13:32.431 is consequently called for this particular entry. 322 00:13:32.431 --> 00:13:35.096 It inserts a new row into the accounts table 323 00:13:35.096 --> 00:13:36.946 and, this is the important bit, 324 00:13:36.946 --> 00:13:39.506 commits all pending transactions 325 00:13:39.506 --> 00:13:43.896 by calling the cursor.connection.commit method. 326 00:13:43.896 --> 00:13:46.183 As well as storing TerryG's details, 327 00:13:46.183 --> 00:13:48.608 our four pending updates at that point 328 00:13:48.608 --> 00:13:49.946 are also committed. 329 00:13:49.946 --> 00:13:52.106 Each value is saved in the accounts table 330 00:13:52.106 --> 00:13:55.533 and is then overwritten by the next pending transaction. 331 00:13:55.533 --> 00:13:57.196 Consequently we end up with a balance 332 00:13:57.196 --> 00:14:00.369 from the 30 cent withdrawal being the final one stored. 333 00:14:00.369 --> 00:14:03.583 That's why the balance is 30 less than it should be. 334 00:14:03.583 --> 00:14:04.619 It may not have been obvious, 335 00:14:04.619 --> 00:14:07.608 but unless you roll back a database transaction, 336 00:14:07.608 --> 00:14:10.894 it's still in there waiting to be committed. 337 00:14:10.894 --> 00:14:12.719 Unless you knew exactly where to look, 338 00:14:12.719 --> 00:14:14.583 using a debugger to detect that problem 339 00:14:14.583 --> 00:14:16.982 would have been far from easy. 340 00:14:16.982 --> 00:14:18.869 That's because the database wasn't updated 341 00:14:18.869 --> 00:14:20.396 until right at the end 342 00:14:20.396 --> 00:14:23.081 so checking the contents of the accounts table 343 00:14:23.081 --> 00:14:26.119 each time around wouldn't have revealed anything useful. 344 00:14:26.119 --> 00:14:28.756 I definitely recommend developing the ability 345 00:14:28.756 --> 00:14:30.746 to step through code manually. 346 00:14:30.746 --> 00:14:32.969 You can make that easier by adding print statements 347 00:14:32.969 --> 00:14:35.003 to print out the values of variables 348 00:14:35.003 --> 00:14:36.806 at various points in the code, 349 00:14:36.806 --> 00:14:39.746 and you'll find detecting and fixing bugs much easier 350 00:14:39.746 --> 00:14:42.669 when you can read code and step through it like this. 351 00:14:42.669 --> 00:14:44.483 It does take time to get the hang of it, 352 00:14:44.483 --> 00:14:48.245 but the more you practise, the better you get at it. 353 00:14:48.245 --> 00:14:51.033 Strange as it may sound, manually stepping through the code 354 00:14:51.033 --> 00:14:54.806 is actually often far quicker than using a debugger. 355 00:14:54.806 --> 00:14:56.469 Before I finish this video, 356 00:14:56.469 --> 00:14:59.081 we better put that rollback back in. 357 00:14:59.081 --> 00:15:00.356 I'm gonna come back up here 358 00:15:00.356 --> 00:15:01.718 to our save method, 359 00:15:01.718 --> 00:15:04.304 our save and else or update method 360 00:15:04.304 --> 00:15:07.643 and we're just going to uncomment the rollback 361 00:15:07.643 --> 00:15:10.718 and delete the pass we no longer need. 362 00:15:10.718 --> 00:15:12.196 Of course the other thing we want to do 363 00:15:12.196 --> 00:15:15.196 is make sure we remove the code here 364 00:15:16.219 --> 00:15:17.846 so that our current time method 365 00:15:17.846 --> 00:15:21.096 is actually returning a valid UTC time. 366 00:15:22.233 --> 00:15:26.496 Let's go back and delete the TerryG record again. 367 00:15:26.496 --> 00:15:27.329 Update it. 368 00:15:29.519 --> 00:15:31.683 Run our programme again. 369 00:15:31.683 --> 00:15:35.546 Refresh and we've got it doing the right thing. 370 00:15:35.546 --> 00:15:36.969 Obviously it's a little bit weird there, 371 00:15:36.969 --> 00:15:38.896 but it did actually correctly add 1000 372 00:15:38.896 --> 00:15:40.846 to the 2980 existing balance. 373 00:15:40.846 --> 00:15:42.583 If you really wanted to be sure about this, 374 00:15:42.583 --> 00:15:45.396 we could just go back to ensure it's working 375 00:15:45.396 --> 00:15:47.283 as it's been working previously. 376 00:15:47.283 --> 00:15:48.569 Delete all the entries. 377 00:15:48.569 --> 00:15:50.619 Same for the history. 378 00:15:50.619 --> 00:15:53.619 Delete all those, commit the change. 379 00:15:55.646 --> 00:15:57.229 Run the code again. 380 00:15:59.316 --> 00:16:01.333 Come back to accounts, refresh. 381 00:16:01.333 --> 00:16:03.018 It's looking like it was. 382 00:16:03.018 --> 00:16:04.101 Do a refresh. 383 00:16:05.346 --> 00:16:07.179 I didn't delete those. 384 00:16:08.258 --> 00:16:09.996 Let's do the right thing this time. 385 00:16:09.996 --> 00:16:11.304 Delete them this time 386 00:16:11.304 --> 00:16:14.556 and just to be sure I'll delete the accounts 387 00:16:14.556 --> 00:16:15.896 and our table entries. 388 00:16:15.896 --> 00:16:17.819 We updated both of those. 389 00:16:17.819 --> 00:16:19.269 This is what I was trying to do last time. 390 00:16:19.269 --> 00:16:20.102 Run it. 391 00:16:21.883 --> 00:16:23.044 History, refresh. 392 00:16:23.044 --> 00:16:25.395 We're now going to have four entries which is correct. 393 00:16:25.395 --> 00:16:28.859 If you refresh that, we've got our correct balances. 394 00:16:28.859 --> 00:16:29.692 All right? 395 00:16:29.692 --> 00:16:30.525 That's that. 396 00:16:30.525 --> 00:16:31.358 I hope you got a lot out of that. 397 00:16:31.358 --> 00:16:32.815 I'll see you in the next video.