WEBVTT 1 00:00:00.240 --> 00:00:01.470 Hi guys and welcome back. 2 00:00:01.470 --> 00:00:03.010 In this video we're going to be working with 3 00:00:03.010 --> 00:00:07.530 Python and SQLite to retrieve data from our data base. 4 00:00:07.530 --> 00:00:08.900 We've already looked at the presentation 5 00:00:08.900 --> 00:00:11.740 and we know how the select statement kind of works. 6 00:00:11.740 --> 00:00:13.470 We're now going to go in here and 7 00:00:13.470 --> 00:00:17.250 replace this get entries function with our new one. 8 00:00:17.250 --> 00:00:20.150 So, as we know, we can create cursors 9 00:00:20.150 --> 00:00:22.420 to hold the result sets, 10 00:00:22.420 --> 00:00:23.540 but we've also learned that 11 00:00:23.540 --> 00:00:26.610 connection execute does that for us automatically. 12 00:00:26.610 --> 00:00:28.100 So, what do we have to do? 13 00:00:28.100 --> 00:00:30.170 Well, lets start at the beginning. 14 00:00:30.170 --> 00:00:32.160 The first thing is that we don't need 15 00:00:32.160 --> 00:00:34.500 the context manager when were doing get entries 16 00:00:34.500 --> 00:00:36.640 because we're not changing the data base at all. 17 00:00:36.640 --> 00:00:39.950 So therefore there is nothing to commit or roll back. 18 00:00:39.950 --> 00:00:41.860 So we don't need that in here. 19 00:00:41.860 --> 00:00:46.360 The next step is creating the cursor or getting the results. 20 00:00:46.360 --> 00:00:49.520 We know that we can do connection.execute 21 00:00:49.520 --> 00:00:52.990 and then something like select star from entries 22 00:00:52.990 --> 00:00:56.160 in order to run the query and we know that 23 00:00:56.160 --> 00:01:00.070 connection.execute automatically creates a cursor for us. 24 00:01:00.070 --> 00:01:01.660 What we haven't discussed is that it actually 25 00:01:01.660 --> 00:01:05.660 returns that cursor so you can do something like this, 26 00:01:05.660 --> 00:01:10.270 cursor equal connection.execute select star from entries. 27 00:01:10.270 --> 00:01:11.170 If you do this, 28 00:01:11.170 --> 00:01:12.960 connection and execute creates the cursor, 29 00:01:12.960 --> 00:01:15.270 uses it to run your query, 30 00:01:15.270 --> 00:01:17.260 loads the results into the cursor 31 00:01:17.260 --> 00:01:18.450 and then gives it back to you 32 00:01:18.450 --> 00:01:20.190 so that you can assign it to a variable, 33 00:01:20.190 --> 00:01:21.520 so it's pretty handy. 34 00:01:21.520 --> 00:01:23.900 The other option, instead of this, 35 00:01:23.900 --> 00:01:25.800 was to do something like this, 36 00:01:25.800 --> 00:01:28.450 cursor equal connection.cursor 37 00:01:28.450 --> 00:01:30.510 and then cursor.execute, 38 00:01:30.510 --> 00:01:33.090 select star from entries. 39 00:01:33.090 --> 00:01:35.470 Again you can do either this 40 00:01:35.470 --> 00:01:36.570 or this one. 41 00:01:36.570 --> 00:01:38.090 You don't need to do both 42 00:01:38.090 --> 00:01:39.780 I believe this ones a bit shorter, 43 00:01:39.780 --> 00:01:42.800 and but this one is a little bit clearer of what's going on. 44 00:01:42.800 --> 00:01:46.150 Totally up to you which one you want to use. 45 00:01:46.150 --> 00:01:49.020 I'm gonna go ahead and keep the connection.execute one 46 00:01:49.020 --> 00:01:51.350 because I feel like when you are signing into a variable 47 00:01:51.350 --> 00:01:55.160 it's pretty clear that it is giving you a cursor back. 48 00:01:55.160 --> 00:01:56.710 So now that we've got the cursor 49 00:01:56.710 --> 00:01:58.590 and we've executed the query, 50 00:01:58.590 --> 00:02:01.390 it is pointing at the very first row of results 51 00:02:01.390 --> 00:02:03.400 returned by the data base. 52 00:02:03.400 --> 00:02:05.290 So we just need to use the results. 53 00:02:05.290 --> 00:02:07.800 We can use the cursor to either, 54 00:02:07.800 --> 00:02:10.060 fetch one row and load it 55 00:02:10.060 --> 00:02:12.250 and give it to us as a variable 56 00:02:12.250 --> 00:02:14.450 so we can do something like cursor.fetchone, 57 00:02:15.700 --> 00:02:18.590 and that is going to essentially give us the first row 58 00:02:18.590 --> 00:02:21.510 and move the pointing arrow to the second row. 59 00:02:21.510 --> 00:02:24.230 So if we do cursor.fetchone again, 60 00:02:24.230 --> 00:02:25.940 that is going to give us the second row 61 00:02:25.940 --> 00:02:27.820 and move the arrow to the third row. 62 00:02:27.820 --> 00:02:30.030 Of course doing this gives us the value back, 63 00:02:30.030 --> 00:02:30.970 we need to either print it, 64 00:02:30.970 --> 00:02:33.660 or assign it to a variable, or return it, etc. 65 00:02:33.660 --> 00:02:35.300 So we're not going to be doing that because 66 00:02:35.300 --> 00:02:36.870 what we want to do here is we want to 67 00:02:36.870 --> 00:02:39.370 allow the caller of this function, 68 00:02:39.370 --> 00:02:41.070 which is app.py, 69 00:02:41.070 --> 00:02:44.170 to then iterate over it in a for-loop. 70 00:02:44.170 --> 00:02:47.200 So we don't want to be getting individual rows. 71 00:02:47.200 --> 00:02:49.060 Alternatively you can do fetchall 72 00:02:50.120 --> 00:02:53.003 and that gets all of the results from the cursor 73 00:02:53.003 --> 00:02:54.550 puts them into a list 74 00:02:54.550 --> 00:02:56.300 and then you can either return that 75 00:02:56.300 --> 00:02:58.210 or put it into a variable, etc. 76 00:02:58.210 --> 00:03:02.100 So maybe this is what we want to do, return that. 77 00:03:02.100 --> 00:03:03.840 That is going to, again, get all the results, 78 00:03:03.840 --> 00:03:05.670 put them in a list and give them back to the caller. 79 00:03:05.670 --> 00:03:07.280 Which means that get entries 80 00:03:07.280 --> 00:03:10.270 down here would become that list, 81 00:03:10.270 --> 00:03:13.040 then it would get passed to view entries. 82 00:03:13.040 --> 00:03:15.000 So entries would become that list 83 00:03:15.000 --> 00:03:18.570 and then we can iterate over the entries and entries 84 00:03:18.570 --> 00:03:20.890 printing out each piece of information. 85 00:03:20.890 --> 00:03:22.180 But as we learned earlier on 86 00:03:22.180 --> 00:03:25.606 a cursor is precisely for 87 00:03:25.606 --> 00:03:27.510 iterating over the results. 88 00:03:27.510 --> 00:03:31.580 So we actually can just return the cursor, 89 00:03:31.580 --> 00:03:35.170 we don't need to worry about fetching the rows, 90 00:03:35.170 --> 00:03:38.870 it can do that for us as it goes over it in the for-loop 91 00:03:38.870 --> 00:03:40.910 so it's smart enough to do that for us. 92 00:03:40.910 --> 00:03:42.900 As you iterate over a cursor, 93 00:03:42.900 --> 00:03:44.750 which is what this would be, 94 00:03:44.750 --> 00:03:49.033 each entry in the for-loop is one row in the results. 95 00:03:50.080 --> 00:03:53.110 It's important to know that each row in the results actually 96 00:03:53.110 --> 00:03:54.660 is going to be a tuple, 97 00:03:54.660 --> 00:03:55.990 it's not going to be a dictionally. 98 00:03:55.990 --> 00:03:57.660 We're going to have to change this 99 00:03:57.660 --> 00:04:00.720 so that instead of accessing the date and the content 100 00:04:00.720 --> 00:04:04.300 we access index one and zero. 101 00:04:04.300 --> 00:04:06.700 Remember that the content is the 102 00:04:06.700 --> 00:04:08.480 first column in our data base, 103 00:04:08.480 --> 00:04:10.570 so therefore, the select statement is going to retrieve it 104 00:04:10.570 --> 00:04:11.720 in that order. 105 00:04:11.720 --> 00:04:15.880 Entry zero is the content, entry one is the date. 106 00:04:15.880 --> 00:04:17.220 Going back to the database.py, 107 00:04:17.220 --> 00:04:19.550 there is a further improvement, 108 00:04:19.550 --> 00:04:22.070 and I'm making air quotes here, I know you can't see me, 109 00:04:22.070 --> 00:04:26.760 but you could just return this like that. 110 00:04:26.760 --> 00:04:29.600 I think that when you do something like this, it's shorter, 111 00:04:29.600 --> 00:04:32.070 but its also become a little less obvious that 112 00:04:32.070 --> 00:04:33.920 you're actually getting a cursor back 113 00:04:33.920 --> 00:04:36.490 so I would actually advise against doing this. 114 00:04:36.490 --> 00:04:38.500 I think you should either do this, 115 00:04:38.500 --> 00:04:40.650 to make it clear that you're getting back a cursor, 116 00:04:40.650 --> 00:04:42.730 or you should create your cursor yourself 117 00:04:42.730 --> 00:04:44.740 and then return the cursor. 118 00:04:44.740 --> 00:04:46.050 This is a perfectly valid option, 119 00:04:46.050 --> 00:04:47.290 I think this is clear enough 120 00:04:47.290 --> 00:04:49.570 so I'm going to stick with that. 121 00:04:49.570 --> 00:04:53.090 We can make SQlite give us a dictionary like result 122 00:04:53.090 --> 00:04:57.280 instead of a tuple when we are executing a select statement 123 00:04:57.280 --> 00:04:59.680 and so that we can go back to our for-loop 124 00:04:59.680 --> 00:05:00.720 and access the dates 125 00:05:00.720 --> 00:05:02.800 and the content as we have before. 126 00:05:02.800 --> 00:05:04.650 So that's something we can do 127 00:05:04.650 --> 00:05:06.900 and all you have to do in order to do that is 128 00:05:06.900 --> 00:05:09.740 to tell the connection that 129 00:05:09.740 --> 00:05:12.830 when it is getting rows from the database, 130 00:05:12.830 --> 00:05:16.860 to not use the default method of getting rows 131 00:05:16.860 --> 00:05:19.660 and instead use a different way of doing them. 132 00:05:19.660 --> 00:05:20.493 In order to do that 133 00:05:20.493 --> 00:05:24.250 we have to tell SQlite how to construct each row. 134 00:05:24.250 --> 00:05:26.780 By default SQlite is going to construct a row 135 00:05:26.780 --> 00:05:29.010 just by getting a tuple, a python tuple, 136 00:05:29.010 --> 00:05:32.100 and putting each column of data into the tuple. 137 00:05:32.100 --> 00:05:35.530 That's why we end up with a cursor of tuples. 138 00:05:35.530 --> 00:05:38.010 But, if you wanted to get a cursor off something 139 00:05:38.010 --> 00:05:39.030 like a dictionally, 140 00:05:39.030 --> 00:05:41.490 then you can do connection.row underscore 141 00:05:41.490 --> 00:05:46.490 factory equal SQlite3.row with a capital 'R'. 142 00:05:46.670 --> 00:05:50.950 Now whenever SQlite3 is getting rows from your table 143 00:05:50.950 --> 00:05:54.800 it's going to use a SQlite row instead of a tuple 144 00:05:54.800 --> 00:05:58.210 as your holder of each row. 145 00:05:58.210 --> 00:05:59.940 That means that when we print them out, 146 00:05:59.940 --> 00:06:04.940 we can now, once again, access the date and the content. 147 00:06:05.570 --> 00:06:06.403 Totally optional, 148 00:06:06.403 --> 00:06:08.560 if you don't need to access the date on the content, 149 00:06:08.560 --> 00:06:11.600 or you don't want to access the string properties of a row 150 00:06:11.600 --> 00:06:14.490 and you're happy enough just using the indices 151 00:06:14.490 --> 00:06:17.250 like zero and one, then avoid this. 152 00:06:17.250 --> 00:06:19.010 Its going to be a little bit slower 153 00:06:19.010 --> 00:06:21.150 and it's going to be a little bit more confusing generally, 154 00:06:21.150 --> 00:06:23.830 so if you don't need the named axis, 155 00:06:23.830 --> 00:06:26.810 just don't do this and you'll be good to go. 156 00:06:26.810 --> 00:06:29.593 All right, lets run our code and see what happens. 157 00:06:31.470 --> 00:06:33.080 We've got here the app.py, 158 00:06:33.080 --> 00:06:35.230 now we can view the entries that are there already, 159 00:06:35.230 --> 00:06:37.580 the ones we had in our database before. 160 00:06:37.580 --> 00:06:38.440 And so you can see that 161 00:06:38.440 --> 00:06:39.800 we've added some earlier on 162 00:06:39.800 --> 00:06:41.910 but we can add the new ones as well. 163 00:06:41.910 --> 00:06:43.440 And so what have we learned today, 164 00:06:43.440 --> 00:06:44.510 today we've learned 165 00:06:44.510 --> 00:06:49.080 how to use SQlite3.row 166 00:06:49.080 --> 00:06:53.363 to get named access to row fields. 167 00:06:54.490 --> 00:06:58.580 And the date is the second of January, 2020. 168 00:06:58.580 --> 00:06:59.810 Its actually not but, you know, 169 00:06:59.810 --> 00:07:01.320 I can't think of any other dates at the moment, 170 00:07:01.320 --> 00:07:03.260 I can't remember today's date, so 171 00:07:03.260 --> 00:07:04.760 and this is what we've got. 172 00:07:04.760 --> 00:07:06.390 Now we can go ahead and view the entries 173 00:07:06.390 --> 00:07:08.430 and you'll see that they come back 174 00:07:08.430 --> 00:07:10.470 in the order that we entered them 175 00:07:10.470 --> 00:07:13.500 and this one is there now, as well. 176 00:07:13.500 --> 00:07:14.490 So congratulations, 177 00:07:14.490 --> 00:07:17.930 we have created our first app that uses SQlite 178 00:07:17.930 --> 00:07:19.990 to store and retrieve data. 179 00:07:19.990 --> 00:07:21.640 So we started on this journey 180 00:07:21.640 --> 00:07:25.700 of being able to use databases from our Python programmes. 181 00:07:25.700 --> 00:07:27.360 There is so much more we can do 182 00:07:27.360 --> 00:07:28.930 and were going to be learning a lot 183 00:07:28.930 --> 00:07:30.710 of what you can do throughout this course. 184 00:07:30.710 --> 00:07:32.280 So I hope you guys are as excited as I am 185 00:07:32.280 --> 00:07:34.320 to go through the rest of this course content. 186 00:07:34.320 --> 00:07:35.370 Thank you for watching, 187 00:07:35.370 --> 00:07:37.070 and ill see you in the next video.