WEBVTT 1 00:00:00.230 --> 00:00:01.610 Hi and welcome back. 2 00:00:01.610 --> 00:00:03.840 In this video, we're going to be adding select 3 00:00:03.840 --> 00:00:06.696 statements to our milestone project. 4 00:00:06.696 --> 00:00:10.070 First of all, let's remember to delete this milestone 5 00:00:10.070 --> 00:00:13.470 or books file, here because we don't need it any longer. 6 00:00:13.470 --> 00:00:16.080 Just to make sure that it gives us an error 7 00:00:16.080 --> 00:00:19.260 when we try to use it, we're not misusing it anywhere. 8 00:00:19.260 --> 00:00:22.690 Then let's go down to our get all books function. 9 00:00:22.690 --> 00:00:25.120 And notice how now, it's giving us two errors. 10 00:00:25.120 --> 00:00:27.570 Trying to open the books file that no longer exists 11 00:00:27.570 --> 00:00:29.830 and it shouldn't, because we don't want to use it. 12 00:00:29.830 --> 00:00:32.430 And also it's using the json module, which it shouldn't. 13 00:00:32.430 --> 00:00:34.593 Because again, we're no longer using json. 14 00:00:35.685 --> 00:00:38.450 We want to retrieve all the books 15 00:00:38.450 --> 00:00:41.023 from our database and from our table. 16 00:00:42.100 --> 00:00:44.565 You know how to do all of this, 17 00:00:44.565 --> 00:00:48.610 except for actually getting the books back. 18 00:00:48.610 --> 00:00:51.480 So, you know how to set up the connection, get a cursor, 19 00:00:51.480 --> 00:00:52.640 execute the the query that will 20 00:00:52.640 --> 00:00:56.690 select all our books, commit, and close. 21 00:00:56.690 --> 00:00:59.000 I'd encourage you to give this a go, now. 22 00:00:59.000 --> 00:01:01.800 Just implement as much as you know about this function 23 00:01:01.800 --> 00:01:03.190 and then we'll explain exactly what 24 00:01:03.190 --> 00:01:05.555 we're missing and how it works. 25 00:01:05.555 --> 00:01:07.320 I hope you got that. 26 00:01:07.320 --> 00:01:09.110 All we have to do, is, of course, 27 00:01:09.110 --> 00:01:10.860 first of all, open a connection 28 00:01:13.036 --> 00:01:16.510 Make sure to use the same date file as before 29 00:01:16.510 --> 00:01:19.193 so that all of these connections go to the same file. 30 00:01:20.260 --> 00:01:21.353 Get a cursor, 31 00:01:23.960 --> 00:01:27.020 then execute our query, which is going 32 00:01:27.020 --> 00:01:30.780 to be select, start from books. 33 00:01:30.780 --> 00:01:32.090 And that's going to give us the name, 34 00:01:32.090 --> 00:01:34.453 the author and the read status. 35 00:01:36.430 --> 00:01:41.430 Then we can commit, but of course 36 00:01:41.750 --> 00:01:44.260 we've not actually written anything to our database. 37 00:01:44.260 --> 00:01:46.330 We've just read something from it. 38 00:01:46.330 --> 00:01:49.160 So there's nothing to save to the disc. 39 00:01:49.160 --> 00:01:52.783 Therefore, we don't need to commit in this case. 40 00:01:53.950 --> 00:01:57.993 We can just go straight to connection dot close. 41 00:01:57.993 --> 00:02:02.993 Now, we've not saved the output of this to any variable. 42 00:02:04.870 --> 00:02:06.723 So, as soon as we close the connection of course, 43 00:02:06.723 --> 00:02:08.480 this is going to get lost, we're not 44 00:02:08.480 --> 00:02:10.373 going to have access to our books. 45 00:02:11.860 --> 00:02:14.100 And here is the exciting part, 46 00:02:14.100 --> 00:02:15.730 or the interesting part about cursors. 47 00:02:15.730 --> 00:02:17.170 Remember when I explained about cursors 48 00:02:17.170 --> 00:02:19.020 that they are a pointer. 49 00:02:19.020 --> 00:02:21.200 When you make a select statement that starts at the very top 50 00:02:21.200 --> 00:02:25.300 of your table, and then moves over to the next row when you 51 00:02:25.300 --> 00:02:28.420 ask for the next row, and then go to the following one and 52 00:02:28.420 --> 00:02:31.240 so forth, until you get to the end of the table. 53 00:02:31.240 --> 00:02:34.940 Well, now that we've executed the query with a select, 54 00:02:34.940 --> 00:02:38.360 the cursor is at the top of the results returned, 55 00:02:38.360 --> 00:02:42.066 and we want to go down one by one in all of the rows 56 00:02:42.066 --> 00:02:44.840 until we reach the end of the table 57 00:02:44.840 --> 00:02:47.410 so that we get all of our rows back. 58 00:02:47.410 --> 00:02:50.610 The way we do that, is we say for example, books 59 00:02:50.610 --> 00:02:55.377 which is now our variable of books is cursor dot 60 00:02:55.377 --> 00:03:00.377 and notice how there are a few fetch methods in here. 61 00:03:01.580 --> 00:03:03.600 You've got fetchall, and that's 62 00:03:03.600 --> 00:03:05.060 going to give you all of the rows. 63 00:03:05.060 --> 00:03:09.290 Fetchmany, and that is an argument here, 64 00:03:09.290 --> 00:03:11.630 which is how many rows you want to fetch. 65 00:03:11.630 --> 00:03:14.690 And fetchone, which gives you the top row 66 00:03:14.690 --> 00:03:18.000 of your table of your results set only. 67 00:03:18.000 --> 00:03:19.900 So we're going to go for fetchall. 68 00:03:19.900 --> 00:03:22.610 And remember that our results are going to be the entire 69 00:03:22.610 --> 00:03:25.300 table, because we've selected star from books, 70 00:03:25.300 --> 00:03:28.550 which gives us all the columns, and we've not limited 71 00:03:28.550 --> 00:03:30.640 any number of rows, we don't know how to do that yet. 72 00:03:30.640 --> 00:03:32.250 We're gonna learn in a moment. 73 00:03:32.250 --> 00:03:35.310 So this is going to give us the entire table, cursor dot. 74 00:03:35.310 --> 00:03:37.583 Fetchall is going to give us all of the rows. 75 00:03:37.583 --> 00:03:41.810 If you wanted just one book, you could just fetchone, 76 00:03:41.810 --> 00:03:42.790 but that's just going to give you 77 00:03:42.790 --> 00:03:44.933 the top most book in your table. 78 00:03:46.141 --> 00:03:49.890 Now, this, and when you do fetchall, 79 00:03:49.890 --> 00:03:53.456 is going to give you essentially a list of topalls. 80 00:03:53.456 --> 00:03:57.890 Each topall is going to have a name, author, read status 81 00:03:57.890 --> 00:04:02.200 and then name, author, read status, and so forth. 82 00:04:02.200 --> 00:04:05.633 So when you do fetchall, you essentially get this list here. 83 00:04:06.650 --> 00:04:11.650 So we can return that, and then make a programme access 84 00:04:11.910 --> 00:04:14.360 the zeroth element of a topall, the first element 85 00:04:14.360 --> 00:04:16.960 of a topall, the second element of a topall, 86 00:04:16.960 --> 00:04:21.458 or we could convert these topalls that we are receiving, 87 00:04:21.458 --> 00:04:23.570 we're just going to bring them back actually. 88 00:04:23.570 --> 00:04:26.503 Convert these topalls we're receiving into dictionaries, 89 00:04:27.787 --> 00:04:29.968 so that the rest of our programme can still use them 90 00:04:29.968 --> 00:04:31.710 as it would normally, and I think this is the best 91 00:04:31.710 --> 00:04:33.320 thing to do, just make sure we are 92 00:04:33.320 --> 00:04:36.132 using consistent data structures. 93 00:04:36.132 --> 00:04:39.470 So again, what to do, is fetch them all, 94 00:04:39.470 --> 00:04:42.870 but, put this in a list comprehension. 95 00:04:42.870 --> 00:04:45.130 And then what we're going to do in this list comprehension, 96 00:04:45.130 --> 00:04:47.890 is put in a dictionary of name. 97 00:04:47.890 --> 00:04:50.980 It's going to be row zero. 98 00:04:50.980 --> 00:04:53.930 Notice how that's going to be the first element of the row. 99 00:04:55.030 --> 00:04:57.320 Author is going to be row one, 100 00:04:57.320 --> 00:05:00.050 and read is going to be row two. 101 00:05:00.050 --> 00:05:01.330 And I know that things are disappearing 102 00:05:01.330 --> 00:05:06.003 off the side of the screen, so let's just bring them back. 103 00:05:06.970 --> 00:05:08.593 I'm just going to delete this now. 104 00:05:10.430 --> 00:05:14.430 And now notice how we have a list comprehension that puts 105 00:05:14.430 --> 00:05:17.410 a dictionary as the first value here of our list 106 00:05:17.410 --> 00:05:20.300 comprehension into this new list 107 00:05:20.300 --> 00:05:24.223 where row is each of the elements in cursor dot fetchall. 108 00:05:25.114 --> 00:05:27.327 Notice that each of the elements is a topall, 109 00:05:27.327 --> 00:05:29.920 so we access row zero as the name, 110 00:05:29.920 --> 00:05:33.535 row one as the author, and row two as the read status. 111 00:05:33.535 --> 00:05:37.850 Hopefully this makes sense, as we do this, we'll end with a 112 00:05:37.850 --> 00:05:39.750 dictionary with a list of dictionaries, 113 00:05:39.750 --> 00:05:42.880 where each dictionary represents our book. 114 00:05:42.880 --> 00:05:45.220 Then at the end, after we close the connection, 115 00:05:45.220 --> 00:05:48.393 we can return books, and that's the list here. 116 00:05:49.571 --> 00:05:54.571 Now our app dot PUI, which was using book name, book read 117 00:05:55.930 --> 00:06:00.580 and book author, doesn't have to change at all because 118 00:06:01.768 --> 00:06:04.860 it's still using the dictionary in the background. 119 00:06:04.860 --> 00:06:07.580 Even though our database does not return dictionaries, 120 00:06:07.580 --> 00:06:10.490 the interphase that we've designed here has kept things 121 00:06:10.490 --> 00:06:13.180 consistent so that our business logic, 122 00:06:13.180 --> 00:06:16.050 our app dot PUI, doesn't have to change. 123 00:06:16.050 --> 00:06:17.900 And that's a really good thing to do. 124 00:06:19.035 --> 00:06:22.660 Now that we've selected all of the books, we can go and 125 00:06:22.660 --> 00:06:25.280 look at the next thing, which is figuring 126 00:06:25.280 --> 00:06:28.080 for specific books using the where statement. 127 00:06:28.080 --> 00:06:29.930 Let's look at that in the next video.