1 00:00:05,400 --> 00:00:06,520 Alright, so we're on the way 2 00:00:06,520 --> 00:00:08,920 to create a scrollable list box 3 00:00:08,920 --> 00:00:11,740 that can populate itself from our database. 4 00:00:11,740 --> 00:00:14,660 Now, we got the scrollable bit working in the previous video 5 00:00:14,660 --> 00:00:16,420 so the next step now is to look at getting 6 00:00:16,420 --> 00:00:18,980 the data into the lists. 7 00:00:18,980 --> 00:00:21,720 Now, we're gonna be creating another subclass soon 8 00:00:21,720 --> 00:00:23,610 but first though, let's see what we have to do 9 00:00:23,610 --> 00:00:25,450 to populate the list boxes 10 00:00:25,450 --> 00:00:27,760 and allow clicking on an item in one list 11 00:00:27,760 --> 00:00:30,160 to cause another list to update. 12 00:00:30,160 --> 00:00:32,700 Now, the artist list is the simplest 13 00:00:32,700 --> 00:00:34,330 because that doesn't really update 14 00:00:34,330 --> 00:00:36,060 when something else is clicked, 15 00:00:36,060 --> 00:00:37,730 so we just execute a query 16 00:00:37,730 --> 00:00:41,320 and then insert the query result into the list box. 17 00:00:41,320 --> 00:00:45,270 So, to do that let's go to the artist list box 18 00:00:45,270 --> 00:00:46,900 and the configuration code here, 19 00:00:46,900 --> 00:00:50,060 below that artist list dot config line 20 00:00:50,990 --> 00:00:53,950 and on there we're gonna type in four, 21 00:00:53,950 --> 00:00:58,650 artist in, conn dot execute 22 00:00:58,650 --> 00:01:00,700 and the SQL query in double quotes will be 23 00:01:00,700 --> 00:01:03,370 select, space, artists dot name, 24 00:01:04,819 --> 00:01:08,740 from artists, order by, 25 00:01:10,090 --> 00:01:14,160 artists dot name, 26 00:01:14,160 --> 00:01:16,000 colon and then we're gonna do 27 00:01:16,000 --> 00:01:19,500 artist list dot insert 28 00:01:20,570 --> 00:01:23,420 and it's gonna be TK inter dot END 29 00:01:23,420 --> 00:01:25,510 in upper case, comma, space, 30 00:01:25,510 --> 00:01:30,240 then artist zero in left square brackets 31 00:01:30,240 --> 00:01:31,920 and right parentheses. 32 00:01:31,920 --> 00:01:32,990 So, let's actually try that. 33 00:01:32,990 --> 00:01:36,570 We'll try running this to see what happens. 34 00:01:37,930 --> 00:01:41,320 And we can see that very nicely. 35 00:01:41,320 --> 00:01:42,150 Let's move this out of the way 36 00:01:42,150 --> 00:01:43,050 so that we can see the code. 37 00:01:43,050 --> 00:01:46,270 We got the artists showing in the first list box, 38 00:01:46,270 --> 00:01:47,740 in the artist list box on the screen. 39 00:01:47,740 --> 00:01:48,970 So, that's pretty cool 40 00:01:48,970 --> 00:01:50,190 and obviously, I'm scrolling through that 41 00:01:50,190 --> 00:01:52,230 and that's working quite nicely. 42 00:01:52,230 --> 00:01:53,840 So, the next step that is to respond 43 00:01:53,840 --> 00:01:55,220 to one of these artists being clicked. 44 00:01:55,220 --> 00:01:57,560 At the moment that we can click it but nothing's happening. 45 00:01:57,560 --> 00:01:59,420 So, let's close that down. 46 00:01:59,420 --> 00:02:01,980 So, what do we need to do to get that to work? 47 00:02:01,980 --> 00:02:03,540 Well, we've seen how to cause a function 48 00:02:03,540 --> 00:02:06,030 to be executed when a button's clicked 49 00:02:06,030 --> 00:02:09,090 way back in the black jack game in section 11. 50 00:02:09,090 --> 00:02:10,860 Now, list boxes actually don't have 51 00:02:10,860 --> 00:02:13,040 an explicit command property, 52 00:02:13,040 --> 00:02:14,440 but they do have a number of events 53 00:02:14,440 --> 00:02:17,010 that we can bind functions to. 54 00:02:17,010 --> 00:02:20,100 Now, the one that we want is here is a virtual event 55 00:02:20,100 --> 00:02:21,850 called list box select, 56 00:02:21,850 --> 00:02:24,780 which the list box receives when an item's selected. 57 00:02:24,780 --> 00:02:27,060 And the good news is that we can bind our own function 58 00:02:27,060 --> 00:02:29,440 or method to that virtual event, 59 00:02:29,440 --> 00:02:32,600 so our function's called when the even happens. 60 00:02:32,600 --> 00:02:34,450 So, let's have a look at adding that. 61 00:02:34,450 --> 00:02:38,240 So, we're gonna add that below the SQL code 62 00:02:38,240 --> 00:02:40,510 and that's gonna be artist list 63 00:02:40,510 --> 00:02:41,350 dot bind 64 00:02:42,880 --> 00:02:45,820 then parentheses, single quote, 65 00:02:45,820 --> 00:02:48,150 and I want two less than signs, 66 00:02:48,150 --> 00:02:50,470 then list box selects 67 00:02:50,470 --> 00:02:54,370 and then we have two greater thans signs 68 00:02:54,370 --> 00:02:55,690 and then a single quote, 69 00:02:55,690 --> 00:02:57,710 comma, space, 70 00:02:57,710 --> 00:02:59,850 get_albums, 71 00:02:59,850 --> 00:03:01,490 then right parentheses. 72 00:03:01,490 --> 00:03:02,560 And obviously, we're getting an error there 73 00:03:02,560 --> 00:03:04,800 because we need to write that. 74 00:03:04,800 --> 00:03:05,910 But what we're actually doing here 75 00:03:05,910 --> 00:03:09,460 is that whenever an item's selected in the artist list, 76 00:03:09,460 --> 00:03:13,850 we going to get our get_albums method to be called. 77 00:03:13,850 --> 00:03:15,190 So, for that reason we better go ahead 78 00:03:15,190 --> 00:03:16,850 and actually write that method. 79 00:03:16,850 --> 00:03:19,380 Now, I'm gonna add it after the class definition, 80 00:03:19,380 --> 00:03:22,130 leaving the usual two lines before it. 81 00:03:22,130 --> 00:03:24,300 So, let's go up and do that. 82 00:03:24,300 --> 00:03:28,640 So, we're actually gonna add it right up here. 83 00:03:28,640 --> 00:03:30,160 Okay, on line 26 84 00:03:30,160 --> 00:03:34,250 and we're gonna start with def, space, get_albums 85 00:03:36,460 --> 00:03:39,120 and event in parentheses, colon, 86 00:03:40,420 --> 00:03:44,590 and then we're gonna do lb equals event dot widget, 87 00:03:46,670 --> 00:03:50,830 index equals lb dot cur selection, 88 00:03:52,660 --> 00:03:56,670 parentheses and then in square brackets, zero. 89 00:03:56,670 --> 00:04:00,250 Then we want artist_name is equal to lb dot 90 00:04:01,530 --> 00:04:03,940 get index, 91 00:04:03,940 --> 00:04:06,560 then a comma. 92 00:04:06,560 --> 00:04:08,040 And now what we need to do is, 93 00:04:08,040 --> 00:04:12,200 so we wanna get the artist ID from the database row, 94 00:04:16,310 --> 00:04:20,279 So, we do artist_ID is equal to 95 00:04:20,279 --> 00:04:21,620 conn dot execute 96 00:04:22,620 --> 00:04:25,150 and double quotes within parentheses 97 00:04:25,150 --> 00:04:29,310 is gonna be select artists dot _ID 98 00:04:30,580 --> 00:04:34,030 from artists where, 99 00:04:34,030 --> 00:04:35,180 and I'm not mentioning the spaces, 100 00:04:35,180 --> 00:04:38,050 but you'll know by now with SQL that we need to have spaces 101 00:04:38,050 --> 00:04:39,350 at the appropriate places there, 102 00:04:39,350 --> 00:04:43,340 where artists dot name equals question mark. 103 00:04:43,340 --> 00:04:45,440 So, we're using a placeholder there. 104 00:04:45,440 --> 00:04:47,810 Comma after the double quote, 105 00:04:47,810 --> 00:04:49,530 artist_name, 106 00:04:49,530 --> 00:04:51,170 right parentheses 107 00:04:51,170 --> 00:04:55,290 dot fetch one. 108 00:04:55,290 --> 00:04:56,220 On the next line we're gonna type 109 00:04:56,220 --> 00:04:59,980 list equals and two square brackets, 110 00:04:59,980 --> 00:05:04,930 then four, row in, conn dot execute, 111 00:05:04,930 --> 00:05:06,570 then the SQL we wanna write for this one 112 00:05:06,570 --> 00:05:08,450 in double quotes is gonna be 113 00:05:08,450 --> 00:05:10,280 select albums dot name 114 00:05:11,550 --> 00:05:14,180 from albums 115 00:05:14,180 --> 00:05:18,760 where albums dot artist 116 00:05:18,760 --> 00:05:19,860 equals placeholder 117 00:05:19,860 --> 00:05:22,530 or question mark and you wanna order by 118 00:05:22,530 --> 00:05:24,290 the albums dot name. 119 00:05:24,290 --> 00:05:26,320 Albums dot name. 120 00:05:26,320 --> 00:05:27,740 Then double quote, comma, space, 121 00:05:27,740 --> 00:05:30,630 then artist_ID, 122 00:05:30,630 --> 00:05:33,540 then colon, then a list dot append. 123 00:05:35,110 --> 00:05:39,280 In parentheses we wanna do row and square brackets and zero. 124 00:05:41,310 --> 00:05:44,350 And finally, after that has finished, 125 00:05:44,350 --> 00:05:48,330 we gonna type album LV dot set 126 00:05:48,330 --> 00:05:52,500 and in parentheses, tuple, a list. 127 00:05:53,780 --> 00:05:56,200 Alright, so when a bound function's called, 128 00:05:56,200 --> 00:05:58,130 it gets passed as single argument, 129 00:05:58,130 --> 00:06:01,460 this event here that you can see we've defined on line 26. 130 00:06:01,460 --> 00:06:03,520 So, we can use that to then retrieve a reference 131 00:06:03,520 --> 00:06:05,830 to the widget that triggered the effect. 132 00:06:05,830 --> 00:06:08,270 We're doing that you can see here on line 27. 133 00:06:08,270 --> 00:06:11,740 Now, a list box has got a cur selection method 134 00:06:11,740 --> 00:06:13,600 that returns a tuple, 135 00:06:13,600 --> 00:06:14,430 containing the positions 136 00:06:14,430 --> 00:06:16,880 of all the selector items in the list 137 00:06:16,880 --> 00:06:19,820 and we're calling that on line 28. 138 00:06:19,820 --> 00:06:21,390 Now, you've probably used list boxes 139 00:06:21,390 --> 00:06:23,620 in your operating system's GUI 140 00:06:23,620 --> 00:06:25,430 and know that you can use the control key 141 00:06:25,430 --> 00:06:29,030 or command on a Mac to select several items in the list. 142 00:06:29,030 --> 00:06:31,000 Now, we're actually only going to be allowing 143 00:06:31,000 --> 00:06:33,120 a single item to be selected 144 00:06:33,120 --> 00:06:34,550 and we're gonna be coming back to that 145 00:06:34,550 --> 00:06:36,300 when we produce our class. 146 00:06:36,300 --> 00:06:37,550 But that's where we're only allowing 147 00:06:37,550 --> 00:06:39,770 a single item to be selected, 148 00:06:39,770 --> 00:06:42,240 we'd actually just hard coding that fact 149 00:06:42,240 --> 00:06:45,020 for this value in square brackets here with zero 150 00:06:45,020 --> 00:06:46,790 from the return tuple. 151 00:06:46,790 --> 00:06:49,390 Now, once we know the position of the selected item, 152 00:06:49,390 --> 00:06:51,870 we can then use the get method 153 00:06:51,870 --> 00:06:54,310 to get that from the list box's list. 154 00:06:54,310 --> 00:06:55,800 So, at that point we know the artist name 155 00:06:55,800 --> 00:06:57,370 that was selected 156 00:06:57,370 --> 00:06:59,480 but unfortunately, the TK list box 157 00:06:59,480 --> 00:07:02,440 doesn't provide any way to associate an ID 158 00:07:02,440 --> 00:07:05,050 with the strings that it displays. 159 00:07:05,050 --> 00:07:07,640 So consequently, we have to query the database 160 00:07:07,640 --> 00:07:09,740 to retrieve the artist ID 161 00:07:09,740 --> 00:07:12,910 and you can see that we're doing that here on line 32. 162 00:07:12,910 --> 00:07:14,180 Now, we're using a local database, 163 00:07:14,180 --> 00:07:16,230 so that's not really a problem here. 164 00:07:16,230 --> 00:07:17,830 But if we were retrieving data 165 00:07:17,830 --> 00:07:19,470 perhaps from a remote database, 166 00:07:19,470 --> 00:07:21,940 then you might wanna reduce the number of network calls 167 00:07:21,940 --> 00:07:25,190 that you may have to make to fetch data. 168 00:07:25,190 --> 00:07:28,380 In that case, we could add a list to our subclass list box. 169 00:07:28,380 --> 00:07:31,040 We'd then store the database IDs in the list 170 00:07:31,040 --> 00:07:33,760 in the same position as the names in the list box. 171 00:07:33,760 --> 00:07:35,720 Now, that's not particularly difficult 172 00:07:35,720 --> 00:07:38,780 but you do have to be careful to keep the list in sync 173 00:07:38,780 --> 00:07:42,030 if rows can be inserted into and deleted from the database. 174 00:07:42,030 --> 00:07:43,490 So, a better option though in that case, 175 00:07:43,490 --> 00:07:47,160 might be to use the new TTK tree view widget 176 00:07:48,030 --> 00:07:49,990 and that'll let you store the IDs in a column 177 00:07:49,990 --> 00:07:51,790 alongside the names. 178 00:07:51,790 --> 00:07:53,640 But in this case, we're using a local database, 179 00:07:53,640 --> 00:07:57,900 so we just gonna run a query as you can see here on line 32, 180 00:07:57,900 --> 00:08:00,030 to return the ID for the specified artist, 181 00:08:00,030 --> 00:08:03,590 and it's obviously in the artist_ID variable. 182 00:08:03,590 --> 00:08:05,980 And now I've done something slightly sneaky 183 00:08:05,980 --> 00:08:10,460 when assigning the artist name to the artist_name variable. 184 00:08:10,460 --> 00:08:13,160 So, we're gonna use artist_name as a parameter 185 00:08:13,160 --> 00:08:15,810 to the query and we have to pass a tuple 186 00:08:15,810 --> 00:08:17,860 rather than a single value. 187 00:08:17,860 --> 00:08:21,170 I'm talking about this code here on line 29. 188 00:08:21,170 --> 00:08:24,260 So, artist_name is actually a tuple 189 00:08:24,260 --> 00:08:25,940 and we don't have to worry about using it 190 00:08:25,940 --> 00:08:27,620 when executing the query. 191 00:08:27,620 --> 00:08:31,330 The fetch one method that we're using on line 32 192 00:08:31,330 --> 00:08:34,230 on the cursor that comes back from conn dot execute, 193 00:08:34,230 --> 00:08:35,789 well, that returns a tuple, 194 00:08:35,789 --> 00:08:39,650 so the variable artist_ID will already be suitable 195 00:08:39,650 --> 00:08:42,020 for passing on to the next query. 196 00:08:42,020 --> 00:08:44,020 So, once we've got the artist ID, 197 00:08:44,020 --> 00:08:46,360 we then use that to query the album's table. 198 00:08:46,360 --> 00:08:49,190 You can see that code on line 34 199 00:08:49,190 --> 00:08:51,380 and from there, we're getting a list of the albums. 200 00:08:51,380 --> 00:08:52,560 Now, the remaining code after that 201 00:08:52,560 --> 00:08:55,320 should hopefully, be pretty straight forward for you. 202 00:08:55,320 --> 00:08:59,450 We're just appending the names to a list here on line 35 203 00:08:59,450 --> 00:09:01,410 and remember that the list box wants a tuple 204 00:09:01,410 --> 00:09:02,770 in the list variable, 205 00:09:02,770 --> 00:09:05,470 so we convert a list from a list to a tuple 206 00:09:05,470 --> 00:09:07,260 here on line 36, 207 00:09:07,260 --> 00:09:10,450 before setting it as the value for album LV. 208 00:09:10,450 --> 00:09:12,840 Alright, so at this point, with the code we've got, 209 00:09:12,840 --> 00:09:14,760 this should hopefully work, 210 00:09:14,760 --> 00:09:18,600 so let's actually run it and see what happens. 211 00:09:22,030 --> 00:09:25,510 Alright, so there's our list of artists. 212 00:09:25,510 --> 00:09:26,400 So, we can click a couple there, 213 00:09:26,400 --> 00:09:28,500 like 1,000 Maniacs 214 00:09:28,500 --> 00:09:30,780 and you can see Albums got updated here. 215 00:09:30,780 --> 00:09:32,690 B.B. King, Black Crows. 216 00:09:34,080 --> 00:09:35,400 Let's try one that's got a lot. 217 00:09:35,400 --> 00:09:37,180 I think Blue Oyster Cult. 218 00:09:37,180 --> 00:09:38,580 You can see that's got multiple albums there, 219 00:09:38,580 --> 00:09:40,100 so that's worked quite nicely. 220 00:09:40,100 --> 00:09:41,690 Bruce Springsteen 221 00:09:41,690 --> 00:09:42,680 and you got a single one. 222 00:09:42,680 --> 00:09:44,730 We can try another one from Aerosmith. 223 00:09:44,730 --> 00:09:46,230 I know he's got quite a few. 224 00:09:46,230 --> 00:09:48,770 So, that's working nicely as well. 225 00:09:48,770 --> 00:09:51,620 Alright, but what I'm gonna do is finish the video here. 226 00:09:51,620 --> 00:09:53,770 In the next video we're gonna continue on 227 00:09:53,770 --> 00:09:56,230 and we're going to get the songs working as well. 228 00:09:56,230 --> 00:09:58,050 So, we're gonna be able to click on an album 229 00:09:58,050 --> 00:10:00,170 and retrieve a list of songs. 230 00:10:00,170 --> 00:10:02,190 So, I'll see you in the next video.