1 00:00:00,319 --> 00:00:06,000 Hey welcome to this new lecture and here we are continuing to build our bookstore 2 00:00:06,000 --> 00:00:12,900 program that stores and retrieves book records from a database. So far as you 3 00:00:12,900 --> 00:00:18,539 know we have been the graphical user interface with tkinter, so this is our 4 00:00:18,539 --> 00:00:28,529 script. So basically we have the front end of our program, and now so this is 5 00:00:28,529 --> 00:00:32,759 our graphical user interface and basically what we need to do now is 6 00:00:32,759 --> 00:00:41,070 to attach some functions to all these buttons, so let's say for example when we 7 00:00:41,070 --> 00:00:46,800 press the view all button a function will be trigger that selects data from 8 00:00:46,800 --> 00:00:54,989 the SQLite database, and then gets those data and put them all these listbox here, so 9 00:00:54,989 --> 00:01:02,160 that means we need to create a database an SQLite database with SQLite3 and we 10 00:01:02,160 --> 00:01:07,770 also need to create a table first, so we could do this in the script, but I prefer 11 00:01:07,770 --> 00:01:12,869 to actually create another script and then import that script in here. 12 00:01:12,869 --> 00:01:21,740 So, import, let's call the new script backend, so we need to create the backend 13 00:01:21,740 --> 00:01:30,299 dot py script all right and I also want to change the name of this, 14 00:01:30,299 --> 00:01:36,770 of my frontend scripts, so that we keep the same pattern, so let's say frontend. 15 00:01:36,770 --> 00:01:44,460 Alright, so we are in frontend script now and here we are in the backend script so 16 00:01:44,460 --> 00:01:48,780 what that means is let's say you create a view function, so that selects data 17 00:01:48,780 --> 00:01:53,250 from the database and you create that function here, and then you go to the 18 00:01:53,250 --> 00:01:58,439 view all button and then you'd add a command parameter here, and that would be 19 00:01:58,439 --> 00:02:04,079 equal to let's say command just to give you an idea. Command equals to backend 20 00:02:04,079 --> 00:02:08,899 and the name of the function. So something like that. 21 00:02:08,899 --> 00:02:16,380 Alright, hope this makes sense. However for now it would be great if we 22 00:02:16,380 --> 00:02:21,240 forget about this script, so we need to keep in mind the graphical user 23 00:02:21,240 --> 00:02:27,420 interface but it's good not to interact with these widgets for now, so what we do 24 00:02:27,420 --> 00:02:32,070 instead is print out the output of the functions, the function that 25 00:02:32,070 --> 00:02:38,970 we built as strings in the command line here. So will not throw those data 26 00:02:38,970 --> 00:02:47,600 in the list box, but in the terminal here. Alright, let's start coding, import 27 00:02:47,600 --> 00:02:54,570 sqlite3. That is the first thing you want to do, and I'll keep an image of the 28 00:02:54,570 --> 00:03:00,959 graphical user interface here so that he it's easy for you to refer to it. 29 00:03:00,959 --> 00:03:06,630 So we need to create a view function, but before that I would like to create a 30 00:03:06,630 --> 00:03:11,280 database, so a connection to the database. And I would like to put that inside 31 00:03:11,280 --> 00:03:21,090 a function, so let's say function connect. And we have a connection object, so that 32 00:03:21,090 --> 00:03:30,450 would be sqlite3.connect, and let's call what database books.db. 33 00:03:30,450 --> 00:03:36,180 Alright, so we establish a connection to the database, then we define a cursor 34 00:03:36,180 --> 00:03:45,750 object like that and next we need to execute an SQL statement, so cursor 35 00:03:45,750 --> 00:03:56,970 object execute and inside double quotes we input the SQL statement. So we have a 36 00:03:56,970 --> 00:04:01,890 database and we also need a table in the database where to store or book information. 37 00:04:01,890 --> 00:04:08,730 Now think about that. When the user, when you give this program to the 38 00:04:08,730 --> 00:04:15,060 user they will execute the program and every time they execute the program your 39 00:04:15,060 --> 00:04:21,479 script has to check if there is an existing table in a books database, so if 40 00:04:21,479 --> 00:04:26,380 there is a table you'd say don't create the table. If there is not a table, 41 00:04:26,380 --> 00:04:35,130 you'd want to create the table, so create table if not exists, and let's call table 42 00:04:35,130 --> 00:04:42,160 book just to differentiate it from the database name, and then you'd want to 43 00:04:42,160 --> 00:04:48,010 pass some parameters. The first parameter I would like to pass is the ID. 44 00:04:48,010 --> 00:04:53,680 Don't confuse is with the ISBN number, the book identification. This is just a 45 00:04:53,680 --> 00:04:59,560 number that will start from zero and it will auto increment by one so that we 46 00:04:59,560 --> 00:05:04,960 have control of how many records we have in the database, so it's a good idea to 47 00:05:04,960 --> 00:05:10,600 have this ID here and that would be an integer and you need to set it to 48 00:05:10,600 --> 00:05:18,700 primary key, so that's mandatory column in your table and you have title as text. 49 00:05:18,700 --> 00:05:33,550 Author as text, year as integer, and ISBN. That's an integer as well. Aright, that's it. 50 00:05:33,550 --> 00:05:45,970 You want to commit your changes and then close the connection. Alright, so we 51 00:05:45,970 --> 00:05:50,800 created a connect function. Later when we will run this script, so the frontend 52 00:05:50,800 --> 00:05:56,200 script and then you import backend, this script, the script on the backend, 53 00:05:56,200 --> 00:06:01,210 so the code of the backend will be read by Python, but if you 54 00:06:01,210 --> 00:06:06,730 haven't called this function, this code will not be executed. So what you want to 55 00:06:06,730 --> 00:06:13,630 do instead is to have this function executed any time you'd execute this 56 00:06:13,630 --> 00:06:19,720 function, so you execute the frontend and this imports backend, so it executes 57 00:06:19,720 --> 00:06:24,310 all the code and this line here is executed, so a function will run 58 00:06:24,310 --> 00:06:31,750 any time you run the frontend. Alright. I hope that makes sense or let me 59 00:06:31,750 --> 00:06:38,350 quickly explain that better. So we have an A dot py script, and we have 60 00:06:38,350 --> 00:06:44,800 a B dot py script, and in the A dot py script we import the B script and what 61 00:06:44,800 --> 00:06:51,640 we have in the B script is a function through that prints out something on the 62 00:06:51,640 --> 00:07:03,990 screen, 'Hey', and save this and save this as well, so when you execute a dot py 63 00:07:03,990 --> 00:07:08,680 nothing happens for now because you have just defined your script, but if you call 64 00:07:08,680 --> 00:07:16,180 the script here, alright, save and execute A again. This time you'll get the 65 00:07:16,180 --> 00:07:20,410 the output of the function. So that's the idea. Thanks. 66 00:07:20,410 --> 00:07:28,600 And back to our real world script. Alright, now we have the connect function. 67 00:07:28,600 --> 00:07:33,670 Let's start by creating an insert function, so a function that 68 00:07:33,670 --> 00:07:39,370 will insert some data in the database. So I'm starting with the insert function 69 00:07:39,370 --> 00:07:44,740 because that's, because that's an easier function than the other one, so I'd like 70 00:07:44,740 --> 00:07:53,140 to start from the easy things first. Def insert, so that's how I call my function, 71 00:07:53,140 --> 00:08:00,160 and think about that. You have four entries there so you expect from the 72 00:08:00,160 --> 00:08:08,500 user to enter a title, author, and a year, and an ISBN number. So you want to pass 73 00:08:08,500 --> 00:08:20,920 four parameters here, and ISBN. Alright. And then of course you need to 74 00:08:20,920 --> 00:08:29,740 connect to the database because this function here it will just connect to 75 00:08:29,740 --> 00:08:33,880 the database temporarily and it will check if there is a table and it will 76 00:08:33,880 --> 00:08:38,800 close the connection, so you have a closed connection here. When you when you press 77 00:08:38,800 --> 00:08:43,300 the insert button, well when you press the add entry button you need to create 78 00:08:43,300 --> 00:08:49,090 a new connection to the database. And let me go ahead and get these two 79 00:08:49,090 --> 00:08:53,970 lines as well because we need them too. Alright, and here is where 80 00:08:53,970 --> 00:09:02,550 where we execute our specific SQL statement inside quotes. So what the add 81 00:09:02,550 --> 00:09:13,490 entry button will do is conserved into book so the table book values, and here 82 00:09:13,490 --> 00:09:21,750 is a trick now. We have an id here, so this is an auto increment value, so we 83 00:09:21,750 --> 00:09:29,550 don't have to pass that manually and what we can do instead is pass a null value 84 00:09:29,550 --> 00:09:35,819 here and Python understand that and it will create the id automatically, and then 85 00:09:35,819 --> 00:09:45,110 we have for question marks. One for each of our values, and then you need to pass your 86 00:09:45,110 --> 00:09:52,639 parameters as a second parameter over the execute function so as a tuple here. 87 00:09:52,639 --> 00:10:04,680 Title, author, year, and ISBN, all right. Let me write another function, and then we 88 00:10:04,680 --> 00:10:10,800 execute the script to try it and see how it is going, so def let's save the 89 00:10:10,800 --> 00:10:19,579 view function, so this will fetch all the rows of the table, 90 00:10:19,579 --> 00:10:26,790 so we copy this and put them here, so we connect to the database, create a cursor 91 00:10:26,790 --> 00:10:32,220 object, and then we execute an SQL statement, and then because this this 92 00:10:32,220 --> 00:10:37,050 would be a select statement so will not perform any changes to the database 93 00:10:37,050 --> 00:10:47,300 we don't need a commit method here. And this is actually quite simple. Select all from 94 00:10:47,300 --> 00:10:55,110 book, so you actually need to return your selection as a tuple, and then you grab 95 00:10:55,110 --> 00:11:00,059 that selection and you put that in the list box, so you want the view function 96 00:11:00,059 --> 00:11:05,210 to return a tuple, so let's say rows equals to cur.fetchall. 97 00:11:05,210 --> 00:11:12,290 And then you can close the connection, but your data will be stored will 98 00:11:12,290 --> 00:11:19,880 remain stored in the rows variable, so you can just return rows here, alright. 99 00:11:19,880 --> 00:11:27,290 So let's try out the script now, and the way to try it out would be to use a 100 00:11:27,290 --> 00:11:35,210 print statement, so we'd want to call insert and then we would enter some data 101 00:11:35,210 --> 00:11:40,490 manually, so in the real program this insert function will be executed 102 00:11:40,490 --> 00:11:47,780 somewhere in the add entry button, but for now we need to keep things simple 103 00:11:47,780 --> 00:11:55,420 and reach our objective step-by-step so insert let's pass some values here, title. 104 00:11:55,420 --> 00:12:05,810 Let's say "The sea" and then author John Tablet. Alright and year is an integer, 105 00:12:05,810 --> 00:12:12,200 so you just need to pass a number and ISBN also is an integer so no need to 106 00:12:12,200 --> 00:12:17,480 put them inside quotes, and then you would also want to print out the output 107 00:12:17,480 --> 00:12:26,800 of the view function. Great, so let's see how this goes. 108 00:12:27,010 --> 00:12:35,120 That'll be the backend script. Great, so the record was 109 00:12:35,120 --> 00:12:39,590 inserted into the database and it was printed out from the view function. 110 00:12:39,590 --> 00:12:44,600 If you execute the script again, you'll get two records because the insert function was 111 00:12:44,600 --> 00:12:52,760 executed two times. Alright, seems to be going well, so what else do we have? 112 00:12:52,760 --> 00:13:01,580 We have a search function and we would implement an or search, so what that 113 00:13:01,580 --> 00:13:06,500 means is that the user will enter a title, or an author name, or the year, 114 00:13:06,500 --> 00:13:12,260 or the ISBN number, or he may choose to enter all of them at the same time, 115 00:13:12,260 --> 00:13:19,160 but if let's say he enters the year only he'll get all the rows corresponding to that year. 116 00:13:19,160 --> 00:13:38,269 So here we need to pass four parameters as well, ISBN, alright. And then we need 117 00:13:38,269 --> 00:13:43,279 these lines of code, so connects to the database, cursor objects, and then 118 00:13:43,279 --> 00:13:54,790 execute, so let's stay here for a while. We want to select all from book where 119 00:13:54,790 --> 00:14:07,279 title is equal to something or author is equal to something, or year equals to 120 00:14:07,279 --> 00:14:16,670 something, or ISBN, and then the second parameter would be the tuple of the 121 00:14:16,670 --> 00:14:25,970 arguments, title, author, year, ISBN, and then we need to fetch all the rows that will be 122 00:14:25,970 --> 00:14:32,779 returned from this query, and store them in these rows variable, close the 123 00:14:32,779 --> 00:14:41,180 connection, and again return rows. Alright, one problem here though that we 124 00:14:41,180 --> 00:14:46,550 may encounter is that the user may want to pass let's say the author name only 125 00:14:46,550 --> 00:14:51,589 to the entries, so this function will get only the arguments for the author 126 00:14:51,589 --> 00:14:57,769 parameter and these other parameters will remain without a value, and then 127 00:14:57,769 --> 00:15:01,490 you'll get an error because your function is designed to have four 128 00:15:01,490 --> 00:15:06,769 parameters. In that case you'd want to pass some empty strings as default values. 129 00:15:06,769 --> 00:15:16,610 Alright, this too. So now if the user passes an author name, let's say 130 00:15:16,610 --> 00:15:23,660 John Smith this SQL statement will search for an empty title, or for the author name 131 00:15:23,660 --> 00:15:29,120 John Smith, or for an empty year, or ISBN and so on, and it will return all the rows 132 00:15:29,120 --> 00:15:35,510 with John Smith as author name. Alright, so let's save this and try it out. 133 00:15:35,510 --> 00:15:44,870 So we have connect, insert, connect, print view. Let's say "The earth". I'm 134 00:15:44,870 --> 00:15:57,160 inserting another record here. John Smith. Alright, and then let's say print search and 135 00:15:57,160 --> 00:16:07,490 you want to pass some arguments. For example author equals to John Smith, alright. 136 00:16:07,490 --> 00:16:14,740 Without a quote here and let's see how this goes. 137 00:16:21,220 --> 00:16:29,839 So we got here this list of rows from the view function because we also have 138 00:16:29,839 --> 00:16:35,149 a view function here and then the search function returned this row for us. 139 00:16:35,149 --> 00:16:43,250 So the row with ID 3 and with John Smith as author, so that is working well. 140 00:16:43,250 --> 00:16:49,520 Let's go ahead and build a delete function now. So think about how the users want to 141 00:16:49,520 --> 00:16:55,399 delete a record? So most probably they'd want you to see or some records in 142 00:16:55,399 --> 00:17:00,980 the list box, and that they would want to select one of the records, let's say the 143 00:17:00,980 --> 00:17:06,770 record for ID 3, "The earth", John Smith etc. So they would select that and then would press 144 00:17:06,770 --> 00:17:14,300 the delete button. So that means we want to first grab the list selection, so the 145 00:17:14,300 --> 00:17:19,640 selection from the list box. We would grab that as a tuple, but we'll think about 146 00:17:19,640 --> 00:17:25,819 that later, so we'll grab that as a tuple and then that tuple has a unique identification 147 00:17:25,819 --> 00:17:32,570 number, so the ID, so we would want to refer to that ID and then go to our database 148 00:17:32,570 --> 00:17:41,179 table and delete the row with that ID. So hope that is not very confusing and in the 149 00:17:41,179 --> 00:17:43,910 delete function so we would expect the ID of the tuple 150 00:17:43,910 --> 00:17:54,460 that the user will select. And then we would want to have this code 151 00:17:55,210 --> 00:18:00,500 and start from there, so connect to the database cursor object execute a statement. 152 00:18:00,500 --> 00:18:06,520 Alright, let's see how this goes. 153 00:18:09,520 --> 00:18:25,520 So we want to delete from book where ID is equal to something, great and then 154 00:18:25,520 --> 00:18:32,860 that's something is the ID parameter, so the argument of this parameter here. 155 00:18:32,860 --> 00:18:38,510 And this is the column name so don't confuse this with this. Column name, 156 00:18:38,510 --> 00:18:44,630 function, parameter. And don't forget the comma here and yeah that's basically it. 157 00:18:44,630 --> 00:18:51,140 Let's save this script now and let's execute it. We assert some data there. 158 00:18:51,140 --> 00:18:57,560 Let's say "The sun" this time, and so we'd have "The sea", "The sea" again, "The earth", 159 00:18:57,560 --> 00:19:08,750 and "The sun", so I'd like to delete "The earth" and I would want to put them in 160 00:19:08,750 --> 00:19:19,520 here, delete, so Earth has an ID of 3 so we would want to pass 3 here, and then 161 00:19:19,520 --> 00:19:23,560 we would view all the current records once we have deleted them. 162 00:19:23,560 --> 00:19:32,810 Alright, so here are our records and the Earth has disappeared, so it's working great. 163 00:19:32,810 --> 00:19:38,120 Again as I said this ID will be extracted from the tuple, so the tuple 164 00:19:38,120 --> 00:19:45,170 will have the ID as the first item and so basically we'll get from tuple get 165 00:19:45,170 --> 00:19:50,740 the icon with index 0 and we'll pass that first item to this delete function. 166 00:19:50,740 --> 00:19:59,810 Great, last function would be update. Update and again how would you want to 167 00:19:59,810 --> 00:20:05,870 update our records? So how the user wants to update the records? Well again they may 168 00:20:05,870 --> 00:20:13,160 expect to select row from the list box from the tkinter list box. The values of 169 00:20:13,160 --> 00:20:19,130 that row would display in the entry widgets. And that from where the user may want to 170 00:20:19,130 --> 00:20:25,400 change one of those cells, of those entries and press the update button, 171 00:20:25,400 --> 00:20:30,640 so again we will get the selection from the list box and then we would refer to the ID. 172 00:20:30,640 --> 00:20:38,480 So I did here as the first parameter, but we would also want to get the new values 173 00:20:38,480 --> 00:20:44,980 that the user would want from those entries, so let's say the user wanted to 174 00:20:44,980 --> 00:20:52,910 change the title from "The Sun" to "The moon" let's say, and you'd get that title 175 00:20:52,910 --> 00:20:56,980 so the title will be in the entry box and pass it here. 176 00:20:56,980 --> 00:21:09,320 Same for author, year and ISBN. Alright. And then you'd want to update your table, 177 00:21:09,320 --> 00:21:16,070 so date table where the ID is equal to this. Update table with these new values 178 00:21:16,070 --> 00:21:25,760 where ID is equal to this. First of all we need to get all these rows, alright. 179 00:21:25,760 --> 00:21:40,270 So update book and you say set, so the keyword set. Title equal to question mark. 180 00:21:41,680 --> 00:21:48,500 Same, year question mark and ISBN equals to question mark. 181 00:21:48,500 --> 00:21:56,120 So update book and set these values where ID is equal to another question 182 00:21:56,120 --> 00:22:03,800 mark, so then we pass the tuple here, so ID, title, author, and ISBN. 183 00:22:03,800 --> 00:22:10,040 So it's looking good. We commit the changes and we close the connection. 184 00:22:10,040 --> 00:22:18,050 And let's see how this goes. So currently we have these rows in the 185 00:22:18,050 --> 00:22:26,570 table, "The see", "The see" again, "The Sun". So I'd want to comment this out first 186 00:22:30,770 --> 00:22:42,200 and also delete, so let's focus on the update function, and update so you would 187 00:22:42,200 --> 00:22:49,910 want to pass the ID of the row so that you want to update, and then you want to 188 00:22:49,910 --> 00:22:54,620 pass the values in the correct order. So what you want for the new title? Let's 189 00:22:54,620 --> 00:22:58,430 say you "The moon" and then in the real interface, in the graphical user 190 00:22:58,430 --> 00:23:03,820 interface if the user wants to change only the title, he'll leave the other 191 00:23:03,820 --> 00:23:09,200 values as they are, so the these values would be replaced to the existing values. 192 00:23:09,200 --> 00:23:17,090 So John Smith and Year 1918, the ISBN number, but in this case we are just 193 00:23:17,090 --> 00:23:23,750 in a test mode so we can pass these values. Smooth, alright. 194 00:23:23,750 --> 00:23:37,460 And a number, so let's see how these goes now, and it seems it didn't work. 195 00:23:37,460 --> 00:23:43,520 "The Sun" is still there so let's see what we got here. Update title, author, 196 00:23:43,520 --> 00:23:53,680 year, ISBN where ID is, so the ID should be at the end of the tuple here. 197 00:23:56,140 --> 00:24:00,880 Because you know we put them in the correct order that we have set here. 198 00:24:00,880 --> 00:24:10,490 So let's try this out again. Now it's working, so the ID 4 was replaced to 199 00:24:10,490 --> 00:24:15,950 "The moon", John Smooth and the other values. Great, so I think we have the backend 200 00:24:15,950 --> 00:24:20,840 script ready. And what we need now is to attach all these 201 00:24:20,840 --> 00:24:26,960 functions to our buttons, so we still have work to do, and we'll do that 202 00:24:26,960 --> 00:24:27,679 in the next lecture.