1 00:00:00,000 --> 00:00:02,729 So in the previous lecture, I introduced 2 00:00:02,730 --> 00:00:06,000 you to the sqlite3 library. And 3 00:00:06,000 --> 00:00:07,920 I showed you how to create a table in 4 00:00:07,920 --> 00:00:11,670 your SQLite database, and how to 5 00:00:11,670 --> 00:00:13,920 insert data in your database and how to 6 00:00:13,920 --> 00:00:17,430 select data and view them in Python. So 7 00:00:17,430 --> 00:00:20,400 we have these four lines, four rows in 8 00:00:20,400 --> 00:00:23,460 our database table. And in this lecture, 9 00:00:23,460 --> 00:00:26,160 I'll quickly show you how to delete and 10 00:00:26,160 --> 00:00:29,520 update data from your table. So 11 00:00:29,520 --> 00:00:32,640 again, I'll create a function. So let me 12 00:00:32,640 --> 00:00:36,540 copy this. And we still need to connect 13 00:00:36,540 --> 00:00:38,880 to the database and create a cursor 14 00:00:38,880 --> 00:00:41,040 object. And then we need to use the 15 00:00:41,040 --> 00:00:44,250 execute method. And in this case, I 16 00:00:44,250 --> 00:00:47,700 would like to delete the row 17 00:00:47,700 --> 00:00:52,770 that has the Wine Glass. So I saw it 18 00:00:52,772 --> 00:00:57,430 execute, we would write DELETE FROM store 19 00:00:57,431 --> 00:00:59,526 [Author typing] 20 00:00:59,528 --> 00:01:04,260 WHERE item equals, I would like to use 21 00:01:04,410 --> 00:01:07,230 a question mark placeholder here. So 22 00:01:07,260 --> 00:01:09,600 question mark, and in brackets, we would 23 00:01:09,600 --> 00:01:12,120 want the parameter that we would pass to 24 00:01:12,120 --> 00:01:15,600 our function. So this item here is the 25 00:01:15,600 --> 00:01:19,080 one we put inside here, item and let me 26 00:01:19,080 --> 00:01:24,780 change the name of our function. So this 27 00:01:24,840 --> 00:01:27,300 is this one. Don't confuse it with this, 28 00:01:27,330 --> 00:01:30,600 this is the column of our table and the 29 00:01:30,600 --> 00:01:33,423 source, the row from the previous function. 30 00:01:33,424 --> 00:01:35,729 [Author typing] 31 00:01:35,731 --> 00:01:38,836 Delete this as well. And you also want to commit 32 00:01:38,837 --> 00:01:42,835 the changes to your database. So commit. 33 00:01:42,836 --> 00:01:45,596 [Author typing] 34 00:01:45,598 --> 00:01:49,562 So that's it. And I'd like comment this out. 35 00:01:49,563 --> 00:01:52,110 So I don't want to execute the Insert Function again. 36 00:01:52,890 --> 00:01:54,780 And I want the view function to be 37 00:01:54,780 --> 00:01:59,310 executed after we delete the row. So 38 00:01:59,340 --> 00:02:03,090 delete. So which item the row of 39 00:02:03,090 --> 00:02:06,510 which item would you want to delete. So 40 00:02:06,510 --> 00:02:10,920 the item Wine Glass, and let's see what 41 00:02:10,920 --> 00:02:15,483 will happen. We got an error on sqlite3 42 00:02:15,485 --> 00:02:17,790 error, incorrect number of binding 43 00:02:17,790 --> 00:02:23,370 supplied line 28. So I can see, yeah, there's 44 00:02:23,370 --> 00:02:26,010 something I often forgets, you need to 45 00:02:26,010 --> 00:02:28,410 add a comma here, you'll see that when I 46 00:02:28,410 --> 00:02:31,200 teach you how to connect to a PostgreSQL 47 00:02:31,200 --> 00:02:33,840 database, and how to query data from the 48 00:02:33,870 --> 00:02:37,200 PostgreSQL database. The code is quite 49 00:02:37,200 --> 00:02:39,660 similar, but it has some critical 50 00:02:39,660 --> 00:02:42,270 changes like this one here. So it can be 51 00:02:42,270 --> 00:02:44,460 confusing when you work with different 52 00:02:44,460 --> 00:02:48,367 kinds of databases. Let's try this again. 53 00:02:48,368 --> 00:02:50,999 [No audio] 54 00:02:51,000 --> 00:02:52,703 And yeah, it seems to be working now. 55 00:02:53,370 --> 00:02:56,910 Wine Glass is not here. So that's 56 00:02:56,910 --> 00:03:00,810 how you delete a row. Let's now add 57 00:03:00,840 --> 00:03:06,120 another function. For updating data 58 00:03:07,230 --> 00:03:09,630 update. In this one, I would like to 59 00:03:09,630 --> 00:03:11,940 update the quantity of Water 60 00:03:11,940 --> 00:03:14,550 Glasses. So I'll want to change this 61 00:03:14,550 --> 00:03:17,550 from 10 to 11. Again, you establish a 62 00:03:17,550 --> 00:03:19,140 connection to the database, so create a 63 00:03:19,140 --> 00:03:21,390 cursor object, and then you execute. And 64 00:03:21,390 --> 00:03:26,850 here you enter the UPDATE SQL command. 65 00:03:27,030 --> 00:03:29,130 So update, what you want to update is 66 00:03:29,400 --> 00:03:33,360 the store table. And which column do you 67 00:03:33,360 --> 00:03:36,660 want to update from the store table? So 68 00:03:36,720 --> 00:03:38,880 you want to SET the name of the column, 69 00:03:39,780 --> 00:03:42,000 which in this case would be quantity? 70 00:03:43,470 --> 00:03:45,630 And that would be equal to what, guess 71 00:03:45,630 --> 00:03:49,710 what, question mark. And then you say 72 00:03:49,770 --> 00:03:52,680 where, or let me show you how to update 73 00:03:52,680 --> 00:03:55,440 more than one column. So if you wanted 74 00:03:55,440 --> 00:03:57,840 more than one column, you'd use a comma 75 00:03:58,080 --> 00:04:01,050 and then say price equals another 76 00:04:01,050 --> 00:04:03,900 question mark. Alright, so we say update 77 00:04:03,900 --> 00:04:06,120 store and set quantity and price to 78 00:04:06,120 --> 00:04:09,120 these but which row so we need to 79 00:04:09,120 --> 00:04:14,250 specify conditional here. So WHERE 80 00:04:14,250 --> 00:04:20,280 item equals to Wine Glass, or you can 81 00:04:20,280 --> 00:04:22,140 also tell this to a question mark. So 82 00:04:22,140 --> 00:04:25,920 you have three placeholders here. And 83 00:04:25,920 --> 00:04:29,850 then you have the quote to close your 84 00:04:29,970 --> 00:04:32,280 SQL statement. And then inside brackets, 85 00:04:32,280 --> 00:04:35,550 you want to pass the parameters, 86 00:04:36,030 --> 00:04:39,120 quantity, and you have a second one is 87 00:04:39,120 --> 00:04:43,350 price, and item. When you have more than 88 00:04:43,350 --> 00:04:45,720 one parameter here, you don't put a 89 00:04:45,720 --> 00:04:48,738 comma at the end. So this adds like this. 90 00:04:48,739 --> 00:04:50,919 [No audio] 91 00:04:50,921 --> 00:04:52,920 And you also want to pass the 92 00:04:52,920 --> 00:04:55,860 place parameters here, so they reflect 93 00:04:55,890 --> 00:05:00,210 your function input. So what we 94 00:05:00,210 --> 00:05:02,850 have here is, we are updating the store 95 00:05:02,850 --> 00:05:05,430 table and we set the value of the 96 00:05:05,430 --> 00:05:07,950 quantity column and the price column to 97 00:05:07,950 --> 00:05:11,760 these values, but only for those roles 98 00:05:11,760 --> 00:05:14,190 where the item will be equal to a given 99 00:05:14,190 --> 00:05:17,370 value. So we give that value to two, 100 00:05:17,370 --> 00:05:19,410 this one, here are the first two to 101 00:05:19,410 --> 00:05:22,410 this ones. So you get the idea. Save the 102 00:05:22,410 --> 00:05:24,720 script, and you need to call it 103 00:05:24,720 --> 00:05:30,774 here. So let me comment this out and update. 104 00:05:32,243 --> 00:05:33,930 So what quantity do you want to 105 00:05:33,930 --> 00:05:37,530 pass 11, let's say, the price was 5, 106 00:05:37,530 --> 00:05:40,920 let's say it would go up to 6, and 107 00:05:41,730 --> 00:05:45,836 which item you want to update Water Glass. 108 00:05:45,837 --> 00:05:48,439 [No audio] 109 00:05:48,441 --> 00:05:51,030 That's it, save your script and 110 00:05:51,030 --> 00:05:56,040 try it out. And we got this values 111 00:05:56,040 --> 00:05:59,730 updated, as we expected. And that's what 112 00:05:59,730 --> 00:06:01,470 I wanted to teach you in this lecture. 113 00:06:02,220 --> 00:06:04,140 It could be more practical to actually 114 00:06:04,140 --> 00:06:07,650 have an ID here. So we say quantity and 115 00:06:07,650 --> 00:06:10,200 price where the ID is equal to a number. 116 00:06:10,380 --> 00:06:13,650 So we don't have to pass the entire name 117 00:06:13,650 --> 00:06:16,440 which can be confusing or it's prone to 118 00:06:16,440 --> 00:06:19,890 errors. So in that case, we would want 119 00:06:19,920 --> 00:06:22,710 to change the table here. And we would 120 00:06:22,710 --> 00:06:25,200 want to add a primary key that auto 121 00:06:25,200 --> 00:06:28,380 increments. But you will learn how to do 122 00:06:28,387 --> 00:06:30,637 that in the next section of the course. 123 00:06:30,780 --> 00:06:33,540 So there, I'll show you how to make the 124 00:06:33,540 --> 00:06:35,820 real world program and we will implement 125 00:06:35,820 --> 00:06:40,080 that in that program. So these were some 126 00:06:40,080 --> 00:06:42,960 trivial examples, and you will get a 127 00:06:42,960 --> 00:06:46,230 much better idea in the next session. So 128 00:06:46,230 --> 00:06:49,968 next we'll jump to PostgreSQL and we will 129 00:06:50,164 --> 00:06:53,931 work with a psycopg2 library. Talk to you later.