WEBVTT 1 00:00:00.200 --> 00:00:01.380 Hi and welcome back. 2 00:00:01.380 --> 00:00:04.330 In this video we're going to finish our milestone project 3 00:00:04.330 --> 00:00:07.323 by including the update and delete statements. 4 00:00:09.080 --> 00:00:11.390 We've added the create_book_table, 5 00:00:11.390 --> 00:00:14.320 which creates our table if it doesn't exist already. 6 00:00:14.320 --> 00:00:17.620 We've inserted a new book into our table. 7 00:00:17.620 --> 00:00:19.810 And we've also looked at selecting all of the books 8 00:00:19.810 --> 00:00:22.263 from our table and returning them as dictionaries. 9 00:00:23.490 --> 00:00:26.340 Now we've got two more functions, mark_book_as_read 10 00:00:27.310 --> 00:00:28.613 and delete_book. 11 00:00:29.640 --> 00:00:33.510 With our files, what these did, was, read the entire file, 12 00:00:33.510 --> 00:00:35.580 find the appropriate book, update it, 13 00:00:35.580 --> 00:00:38.190 and then save all the books back. 14 00:00:38.190 --> 00:00:42.680 With SQL we can actually find the correct book by name 15 00:00:42.680 --> 00:00:44.770 on the database and update it in place, 16 00:00:44.770 --> 00:00:47.770 so we no longer need to get all the books, 17 00:00:47.770 --> 00:00:50.550 change one and then save them all back. 18 00:00:50.550 --> 00:00:53.390 For that reason, I'm going to delete this save_all_books 19 00:00:53.390 --> 00:00:58.050 function, and that's a good reason why it had the underscore 20 00:00:58.050 --> 00:00:58.970 in the beginning. 21 00:00:58.970 --> 00:01:01.410 It was something that we wanted to use internally 22 00:01:01.410 --> 00:01:04.590 to make our interface work, but it wasn't something 23 00:01:04.590 --> 00:01:08.390 we wanted the app use. 24 00:01:08.390 --> 00:01:09.980 So that's why it had the underscore before, 25 00:01:09.980 --> 00:01:12.460 to mark it as, sort of, private. 26 00:01:12.460 --> 00:01:13.760 So that other part of our programme 27 00:01:13.760 --> 00:01:15.900 wouldn't want to use it too. 28 00:01:15.900 --> 00:01:19.340 To mark a book as read, you know how to do this now, 29 00:01:19.340 --> 00:01:22.360 'cause we have to initialise a connection, get a cursor, 30 00:01:22.360 --> 00:01:25.370 execute our update statement with a where clause 31 00:01:25.370 --> 00:01:28.210 that would where by the name, 32 00:01:28.210 --> 00:01:30.650 and then commit and close the connection. 33 00:01:30.650 --> 00:01:32.170 We don't even need to return anything 34 00:01:32.170 --> 00:01:35.750 or get any data out of the database in order to do this. 35 00:01:35.750 --> 00:01:38.460 I think you can implement this function on your own, 36 00:01:38.460 --> 00:01:39.413 so give it a go. 37 00:01:40.390 --> 00:01:41.890 I'm sure you managed to do that. 38 00:01:41.890 --> 00:01:44.340 We've just looked at the update, and all we have to do, 39 00:01:44.340 --> 00:01:48.060 is again, copy this boilerplate sqlite3.connect 40 00:01:48.060 --> 00:01:49.693 to data.db. 41 00:01:51.710 --> 00:01:54.980 Make sure to use the correct data file. 42 00:01:54.980 --> 00:01:56.153 Get a cursor. 43 00:01:58.660 --> 00:02:02.040 Then execute our SQL, and this is going to be, 44 00:02:02.040 --> 00:02:07.040 update the books table, set the read to one, 45 00:02:08.630 --> 00:02:13.140 where the name is equal to a question mark. 46 00:02:13.140 --> 00:02:16.703 Remember, you must not use f-strings here, 47 00:02:18.070 --> 00:02:19.890 because that opens you up 48 00:02:19.890 --> 00:02:22.610 for potential SQL injection attacks. 49 00:02:22.610 --> 00:02:26.560 So what we're gonna put here is a tuple of name. 50 00:02:26.560 --> 00:02:28.570 And notice that, in order to make a tuple, 51 00:02:28.570 --> 00:02:31.940 we have to include the quote, the brackets, sorry, 52 00:02:31.940 --> 00:02:35.660 as well as the comma, in order to say this is a tuple, 53 00:02:35.660 --> 00:02:38.510 it's not a mathematical thing 54 00:02:38.510 --> 00:02:40.360 that we want to evaluate first. 55 00:02:40.360 --> 00:02:43.500 And certainly, if you don't put the brackets in 56 00:02:43.500 --> 00:02:45.618 and you just do this, it thinks that this is an entirely 57 00:02:45.618 --> 00:02:50.618 new argument to the execute method, which we don't wanna do. 58 00:02:51.040 --> 00:02:53.790 We wanna make sure it's a tuple, so we do it like this. 59 00:02:55.200 --> 00:02:57.400 Now that we've ran this, we want to make sure that 60 00:02:57.400 --> 00:02:59.520 these changes get saved to disc. 61 00:02:59.520 --> 00:03:04.083 So connection.commit and finally connection.close, 62 00:03:04.920 --> 00:03:05.753 and that's it. 63 00:03:05.753 --> 00:03:07.150 Now we've updated our book, 64 00:03:07.150 --> 00:03:11.483 set the red flag to one, where the name was equal to name. 65 00:03:14.170 --> 00:03:15.700 Going over to delete_book. 66 00:03:15.700 --> 00:03:17.310 I think you can also implement this. 67 00:03:17.310 --> 00:03:20.090 For, what we have to do is pretty much the same. 68 00:03:20.090 --> 00:03:22.883 Once again, pause the video and give this a go. 69 00:03:24.440 --> 00:03:26.650 Now, I'm sure you really managed to get this, 70 00:03:26.650 --> 00:03:28.350 what I'm going to do is copy this boilerplate, 71 00:03:28.350 --> 00:03:30.760 not something you should be doing very often. 72 00:03:30.760 --> 00:03:34.913 But, instead of updating, we're gonna say DELETE FROM books, 73 00:03:36.010 --> 00:03:38.960 where the name is this question mark. 74 00:03:38.960 --> 00:03:40.270 And once again, the name gets passed in 75 00:03:40.270 --> 00:03:41.320 as an argument there. 76 00:03:42.480 --> 00:03:44.900 Then we commit once again and we close. 77 00:03:44.900 --> 00:03:47.050 We don't have to fetch any rules or anything, 78 00:03:47.050 --> 00:03:48.910 'cause there's nothing for us to return 79 00:03:48.910 --> 00:03:50.403 from this DELETE statement. 80 00:03:52.350 --> 00:03:56.070 And that's it, we've got all the data 81 00:03:56.070 --> 00:03:58.200 in and out of our database that we needed. 82 00:03:58.200 --> 00:04:02.250 We can go back to app.py and all we have to do, 83 00:04:02.250 --> 00:04:06.360 is potentially update this book['read'] flag here. 84 00:04:06.360 --> 00:04:07.203 We could say, 85 00:04:09.700 --> 00:04:14.700 YES, if the book's read status is true, otherwise NO. 86 00:04:16.600 --> 00:04:20.480 But what you didn't know, is that if zero 87 00:04:21.710 --> 00:04:26.710 actually evaluates to false and if one evaluates to true. 88 00:04:27.470 --> 00:04:32.290 So if book['read'] is one, contains the integer one, 89 00:04:32.290 --> 00:04:33.530 we're going to get YES. 90 00:04:33.530 --> 00:04:36.890 If it contains the integer zero, we're going to get NO. 91 00:04:36.890 --> 00:04:39.360 So we don't actually have to change this at all. 92 00:04:39.360 --> 00:04:42.990 Python automatically treats one and zero as true and false 93 00:04:42.990 --> 00:04:44.563 as well, which is pretty handy. 94 00:04:45.570 --> 00:04:48.030 So we don't actually have to make any changes at all 95 00:04:48.030 --> 00:04:50.480 to our app.py, which tells us that we've done 96 00:04:50.480 --> 00:04:54.360 an excellent job isolating our data storage device, 97 00:04:54.360 --> 00:04:57.160 our database, from our business logic. 98 00:04:57.160 --> 00:04:59.557 And I keep going on and on about this interface, 99 00:04:59.557 --> 00:05:02.110 because it's really important part of programming, 100 00:05:02.110 --> 00:05:05.580 to be able to decouple your different components, 101 00:05:05.580 --> 00:05:09.190 and make sure that they can operate without knowing 102 00:05:09.190 --> 00:05:12.090 how the other one is implemented, that's really important. 103 00:05:12.960 --> 00:05:15.630 Let's go ahead and run our app. 104 00:05:15.630 --> 00:05:17.730 And then we can look at adding a new book. 105 00:05:21.890 --> 00:05:23.873 Ooh, and we get an error. 106 00:05:25.460 --> 00:05:27.180 We get an error, that says, 107 00:05:27.180 --> 00:05:31.670 IntegrityError: UNIQUE constraint failed on books.name. 108 00:05:31.670 --> 00:05:34.140 That means that a book with this name 109 00:05:34.140 --> 00:05:36.780 already existed in our database. 110 00:05:36.780 --> 00:05:37.800 Interesting error. 111 00:05:37.800 --> 00:05:40.110 Of course, we know that this should be an error, 112 00:05:40.110 --> 00:05:42.160 we don't want the same book twice. 113 00:05:42.160 --> 00:05:45.330 So, what happened is that our data.db was existing 114 00:05:45.330 --> 00:05:48.110 from the last time we ran this and I entered the same book. 115 00:05:48.110 --> 00:05:50.480 So, if you want to get rid of this error just temporarily, 116 00:05:50.480 --> 00:05:53.850 delete data.db and run your app again. 117 00:05:53.850 --> 00:05:56.363 Then, you can enter Clean Code. 118 00:05:59.810 --> 00:06:01.740 Then you can list all your books now 119 00:06:01.740 --> 00:06:04.610 and that gives you Clean Code by Robert C. Martin. 120 00:06:04.610 --> 00:06:06.693 You can, of course, mark a book as read, 121 00:06:08.840 --> 00:06:10.080 then you can list the books again 122 00:06:10.080 --> 00:06:12.480 and it gives you Clean Code by Robert C. Martin. 123 00:06:13.710 --> 00:06:14.833 You can delete a book, 124 00:06:16.330 --> 00:06:18.230 not that you'd want to delete this book, 125 00:06:18.230 --> 00:06:21.083 and then you get nothing when you list the books back again. 126 00:06:22.070 --> 00:06:25.793 So we've made our programme work with a database, 127 00:06:25.793 --> 00:06:29.040 a SQLite database, which is very very similar 128 00:06:29.040 --> 00:06:31.350 to a Postgres or a MySQL database. 129 00:06:31.350 --> 00:06:34.090 The way we interact with it, using SQL, 130 00:06:34.090 --> 00:06:35.800 is pretty much the same. 131 00:06:35.800 --> 00:06:39.460 Postgres and MySQL are more powerful, 132 00:06:39.460 --> 00:06:41.083 they do more things for you, 133 00:06:41.930 --> 00:06:43.860 and they also have a bit more functionality, 134 00:06:43.860 --> 00:06:46.890 more different types of data you can store, and so forth. 135 00:06:46.890 --> 00:06:49.570 But really, what we've covered is the same amongst 136 00:06:49.570 --> 00:06:52.143 all major databases, which is really cool. 137 00:06:53.390 --> 00:06:56.760 Of course, we haven't gotten rid of the insert error, 138 00:06:56.760 --> 00:06:59.250 when we insert something that already exists, 139 00:06:59.250 --> 00:07:00.690 but you could do that. 140 00:07:00.690 --> 00:07:04.850 You could put a try accept here on a integrity error, 141 00:07:04.850 --> 00:07:07.300 and just print out something that says, 142 00:07:07.300 --> 00:07:08.700 we can put out your book 143 00:07:08.700 --> 00:07:11.360 or you can return false potentially, 144 00:07:11.360 --> 00:07:16.360 and then in your app.py, say, if this succeeded, 145 00:07:16.490 --> 00:07:18.360 print nothing and then if it didn't succeed, print, 146 00:07:18.360 --> 00:07:20.930 you've already got that book in your database. 147 00:07:20.930 --> 00:07:24.200 Feel free to extend this as far as you want, 148 00:07:24.200 --> 00:07:26.060 and of course ask questions in the course journey 149 00:07:26.060 --> 00:07:28.830 if you encounter any issues, we'd be happy to help you. 150 00:07:28.830 --> 00:07:30.480 I'm not going to bore you any longer 151 00:07:30.480 --> 00:07:32.010 with continuing with this project, 152 00:07:32.010 --> 00:07:34.850 we can move on to better things. 153 00:07:34.850 --> 00:07:36.780 So with that said, I'll see you on the next video. 154 00:07:36.780 --> 00:07:39.700 We're gonna talk briefly about one more thing in SQL 155 00:07:39.700 --> 00:07:41.130 that we didn't get to use in this project 156 00:07:41.130 --> 00:07:43.620 but still pretty important, and then we can go over 157 00:07:43.620 --> 00:07:45.430 to the following section. 158 00:07:45.430 --> 00:07:46.330 I'll see you soon.