WEBVTT 1 00:00:02.196 --> 00:00:03.315 Alright, so we're on the way 2 00:00:03.315 --> 00:00:05.714 to create a scrollable list box 3 00:00:05.714 --> 00:00:08.539 that can populate itself from our database. 4 00:00:08.539 --> 00:00:11.454 Now, we got the scrollable bit working in the previous video 5 00:00:11.454 --> 00:00:13.210 so the next step now is to look at getting 6 00:00:13.210 --> 00:00:15.778 the data into the lists. 7 00:00:15.778 --> 00:00:18.514 Now, we're gonna be creating another subclass soon 8 00:00:18.514 --> 00:00:20.408 but first though, let's see what we have to do 9 00:00:20.408 --> 00:00:22.242 to populate the list boxes 10 00:00:22.242 --> 00:00:24.556 and allow clicking on an item in one list 11 00:00:24.556 --> 00:00:26.957 to cause another list to update. 12 00:00:26.957 --> 00:00:29.499 Now, the artist list is the simplest 13 00:00:29.499 --> 00:00:31.122 because that doesn't really update 14 00:00:31.122 --> 00:00:32.850 when something else is clicked, 15 00:00:32.850 --> 00:00:34.522 so we just execute a query 16 00:00:34.522 --> 00:00:38.114 and then insert the query result into the list box. 17 00:00:38.114 --> 00:00:42.061 So, to do that let's go to the artist list box 18 00:00:42.061 --> 00:00:43.691 and the configuration code here, 19 00:00:43.691 --> 00:00:46.858 below that artist list dot config line 20 00:00:47.786 --> 00:00:50.749 and on there we're gonna type in four, 21 00:00:50.749 --> 00:00:55.442 artist in, conn dot execute 22 00:00:55.442 --> 00:00:57.497 and the SQL query in double quotes will be 23 00:00:57.497 --> 00:01:00.164 select, space, artists dot name, 24 00:01:01.617 --> 00:01:05.534 from artists, order by, 25 00:01:06.886 --> 00:01:10.959 artists dot name, 26 00:01:10.959 --> 00:01:12.792 colon and then we're gonna do 27 00:01:12.792 --> 00:01:16.292 artist list dot insert 28 00:01:17.368 --> 00:01:20.213 and it's gonna be TK inter dot END 29 00:01:20.213 --> 00:01:22.302 in upper case, comma, space, 30 00:01:22.302 --> 00:01:27.037 then artist zero in left square brackets 31 00:01:27.037 --> 00:01:28.713 and right parentheses. 32 00:01:28.713 --> 00:01:29.785 So, let's actually try that. 33 00:01:29.785 --> 00:01:33.368 We'll try running this to see what happens. 34 00:01:34.724 --> 00:01:38.114 And we can see that very nicely. 35 00:01:38.114 --> 00:01:38.947 Let's move this out of the way 36 00:01:38.947 --> 00:01:39.842 so that we can see the code. 37 00:01:39.842 --> 00:01:43.066 We got the artists showing in the first list box, 38 00:01:43.066 --> 00:01:44.530 in the artist list box on the screen. 39 00:01:44.530 --> 00:01:45.766 So, that's pretty cool 40 00:01:45.766 --> 00:01:46.985 and obviously, I'm scrolling through that 41 00:01:46.985 --> 00:01:49.021 and that's working quite nicely. 42 00:01:49.021 --> 00:01:50.630 So, the next step that is to respond 43 00:01:50.630 --> 00:01:52.015 to one of these artists being clicked. 44 00:01:52.015 --> 00:01:54.358 At the moment that we can click it but nothing's happening. 45 00:01:54.358 --> 00:01:56.217 So, let's close that down. 46 00:01:56.217 --> 00:01:58.774 So, what do we need to do to get that to work? 47 00:01:58.774 --> 00:02:00.337 Well, we've seen how to cause a function 48 00:02:00.337 --> 00:02:02.822 to be executed when a button's clicked 49 00:02:02.822 --> 00:02:05.886 way back in the black jack game in section 11. 50 00:02:05.886 --> 00:02:07.658 Now, list boxes actually don't have 51 00:02:07.658 --> 00:02:09.836 an explicit command property, 52 00:02:09.836 --> 00:02:11.238 but they do have a number of events 53 00:02:11.238 --> 00:02:13.803 that we can bind functions to. 54 00:02:13.803 --> 00:02:16.891 Now, the one that we want is here is a virtual event 55 00:02:16.891 --> 00:02:18.645 called list box select, 56 00:02:18.645 --> 00:02:21.573 which the list box receives when an item's selected. 57 00:02:21.573 --> 00:02:23.853 And the good news is that we can bind our own function 58 00:02:23.853 --> 00:02:26.234 or method to that virtual event, 59 00:02:26.234 --> 00:02:29.399 so our function's called when the even happens. 60 00:02:29.399 --> 00:02:31.240 So, let's have a look at adding that. 61 00:02:31.240 --> 00:02:35.037 So, we're gonna add that below the SQL code 62 00:02:35.037 --> 00:02:37.309 and that's gonna be artist list 63 00:02:37.309 --> 00:02:38.142 dot bind 64 00:02:39.679 --> 00:02:42.612 then parentheses, single quote, 65 00:02:42.612 --> 00:02:44.945 and I want two less than signs, 66 00:02:44.945 --> 00:02:47.260 then list box selects 67 00:02:47.260 --> 00:02:51.164 and then we have two greater thans signs 68 00:02:51.164 --> 00:02:52.484 and then a single quote, 69 00:02:52.484 --> 00:02:54.500 comma, space, 70 00:02:54.500 --> 00:02:56.649 get_albums, 71 00:02:56.649 --> 00:02:58.287 then right parentheses. 72 00:02:58.287 --> 00:02:59.355 And obviously, we're getting an error there 73 00:02:59.355 --> 00:03:01.594 because we need to write that. 74 00:03:01.594 --> 00:03:02.700 But what we're actually doing here 75 00:03:02.700 --> 00:03:06.255 is that whenever an item's selected in the artist list, 76 00:03:06.255 --> 00:03:10.648 we going to get our get_albums method to be called. 77 00:03:10.648 --> 00:03:11.981 So, for that reason we better go ahead 78 00:03:11.981 --> 00:03:13.646 and actually write that method. 79 00:03:13.646 --> 00:03:16.170 Now, I'm gonna add it after the class definition, 80 00:03:16.170 --> 00:03:18.922 leaving the usual two lines before it. 81 00:03:18.922 --> 00:03:21.098 So, let's go up and do that. 82 00:03:21.098 --> 00:03:25.434 So, we're actually gonna add it right up here. 83 00:03:25.434 --> 00:03:26.959 Okay, on line 26 84 00:03:26.959 --> 00:03:31.042 and we're gonna start with def, space, get_albums 85 00:03:33.250 --> 00:03:35.917 and event in parentheses, colon, 86 00:03:37.213 --> 00:03:41.380 and then we're gonna do lb equals event dot widget, 87 00:03:43.461 --> 00:03:47.628 index equals lb dot cur selection, 88 00:03:49.459 --> 00:03:53.462 parentheses and then in square brackets, zero. 89 00:03:53.462 --> 00:03:57.045 Then we want artist_name is equal to lb dot 90 00:03:58.323 --> 00:04:00.731 get index, 91 00:04:00.731 --> 00:04:03.351 then a comma. 92 00:04:03.351 --> 00:04:04.831 And now what we need to do is, 93 00:04:04.831 --> 00:04:08.998 so we wanna get the artist ID from the database row, 94 00:04:13.104 --> 00:04:17.079 So, we do artist_ID is equal to 95 00:04:17.079 --> 00:04:18.412 conn dot execute 96 00:04:19.419 --> 00:04:21.941 and double quotes within parentheses 97 00:04:21.941 --> 00:04:26.108 is gonna be select artists dot _ID 98 00:04:27.379 --> 00:04:30.828 from artists where, 99 00:04:30.828 --> 00:04:31.974 and I'm not mentioning the spaces, 100 00:04:31.974 --> 00:04:34.847 but you'll know by now with SQL that we need to have spaces 101 00:04:34.847 --> 00:04:36.149 at the appropriate places there, 102 00:04:36.149 --> 00:04:40.137 where artists dot name equals question mark. 103 00:04:40.137 --> 00:04:42.234 So, we're using a placeholder there. 104 00:04:42.234 --> 00:04:44.609 Comma after the double quote, 105 00:04:44.609 --> 00:04:46.321 artist_name, 106 00:04:46.321 --> 00:04:47.968 right parentheses 107 00:04:47.968 --> 00:04:52.088 dot fetch one. 108 00:04:52.088 --> 00:04:53.011 On the next line we're gonna type 109 00:04:53.011 --> 00:04:56.776 list equals and two square brackets, 110 00:04:56.776 --> 00:05:01.721 then four, row in, conn dot execute, 111 00:05:01.721 --> 00:05:03.367 then the SQL we wanna write for this one 112 00:05:03.367 --> 00:05:05.245 in double quotes is gonna be 113 00:05:05.245 --> 00:05:07.078 select albums dot name 114 00:05:08.344 --> 00:05:10.973 from albums 115 00:05:10.973 --> 00:05:15.553 where albums dot artist 116 00:05:15.553 --> 00:05:16.651 equals placeholder 117 00:05:16.651 --> 00:05:19.320 or question mark and you wanna order by 118 00:05:19.320 --> 00:05:21.088 the albums dot name. 119 00:05:21.088 --> 00:05:23.115 Albums dot name. 120 00:05:23.115 --> 00:05:24.537 Then double quote, comma, space, 121 00:05:24.537 --> 00:05:27.420 then artist_ID, 122 00:05:27.420 --> 00:05:30.337 then colon, then a list dot append. 123 00:05:31.907 --> 00:05:36.074 In parentheses we wanna do row and square brackets and zero. 124 00:05:38.102 --> 00:05:41.144 And finally, after that has finished, 125 00:05:41.144 --> 00:05:45.123 we gonna type album LV dot set 126 00:05:45.123 --> 00:05:49.290 and in parentheses, tuple, a list. 127 00:05:50.574 --> 00:05:52.999 Alright, so when a bound function's called, 128 00:05:52.999 --> 00:05:54.928 it gets passed as single argument, 129 00:05:54.928 --> 00:05:58.255 this event here that you can see we've defined on line 26. 130 00:05:58.255 --> 00:06:00.311 So, we can use that to then retrieve a reference 131 00:06:00.311 --> 00:06:02.622 to the widget that triggered the effect. 132 00:06:02.622 --> 00:06:05.064 We're doing that you can see here on line 27. 133 00:06:05.064 --> 00:06:08.538 Now, a list box has got a cur selection method 134 00:06:08.538 --> 00:06:10.390 that returns a tuple, 135 00:06:10.390 --> 00:06:11.227 containing the positions 136 00:06:11.227 --> 00:06:13.673 of all the selector items in the list 137 00:06:13.673 --> 00:06:16.611 and we're calling that on line 28. 138 00:06:16.611 --> 00:06:18.180 Now, you've probably used list boxes 139 00:06:18.180 --> 00:06:20.412 in your operating system's GUI 140 00:06:20.412 --> 00:06:22.228 and know that you can use the control key 141 00:06:22.228 --> 00:06:25.825 or command on a Mac to select several items in the list. 142 00:06:25.825 --> 00:06:27.796 Now, we're actually only going to be allowing 143 00:06:27.796 --> 00:06:29.917 a single item to be selected 144 00:06:29.917 --> 00:06:31.340 and we're gonna be coming back to that 145 00:06:31.340 --> 00:06:33.095 when we produce our class. 146 00:06:33.095 --> 00:06:34.345 But that's where we're only allowing 147 00:06:34.345 --> 00:06:36.569 a single item to be selected, 148 00:06:36.569 --> 00:06:39.031 we'd actually just hard coding that fact 149 00:06:39.031 --> 00:06:41.811 for this value in square brackets here with zero 150 00:06:41.811 --> 00:06:43.589 from the return tuple. 151 00:06:43.589 --> 00:06:46.183 Now, once we know the position of the selected item, 152 00:06:46.183 --> 00:06:48.662 we can then use the get method 153 00:06:48.662 --> 00:06:51.103 to get that from the list box's list. 154 00:06:51.103 --> 00:06:52.590 So, at that point we know the artist name 155 00:06:52.590 --> 00:06:54.166 that was selected 156 00:06:54.166 --> 00:06:56.279 but unfortunately, the TK list box 157 00:06:56.279 --> 00:06:59.233 doesn't provide any way to associate an ID 158 00:06:59.233 --> 00:07:01.840 with the strings that it displays. 159 00:07:01.840 --> 00:07:04.436 So consequently, we have to query the database 160 00:07:04.436 --> 00:07:06.533 to retrieve the artist ID 161 00:07:06.533 --> 00:07:09.700 and you can see that we're doing that here on line 32. 162 00:07:09.700 --> 00:07:10.977 Now, we're using a local database, 163 00:07:10.977 --> 00:07:13.029 so that's not really a problem here. 164 00:07:13.029 --> 00:07:14.622 But if we were retrieving data 165 00:07:14.622 --> 00:07:16.262 perhaps from a remote database, 166 00:07:16.262 --> 00:07:18.739 then you might wanna reduce the number of network calls 167 00:07:18.739 --> 00:07:21.989 that you may have to make to fetch data. 168 00:07:21.989 --> 00:07:25.171 In that case, we could add a list to our subclass list box. 169 00:07:25.171 --> 00:07:27.833 We'd then store the database IDs in the list 170 00:07:27.833 --> 00:07:30.552 in the same position as the names in the list box. 171 00:07:30.552 --> 00:07:32.512 Now, that's not particularly difficult 172 00:07:32.512 --> 00:07:35.577 but you do have to be careful to keep the list in sync 173 00:07:35.577 --> 00:07:38.828 if rows can be inserted into and deleted from the database. 174 00:07:38.828 --> 00:07:40.288 So, a better option though in that case, 175 00:07:40.288 --> 00:07:43.955 might be to use the new TTK tree view widget 176 00:07:44.826 --> 00:07:46.784 and that'll let you store the IDs in a column 177 00:07:46.784 --> 00:07:48.580 alongside the names. 178 00:07:48.580 --> 00:07:50.432 But in this case, we're using a local database, 179 00:07:50.432 --> 00:07:54.699 so we just gonna run a query as you can see here on line 32, 180 00:07:54.699 --> 00:07:56.824 to return the ID for the specified artist, 181 00:07:56.824 --> 00:08:00.389 and it's obviously in the artist_ID variable. 182 00:08:00.389 --> 00:08:02.779 And now I've done something slightly sneaky 183 00:08:02.779 --> 00:08:07.251 when assigning the artist name to the artist_name variable. 184 00:08:07.251 --> 00:08:09.954 So, we're gonna use artist_name as a parameter 185 00:08:09.954 --> 00:08:12.602 to the query and we have to pass a tuple 186 00:08:12.602 --> 00:08:14.650 rather than a single value. 187 00:08:14.650 --> 00:08:17.962 I'm talking about this code here on line 29. 188 00:08:17.962 --> 00:08:21.050 So, artist_name is actually a tuple 189 00:08:21.050 --> 00:08:22.730 and we don't have to worry about using it 190 00:08:22.730 --> 00:08:24.410 when executing the query. 191 00:08:24.410 --> 00:08:28.125 The fetch one method that we're using on line 32 192 00:08:28.125 --> 00:08:31.026 on the cursor that comes back from conn dot execute, 193 00:08:31.026 --> 00:08:32.589 well, that returns a tuple, 194 00:08:32.589 --> 00:08:36.445 so the variable artist_ID will already be suitable 195 00:08:36.445 --> 00:08:38.811 for passing on to the next query. 196 00:08:38.811 --> 00:08:40.811 So, once we've got the artist ID, 197 00:08:40.811 --> 00:08:43.155 we then use that to query the album's table. 198 00:08:43.155 --> 00:08:45.987 You can see that code on line 34 199 00:08:45.987 --> 00:08:48.177 and from there, we're getting a list of the albums. 200 00:08:48.177 --> 00:08:49.351 Now, the remaining code after that 201 00:08:49.351 --> 00:08:52.111 should hopefully, be pretty straight forward for you. 202 00:08:52.111 --> 00:08:56.249 We're just appending the names to a list here on line 35 203 00:08:56.249 --> 00:08:58.203 and remember that the list box wants a tuple 204 00:08:58.203 --> 00:08:59.564 in the list variable, 205 00:08:59.564 --> 00:09:02.266 so we convert a list from a list to a tuple 206 00:09:02.266 --> 00:09:04.059 here on line 36, 207 00:09:04.059 --> 00:09:07.248 before setting it as the value for album LV. 208 00:09:07.248 --> 00:09:09.636 Alright, so at this point, with the code we've got, 209 00:09:09.636 --> 00:09:11.559 this should hopefully work, 210 00:09:11.559 --> 00:09:15.392 so let's actually run it and see what happens. 211 00:09:18.828 --> 00:09:22.307 Alright, so there's our list of artists. 212 00:09:22.307 --> 00:09:23.196 So, we can click a couple there, 213 00:09:23.196 --> 00:09:25.299 like 1,000 Maniacs 214 00:09:25.299 --> 00:09:27.571 and you can see Albums got updated here. 215 00:09:27.571 --> 00:09:29.488 B.B. King, Black Crows. 216 00:09:30.875 --> 00:09:32.195 Let's try one that's got a lot. 217 00:09:32.195 --> 00:09:33.971 I think Blue Oyster Cult. 218 00:09:33.971 --> 00:09:35.370 You can see that's got multiple albums there, 219 00:09:35.370 --> 00:09:36.899 so that's worked quite nicely. 220 00:09:36.899 --> 00:09:38.482 Bruce Springsteen 221 00:09:38.482 --> 00:09:39.471 and you got a single one. 222 00:09:39.471 --> 00:09:41.522 We can try another one from Aerosmith. 223 00:09:41.522 --> 00:09:43.021 I know he's got quite a few. 224 00:09:43.021 --> 00:09:45.562 So, that's working nicely as well. 225 00:09:45.562 --> 00:09:48.412 Alright, but what I'm gonna do is finish the video here. 226 00:09:48.412 --> 00:09:50.567 In the next video we're gonna continue on 227 00:09:50.567 --> 00:09:53.028 and we're going to get the songs working as well. 228 00:09:53.028 --> 00:09:54.849 So, we're gonna be able to click on an album 229 00:09:54.849 --> 00:09:56.964 and retrieve a list of songs. 230 00:09:56.964 --> 00:09:58.980 So, I'll see you in the next video.