WEBVTT 1 00:00:00.160 --> 00:00:00.993 Hi guys! 2 00:00:00.993 --> 00:00:01.826 And welcome back! 3 00:00:01.826 --> 00:00:02.659 In this video, 4 00:00:02.659 --> 00:00:03.570 we're going to learn about, 5 00:00:03.570 --> 00:00:05.540 what is a cursor? 6 00:00:05.540 --> 00:00:07.050 This is a pretty important concept 7 00:00:07.050 --> 00:00:09.891 because there's a few different types of cursors, 8 00:00:09.891 --> 00:00:11.930 when you use databases. 9 00:00:11.930 --> 00:00:13.340 You've got SQLite cursors, 10 00:00:13.340 --> 00:00:14.480 and database cursors. 11 00:00:14.480 --> 00:00:16.720 So it's important to know the difference between them, 12 00:00:16.720 --> 00:00:19.160 as well as understand what they're for. 13 00:00:19.160 --> 00:00:21.150 So what is a cursor? 14 00:00:21.150 --> 00:00:23.260 A cursor is a structure 15 00:00:23.260 --> 00:00:26.550 that allows us to traverse a result set. 16 00:00:26.550 --> 00:00:28.530 Very esoteric description there, 17 00:00:28.530 --> 00:00:29.830 maybe not all that helpful. 18 00:00:29.830 --> 00:00:31.380 Database cursors 19 00:00:31.380 --> 00:00:34.770 are a thing in databases in RDBMS 20 00:00:34.770 --> 00:00:37.010 that we can use to tell the database 21 00:00:37.010 --> 00:00:39.730 that we want this result from the database, 22 00:00:39.730 --> 00:00:41.430 or this amount of data. 23 00:00:41.430 --> 00:00:45.320 But we wanna get that data one piece at a time. 24 00:00:45.320 --> 00:00:47.020 And so it allows us to, you know, 25 00:00:47.020 --> 00:00:49.710 tell the database we want to get this table of data 26 00:00:49.710 --> 00:00:52.470 but only give me the first 100 rows. 27 00:00:52.470 --> 00:00:55.380 And then we can ask it for the next 100 rows and so on. 28 00:00:55.380 --> 00:00:58.360 So it allows us to traverse the results, 29 00:00:58.360 --> 00:01:01.394 loading the results as we go along. 30 00:01:01.394 --> 00:01:04.600 SQLite cursors, though are a different thing. 31 00:01:04.600 --> 00:01:06.100 In, SQLite cursors, 32 00:01:06.100 --> 00:01:08.650 we load all the results. 33 00:01:08.650 --> 00:01:11.310 But, they're there to sort of help us 34 00:01:11.310 --> 00:01:13.690 go over them a bit more easily. 35 00:01:13.690 --> 00:01:16.480 So this is essentially what a cursor looks like. 36 00:01:16.480 --> 00:01:20.040 This is a table that we've loaded using SQLite. 37 00:01:20.040 --> 00:01:21.890 And the cursor is the little arrow. 38 00:01:21.890 --> 00:01:23.890 So we can use the cursor 39 00:01:23.890 --> 00:01:26.210 to sort of go through the results, 40 00:01:26.210 --> 00:01:27.940 one by one, or 10 by 10. 41 00:01:27.940 --> 00:01:29.050 However we want, 42 00:01:29.050 --> 00:01:31.550 we can sort of go through them like that. 43 00:01:31.550 --> 00:01:32.550 Okay! 44 00:01:32.550 --> 00:01:33.960 So it's very important to distinguish 45 00:01:33.960 --> 00:01:35.970 between database cursors 46 00:01:35.970 --> 00:01:38.470 that are things in the RDBMS, 47 00:01:38.470 --> 00:01:40.040 which SQLite does not have, 48 00:01:40.040 --> 00:01:41.900 or at least we're not gonna use them. 49 00:01:41.900 --> 00:01:45.060 That allow us to load results bit by bit, 50 00:01:45.060 --> 00:01:47.170 so that we don't fetch the entire table 51 00:01:47.170 --> 00:01:49.060 which could potentially be millions of rows 52 00:01:49.060 --> 00:01:50.550 at once from the database. 53 00:01:50.550 --> 00:01:52.000 So there's a difference between that 54 00:01:52.000 --> 00:01:53.230 and the SQLite cursor 55 00:01:53.230 --> 00:01:55.648 which is only there to help our code, 56 00:01:55.648 --> 00:01:57.330 navigate the results. 57 00:01:57.330 --> 00:01:59.668 It doesn't do anything to help us, 58 00:01:59.668 --> 00:02:03.610 you know, not have to load all the results at once. 59 00:02:03.610 --> 00:02:05.020 We're going to understand more 60 00:02:05.020 --> 00:02:05.853 about what this means, 61 00:02:05.853 --> 00:02:08.200 later on when we learn more about database cursors. 62 00:02:08.200 --> 00:02:09.033 But for now, 63 00:02:09.033 --> 00:02:10.390 just bear with me on this one. 64 00:02:10.390 --> 00:02:12.430 So for SQLite cursors, 65 00:02:12.430 --> 00:02:14.520 they represent that arrow, 66 00:02:14.520 --> 00:02:16.750 that pointer that we saw a moment ago. 67 00:02:16.750 --> 00:02:19.290 They help us iterate over the results 68 00:02:19.290 --> 00:02:22.100 or go over them one by one. 69 00:02:22.100 --> 00:02:25.450 Therefore, using them in for loops like this, 70 00:02:25.450 --> 00:02:26.480 sort of makes sense, 71 00:02:26.480 --> 00:02:27.930 when you have a cursor. 72 00:02:27.930 --> 00:02:28.930 You get, you know, 73 00:02:28.930 --> 00:02:32.010 you create your cursor with connection.cursor 74 00:02:32.010 --> 00:02:33.660 then you use that cursor 75 00:02:33.660 --> 00:02:36.670 to execute a query such as says, SELECT. 76 00:02:36.670 --> 00:02:38.590 And then you can use the cursor 77 00:02:38.590 --> 00:02:41.380 that has loaded that data 78 00:02:41.380 --> 00:02:43.100 in a for loop like this. 79 00:02:43.100 --> 00:02:45.740 And that gives you each row one by one. 80 00:02:45.740 --> 00:02:47.730 So essentially, you can think of this for loop 81 00:02:47.730 --> 00:02:50.170 as that arrow that is giving you each row, 82 00:02:50.170 --> 00:02:52.820 or pointing to each row, one at a time. 83 00:02:52.820 --> 00:02:54.720 So when we're doing something like this, 84 00:02:54.720 --> 00:02:56.960 a SELECT and then an iteration. 85 00:02:56.960 --> 00:02:58.070 A cursor makes sense. 86 00:02:58.070 --> 00:02:59.060 It's sort of useful, 87 00:02:59.060 --> 00:03:00.730 we can iterate over the cursor. 88 00:03:00.730 --> 00:03:01.719 We don't have to, you know, 89 00:03:01.719 --> 00:03:03.130 do anything else, 90 00:03:03.130 --> 00:03:05.010 like build a list out of the results 91 00:03:05.010 --> 00:03:06.260 or anything like that. 92 00:03:06.260 --> 00:03:08.170 So that is interesting. 93 00:03:08.170 --> 00:03:11.840 But when we're not iterating over the cursor, 94 00:03:11.840 --> 00:03:14.280 such as, if we try to do an INSERT, 95 00:03:14.280 --> 00:03:15.480 then SQLite cursor 96 00:03:15.480 --> 00:03:17.060 seem kind of pointless, right? 97 00:03:17.060 --> 00:03:18.070 Here in this code, 98 00:03:18.070 --> 00:03:20.110 we are creating our cursor, 99 00:03:20.110 --> 00:03:22.520 And then we're executing an INSERT. 100 00:03:22.520 --> 00:03:24.610 And we're never iterating over the cursor. 101 00:03:24.610 --> 00:03:25.700 So, this arrow, 102 00:03:25.700 --> 00:03:27.270 is really not pointing towards anything, 103 00:03:27.270 --> 00:03:28.530 because an INSERT query 104 00:03:28.530 --> 00:03:30.773 doesn't even return anything useful to us. 105 00:03:30.773 --> 00:03:32.453 And so in this case, 106 00:03:32.453 --> 00:03:35.160 the cursor does seem a bit pointless. 107 00:03:35.160 --> 00:03:36.300 And some people suggest that 108 00:03:36.300 --> 00:03:39.290 this is really just a small problem in SQLite. 109 00:03:39.290 --> 00:03:41.870 This cursor shouldn't really exist in all cases. 110 00:03:41.870 --> 00:03:44.880 I believe that the reason why we use cursors 111 00:03:44.880 --> 00:03:48.060 for both selecting and inserting in SQLite 112 00:03:48.060 --> 00:03:49.660 is just for consistency. 113 00:03:49.660 --> 00:03:51.050 To make your code more consistent. 114 00:03:51.050 --> 00:03:52.925 Every query uses a cursor, 115 00:03:52.925 --> 00:03:54.500 and that's it. 116 00:03:54.500 --> 00:03:56.440 Otherwise, you'd need some queries using a cursor 117 00:03:56.440 --> 00:03:57.760 or some not using a cursor. 118 00:03:57.760 --> 00:03:58.720 It could be a bit confusing, 119 00:03:58.720 --> 00:04:00.270 especially for new coders. 120 00:04:00.270 --> 00:04:03.180 So this is just the way it's done in SQLite. 121 00:04:03.180 --> 00:04:04.810 However, you may have noticed earlier on 122 00:04:04.810 --> 00:04:05.830 in a previous presentation 123 00:04:05.830 --> 00:04:08.130 that we didn't use a cursor. 124 00:04:08.130 --> 00:04:12.080 And that's because when we do connection.execute 125 00:04:12.080 --> 00:04:13.610 that creates a cursor for us. 126 00:04:13.610 --> 00:04:15.030 And that saves us some typing. 127 00:04:15.030 --> 00:04:15.970 And it makes a bit more sense 128 00:04:15.970 --> 00:04:17.490 when doing things like inserting. 129 00:04:17.490 --> 00:04:18.600 We've seen this earlier on, 130 00:04:18.600 --> 00:04:19.730 here for example, 131 00:04:19.730 --> 00:04:21.280 we're not creating a cursor 132 00:04:21.280 --> 00:04:23.080 with connection.cursor 133 00:04:23.080 --> 00:04:25.630 We're just doing connection.execute 134 00:04:25.630 --> 00:04:27.330 And sqlite3 behind the scenes 135 00:04:27.330 --> 00:04:29.700 automatically creates a cursor for us, 136 00:04:29.700 --> 00:04:32.700 and executes the query using the cursor. 137 00:04:32.700 --> 00:04:33.879 But we don't have to do that ourselves. 138 00:04:33.879 --> 00:04:35.450 And again, it makes a bit more sense 139 00:04:35.450 --> 00:04:36.920 when doing things like CREATE TABLE 140 00:04:36.920 --> 00:04:38.083 or inserting data. 141 00:04:39.240 --> 00:04:41.220 You can create the cursor yourself. 142 00:04:41.220 --> 00:04:43.590 And I would normally recommend doing that. 143 00:04:43.590 --> 00:04:45.040 So it's a bit clearer 144 00:04:45.040 --> 00:04:47.360 when you want to use the cursor. 145 00:04:47.360 --> 00:04:49.130 And when you don't want to use the cursor, 146 00:04:49.130 --> 00:04:51.070 I would normally recommend you do this 147 00:04:51.070 --> 00:04:52.450 so that it's a bit clearer, 148 00:04:52.450 --> 00:04:54.480 that you are not getting a cursor 149 00:04:54.480 --> 00:04:56.602 that is not gonna be used. 150 00:04:56.602 --> 00:04:57.435 All right! 151 00:04:57.435 --> 00:04:58.700 So I hope this helps understand 152 00:04:58.700 --> 00:05:00.170 what cursors are for. 153 00:05:00.170 --> 00:05:01.284 Gives you a little bit of information. 154 00:05:01.284 --> 00:05:03.830 It's not going to be an obvious 155 00:05:03.830 --> 00:05:06.000 until you start using them a bit more. 156 00:05:06.000 --> 00:05:06.990 But I hope this helps. 157 00:05:06.990 --> 00:05:10.210 And let's go and write some of this code in the next video. 158 00:05:10.210 --> 00:05:11.160 I'll see you there.