1 00:00:05,020 --> 00:00:06,470 So in the last few videos, 2 00:00:06,470 --> 00:00:09,490 we've been working towards creating a listbox class 3 00:00:09,490 --> 00:00:12,090 that can load its data from a database table 4 00:00:12,090 --> 00:00:13,990 and also tell other listboxes 5 00:00:13,990 --> 00:00:16,290 when they should refresh their data. 6 00:00:16,290 --> 00:00:18,410 Now, we had that working with a basic listbox 7 00:00:18,410 --> 00:00:22,060 by binding a function to a listbox select virtual event 8 00:00:22,060 --> 00:00:24,860 and you can see that here on line 107 9 00:00:24,860 --> 00:00:27,770 so the artistList is our bind and then listboxSelect 10 00:00:27,770 --> 00:00:29,980 to specify the virtual event. 11 00:00:29,980 --> 00:00:33,100 Now, when an item is selected in the artist listbox, 12 00:00:33,100 --> 00:00:35,800 the get_albums function is called. 13 00:00:35,800 --> 00:00:39,910 Now, looking at the get_albums function, 14 00:00:39,910 --> 00:00:42,050 you can see here it here starting on line 55, 15 00:00:42,050 --> 00:00:45,240 it retrieves the artist ID for the selected artist 16 00:00:45,240 --> 00:00:48,850 then selects all the rows from the album's table 17 00:00:48,850 --> 00:00:50,270 for that ID. 18 00:00:50,270 --> 00:00:52,120 Now, the album's data listbox 19 00:00:52,120 --> 00:00:56,290 already performs most of that query in its requery method. 20 00:00:57,170 --> 00:01:00,780 This is the method starting on line 45. 21 00:01:00,780 --> 00:01:05,060 So if we passed in the artist ID to its requery method, 22 00:01:05,060 --> 00:01:08,580 it could then retrieve the albums for a specific artist. 23 00:01:08,580 --> 00:01:10,570 So let's actually start working on that 24 00:01:10,570 --> 00:01:13,340 and we're going to start modifying requery. 25 00:01:13,340 --> 00:01:15,850 Now, what we're going to do is give it a single parameter 26 00:01:15,850 --> 00:01:17,730 which will be the ID to match 27 00:01:17,730 --> 00:01:19,710 and we'll make that default to none 28 00:01:19,710 --> 00:01:22,420 so that we can populate a list with all records 29 00:01:22,420 --> 00:01:24,350 as we did for the artists. 30 00:01:24,350 --> 00:01:26,710 So I'm gonna come up here and change the definition 31 00:01:26,710 --> 00:01:30,460 from self to comma space link_value=None 32 00:01:33,470 --> 00:01:34,360 and then what we wanna do 33 00:01:34,360 --> 00:01:38,280 is add the code on the next line if link_value: 34 00:01:40,530 --> 00:01:44,700 and what we're going to do is put sql = self.sql_select 35 00:01:47,890 --> 00:01:51,800 plus in double quotes where, starting with a space there, 36 00:01:51,800 --> 00:01:54,120 where and a space before the end 37 00:01:54,120 --> 00:01:58,280 plus an artist plus equals question mark plus self.sql_sort. 38 00:02:06,860 --> 00:02:08,860 And we're also going to, 39 00:02:09,930 --> 00:02:12,220 actually what we'll do is change this 40 00:02:12,220 --> 00:02:16,400 and under that line we're going to put print(sql), 41 00:02:16,400 --> 00:02:18,840 put our hash TODO to link this line 42 00:02:18,840 --> 00:02:21,020 'cause it's only a temporary thing. 43 00:02:21,020 --> 00:02:21,890 Then on the next line, 44 00:02:21,890 --> 00:02:24,390 we'll do a self.cursor.execute 45 00:02:26,400 --> 00:02:29,070 and we're gonna execute in parenthesis sql 46 00:02:29,070 --> 00:02:33,790 comma space and then in parenthesis link_value comma 47 00:02:33,790 --> 00:02:36,160 and close off the parenthesis. 48 00:02:36,160 --> 00:02:39,240 Then we're gonna put an else and the else, 49 00:02:39,240 --> 00:02:40,430 we're gonna put these other two statements 50 00:02:40,430 --> 00:02:41,260 to where they were before 51 00:02:41,260 --> 00:02:44,150 and just Tab those out, indent them to the correct level 52 00:02:44,150 --> 00:02:45,650 and then we're gonna leave the other code down the bottom 53 00:02:45,650 --> 00:02:47,690 to do the clear of the listbox 54 00:02:47,690 --> 00:02:49,120 and then go through each item 55 00:02:49,120 --> 00:02:51,260 that we find from the sql select 56 00:02:51,260 --> 00:02:54,100 and insert that into the listbox. 57 00:02:54,100 --> 00:02:58,140 So basically now it's link_value passes an argument 58 00:02:58,140 --> 00:03:01,010 and would be going to be running this code in the else block 59 00:03:01,010 --> 00:03:03,140 which was the code that was there prior. 60 00:03:03,140 --> 00:03:05,370 However, when a link value is provided, 61 00:03:05,370 --> 00:03:08,260 we're inserting this where clause as you can see here 62 00:03:08,260 --> 00:03:11,650 where artist equals and then a place holder question mark 63 00:03:11,650 --> 00:03:13,430 to filter the records. 64 00:03:13,430 --> 00:03:17,000 Now, you might be wondering looking at this code on line 47 65 00:03:17,000 --> 00:03:18,790 why I write the where clause like that 66 00:03:18,790 --> 00:03:20,580 where a query is a single string 67 00:03:20,580 --> 00:03:23,150 instead of concatenating it like I've done. 68 00:03:23,150 --> 00:03:24,680 The reason is that we're gonna have to filter 69 00:03:24,680 --> 00:03:27,500 on different fields to seek the data 70 00:03:27,500 --> 00:03:29,660 that the data listbox is displaying. 71 00:03:29,660 --> 00:03:33,670 So we're gonna be replacing artist as you can see on line 47 72 00:03:33,670 --> 00:03:35,590 with a variable shortly. 73 00:03:35,590 --> 00:03:37,640 So actually though let's see if it works. 74 00:03:37,640 --> 00:03:39,410 Now, we can choose a random artist ID 75 00:03:39,410 --> 00:03:41,740 when we requery the albums list 76 00:03:41,740 --> 00:03:46,110 and that's going to be down here on line 118. 77 00:03:46,110 --> 00:03:49,950 So instead of passing none, we're now passing a parameter. 78 00:03:49,950 --> 00:03:52,200 Let's actually put a number in there 12 79 00:03:52,200 --> 00:03:54,350 which happens to be Wish By Nash 80 00:03:54,350 --> 00:03:56,390 so if we actually run that, 81 00:03:56,390 --> 00:04:01,340 we should hopefully see their three albums pop up. 82 00:04:01,340 --> 00:04:02,890 Those are the select clauses now for albums 83 00:04:02,890 --> 00:04:06,190 where artist equals question mark ordered by name. 84 00:04:06,190 --> 00:04:08,200 And we'll have a look now. 85 00:04:08,200 --> 00:04:09,960 You can see their three albums 86 00:04:09,960 --> 00:04:12,760 correctly showing up from the database. 87 00:04:12,760 --> 00:04:15,360 All right, so that's one side of the problem solved. 88 00:04:15,360 --> 00:04:19,850 A listbox can populate itself with data for a specific ID. 89 00:04:19,850 --> 00:04:23,200 The next step though is to get one listbox to tell another 90 00:04:23,200 --> 00:04:24,640 which ID to use. 91 00:04:24,640 --> 00:04:26,060 Now, we've already got some code for that 92 00:04:26,060 --> 00:04:28,360 and I'll just close this down 93 00:04:28,360 --> 00:04:32,340 and that's the code in the get_albums function 94 00:04:32,340 --> 00:04:35,380 and also the get_songs function. 95 00:04:35,380 --> 00:04:36,400 All right, so what we need to do 96 00:04:36,400 --> 00:04:39,450 is actually change the get_albums method 97 00:04:39,450 --> 00:04:41,640 and what we're going to do is 98 00:04:41,640 --> 00:04:44,390 or what I'm going to do is comment out everything, 99 00:04:44,390 --> 00:04:48,350 actually I'll take a copy of this line so this is artist_id, 100 00:04:48,350 --> 00:04:50,580 paste that in and I'm gonna copy out 101 00:04:50,580 --> 00:04:53,060 or comment out rather everything else 102 00:04:53,060 --> 00:04:54,890 and we're gonna change that now 103 00:04:54,890 --> 00:04:57,800 to use the albums listbox requery method. 104 00:04:57,800 --> 00:04:59,580 So the actual artist_id, 105 00:04:59,580 --> 00:05:01,190 we're gonna change that marginally 106 00:05:01,190 --> 00:05:02,930 just on the end of the line in square brackets 107 00:05:02,930 --> 00:05:04,670 we're gonna put zero 108 00:05:04,670 --> 00:05:08,840 then we're going to do albumList.requery(artist_id). 109 00:05:13,030 --> 00:05:13,860 So at the moment, 110 00:05:13,860 --> 00:05:15,930 artist_id is returned 111 00:05:15,930 --> 00:05:18,130 from the conn.execute call as a topple, 112 00:05:18,130 --> 00:05:19,950 the code ultimately come here. 113 00:05:19,950 --> 00:05:21,270 That was fine when we were using it 114 00:05:21,270 --> 00:05:24,470 to substitute for the question mark in the select string 115 00:05:24,470 --> 00:05:27,920 because parameter substitution calls requires a topple, 116 00:05:27,920 --> 00:05:29,870 but here we only wanna provide a single value 117 00:05:29,870 --> 00:05:31,250 to the requery method 118 00:05:31,250 --> 00:05:32,250 so we're getting the topple 119 00:05:32,250 --> 00:05:34,630 from the fetch one method as per normal, 120 00:05:34,630 --> 00:05:36,130 but then we're actually specifying zero 121 00:05:36,130 --> 00:05:39,420 here in square brackets for the ID value. 122 00:05:39,420 --> 00:05:42,380 And then we're actually requering the albums list 123 00:05:42,380 --> 00:05:44,410 passing it the ID of the artist 124 00:05:44,410 --> 00:05:46,780 that it should display the albums for. 125 00:05:46,780 --> 00:05:49,310 So we run the programme now and choose different artists, 126 00:05:49,310 --> 00:05:51,090 it should result in their albums being displayed 127 00:05:51,090 --> 00:05:53,420 so let's actually check that 128 00:06:01,950 --> 00:06:03,380 and you can see that's obviously working now. 129 00:06:03,380 --> 00:06:05,340 We're gonna have a different album showing 130 00:06:05,340 --> 00:06:08,090 when I select a different artist. 131 00:06:09,100 --> 00:06:12,220 All right, so we can now move that function into our class 132 00:06:12,220 --> 00:06:14,980 and then call it whenever a new item is selected. 133 00:06:14,980 --> 00:06:16,190 Moving it is easy. 134 00:06:16,190 --> 00:06:17,350 It's already in the right place 135 00:06:17,350 --> 00:06:19,080 just after the end of the class 136 00:06:19,080 --> 00:06:21,350 so I've actually got the class here 137 00:06:21,350 --> 00:06:23,650 that we've written our data listbox class 138 00:06:23,650 --> 00:06:27,470 and we want this function to become method in that class. 139 00:06:27,470 --> 00:06:28,330 So what I'm going to do there 140 00:06:28,330 --> 00:06:31,580 is just select the entire function here 141 00:06:35,230 --> 00:06:39,140 like so and then just indent it by pressing Tab 142 00:06:39,140 --> 00:06:41,530 and then we're gonna delete one of those lines 143 00:06:41,530 --> 00:06:43,070 just to keep it happy 144 00:06:43,070 --> 00:06:44,570 and the other thing we need do 145 00:06:44,570 --> 00:06:48,520 is all methods in a class have got a self argument. 146 00:06:48,520 --> 00:06:50,110 I've got self as the first option there 147 00:06:50,110 --> 00:06:53,230 so I'm gonna do a self comma space event. 148 00:06:53,230 --> 00:06:54,790 And obviously I removed the second line 149 00:06:54,790 --> 00:06:57,620 to keep IntelliJ and Python happy. 150 00:06:57,620 --> 00:06:59,840 And we have got some errors and warnings here, 151 00:06:59,840 --> 00:07:01,830 but they're going to actually disappear 152 00:07:01,830 --> 00:07:03,750 as we make some more changes. 153 00:07:03,750 --> 00:07:06,560 First thing we do need to change though is the method name. 154 00:07:06,560 --> 00:07:09,220 Now, it was used just to get albums for an artist 155 00:07:09,220 --> 00:07:11,950 and the duplicated code in get_songs 156 00:07:11,950 --> 00:07:14,200 was used to get the songs for an album. 157 00:07:14,200 --> 00:07:16,530 But now that we've converted it to a method, 158 00:07:16,530 --> 00:07:18,610 we're going to use it to get whatever data 159 00:07:18,610 --> 00:07:20,830 the linked listbox is displaying. 160 00:07:20,830 --> 00:07:23,610 Now, because it's called whenever an item is selected, 161 00:07:23,610 --> 00:07:25,000 it's more appropriate to change the name 162 00:07:25,000 --> 00:07:26,920 to something like say on_select 163 00:07:26,920 --> 00:07:30,920 so let's go ahead and call it that so on_select. 164 00:07:33,430 --> 00:07:34,260 So now that we've done that, 165 00:07:34,260 --> 00:07:36,070 you can see that the on_select method 166 00:07:36,070 --> 00:07:39,250 now follows our requery method in our class. 167 00:07:39,250 --> 00:07:42,260 Now, because this method is now part of the class 168 00:07:42,260 --> 00:07:44,690 that will be getting the listbox select event, 169 00:07:44,690 --> 00:07:48,380 there's now no need to retrieve the widget from an event. 170 00:07:48,380 --> 00:07:52,150 So what we can do is replace the LB with self. 171 00:07:52,150 --> 00:07:54,600 Now, I'm actually gonna print out check first 172 00:07:54,600 --> 00:07:55,760 and it should print true 173 00:07:55,760 --> 00:07:58,040 if self is the same as the events widget 174 00:07:58,040 --> 00:07:59,920 so that we can watch for that when we run the programme. 175 00:07:59,920 --> 00:08:02,300 So I'm gonna put that code in, 176 00:08:02,300 --> 00:08:04,850 I'm gonna do print in parenthesis 177 00:08:04,850 --> 00:08:06,520 self is event.widget 178 00:08:09,560 --> 00:08:13,580 and then put a TODO there to link this line again. 179 00:08:13,580 --> 00:08:16,440 So therefore, we actually change this LB 180 00:08:16,440 --> 00:08:20,610 and change index to actually be self.get(index). 181 00:08:22,240 --> 00:08:23,360 And same for artist name, 182 00:08:23,360 --> 00:08:27,410 we need to change that now to self.get(index). 183 00:08:27,410 --> 00:08:28,350 All right, so at the moment, 184 00:08:28,350 --> 00:08:31,950 we're getting an artist ID on line 65 185 00:08:31,950 --> 00:08:33,100 and that's obviously no use 186 00:08:33,100 --> 00:08:35,429 if we're creating a generic class. 187 00:08:35,429 --> 00:08:37,980 We need to get the ID for whatever table 188 00:08:37,980 --> 00:08:40,909 our data listbox is using to display. 189 00:08:40,909 --> 00:08:43,580 Now, as it happens, we already have a SQL statement 190 00:08:43,580 --> 00:08:45,980 that returns all the conns we're displaying 191 00:08:45,980 --> 00:08:47,970 as well as the IDs. 192 00:08:47,970 --> 00:08:50,050 So up on line 36 up here, 193 00:08:51,870 --> 00:08:54,720 we created the sql_select query string 194 00:08:54,720 --> 00:08:58,560 so we can use that sql and add the where clause to it 195 00:08:58,560 --> 00:09:00,820 then we'll also rename the artist_name 196 00:09:00,820 --> 00:09:02,870 and artist_id variables 197 00:09:02,870 --> 00:09:04,770 'cause we might not be getting names, 198 00:09:04,770 --> 00:09:06,910 the listbox could effectively be displaying anything. 199 00:09:06,910 --> 00:09:11,460 Let's go ahead and do that down here on line 65. 200 00:09:11,460 --> 00:09:15,620 So the conn.execute is going to be self.sql_select 201 00:09:17,320 --> 00:09:19,820 plus and then our where clause 202 00:09:23,070 --> 00:09:26,240 plus where and a space plus self.field 203 00:09:30,060 --> 00:09:32,310 plus and we want the equals 204 00:09:33,300 --> 00:09:36,290 and the question mark place holder there 205 00:09:36,290 --> 00:09:38,590 and then comma space and instead of artist_name, 206 00:09:38,590 --> 00:09:42,760 as I mentioned, we're gonna rename to value and .fetchone 207 00:09:44,320 --> 00:09:48,700 and this time we're going to pass one in square brackets 208 00:09:48,700 --> 00:09:50,740 to get the right value. 209 00:09:50,740 --> 00:09:52,880 So the query returns two columns 210 00:09:52,880 --> 00:09:54,660 and that's why we're changing the index 211 00:09:54,660 --> 00:09:56,070 here on line 65 at the end 212 00:09:56,070 --> 00:09:58,340 which you saw me change from zero to one 213 00:09:58,340 --> 00:10:02,450 because the ID is the second column in the topple. 214 00:10:02,450 --> 00:10:04,610 We need to change the naming of that. 215 00:10:04,610 --> 00:10:08,100 So we have the artist_name but again we wanna rename that 216 00:10:08,100 --> 00:10:09,990 so we're going to call it value 217 00:10:09,990 --> 00:10:13,310 and consequently we can pass that on line 65. 218 00:10:13,310 --> 00:10:15,910 Now, we're gonna get a problem with this line of code 219 00:10:15,910 --> 00:10:19,220 on line 65 and it's one that's quite subtle. 220 00:10:19,220 --> 00:10:21,590 In fact, we wouldn't notice it 221 00:10:21,590 --> 00:10:24,840 until we tried to use that class in another programme. 222 00:10:24,840 --> 00:10:27,900 Now, if you recall, we write the get_albums method 223 00:10:27,900 --> 00:10:30,160 to use the global connexion conn. 224 00:10:30,160 --> 00:10:32,310 Now, if we leave on_select as it is 225 00:10:32,310 --> 00:10:34,340 and try to use it in another programme 226 00:10:34,340 --> 00:10:36,790 that call the connexion something else, 227 00:10:36,790 --> 00:10:38,680 then our method's going to crash. 228 00:10:38,680 --> 00:10:40,750 Of course, if we tested it in another programme 229 00:10:40,750 --> 00:10:43,170 and happen to call the connexion conn as well, 230 00:10:43,170 --> 00:10:44,630 we wouldn't spot the error. 231 00:10:44,630 --> 00:10:47,180 So the point here is to be very careful 232 00:10:47,180 --> 00:10:49,240 when moving functions into a class 233 00:10:49,240 --> 00:10:51,420 and make sure you're not using any global objects 234 00:10:51,420 --> 00:10:52,600 in the function. 235 00:10:52,600 --> 00:10:55,200 So as a result, we need to change the method 236 00:10:55,200 --> 00:10:58,860 to use the class' cursor attribute instead. 237 00:10:58,860 --> 00:11:02,150 And we didn't rename the artist_id yet so let's do that now, 238 00:11:02,150 --> 00:11:05,420 we'll call that link_id to be generic 239 00:11:05,420 --> 00:11:08,470 so instead of it being conn.execute, 240 00:11:08,470 --> 00:11:09,630 we're actually gonna change that now 241 00:11:09,630 --> 00:11:11,880 to do a self.cursor.execute 242 00:11:14,810 --> 00:11:18,980 and because we've called the artist_id link_id on line 65, 243 00:11:19,840 --> 00:11:22,940 on line 66 we're gonna rename that as well 244 00:11:22,940 --> 00:11:25,390 and call that link_id. 245 00:11:25,390 --> 00:11:27,010 All right, so we're almost done, 246 00:11:27,010 --> 00:11:28,500 but we need to handle the scenario 247 00:11:28,500 --> 00:11:31,140 where the data listbox is linked to another one. 248 00:11:31,140 --> 00:11:34,290 So let's work on that in the next video.