1 1 00:00:04,470 --> 00:00:08,060 Hello again, in previous lecture, what we did 2 2 00:00:08,060 --> 00:00:11,520 we created our Add Member python file 3 3 00:00:11,520 --> 00:00:13,720 also Add Book python file 4 4 00:00:13,720 --> 00:00:16,890 so it's time to display our books from, 5 5 00:00:16,890 --> 00:00:18,640 we will get all our books from database 6 6 00:00:18,640 --> 00:00:20,700 and display them here. 7 7 00:00:20,700 --> 00:00:22,960 This is first list box, and second list box, 8 8 00:00:22,960 --> 00:00:25,760 we will display details of our books. 9 9 00:00:25,760 --> 00:00:27,420 So to do that, what we need to do, 10 10 00:00:27,420 --> 00:00:31,830 here we need to go to main.py 11 11 00:00:31,830 --> 00:00:36,830 and here I will create a definition to increase my books 12 12 00:00:36,860 --> 00:00:38,720 to do that, what I need to do, I will say here 13 13 00:00:38,720 --> 00:00:41,890 def displayBooks 14 14 00:00:43,040 --> 00:00:46,950 and here I need to say (self): 15 15 00:00:48,350 --> 00:00:50,640 After that here what I need to do, 16 16 00:00:50,640 --> 00:00:54,650 I need to get all my books from my database, right? 17 17 00:00:54,650 --> 00:00:55,980 Here I will say, 18 18 00:00:57,220 --> 00:00:59,390 let's say books= 19 19 00:00:59,390 --> 00:01:02,340 our query is short so I will say here, 20 20 00:01:02,340 --> 00:01:03,890 cur.execute 21 21 00:01:04,980 --> 00:01:07,530 my query will be "SELECT * FROM 22 22 00:01:10,590 --> 00:01:14,700 my table name is book, right? So I will say books" 23 23 00:01:16,910 --> 00:01:19,200 After that what I need to do here, I need to use 24 24 00:01:19,200 --> 00:01:21,730 .fetchall() 25 25 00:01:21,730 --> 00:01:24,720 And now I, 26 26 00:01:24,720 --> 00:01:28,580 this function will return all the books from database. 27 27 00:01:28,580 --> 00:01:30,940 We will return, so what I need to do, 28 28 00:01:30,940 --> 00:01:35,660 now I want to add these books to my list box, right? 29 29 00:01:35,660 --> 00:01:38,140 To do that, what I need to do, I need to use for look, 30 30 00:01:38,140 --> 00:01:42,250 for book in books: 31 31 00:01:42,250 --> 00:01:44,410 and after that, here I will say self. 32 32 00:01:45,400 --> 00:01:48,200 But before that let me show you something here. 33 33 00:01:48,200 --> 00:01:51,370 For example if I say print(book) 34 34 00:01:52,950 --> 00:01:53,780 what I need to do, 35 35 00:01:53,780 --> 00:01:58,020 I need to call this function somewhere here, 36 36 00:01:59,410 --> 00:02:00,240 because 37 37 00:02:01,110 --> 00:02:04,370 here I need to say #functions 38 38 00:02:06,520 --> 00:02:10,020 displayBooks(self) 39 39 00:02:11,180 --> 00:02:14,260 If I run my application now, I want to show you something. 40 40 00:02:16,340 --> 00:02:18,040 So now, here, 41 41 00:02:20,530 --> 00:02:22,200 python returns for us, 42 42 00:02:22,200 --> 00:02:25,520 you see this is our fetchall function, books actually. 43 43 00:02:25,520 --> 00:02:28,370 So it returns for us, a tuple, right? 44 44 00:02:28,370 --> 00:02:30,520 The first one, second one, and so on. 45 45 00:02:30,520 --> 00:02:35,370 So if I want to get ID of my book, I need to say here 46 46 00:02:35,370 --> 00:02:37,730 book, square brackets zero. 47 47 00:02:37,730 --> 00:02:40,360 This is because this first index, okay? 48 48 00:02:40,360 --> 00:02:42,390 For the name, by the way, I will get only 49 49 00:02:42,390 --> 00:02:46,500 for the first list box, ID and the name. 50 50 00:02:46,500 --> 00:02:50,010 So here what I need to say, and we need to come here 51 51 00:02:50,010 --> 00:02:51,060 for loop, right? 52 52 00:02:53,020 --> 00:02:56,320 If I want to add my list box, I need to say here 53 53 00:02:56,320 --> 00:02:57,310 self.list_books 54 54 00:02:59,370 --> 00:03:01,700 and insert() 55 55 00:03:01,700 --> 00:03:05,270 and here, what I need to do, it says index, 56 56 00:03:05,270 --> 00:03:08,680 so, for index I need to create another variable here 57 57 00:03:08,680 --> 00:03:10,610 and I will say count, 58 58 00:03:10,610 --> 00:03:13,000 count=0 59 59 00:03:13,000 --> 00:03:16,330 the first one will be count 60 60 00:03:17,410 --> 00:03:20,470 and after that here for the ID, what I need to say 61 61 00:03:20,470 --> 00:03:21,970 I need to say str 62 62 00:03:25,470 --> 00:03:29,250 and (book[0]) 63 63 00:03:29,250 --> 00:03:31,380 this is my ID, why str? 64 64 00:03:31,380 --> 00:03:34,860 Because my ID is already an integer value 65 65 00:03:34,860 --> 00:03:38,710 and I can only add to list, string values 66 66 00:03:38,710 --> 00:03:41,010 that's why I have to convert it. 67 67 00:03:41,010 --> 00:03:45,320 Also, what I want to do, I want to use here 68 68 00:03:45,320 --> 00:03:46,560 a string, so "-" 69 69 00:03:47,920 --> 00:03:50,950 and again + my book name 70 70 00:03:50,950 --> 00:03:53,940 my book name is at index [1] 71 71 00:03:55,080 --> 00:03:56,840 like that, very simple. 72 72 00:03:56,840 --> 00:03:58,060 And here, 73 73 00:03:59,540 --> 00:04:00,850 there's gaps, like that. 74 74 00:04:02,040 --> 00:04:06,210 After that, each time what I need to do now, I need to 75 75 00:04:06,210 --> 00:04:10,470 increase, I need to raise my count value +=1 76 76 00:04:12,610 --> 00:04:15,560 For the next one, for example for this one, index will be 0 77 77 00:04:15,560 --> 00:04:19,390 for the next one, index will be count 1 78 78 00:04:19,390 --> 00:04:21,770 and on my other book, that one 79 79 00:04:21,770 --> 00:04:24,630 and for other one, count will be 2 80 80 00:04:24,630 --> 00:04:29,230 and here index will be 2 and my book ID and book name. 81 81 00:04:29,230 --> 00:04:33,030 Now if I run my application I can see my books. 82 82 00:04:33,030 --> 00:04:34,470 Let's do it, if I run 83 83 00:04:36,010 --> 00:04:37,830 main.py 84 84 00:04:37,830 --> 00:04:39,440 so something wrong here, 85 85 00:04:40,920 --> 00:04:44,810 19 and the self.list_books.insert 86 86 00:04:44,810 --> 00:04:47,270 count, str, book. 87 87 00:04:47,270 --> 00:04:50,490 And here because, 88 88 00:04:50,490 --> 00:04:55,190 you need to use here, parentheses, close parentheses 89 89 00:04:55,190 --> 00:05:00,190 and that one also needs these parentheses. 90 90 00:05:01,100 --> 00:05:02,980 Actually this one, it doesn't need, 91 91 00:05:04,330 --> 00:05:07,280 that one is fine. 92 92 00:05:07,280 --> 00:05:10,100 And here I need to remove this one. 93 93 00:05:10,100 --> 00:05:15,100 (count,str(book[0])+ "-" +book[1]) 94 94 00:05:16,790 --> 00:05:20,150 okay, now is fine, if I run my application 95 95 00:05:20,150 --> 00:05:23,460 as you see now, here, boom, our books are here 96 96 00:05:25,360 --> 00:05:26,280 what I need to do, 97 97 00:05:27,120 --> 00:05:30,670 the first one is ID and the second one is name of book. 98 98 00:05:30,670 --> 00:05:35,670 So as you see now when I click my book, I don't see anything 99 99 00:05:35,690 --> 00:05:39,700 so what I need to do, I need to, here, create click event 100 100 00:05:39,700 --> 00:05:43,290 for my details of my books. 101 101 00:05:43,290 --> 00:05:47,300 To do that what I need to do, here I will go to 102 102 00:05:47,300 --> 00:05:49,510 somewhere here, okay? 103 103 00:05:49,510 --> 00:05:50,340 And here. 104 104 00:05:53,410 --> 00:05:56,150 I will say, after for loop, and 105 105 00:05:56,400 --> 00:05:58,600 What I need to do now, I need to create here, 106 106 00:05:58,600 --> 00:06:01,190 my single click event, so I will say 107 107 00:06:01,190 --> 00:06:04,180 self.list_books 108 108 00:06:04,180 --> 00:06:07,860 this is my list item, and I will use here, bind method. 109 109 00:06:08,710 --> 00:06:11,410 And for the bind method, I will say, single quotes 110 110 00:06:12,800 --> 00:06:16,130 '<<ListboxSelect>>' 111 111 00:06:17,710 --> 00:06:18,630 and here 112 112 00:06:19,550 --> 00:06:20,380 after that what I need to do, 113 113 00:06:20,380 --> 00:06:23,710 I need to create a function, I will say 114 114 00:06:23,710 --> 00:06:25,930 bookInfo 115 115 00:06:27,740 --> 00:06:30,550 To create my bookInfo, what I need to do, 116 116 00:06:30,550 --> 00:06:32,080 I need to come here 117 117 00:06:34,340 --> 00:06:36,470 and I will say def bookInfo 118 118 00:06:40,060 --> 00:06:43,460 and here I need to pass an event. 119 119 00:06:44,540 --> 00:06:49,030 After that, here, I need to get the value of my book, 120 120 00:06:49,030 --> 00:06:51,030 value=str 121 121 00:06:54,180 --> 00:06:57,030 str, string, 122 122 00:06:57,030 --> 00:07:02,030 (self.list_books.get 123 123 00:07:05,610 --> 00:07:10,610 (self.list_books.curselection())) 124 124 00:07:13,100 --> 00:07:15,980 and after that, what I need to do is say 125 125 00:07:15,980 --> 00:07:18,330 id, I need to id, right? 126 126 00:07:18,330 --> 00:07:20,920 Id=value.split 127 127 00:07:22,700 --> 00:07:27,700 and I need to split by dash, so here I will say, 128 128 00:07:27,740 --> 00:07:28,660 dash ('-') 129 129 00:07:28,660 --> 00:07:31,230 and the first index [0] 130 130 00:07:31,230 --> 00:07:35,480 Remember we already learned that one, what it means. 131 131 00:07:36,720 --> 00:07:38,240 Here I'm getting my value 132 132 00:07:38,240 --> 00:07:41,160 value is my, for example here, what I am listing, 133 133 00:07:41,160 --> 00:07:44,370 I am listing 1-Crime and Punishment. 134 134 00:07:44,370 --> 00:07:49,370 So this is the value but I need to split my value by dash 135 135 00:07:49,540 --> 00:07:54,470 and after that, that means I can get ID of my book. 136 136 00:07:54,470 --> 00:07:58,890 Here, for the book I will say, I need to go to my database 137 137 00:07:58,890 --> 00:08:02,160 to get all information of this book. 138 138 00:08:02,160 --> 00:08:04,300 I will use ID, by the way, 139 139 00:08:04,300 --> 00:08:07,360 =cur.execute 140 140 00:08:09,300 --> 00:08:11,010 and I will say here 141 141 00:08:11,010 --> 00:08:14,730 ("SELECT * FROM books 142 142 00:08:16,550 --> 00:08:20,170 my table name, and I will say here, 143 143 00:08:20,170 --> 00:08:23,450 WHERE book_id=? 144 144 00:08:29,040 --> 00:08:30,920 And here, I need to say here, 145 145 00:08:30,920 --> 00:08:31,900 comma 146 146 00:08:31,900 --> 00:08:36,900 and after that here (id,) but again comma, don't forget that 147 147 00:08:39,070 --> 00:08:40,930 what is next? I need to say 148 148 00:08:40,930 --> 00:08:45,530 book_info, the info of my book, 149 149 00:08:46,470 --> 00:08:51,390 =book.fetchall() 150 150 00:08:53,930 --> 00:08:58,260 And now it's fine, I got all the information from my 151 151 00:08:58,260 --> 00:09:01,220 database for my book info, let's say 152 152 00:09:01,220 --> 00:09:03,710 print(book_info) 153 153 00:09:05,940 --> 00:09:08,240 so if I run my application, 154 154 00:09:10,760 --> 00:09:14,130 Crime and Punishment, but I don't see anything here, 155 155 00:09:14,130 --> 00:09:17,330 let's check our print statement, it's here. 156 156 00:09:18,200 --> 00:09:20,290 This is our print statement 157 157 00:09:20,290 --> 00:09:21,560 but here this is different. 158 158 00:09:21,560 --> 00:09:23,030 You see this is tuple? 159 159 00:09:23,030 --> 00:09:25,490 But this is list and after that tuples. 160 160 00:09:25,490 --> 00:09:29,010 So, if I want to get my book name, I need to say here 161 161 00:09:30,570 --> 00:09:35,570 book_info, index 0, and again after that, index 1. 162 162 00:09:36,760 --> 00:09:40,220 Because this is a list and inside of this we have a tuple. 163 163 00:09:41,570 --> 00:09:44,020 That's why, so let me shrink this guy. 164 164 00:09:44,020 --> 00:09:46,510 Here what I will say, 165 165 00:09:46,510 --> 00:09:51,510 I need to here update my list details. 166 166 00:09:54,730 --> 00:09:57,230 So I would say self.list_details 167 167 00:09:57,230 --> 00:09:58,360 my second list 168 168 00:10:01,900 --> 00:10:03,770 .insert 169 169 00:10:03,770 --> 00:10:07,610 and the first one is index, index will be zero. 170 170 00:10:07,610 --> 00:10:10,650 Now I know this is only for one book, 171 171 00:10:10,650 --> 00:10:13,790 so I don't need to create a count variable. 172 172 00:10:13,790 --> 00:10:14,870 Here I will say "Book Name : " 173 173 00:10:28,030 --> 00:10:30,350 I don't need the ID here, by the way. 174 174 00:10:30,350 --> 00:10:33,350 +book_info 175 175 00:10:35,330 --> 00:10:36,690 and the [0] 176 176 00:10:36,690 --> 00:10:38,610 and the second one is [1] 177 177 00:10:39,590 --> 00:10:42,280 this is for book name and after that, 178 178 00:10:42,280 --> 00:10:46,220 here we can press Ctrl + D 179 179 00:10:47,260 --> 00:10:52,260 four things we have, so that one is going to be index 1 180 180 00:10:52,410 --> 00:10:56,140 index 2, index 3, 181 181 00:10:56,140 --> 00:10:58,520 and this is the author, 182 182 00:10:58,520 --> 00:11:00,420 so I will say here Author, 183 183 00:11:02,170 --> 00:11:07,170 and that one is going to be 2, 3, and 4, 184 184 00:11:07,350 --> 00:11:11,010 and this one is page, let's say Page, 185 185 00:11:13,280 --> 00:11:17,850 and this one is language, so Language. 186 186 00:11:17,850 --> 00:11:20,100 Okay, that's fine, now, 187 187 00:11:23,220 --> 00:11:25,560 let me run our application, actually we need to do 188 188 00:11:25,560 --> 00:11:28,040 also, something else, taken or not, right? 189 189 00:11:28,040 --> 00:11:30,510 So, Crime and Punishment, as you see now it's here, 190 190 00:11:30,510 --> 00:11:32,450 Crime and Punishment. 191 191 00:11:32,450 --> 00:11:34,720 So there will be a problem, I will now show it, 192 192 00:11:34,720 --> 00:11:36,510 and also I want to show here 193 193 00:11:38,450 --> 00:11:40,450 taken or not, okay? 194 194 00:11:40,450 --> 00:11:43,150 Our book is already borrowed, or not. 195 195 00:11:43,150 --> 00:11:44,800 So how can I show it? 196 196 00:11:44,800 --> 00:11:49,370 It is our last field at our table, 197 197 00:11:49,370 --> 00:11:51,600 so here I can say 198 198 00:11:51,600 --> 00:11:56,600 if, use here if statement, because our default value 199 199 00:11:57,420 --> 00:12:00,960 for status, it was zero, remember. 200 200 00:12:00,960 --> 00:12:04,020 Let me open my database, 201 201 00:12:05,470 --> 00:12:06,300 and, 202 202 00:12:09,110 --> 00:12:09,940 here, 203 203 00:12:13,080 --> 00:12:16,050 books, browse, now check it. 204 204 00:12:16,050 --> 00:12:19,110 Here our status, right? 205 205 00:12:19,110 --> 00:12:21,810 Book status is zero for all of them, 206 206 00:12:21,810 --> 00:12:25,980 so I need to check it here, if book info and, 207 207 00:12:25,980 --> 00:12:28,820 here, this status is ==0 208 208 00:12:28,820 --> 00:12:32,290 that means this book is in my library. 209 209 00:12:32,290 --> 00:12:34,800 If it is 1, that means it is taken, right? 210 210 00:12:34,800 --> 00:12:37,460 So, here what I will say, 211 211 00:12:40,760 --> 00:12:41,590 if book_info 212 212 00:12:43,070 --> 00:12:47,420 that one is going to be [0], but for the book status, 213 213 00:12:47,420 --> 00:12:49,940 I'm going to say here, after 4, 5. 214 214 00:12:51,630 --> 00:12:54,970 ==0, that means it is in my library, 215 215 00:12:54,970 --> 00:12:59,560 self.list_details.insert 216 216 00:12:59,560 --> 00:13:04,170 and this index will be 4, and I will say here, 217 217 00:13:04,170 --> 00:13:08,160 "Status : Not Taken" 218 218 00:13:08,160 --> 00:13:10,770 okay, "Not Taken", 219 219 00:13:10,770 --> 00:13:13,500 or "In Library", or "Available", doesn't matter, 220 220 00:13:13,500 --> 00:13:14,450 whatever you want. 221 221 00:13:14,450 --> 00:13:16,040 You can say "Available", "Not Available". 222 222 00:13:16,040 --> 00:13:20,340 Let's say available is better, "Available" 223 223 00:13:21,860 --> 00:13:26,050 and if not, that means this book is not available any more. 224 224 00:13:26,050 --> 00:13:26,880 So, self. 225 225 00:13:28,900 --> 00:13:32,100 List_details.insert 226 226 00:13:32,100 --> 00:13:36,460 and again my index is 4, and comma here, I will say 227 227 00:13:36,460 --> 00:13:39,070 "Status: Not Available" 228 228 00:13:43,660 --> 00:13:47,710 Okay, great, so if I run my application now, again. 229 229 00:13:49,060 --> 00:13:51,540 Crime and Punishment, Status : Available. 230 230 00:13:51,540 --> 00:13:55,340 Da Vinci Code, so we have also another problem. 231 231 00:13:56,610 --> 00:13:57,880 For the first one, it's okay, 232 232 00:13:57,880 --> 00:14:00,320 but this is Crime and Punishment, you see this here? 233 233 00:14:00,320 --> 00:14:04,400 And other one is, book name is origin is here, 234 234 00:14:04,400 --> 00:14:07,800 each time when I click this list box, 235 235 00:14:08,790 --> 00:14:11,550 I see all the informations, 236 236 00:14:11,550 --> 00:14:13,870 the other books before I clicked. 237 237 00:14:13,870 --> 00:14:16,160 So, how can I solve this problem? 238 238 00:14:16,160 --> 00:14:18,500 To do that, we need to come here and say, 239 239 00:14:18,500 --> 00:14:22,160 we need to delete our list details each time. 240 240 00:14:24,100 --> 00:14:27,660 I need to do it here, before adding my details. 241 241 00:14:27,660 --> 00:14:29,240 So I will say here, simply, 242 242 00:14:29,240 --> 00:14:32,180 self.list_details.delete 243 243 00:14:34,180 --> 00:14:37,540 from first characters until 'end'. 244 244 00:14:37,540 --> 00:14:39,040 That's all. 245 245 00:14:39,040 --> 00:14:41,880 And now everything is fine. 246 246 00:14:43,010 --> 00:14:44,950 Just click Crime and Punishment, 247 247 00:14:44,950 --> 00:14:48,530 Book Name is this one, Author, Page, Language, 248 248 00:14:48,530 --> 00:14:51,010 the Status is Available, because all of them is now, 249 249 00:14:51,010 --> 00:14:53,310 the book status is zero, right? 250 250 00:14:53,310 --> 00:14:55,000 So, The Da Vinci Code, you see? 251 251 00:14:55,000 --> 00:14:57,060 I delete the first one, first record, 252 252 00:14:57,060 --> 00:14:59,630 and now this is for Da Vinci Code. 253 253 00:14:59,630 --> 00:15:01,630 Origin, and Cyber Storm, 254 254 00:15:03,060 --> 00:15:07,660 The Power of Positive Energy, is also here. 255 255 00:15:07,660 --> 00:15:11,350 So for this lesson, that's all, see you in the next.