WEBVTT 1 00:00:02.194 --> 00:00:04.167 line:15% Okay so lets have a look at that short cut 2 00:00:04.167 --> 00:00:06.529 line:15% that I mentioned in the previous video. 3 00:00:06.529 --> 00:00:09.748 So we're gonna go ahead and create a new python file. 4 00:00:09.748 --> 00:00:13.451 So I'm going to right click, new, select python file. 5 00:00:13.451 --> 00:00:15.951 We'll call this one contacts2. 6 00:00:18.132 --> 00:00:20.745 Then let's go back to the orignal contacts profile 7 00:00:20.745 --> 00:00:22.551 that we created in the previous video, 8 00:00:22.551 --> 00:00:24.119 and just copy the first three lines 9 00:00:24.119 --> 00:00:26.366 including the second line which is blank. 10 00:00:26.366 --> 00:00:27.629 Paste that in there. 11 00:00:27.629 --> 00:00:29.748 Then I'm gonna close down this rom window. 12 00:00:29.748 --> 00:00:31.393 And let's double click that 13 00:00:31.393 --> 00:00:33.384 so we've got a full screen there. 14 00:00:33.384 --> 00:00:35.598 Alright, so at the moment we've got 15 00:00:35.598 --> 00:00:39.418 the import and we're opening the contacts.sqlite. 16 00:00:39.418 --> 00:00:41.458 Now sometimes cursors are useful, 17 00:00:41.458 --> 00:00:43.850 and again we used a cursor in the previous video, 18 00:00:43.850 --> 00:00:46.281 and we will be using those a little later. 19 00:00:46.281 --> 00:00:48.977 But in the scenario where you just want to iterate 20 00:00:48.977 --> 00:00:50.943 over the resulting data set, 21 00:00:50.943 --> 00:00:54.635 python allows you to execute queries using the connexion 22 00:00:54.635 --> 00:00:57.325 without having to create a cursor. 23 00:00:57.325 --> 00:00:58.997 So let's look at how to do that. 24 00:00:58.997 --> 00:01:01.562 So we've still got our connexion. 25 00:01:01.562 --> 00:01:05.729 On line five I've gotta type, for row in db.execute 26 00:01:08.275 --> 00:01:11.351 and select, and that is how 27 00:01:11.351 --> 00:01:13.836 now that intellij is helping us 28 00:01:13.836 --> 00:01:15.841 with these commands, so I can just press enter there, 29 00:01:15.841 --> 00:01:18.616 select, star, type in F, 30 00:01:18.616 --> 00:01:20.686 and then it comes up with from for us automatically. 31 00:01:20.686 --> 00:01:21.793 Press enter there, 32 00:01:21.793 --> 00:01:25.960 and then the actual table name which on it was contacts. 33 00:01:27.285 --> 00:01:28.452 And print row. 34 00:01:30.186 --> 00:01:32.157 So you can see we haven't used a cursor there. 35 00:01:32.157 --> 00:01:35.203 Then lastly I'll just close the data base off. 36 00:01:35.203 --> 00:01:37.912 Right so, that's an alternative. 37 00:01:37.912 --> 00:01:40.643 And when you go to run this remember to right click it first 38 00:01:40.643 --> 00:01:42.447 and choose run contacts2, 39 00:01:42.447 --> 00:01:44.644 so that you're running this python script 40 00:01:44.644 --> 00:01:45.927 and not the original one. 41 00:01:45.927 --> 00:01:48.362 So if I run this, you're probably expecting 42 00:01:48.362 --> 00:01:50.429 to see the two rows printed out. 43 00:01:50.429 --> 00:01:53.152 So if we actually run it now we'll see the two rows 44 00:01:53.152 --> 00:01:55.773 and in fact, they're not printed. 45 00:01:55.773 --> 00:01:57.921 So at this point you might be checking a code, 46 00:01:57.921 --> 00:01:59.653 or looking at the screen and wondering, 47 00:01:59.653 --> 00:02:01.121 you know trying to work out what's going on 48 00:02:01.121 --> 00:02:02.731 and why nothing's printed. 49 00:02:02.731 --> 00:02:04.923 And you might even wanna launch a terminal session 50 00:02:04.923 --> 00:02:07.136 or command prompt and check the data base 51 00:02:07.136 --> 00:02:08.871 with the command line shell, 52 00:02:08.871 --> 00:02:11.659 but I won't bother because there's nothing in it. 53 00:02:11.659 --> 00:02:15.138 Now, reason is that when you make changes to a table, 54 00:02:15.138 --> 00:02:18.049 using insert, update or delete statements, 55 00:02:18.049 --> 00:02:19.870 nothing's made permanent, 56 00:02:19.870 --> 00:02:22.082 until you commit the changes. 57 00:02:22.082 --> 00:02:25.587 Now, some commands were forced the data to be committed, 58 00:02:25.587 --> 00:02:28.247 and we're looking at things like bulk update 59 00:02:28.247 --> 00:02:30.343 and sequel scripts a bit later, 60 00:02:30.343 --> 00:02:34.049 but it's not a good idea generally to rely on that behaviour. 61 00:02:34.049 --> 00:02:35.839 So the problem we've got is nothing to do 62 00:02:35.839 --> 00:02:37.569 with the code in this programme, 63 00:02:37.569 --> 00:02:39.619 it's because we didn't commit the changes 64 00:02:39.619 --> 00:02:40.985 in our previous script. 65 00:02:40.985 --> 00:02:43.883 So our insert statements were rolled back 66 00:02:43.883 --> 00:02:46.124 when we closed the data base. 67 00:02:46.124 --> 00:02:48.272 And looking at that other code, 68 00:02:48.272 --> 00:02:49.546 if you think about it 69 00:02:49.546 --> 00:02:53.187 it does make sense because when we run this programme, 70 00:02:53.187 --> 00:02:54.020 we run it now, 71 00:02:54.020 --> 00:02:57.403 this is running the contacts and not the contacts2, 72 00:02:57.403 --> 00:02:59.839 it's showing two records and every time I run it, 73 00:02:59.839 --> 00:03:01.847 it's only showing the two records. 74 00:03:01.847 --> 00:03:03.767 Well it's actually showing none as the third record, 75 00:03:03.767 --> 00:03:06.076 but the point is we've got these insert statements 76 00:03:06.076 --> 00:03:07.636 on line five and six, 77 00:03:07.636 --> 00:03:10.276 so if it was inserting records every time we run it, 78 00:03:10.276 --> 00:03:12.303 we would have many more records 79 00:03:12.303 --> 00:03:13.593 than actually what we've got showing, 80 00:03:13.593 --> 00:03:15.602 which is only those two. 81 00:03:15.602 --> 00:03:18.495 So, the reason for this is that sqlite wraps things 82 00:03:18.495 --> 00:03:21.824 like inserts and deletions in a transaction. 83 00:03:21.824 --> 00:03:25.524 Which is something that is very common in data base systems. 84 00:03:25.524 --> 00:03:28.815 The idea is that the entire transaction can be rolled back 85 00:03:28.815 --> 00:03:30.896 if something goes wrong. 86 00:03:30.896 --> 00:03:33.471 Now that's actually an incredibly useful feature. 87 00:03:33.471 --> 00:03:35.888 So in a banking application for example, 88 00:03:35.888 --> 00:03:37.474 you don't want to debit one account 89 00:03:37.474 --> 00:03:40.495 unless the paying account is also credited. 90 00:03:40.495 --> 00:03:42.986 So if something goes wrong between the two updates, 91 00:03:42.986 --> 00:03:45.266 money will go into the account being debited, 92 00:03:45.266 --> 00:03:47.394 but wouldn't be taken out of the other account, 93 00:03:47.394 --> 00:03:49.226 and that's good for the holder of the other account 94 00:03:49.226 --> 00:03:51.576 but not very good for the bank. 95 00:03:51.576 --> 00:03:54.134 So typically, the two updates will be performed 96 00:03:54.134 --> 00:03:55.596 in a transaction. 97 00:03:55.596 --> 00:03:59.543 Now the transaction is only committed if nothing goes wrong. 98 00:03:59.543 --> 00:04:02.855 Now if there's an error in either update in this scenario, 99 00:04:02.855 --> 00:04:05.306 the entire transaction would be rolled back. 100 00:04:05.306 --> 00:04:06.923 So if the bank debited an account 101 00:04:06.923 --> 00:04:09.309 and failed to take the money from the other account, 102 00:04:09.309 --> 00:04:12.035 the bank would owe the money that transferred. 103 00:04:12.035 --> 00:04:13.675 By the way if you're thinking I've got the terms 104 00:04:13.675 --> 00:04:15.917 debit and credit the wrong way round, 105 00:04:15.917 --> 00:04:17.816 your bank statements are produced 106 00:04:17.816 --> 00:04:19.858 from the point of view of the bank. 107 00:04:19.858 --> 00:04:22.149 So if your account's in credit, 108 00:04:22.149 --> 00:04:25.072 that means the bank effectively owes you money. 109 00:04:25.072 --> 00:04:28.850 And when you pay money in, the bank debits your account 110 00:04:28.850 --> 00:04:31.929 and credits theirs with the corresponding amount. 111 00:04:31.929 --> 00:04:33.631 So from their point of view, 112 00:04:33.631 --> 00:04:35.671 their account with you is in credit, 113 00:04:35.671 --> 00:04:37.538 and they owe you money. 114 00:04:37.538 --> 00:04:39.631 As a programmer you may well have to work on 115 00:04:39.631 --> 00:04:41.209 financial systems so, 116 00:04:41.209 --> 00:04:43.980 bear in mind that the way we learn to use debit and credit, 117 00:04:43.980 --> 00:04:45.231 from having a bank account 118 00:04:45.231 --> 00:04:48.446 is actually the other way round in accounting. 119 00:04:48.446 --> 00:04:50.341 Alright, back to python. 120 00:04:50.341 --> 00:04:53.309 So there's nothing in our contacts table, 121 00:04:53.309 --> 00:04:55.090 because we didn't commit the inserts 122 00:04:55.090 --> 00:04:57.199 in the previous programme which I've now got on screen, 123 00:04:57.199 --> 00:04:58.971 our contacts.py. 124 00:04:58.971 --> 00:05:02.196 So let's actually add a line to that script now, 125 00:05:02.196 --> 00:05:05.768 to commit the changes before we close the data base. 126 00:05:05.768 --> 00:05:06.837 So where we do that is, 127 00:05:06.837 --> 00:05:10.259 in between the cursor.close and the db.close, 128 00:05:10.259 --> 00:05:14.320 so we type in db.commit, obviously parenthesis 129 00:05:14.320 --> 00:05:16.169 because it's a function. 130 00:05:16.169 --> 00:05:17.941 Alright so now if I right click it, 131 00:05:17.941 --> 00:05:20.759 making sure I'm running contacts and not contacts2, 132 00:05:20.759 --> 00:05:22.220 and run it. 133 00:05:22.220 --> 00:05:23.909 We still get the same output, 134 00:05:23.909 --> 00:05:26.319 but this time the data has actually been committed 135 00:05:26.319 --> 00:05:27.928 to the table. 136 00:05:27.928 --> 00:05:30.418 So there's two important things we've seen here. 137 00:05:30.418 --> 00:05:33.487 The first is that it's crucial to commit any changes 138 00:05:33.487 --> 00:05:35.493 that you make to the data base, 139 00:05:35.493 --> 00:05:37.934 if you want those changes to be persistent. 140 00:05:37.934 --> 00:05:39.871 If you fail to commit the changes, 141 00:05:39.871 --> 00:05:42.577 they'll be lost when you close the connexion. 142 00:05:42.577 --> 00:05:44.227 Now you will forget at some point, 143 00:05:44.227 --> 00:05:45.884 but at least now you won't spend hours, 144 00:05:45.884 --> 00:05:49.330 or hopefully won't spend hours trying to debug your code. 145 00:05:49.330 --> 00:05:51.272 You'll actually realise straight away 146 00:05:51.272 --> 00:05:53.090 that you didn't commit the changes 147 00:05:53.090 --> 00:05:54.960 and you can add the commit to your code, 148 00:05:54.960 --> 00:05:57.647 run your test again and then move on. 149 00:05:57.647 --> 00:06:01.031 Alright the second thing, is that shortcut I mentioned. 150 00:06:01.031 --> 00:06:03.014 Very often we're not interested in a cursor, 151 00:06:03.014 --> 00:06:05.982 and I'll just go back to contacts2 pyscript. 152 00:06:05.982 --> 00:06:08.034 Now cursors can be very useful, 153 00:06:08.034 --> 00:06:09.573 but if you just wanna run a query 154 00:06:09.573 --> 00:06:11.018 and loop through the results, 155 00:06:11.018 --> 00:06:13.306 then you can execute the query using the connexion 156 00:06:13.306 --> 00:06:17.529 as I showed, without explicitly creating the cursor. 157 00:06:17.529 --> 00:06:20.807 So the connexions execute method does return a cursor 158 00:06:20.807 --> 00:06:22.787 when you execute a select statement, 159 00:06:22.787 --> 00:06:26.336 but you don't have to worry about explicitly defining one. 160 00:06:26.336 --> 00:06:29.455 And we can actually unpack the cursors tuples, 161 00:06:29.455 --> 00:06:31.967 just like we did in the previous programme. 162 00:06:31.967 --> 00:06:34.825 Right firstly let's just run this in this scenario first. 163 00:06:34.825 --> 00:06:36.691 So I'm running contacts2 now and making sure 164 00:06:36.691 --> 00:06:38.990 that contacts2 is selected here. 165 00:06:38.990 --> 00:06:40.580 And we're now getting some output, 166 00:06:40.580 --> 00:06:42.108 and that's because in the other script 167 00:06:42.108 --> 00:06:43.881 we've committed the change. 168 00:06:43.881 --> 00:06:45.770 So that's one way of doing it but as I mentioned 169 00:06:45.770 --> 00:06:48.445 we can also unpack the cursors tuple. 170 00:06:48.445 --> 00:06:50.930 So we can do something like this instead. 171 00:06:50.930 --> 00:06:54.068 So for name in, we'll change this here to, 172 00:06:54.068 --> 00:06:55.543 for, 173 00:06:55.543 --> 00:06:57.001 name, 174 00:06:57.001 --> 00:06:58.446 phone, 175 00:06:58.446 --> 00:07:00.526 email in db.execute 176 00:07:00.526 --> 00:07:02.536 and that remains the same, the rest of the line, 177 00:07:02.536 --> 00:07:05.445 and instead we'll have print name, 178 00:07:05.445 --> 00:07:06.445 print phone, 179 00:07:07.808 --> 00:07:08.808 print email, 180 00:07:10.251 --> 00:07:12.576 and let's add a separator as well so print, 181 00:07:12.576 --> 00:07:14.076 little typo there, 182 00:07:15.468 --> 00:07:19.310 and we'll print as mentioned a separator, 183 00:07:19.310 --> 00:07:21.810 times 20, and we can run that. 184 00:07:23.859 --> 00:07:27.709 And you can see we've got the two unpacked tuples. 185 00:07:27.709 --> 00:07:30.520 And note here that I haven't called db.commit, 186 00:07:30.520 --> 00:07:32.424 and the reason for that is we haven't actually written 187 00:07:32.424 --> 00:07:35.321 anything to the data base with this file, 188 00:07:35.321 --> 00:07:38.402 with this python script, so there's no changes to commit. 189 00:07:38.402 --> 00:07:40.468 Now we may have only saved two lines of code 190 00:07:40.468 --> 00:07:42.100 over the previous programme, 191 00:07:42.100 --> 00:07:44.255 but every line on a code you don't write 192 00:07:44.255 --> 00:07:46.415 is one less code you have to debug. 193 00:07:46.415 --> 00:07:49.411 So this short cut I think is very useful. 194 00:07:49.411 --> 00:07:51.991 More importantly, because it's available 195 00:07:51.991 --> 00:07:54.881 you'll almost certainly come across it in production code 196 00:07:54.881 --> 00:07:56.979 that you may have to maintain. 197 00:07:56.979 --> 00:07:58.904 So you may decide that you'll always create a cursor 198 00:07:58.904 --> 00:08:01.273 because it's clearer, what's going on, 199 00:08:01.273 --> 00:08:02.865 but learning to programme isn't just about 200 00:08:02.865 --> 00:08:04.288 learning to write code, 201 00:08:04.288 --> 00:08:06.527 but also about learning to read it. 202 00:08:06.527 --> 00:08:08.428 Now it's often been said that code is read 203 00:08:08.428 --> 00:08:10.599 ten times as often as it's written. 204 00:08:10.599 --> 00:08:12.529 Now I'm not gonna comment on statistics like that, 205 00:08:12.529 --> 00:08:14.652 but I can confirm that I've read far more code 206 00:08:14.652 --> 00:08:15.822 than I've written, 207 00:08:15.822 --> 00:08:18.004 and a lot of the has been my own code. 208 00:08:18.004 --> 00:08:19.659 So in other words, I've written it once 209 00:08:19.659 --> 00:08:21.538 then read it many times afterwards, 210 00:08:21.538 --> 00:08:24.802 when maintaining the programmes and adding new features. 211 00:08:24.802 --> 00:08:27.595 Okay, so we can execute queries using the connexion 212 00:08:27.595 --> 00:08:28.697 as you saw here, 213 00:08:28.697 --> 00:08:31.404 rather than explicitly creating a cursor, 214 00:08:31.404 --> 00:08:32.828 and we saw that right at the start 215 00:08:32.828 --> 00:08:36.231 when we created a table and inserted new records into it. 216 00:08:36.231 --> 00:08:38.298 Now we could have created a cursor to execute 217 00:08:38.298 --> 00:08:40.550 the create table state with incidentally, 218 00:08:40.550 --> 00:08:43.326 but it probably doesn't make sense to do that. 219 00:08:43.326 --> 00:08:45.774 For certain update statements it can make sense 220 00:08:45.774 --> 00:08:46.855 to use a cursor, 221 00:08:46.855 --> 00:08:48.624 even though we won't be getting back any rows 222 00:08:48.624 --> 00:08:50.135 from the data base. 223 00:08:50.135 --> 00:08:52.868 And that's because cursors have a row count property, 224 00:08:52.868 --> 00:08:56.138 that we can use to check how many rows were affected 225 00:08:56.138 --> 00:08:58.599 by the previous sequel statement. 226 00:08:58.599 --> 00:09:00.219 So let's actually see how that works, 227 00:09:00.219 --> 00:09:04.707 by performing an update in this contacts2 pyscript. 228 00:09:04.707 --> 00:09:07.032 So what we're gonna do is make a change here now, 229 00:09:07.032 --> 00:09:08.470 and I'm gonna add an update, 230 00:09:08.470 --> 00:09:11.409 I'm gonna add this starting on line five, 231 00:09:11.409 --> 00:09:12.242 update 232 00:09:13.185 --> 00:09:14.242 underscore 233 00:09:14.242 --> 00:09:15.075 s-q-l 234 00:09:15.075 --> 00:09:16.431 equals 235 00:09:16.431 --> 00:09:17.264 update 236 00:09:18.878 --> 00:09:19.711 contacts 237 00:09:20.736 --> 00:09:21.696 set 238 00:09:21.696 --> 00:09:22.880 email 239 00:09:22.880 --> 00:09:23.864 equals, 240 00:09:23.864 --> 00:09:25.199 and a single quote because we've already got 241 00:09:25.199 --> 00:09:26.906 a double quoted string, 242 00:09:26.906 --> 00:09:28.406 update@update.com, 243 00:09:30.736 --> 00:09:32.017 single quote, 244 00:09:32.017 --> 00:09:33.267 where, contacts 245 00:09:34.893 --> 00:09:36.236 dot phone 246 00:09:36.236 --> 00:09:37.236 equals 1234. 247 00:09:38.185 --> 00:09:40.039 So hopefully you recognise the standard sequel code 248 00:09:40.039 --> 00:09:41.845 we're using now. 249 00:09:41.845 --> 00:09:43.541 Right then we want update 250 00:09:43.541 --> 00:09:46.874 underscore cursor is equal to db.cursor, 251 00:09:49.164 --> 00:09:50.581 and update_cursor 252 00:09:51.489 --> 00:09:52.489 dot execute, 253 00:09:53.908 --> 00:09:54.825 update sql, 254 00:09:56.087 --> 00:09:59.973 which is the sequel code that we want to execute. 255 00:09:59.973 --> 00:10:02.380 And then we'll print out print, 256 00:10:02.380 --> 00:10:04.376 double quote, curly braces 257 00:10:04.376 --> 00:10:07.793 left and right curly braces, rows updated 258 00:10:09.943 --> 00:10:11.207 dot format, 259 00:10:11.207 --> 00:10:12.707 then update_cursor 260 00:10:13.728 --> 00:10:14.932 dot 261 00:10:14.932 --> 00:10:15.765 rowcount, 262 00:10:18.307 --> 00:10:21.228 and then we wanna close the connexion so update_cursor, 263 00:10:21.228 --> 00:10:22.890 or the cursor I should say, I said connexion, 264 00:10:22.890 --> 00:10:24.557 update_cursor.close, 265 00:10:25.884 --> 00:10:28.538 and then we'll leave the rest of the code there as it was. 266 00:10:28.538 --> 00:10:30.461 So here I've stored the sequel statement 267 00:10:30.461 --> 00:10:33.067 in the string variable update underscore sequel, 268 00:10:33.067 --> 00:10:36.068 then created a cursor and used it to execute 269 00:10:36.068 --> 00:10:37.615 the sequel statement. 270 00:10:37.615 --> 00:10:39.303 Now because we've used a cursor, 271 00:10:39.303 --> 00:10:40.968 we can check the row count property 272 00:10:40.968 --> 00:10:43.182 to see how many rows were updated. 273 00:10:43.182 --> 00:10:45.432 So if we actually run this, 274 00:10:47.591 --> 00:10:50.650 we can see in the output one row's updated, 275 00:10:50.650 --> 00:10:52.888 and we can also see the record that was updated 276 00:10:52.888 --> 00:10:55.326 with the phone number of 1234, 277 00:10:55.326 --> 00:10:59.068 has now got a new email address, update@update.com. 278 00:10:59.068 --> 00:11:00.304 And just to confirm that, 279 00:11:00.304 --> 00:11:02.243 if I change the condition over here 280 00:11:02.243 --> 00:11:05.727 so that the phone number doesn't match any records on file, 281 00:11:05.727 --> 00:11:07.420 we should get no rows updated so, 282 00:11:07.420 --> 00:11:08.253 4567, 283 00:11:09.186 --> 00:11:10.436 run this again, 284 00:11:12.349 --> 00:11:14.815 zero rows updated as you can see. 285 00:11:14.815 --> 00:11:18.815 Alright so dropping the where clause altogether, 286 00:11:21.335 --> 00:11:24.021 just delete that actually, 287 00:11:24.021 --> 00:11:27.802 and running that should result in all rows being updated. 288 00:11:27.802 --> 00:11:29.500 Right so if I run that, 289 00:11:29.500 --> 00:11:31.300 or where they actually updated? 290 00:11:31.300 --> 00:11:32.288 Now this is actually a challenge 291 00:11:32.288 --> 00:11:34.705 that I'm setting for you now. 292 00:11:45.812 --> 00:11:47.212 Alright so that's the challenge again, 293 00:11:47.212 --> 00:11:48.852 create a new python file here, 294 00:11:48.852 --> 00:11:50.892 write the code to print out all the records 295 00:11:50.892 --> 00:11:53.461 in the contacts.sqlite data base, 296 00:11:53.461 --> 00:11:56.120 check the result and explain why 297 00:11:56.120 --> 00:11:59.928 the records aren't as we'd expect them to be. 298 00:11:59.928 --> 00:12:04.264 Pause the video, and I'll see you when you get back. 299 00:12:04.264 --> 00:12:05.376 Alright so how did you get on? 300 00:12:05.376 --> 00:12:08.388 Let's actually go through now with a solution 301 00:12:08.388 --> 00:12:09.254 to this challenge. 302 00:12:09.254 --> 00:12:11.663 I'm going to start by creating a new file, 303 00:12:11.663 --> 00:12:12.881 new python file, 304 00:12:12.881 --> 00:12:15.424 and we'll call this one 305 00:12:15.424 --> 00:12:16.257 checkdb. 306 00:12:20.437 --> 00:12:23.520 So I'll start with an import sqlite3. 307 00:12:25.793 --> 00:12:28.876 I'll do a conn = sqlite3.connect 308 00:12:31.299 --> 00:12:32.966 and contacts.sqlite, 309 00:12:33.827 --> 00:12:35.262 as we've done before. 310 00:12:35.262 --> 00:12:38.512 Then we put for, row, in, conn.execute, 311 00:12:40.319 --> 00:12:42.486 select star from contacts, 312 00:12:44.782 --> 00:12:45.615 print row, 313 00:12:47.870 --> 00:12:48.958 conn 314 00:12:48.958 --> 00:12:49.791 dot close. 315 00:12:50.784 --> 00:12:52.499 Now your solution might be different to mine 316 00:12:52.499 --> 00:12:53.755 you may have used a cursor, 317 00:12:53.755 --> 00:12:56.184 or unpacked the rows tuples to print out the results, 318 00:12:56.184 --> 00:12:57.222 for example, 319 00:12:57.222 --> 00:12:59.555 but when I right click this, 320 00:13:00.703 --> 00:13:04.472 and run it making sure I'm running checkdb, 321 00:13:04.472 --> 00:13:07.985 notice how we've got the old records back again. 322 00:13:07.985 --> 00:13:10.015 So each row's got a different email address, 323 00:13:10.015 --> 00:13:13.989 even though we ran the update in the contacts2 script, 324 00:13:13.989 --> 00:13:17.404 that was meant to actually have updated all the records 325 00:13:17.404 --> 00:13:18.617 in the data base. 326 00:13:18.617 --> 00:13:20.586 So in other words our previous update hasn't altered 327 00:13:20.586 --> 00:13:22.511 the rows in the table. 328 00:13:22.511 --> 00:13:24.616 Now the reason that the rows haven't been updated, 329 00:13:24.616 --> 00:13:27.568 is that there was no commit in the contacts2 programme, 330 00:13:27.568 --> 00:13:30.241 so when we closed the connexion the update was rolled back, 331 00:13:30.241 --> 00:13:31.947 and the table's unchanged. 332 00:13:31.947 --> 00:13:34.456 Now it's very easy to do that as I said, 333 00:13:34.456 --> 00:13:37.251 and it was especially easy in contacts2, 334 00:13:37.251 --> 00:13:38.976 and that's because we started off not needing 335 00:13:38.976 --> 00:13:40.182 to commit anything, 336 00:13:40.182 --> 00:13:42.852 when the programme only queried the data base. 337 00:13:42.852 --> 00:13:45.090 Now you could automatically call commit before close 338 00:13:45.090 --> 00:13:46.328 at the end of your programmes, 339 00:13:46.328 --> 00:13:48.910 but I wouldn't necessarily recommend that, 340 00:13:48.910 --> 00:13:50.914 and that's because the time to commit the updates 341 00:13:50.914 --> 00:13:52.870 is once you're happy with the changes, 342 00:13:52.870 --> 00:13:55.999 rather than leading it until you close the connexion. 343 00:13:55.999 --> 00:13:57.999 So go back to contacts2, 344 00:13:59.032 --> 00:14:00.934 we really should commit the changes after 345 00:14:00.934 --> 00:14:02.632 performing the update. 346 00:14:02.632 --> 00:14:05.512 Now something else I recommend is to use the cursor 347 00:14:05.512 --> 00:14:08.193 to commit the changes, rather than the connexion. 348 00:14:08.193 --> 00:14:11.423 Now cursor objects themselves don't have a commit method, 349 00:14:11.423 --> 00:14:13.652 but they do have a connexion property 350 00:14:13.652 --> 00:14:16.528 that we can use to get a reference to the connexion, 351 00:14:16.528 --> 00:14:18.940 that the cursor's using. 352 00:14:18.940 --> 00:14:21.132 So what we wanna do here is after the 353 00:14:21.132 --> 00:14:23.861 print that says how many rows are updated, 354 00:14:23.861 --> 00:14:25.490 this before the close, 355 00:14:25.490 --> 00:14:27.751 we should actually put update 356 00:14:27.751 --> 00:14:29.429 underscore cursor 357 00:14:29.429 --> 00:14:31.214 dot connexion 358 00:14:31.214 --> 00:14:32.348 dot 359 00:14:32.348 --> 00:14:33.181 commit. 360 00:14:34.086 --> 00:14:35.517 Like so. 361 00:14:35.517 --> 00:14:36.886 So you might be asking at this point, 362 00:14:36.886 --> 00:14:39.932 why is this better than calling db.commit? 363 00:14:39.932 --> 00:14:42.639 Well in this small programme you really could use either. 364 00:14:42.639 --> 00:14:44.863 The cursor connexions is db, 365 00:14:44.863 --> 00:14:46.548 so calling commit on either one 366 00:14:46.548 --> 00:14:48.615 will do exactly the same thing, 367 00:14:48.615 --> 00:14:51.836 and we can see all the code on the screen at the same time, 368 00:14:51.836 --> 00:14:54.024 but as your programmes get more complex, 369 00:14:54.024 --> 00:14:56.956 you'll start creating functions or classes 370 00:14:56.956 --> 00:14:59.385 to deal with things like updates and inserts, 371 00:14:59.385 --> 00:15:01.524 and when you start doing that, 372 00:15:01.524 --> 00:15:04.556 your functions may have a cursor object to work with 373 00:15:04.556 --> 00:15:07.755 but won't necessarily know about the programmes connexion. 374 00:15:07.755 --> 00:15:09.783 There's no need for them to know about the connexion, 375 00:15:09.783 --> 00:15:13.874 so it's tidier to use whatever connexion your cursor has. 376 00:15:13.874 --> 00:15:16.362 Now you might also create more than one connexion 377 00:15:16.362 --> 00:15:17.463 to the data base, 378 00:15:17.463 --> 00:15:19.942 and calling the commit method on the wrong connexion 379 00:15:19.942 --> 00:15:21.532 will introduce subtle bugs 380 00:15:21.532 --> 00:15:23.890 that may be really hard to find. 381 00:15:23.890 --> 00:15:25.338 The commit method doesn't give an error 382 00:15:25.338 --> 00:15:26.675 if there's nothing to commit, 383 00:15:26.675 --> 00:15:28.558 so calling it on the wrong connexion 384 00:15:28.558 --> 00:15:30.837 will be a really hard bug to spot. 385 00:15:30.837 --> 00:15:33.276 So for that reason I suggest using the cursors 386 00:15:33.276 --> 00:15:36.534 connexion property, to get a reference to the connexion 387 00:15:36.534 --> 00:15:39.683 that the cursor's using, and then call commit on that. 388 00:15:39.683 --> 00:15:41.541 So just to be clear we are dealing with 389 00:15:41.541 --> 00:15:43.730 exactly the same connexion here, 390 00:15:43.730 --> 00:15:46.581 so let's actually look at our complete code again. 391 00:15:46.581 --> 00:15:48.864 So you can see on line three, 392 00:15:48.864 --> 00:15:50.574 we create a connexion to the data base 393 00:15:50.574 --> 00:15:52.547 and we store it in db. 394 00:15:52.547 --> 00:15:56.988 We then use that same db, that connexion on line six 395 00:15:56.988 --> 00:15:58.840 to create the cursor, 396 00:15:58.840 --> 00:16:02.862 so the cursor is using the connexion stored in db. 397 00:16:02.862 --> 00:16:04.824 Now on line 10, 398 00:16:04.824 --> 00:16:08.631 when we refer to update_cursor.connection, 399 00:16:08.631 --> 00:16:10.185 that's exactly the same connexion 400 00:16:10.185 --> 00:16:12.785 that we stored in the variable db, 401 00:16:12.785 --> 00:16:15.674 and we can actually check that in fact, 402 00:16:15.674 --> 00:16:19.239 we could actually do that after the print here. 403 00:16:19.239 --> 00:16:21.656 We type in print, then print, 404 00:16:22.752 --> 00:16:24.835 are connexions the same, 405 00:16:28.660 --> 00:16:29.910 and dot format, 406 00:16:31.340 --> 00:16:32.640 update 407 00:16:32.640 --> 00:16:33.516 cursor 408 00:16:33.516 --> 00:16:35.183 dot connexion 409 00:16:35.183 --> 00:16:36.433 is equal to db. 410 00:16:38.893 --> 00:16:40.530 Then we do another print there. 411 00:16:40.530 --> 00:16:42.863 Let's just try running that. 412 00:16:44.414 --> 00:16:46.797 You can see, are connexions the same true. 413 00:16:46.797 --> 00:16:49.389 So if you make changes to your data base using a cursor, 414 00:16:49.389 --> 00:16:51.778 I really do suggest you call the commit method 415 00:16:51.778 --> 00:16:54.309 by using the cursors connexion property. 416 00:16:54.309 --> 00:16:55.905 That way your code won't have to care about 417 00:16:55.905 --> 00:16:57.209 which connexion to use, 418 00:16:57.209 --> 00:16:58.788 and you'll always get the correct one 419 00:16:58.788 --> 00:17:01.649 because it's using the cursors connexion property. 420 00:17:01.649 --> 00:17:04.348 Now of course, if you didn't use a cursor 421 00:17:04.348 --> 00:17:05.712 to perform the update, 422 00:17:05.712 --> 00:17:07.669 so if you've used the connexions execute method 423 00:17:07.669 --> 00:17:10.453 like we did in the previous contacts.py programme 424 00:17:10.453 --> 00:17:12.136 then you'd have to actually call commit 425 00:17:12.136 --> 00:17:13.976 using the connexion. 426 00:17:13.976 --> 00:17:16.393 Right so let's just run this. 427 00:17:17.719 --> 00:17:21.886 And if you go back to our checkdb again, and run that, 428 00:17:22.841 --> 00:17:24.402 we can verify that the records this time 429 00:17:24.402 --> 00:17:26.530 have actually been updated. 430 00:17:26.530 --> 00:17:27.618 Alright so there's a couple more things 431 00:17:27.618 --> 00:17:29.599 I wanna cover before we move on. 432 00:17:29.599 --> 00:17:31.829 One thing we need to look at is the email address 433 00:17:31.829 --> 00:17:36.400 that we've hard coded in the update sequel string. 434 00:17:36.400 --> 00:17:37.233 Over here. 435 00:17:38.129 --> 00:17:39.972 Now there's actually a better way of doing that. 436 00:17:39.972 --> 00:17:41.432 So we're gonna look at place holders 437 00:17:41.432 --> 00:17:44.552 and parameter substitution to handle things like this, 438 00:17:44.552 --> 00:17:46.752 and I've also talked about commit, 439 00:17:46.752 --> 00:17:49.208 but I haven't shown you what to do 440 00:17:49.208 --> 00:17:51.822 if you decide you shouldn't commit the transaction. 441 00:17:51.822 --> 00:17:53.473 So we're gonna be talking about 442 00:17:53.473 --> 00:17:55.931 and seeing how to roll back an update. 443 00:17:55.931 --> 00:17:57.703 So I'm gonna stop the video here, 444 00:17:57.703 --> 00:17:59.034 and in the next video we're gonna use 445 00:17:59.034 --> 00:18:02.182 parameter substitution in our sequel statements. 446 00:18:02.182 --> 00:18:04.444 After that we're going to leave databases briefly 447 00:18:04.444 --> 00:18:06.662 and have a look at exceptions in python. 448 00:18:06.662 --> 00:18:07.941 Once we know how to use exceptions 449 00:18:07.941 --> 00:18:10.032 line:15% to detect that something's gone wrong, 450 00:18:10.032 --> 00:18:13.213 line:15% we can see how to roll back our transactions. 451 00:18:13.213 --> 00:18:16.196 line:15% So I'll see you as always, in the next video.