So in the previous lecture, I introduced
you to the sqlite3 library. And
I showed you how to create a table in
your SQLite database, and how to
insert data in your database and how to
select data and view them in Python. So
we have these four lines, four rows in
our database table. And in this lecture,
I'll quickly show you how to delete and
update data from your table. So
again, I'll create a function. So let me
copy this. And we still need to connect
to the database and create a cursor
object. And then we need to use the
execute method. And in this case, I
would like to delete the row
that has the Wine Glass. So I saw it
execute, we would write DELETE FROM store
[Author typing]
WHERE item equals, I would like to use
a question mark placeholder here. So
question mark, and in brackets, we would
want the parameter that we would pass to
our function. So this item here is the
one we put inside here, item and let me
change the name of our function. So this
is this one. Don't confuse it with this,
this is the column of our table and the
source, the row from the previous function.
[Author typing]
Delete this as well. And you also want to commit
the changes to your database. So commit.
[Author typing]
So that's it. And I'd like comment this out.
So I don't want to execute the Insert Function again.
And I want the view function to be
executed after we delete the row. So
delete. So which item the row of
which item would you want to delete. So
the item Wine Glass, and let's see what
will happen. We got an error on sqlite3
error, incorrect number of binding
supplied line 28. So I can see, yeah, there's
something I often forgets, you need to
add a comma here, you'll see that when I
teach you how to connect to a PostgreSQL
database, and how to query data from the
PostgreSQL database. The code is quite
similar, but it has some critical
changes like this one here. So it can be
confusing when you work with different
kinds of databases. Let's try this again.
[No audio]
And yeah, it seems to be working now.
Wine Glass is not here. So that's
how you delete a row. Let's now add
another function. For updating data
update. In this one, I would like to
update the quantity of Water
Glasses. So I'll want to change this
from 10 to 11. Again, you establish a
connection to the database, so create a
cursor object, and then you execute. And
here you enter the UPDATE SQL command.
So update, what you want to update is
the store table. And which column do you
want to update from the store table? So
you want to SET the name of the column,
which in this case would be quantity?
And that would be equal to what, guess
what, question mark. And then you say
where, or let me show you how to update
more than one column. So if you wanted
more than one column, you'd use a comma
and then say price equals another
question mark. Alright, so we say update
store and set quantity and price to
these but which row so we need to
specify conditional here. So WHERE
item equals to Wine Glass, or you can
also tell this to a question mark. So
you have three placeholders here. And
then you have the quote to close your
SQL statement. And then inside brackets,
you want to pass the parameters,
quantity, and you have a second one is
price, and item. When you have more than
one parameter here, you don't put a
comma at the end. So this adds like this.
[No audio]
And you also want to pass the
place parameters here, so they reflect
your function input. So what we
have here is, we are updating the store
table and we set the value of the
quantity column and the price column to
these values, but only for those roles
where the item will be equal to a given
value. So we give that value to two,
this one, here are the first two to
this ones. So you get the idea. Save the
script, and you need to call it
here. So let me comment this out and update.
So what quantity do you want to
pass 11, let's say, the price was 5,
let's say it would go up to 6, and
which item you want to update Water Glass.
[No audio]
That's it, save your script and
try it out. And we got this values
updated, as we expected. And that's what
I wanted to teach you in this lecture.
It could be more practical to actually
have an ID here. So we say quantity and
price where the ID is equal to a number.
So we don't have to pass the entire name
which can be confusing or it's prone to
errors. So in that case, we would want
to change the table here. And we would
want to add a primary key that auto
increments. But you will learn how to do
that in the next section of the course.
So there, I'll show you how to make the
real world program and we will implement
that in that program. So these were some
trivial examples, and you will get a
much better idea in the next session. So
next we'll jump to PostgreSQL and we will
work with a psycopg2 library. Talk to you later.