WEBVTT 1 00:00:01.918 --> 00:00:03.365 So in the last few videos, 2 00:00:03.365 --> 00:00:06.384 we've been working towards creating a listbox class 3 00:00:06.384 --> 00:00:08.984 that can load its data from a database table 4 00:00:08.984 --> 00:00:10.885 and also tell other listboxes 5 00:00:10.885 --> 00:00:13.184 when they should refresh their data. 6 00:00:13.184 --> 00:00:15.302 Now, we had that working with a basic listbox 7 00:00:15.302 --> 00:00:18.955 by binding a function to a listbox select virtual event 8 00:00:18.955 --> 00:00:21.755 and you can see that here on line 107 9 00:00:21.755 --> 00:00:24.665 so the artistList is our bind and then listboxSelect 10 00:00:24.665 --> 00:00:26.875 to specify the virtual event. 11 00:00:26.875 --> 00:00:29.994 Now, when an item is selected in the artist listbox, 12 00:00:29.994 --> 00:00:32.694 the get_albums function is called. 13 00:00:32.694 --> 00:00:36.805 Now, looking at the get_albums function, 14 00:00:36.805 --> 00:00:38.945 you can see here it here starting on line 55, 15 00:00:38.945 --> 00:00:42.134 it retrieves the artist ID for the selected artist 16 00:00:42.134 --> 00:00:45.745 then selects all the rows from the album's table 17 00:00:45.745 --> 00:00:47.166 for that ID. 18 00:00:47.166 --> 00:00:49.014 Now, the album's data listbox 19 00:00:49.014 --> 00:00:53.181 already performs most of that query in its requery method. 20 00:00:54.065 --> 00:00:57.675 This is the method starting on line 45. 21 00:00:57.675 --> 00:01:01.955 So if we passed in the artist ID to its requery method, 22 00:01:01.955 --> 00:01:05.475 it could then retrieve the albums for a specific artist. 23 00:01:05.475 --> 00:01:07.465 So let's actually start working on that 24 00:01:07.465 --> 00:01:10.235 and we're going to start modifying requery. 25 00:01:10.235 --> 00:01:12.745 Now, what we're going to do is give it a single parameter 26 00:01:12.745 --> 00:01:14.622 which will be the ID to match 27 00:01:14.622 --> 00:01:16.603 and we'll make that default to none 28 00:01:16.603 --> 00:01:19.315 so that we can populate a list with all records 29 00:01:19.315 --> 00:01:21.243 as we did for the artists. 30 00:01:21.243 --> 00:01:23.602 So I'm gonna come up here and change the definition 31 00:01:23.602 --> 00:01:27.352 from self to comma space link_value=None 32 00:01:30.367 --> 00:01:31.258 and then what we wanna do 33 00:01:31.258 --> 00:01:35.175 is add the code on the next line if link_value: 34 00:01:37.426 --> 00:01:41.593 and what we're going to do is put sql = self.sql_select 35 00:01:44.784 --> 00:01:48.693 plus in double quotes where, starting with a space there, 36 00:01:48.693 --> 00:01:51.012 where and a space before the end 37 00:01:51.012 --> 00:01:55.179 plus an artist plus equals question mark plus self.sql_sort. 38 00:02:03.753 --> 00:02:05.753 And we're also going to, 39 00:02:06.824 --> 00:02:09.115 actually what we'll do is change this 40 00:02:09.115 --> 00:02:13.291 and under that line we're going to put print(sql), 41 00:02:13.291 --> 00:02:15.732 put our hash TODO to link this line 42 00:02:15.732 --> 00:02:17.913 'cause it's only a temporary thing. 43 00:02:17.913 --> 00:02:18.782 Then on the next line, 44 00:02:18.782 --> 00:02:21.282 we'll do a self.cursor.execute 45 00:02:23.292 --> 00:02:25.964 and we're gonna execute in parenthesis sql 46 00:02:25.964 --> 00:02:30.684 comma space and then in parenthesis link_value comma 47 00:02:30.684 --> 00:02:33.050 and close off the parenthesis. 48 00:02:33.050 --> 00:02:36.132 Then we're gonna put an else and the else, 49 00:02:36.132 --> 00:02:37.322 we're gonna put these other two statements 50 00:02:37.322 --> 00:02:38.155 to where they were before 51 00:02:38.155 --> 00:02:41.042 and just Tab those out, indent them to the correct level 52 00:02:41.042 --> 00:02:42.543 and then we're gonna leave the other code down the bottom 53 00:02:42.543 --> 00:02:44.583 to do the clear of the listbox 54 00:02:44.583 --> 00:02:46.013 and then go through each item 55 00:02:46.013 --> 00:02:48.154 that we find from the sql select 56 00:02:48.154 --> 00:02:50.993 and insert that into the listbox. 57 00:02:50.993 --> 00:02:55.034 So basically now it's link_value passes an argument 58 00:02:55.034 --> 00:02:57.903 and would be going to be running this code in the else block 59 00:02:57.903 --> 00:03:00.034 which was the code that was there prior. 60 00:03:00.034 --> 00:03:02.263 However, when a link value is provided, 61 00:03:02.263 --> 00:03:05.153 we're inserting this where clause as you can see here 62 00:03:05.153 --> 00:03:08.543 where artist equals and then a place holder question mark 63 00:03:08.543 --> 00:03:10.322 to filter the records. 64 00:03:10.322 --> 00:03:13.893 Now, you might be wondering looking at this code on line 47 65 00:03:13.893 --> 00:03:15.682 why I write the where clause like that 66 00:03:15.682 --> 00:03:17.470 where a query is a single string 67 00:03:17.470 --> 00:03:20.042 instead of concatenating it like I've done. 68 00:03:20.042 --> 00:03:21.570 The reason is that we're gonna have to filter 69 00:03:21.570 --> 00:03:24.390 on different fields to seek the data 70 00:03:24.390 --> 00:03:26.550 that the data listbox is displaying. 71 00:03:26.550 --> 00:03:30.562 So we're gonna be replacing artist as you can see on line 47 72 00:03:30.562 --> 00:03:32.488 with a variable shortly. 73 00:03:32.488 --> 00:03:34.530 So actually though let's see if it works. 74 00:03:34.530 --> 00:03:36.300 Now, we can choose a random artist ID 75 00:03:36.300 --> 00:03:38.631 when we requery the albums list 76 00:03:38.631 --> 00:03:43.009 and that's going to be down here on line 118. 77 00:03:43.009 --> 00:03:46.848 So instead of passing none, we're now passing a parameter. 78 00:03:46.848 --> 00:03:49.099 Let's actually put a number in there 12 79 00:03:49.099 --> 00:03:51.240 which happens to be Wish By Nash 80 00:03:51.240 --> 00:03:53.280 so if we actually run that, 81 00:03:53.280 --> 00:03:58.230 we should hopefully see their three albums pop up. 82 00:03:58.230 --> 00:03:59.789 Those are the select clauses now for albums 83 00:03:59.789 --> 00:04:03.087 where artist equals question mark ordered by name. 84 00:04:03.087 --> 00:04:05.098 And we'll have a look now. 85 00:04:05.098 --> 00:04:06.858 You can see their three albums 86 00:04:06.858 --> 00:04:09.658 correctly showing up from the database. 87 00:04:09.658 --> 00:04:12.257 All right, so that's one side of the problem solved. 88 00:04:12.257 --> 00:04:16.749 A listbox can populate itself with data for a specific ID. 89 00:04:16.749 --> 00:04:20.098 The next step though is to get one listbox to tell another 90 00:04:20.098 --> 00:04:21.538 which ID to use. 91 00:04:21.538 --> 00:04:22.959 Now, we've already got some code for that 92 00:04:22.959 --> 00:04:25.258 and I'll just close this down 93 00:04:25.258 --> 00:04:29.238 and that's the code in the get_albums function 94 00:04:29.238 --> 00:04:32.277 and also the get_songs function. 95 00:04:32.277 --> 00:04:33.298 All right, so what we need to do 96 00:04:33.298 --> 00:04:36.349 is actually change the get_albums method 97 00:04:36.349 --> 00:04:38.538 and what we're going to do is 98 00:04:38.538 --> 00:04:41.287 or what I'm going to do is comment out everything, 99 00:04:41.287 --> 00:04:45.245 actually I'll take a copy of this line so this is artist_id, 100 00:04:45.245 --> 00:04:47.477 paste that in and I'm gonna copy out 101 00:04:47.477 --> 00:04:49.957 or comment out rather everything else 102 00:04:49.957 --> 00:04:51.787 and we're gonna change that now 103 00:04:51.787 --> 00:04:54.696 to use the albums listbox requery method. 104 00:04:54.696 --> 00:04:56.476 So the actual artist_id, 105 00:04:56.476 --> 00:04:58.083 we're gonna change that marginally 106 00:04:58.083 --> 00:04:59.825 just on the end of the line in square brackets 107 00:04:59.825 --> 00:05:01.564 we're gonna put zero 108 00:05:01.564 --> 00:05:05.731 then we're going to do albumList.requery(artist_id). 109 00:05:09.925 --> 00:05:10.758 So at the moment, 110 00:05:10.758 --> 00:05:12.824 artist_id is returned 111 00:05:12.824 --> 00:05:15.024 from the conn.execute call as a topple, 112 00:05:15.024 --> 00:05:16.845 the code ultimately come here. 113 00:05:16.845 --> 00:05:18.165 That was fine when we were using it 114 00:05:18.165 --> 00:05:21.365 to substitute for the question mark in the select string 115 00:05:21.365 --> 00:05:24.816 because parameter substitution calls requires a topple, 116 00:05:24.816 --> 00:05:26.764 but here we only wanna provide a single value 117 00:05:26.764 --> 00:05:28.141 to the requery method 118 00:05:28.141 --> 00:05:29.144 so we're getting the topple 119 00:05:29.144 --> 00:05:31.523 from the fetch one method as per normal, 120 00:05:31.523 --> 00:05:33.025 but then we're actually specifying zero 121 00:05:33.025 --> 00:05:36.313 here in square brackets for the ID value. 122 00:05:36.313 --> 00:05:39.272 And then we're actually requering the albums list 123 00:05:39.272 --> 00:05:41.304 passing it the ID of the artist 124 00:05:41.304 --> 00:05:43.671 that it should display the albums for. 125 00:05:43.671 --> 00:05:46.203 So we run the programme now and choose different artists, 126 00:05:46.203 --> 00:05:47.985 it should result in their albums being displayed 127 00:05:47.985 --> 00:05:50.318 so let's actually check that 128 00:05:58.843 --> 00:06:00.273 and you can see that's obviously working now. 129 00:06:00.273 --> 00:06:02.233 We're gonna have a different album showing 130 00:06:02.233 --> 00:06:04.983 when I select a different artist. 131 00:06:05.992 --> 00:06:09.113 All right, so we can now move that function into our class 132 00:06:09.113 --> 00:06:11.873 and then call it whenever a new item is selected. 133 00:06:11.873 --> 00:06:13.083 Moving it is easy. 134 00:06:13.083 --> 00:06:14.243 It's already in the right place 135 00:06:14.243 --> 00:06:15.974 just after the end of the class 136 00:06:15.974 --> 00:06:18.241 so I've actually got the class here 137 00:06:18.241 --> 00:06:20.542 that we've written our data listbox class 138 00:06:20.542 --> 00:06:24.363 and we want this function to become method in that class. 139 00:06:24.363 --> 00:06:25.225 So what I'm going to do there 140 00:06:25.225 --> 00:06:28.475 is just select the entire function here 141 00:06:32.122 --> 00:06:36.035 like so and then just indent it by pressing Tab 142 00:06:36.035 --> 00:06:38.425 and then we're gonna delete one of those lines 143 00:06:38.425 --> 00:06:39.963 just to keep it happy 144 00:06:39.963 --> 00:06:41.464 and the other thing we need do 145 00:06:41.464 --> 00:06:45.414 is all methods in a class have got a self argument. 146 00:06:45.414 --> 00:06:47.003 I've got self as the first option there 147 00:06:47.003 --> 00:06:50.123 so I'm gonna do a self comma space event. 148 00:06:50.123 --> 00:06:51.683 And obviously I removed the second line 149 00:06:51.683 --> 00:06:54.515 to keep IntelliJ and Python happy. 150 00:06:54.515 --> 00:06:56.734 And we have got some errors and warnings here, 151 00:06:56.734 --> 00:06:58.723 but they're going to actually disappear 152 00:06:58.723 --> 00:07:00.643 as we make some more changes. 153 00:07:00.643 --> 00:07:03.454 First thing we do need to change though is the method name. 154 00:07:03.454 --> 00:07:06.115 Now, it was used just to get albums for an artist 155 00:07:06.115 --> 00:07:08.843 and the duplicated code in get_songs 156 00:07:08.843 --> 00:07:11.093 was used to get the songs for an album. 157 00:07:11.093 --> 00:07:13.423 But now that we've converted it to a method, 158 00:07:13.423 --> 00:07:15.505 we're going to use it to get whatever data 159 00:07:15.505 --> 00:07:17.723 the linked listbox is displaying. 160 00:07:17.723 --> 00:07:20.505 Now, because it's called whenever an item is selected, 161 00:07:20.505 --> 00:07:21.893 it's more appropriate to change the name 162 00:07:21.893 --> 00:07:23.812 to something like say on_select 163 00:07:23.812 --> 00:07:27.812 so let's go ahead and call it that so on_select. 164 00:07:30.322 --> 00:07:31.155 So now that we've done that, 165 00:07:31.155 --> 00:07:32.963 you can see that the on_select method 166 00:07:32.963 --> 00:07:36.142 now follows our requery method in our class. 167 00:07:36.142 --> 00:07:39.153 Now, because this method is now part of the class 168 00:07:39.153 --> 00:07:41.582 that will be getting the listbox select event, 169 00:07:41.582 --> 00:07:45.271 there's now no need to retrieve the widget from an event. 170 00:07:45.271 --> 00:07:49.042 So what we can do is replace the LB with self. 171 00:07:49.042 --> 00:07:51.494 Now, I'm actually gonna print out check first 172 00:07:51.494 --> 00:07:52.654 and it should print true 173 00:07:52.654 --> 00:07:54.933 if self is the same as the events widget 174 00:07:54.933 --> 00:07:56.814 so that we can watch for that when we run the programme. 175 00:07:56.814 --> 00:07:59.191 So I'm gonna put that code in, 176 00:07:59.191 --> 00:08:01.745 I'm gonna do print in parenthesis 177 00:08:01.745 --> 00:08:03.412 self is event.widget 178 00:08:06.454 --> 00:08:10.475 and then put a TODO there to link this line again. 179 00:08:10.475 --> 00:08:13.334 So therefore, we actually change this LB 180 00:08:13.334 --> 00:08:17.501 and change index to actually be self.get(index). 181 00:08:19.134 --> 00:08:20.254 And same for artist name, 182 00:08:20.254 --> 00:08:24.305 we need to change that now to self.get(index). 183 00:08:24.305 --> 00:08:25.246 All right, so at the moment, 184 00:08:25.246 --> 00:08:28.843 we're getting an artist ID on line 65 185 00:08:28.843 --> 00:08:29.993 and that's obviously no use 186 00:08:29.993 --> 00:08:32.323 if we're creating a generic class. 187 00:08:32.323 --> 00:08:34.874 We need to get the ID for whatever table 188 00:08:34.874 --> 00:08:37.803 our data listbox is using to display. 189 00:08:37.803 --> 00:08:40.471 Now, as it happens, we already have a SQL statement 190 00:08:40.471 --> 00:08:42.870 that returns all the conns we're displaying 191 00:08:42.870 --> 00:08:44.863 as well as the IDs. 192 00:08:44.863 --> 00:08:46.946 So up on line 36 up here, 193 00:08:48.761 --> 00:08:51.611 we created the sql_select query string 194 00:08:51.611 --> 00:08:55.450 so we can use that sql and add the where clause to it 195 00:08:55.450 --> 00:08:57.711 then we'll also rename the artist_name 196 00:08:57.711 --> 00:08:59.761 and artist_id variables 197 00:08:59.761 --> 00:09:01.663 'cause we might not be getting names, 198 00:09:01.663 --> 00:09:03.801 the listbox could effectively be displaying anything. 199 00:09:03.801 --> 00:09:08.351 Let's go ahead and do that down here on line 65. 200 00:09:08.351 --> 00:09:12.518 So the conn.execute is going to be self.sql_select 201 00:09:14.212 --> 00:09:16.712 plus and then our where clause 202 00:09:19.963 --> 00:09:23.130 plus where and a space plus self.field 203 00:09:26.953 --> 00:09:29.203 plus and we want the equals 204 00:09:30.193 --> 00:09:33.183 and the question mark place holder there 205 00:09:33.183 --> 00:09:35.483 and then comma space and instead of artist_name, 206 00:09:35.483 --> 00:09:39.650 as I mentioned, we're gonna rename to value and .fetchone 207 00:09:41.212 --> 00:09:45.590 and this time we're going to pass one in square brackets 208 00:09:45.590 --> 00:09:47.633 to get the right value. 209 00:09:47.633 --> 00:09:49.775 So the query returns two columns 210 00:09:49.775 --> 00:09:51.553 and that's why we're changing the index 211 00:09:51.553 --> 00:09:52.964 here on line 65 at the end 212 00:09:52.964 --> 00:09:55.233 which you saw me change from zero to one 213 00:09:55.233 --> 00:09:59.344 because the ID is the second column in the topple. 214 00:09:59.344 --> 00:10:01.505 We need to change the naming of that. 215 00:10:01.505 --> 00:10:04.993 So we have the artist_name but again we wanna rename that 216 00:10:04.993 --> 00:10:06.884 so we're going to call it value 217 00:10:06.884 --> 00:10:10.204 and consequently we can pass that on line 65. 218 00:10:10.204 --> 00:10:12.802 Now, we're gonna get a problem with this line of code 219 00:10:12.802 --> 00:10:16.113 on line 65 and it's one that's quite subtle. 220 00:10:16.113 --> 00:10:18.483 In fact, we wouldn't notice it 221 00:10:18.483 --> 00:10:21.734 until we tried to use that class in another programme. 222 00:10:21.734 --> 00:10:24.793 Now, if you recall, we write the get_albums method 223 00:10:24.793 --> 00:10:27.055 to use the global connexion conn. 224 00:10:27.055 --> 00:10:29.204 Now, if we leave on_select as it is 225 00:10:29.204 --> 00:10:31.233 and try to use it in another programme 226 00:10:31.233 --> 00:10:33.685 that call the connexion something else, 227 00:10:33.685 --> 00:10:35.573 then our method's going to crash. 228 00:10:35.573 --> 00:10:37.645 Of course, if we tested it in another programme 229 00:10:37.645 --> 00:10:40.064 and happen to call the connexion conn as well, 230 00:10:40.064 --> 00:10:41.525 we wouldn't spot the error. 231 00:10:41.525 --> 00:10:44.074 So the point here is to be very careful 232 00:10:44.074 --> 00:10:46.132 when moving functions into a class 233 00:10:46.132 --> 00:10:48.314 and make sure you're not using any global objects 234 00:10:48.314 --> 00:10:49.492 in the function. 235 00:10:49.492 --> 00:10:52.093 So as a result, we need to change the method 236 00:10:52.093 --> 00:10:55.754 to use the class' cursor attribute instead. 237 00:10:55.754 --> 00:10:59.045 And we didn't rename the artist_id yet so let's do that now, 238 00:10:59.045 --> 00:11:02.314 we'll call that link_id to be generic 239 00:11:02.314 --> 00:11:05.365 so instead of it being conn.execute, 240 00:11:05.365 --> 00:11:06.524 we're actually gonna change that now 241 00:11:06.524 --> 00:11:08.774 to do a self.cursor.execute 242 00:11:11.704 --> 00:11:15.871 and because we've called the artist_id link_id on line 65, 243 00:11:16.738 --> 00:11:19.834 on line 66 we're gonna rename that as well 244 00:11:19.834 --> 00:11:22.283 and call that link_id. 245 00:11:22.283 --> 00:11:23.904 All right, so we're almost done, 246 00:11:23.904 --> 00:11:25.394 but we need to handle the scenario 247 00:11:25.394 --> 00:11:28.034 where the data listbox is linked to another one. 248 00:11:28.034 --> 00:11:31.183 So let's work on that in the next video.