Python MySQL Update and Delete Data

Hello guys. Welcome to LearnVern.

In the last topic, we saw Fetch All & Fetch by ID from the database, where we fetched all the data from our database & particularly single data as well.

What is today's topic? Update database & delete data from the database.

Moving to the practicals.

I’ll open my Jupyter where you must do 2 of these things.

1 is import PYMySQL & other is create a connection to establish it with the database.

If you don’t know these things, kindly watch the first video of Database which is create and Insert Record and you’ll know what it is about.

Now, first of all we have to Update Data.

For that, I’ll have to fetch the data first.

What I’ll do is, I’ll copy paste my Fetch All data method from the last video.

So we’ll get all the data.

I’ll write down Fetch All Data from Database.

We got all the data from the database.

Now we have to update a single data.

Suppose I want to update Ram’s email ID.

For that, I’ll have to get that ID’s data & then update it.

I’ll markdown Update Data into Database.

Let’s see how to do it.

First of all I’ll make a function Def UpdateData.

How many fields do I have?

1 is SID, which is Student ID, Student Name, Student Email ID & Student’s City.

We have 4 pieces of data.

First of all, I’ll fetch Name, then Email, then City & then SID.

Meaning if you want to update either name, email or city & ID, you’ll have to update ID.

For instance, I want to update Ram’s data...then I’ll have to update it on ID.

We passed those into the argument.

Now the first step is to establish a connection.
So we will call the Create Connection method.

We will create a cursor as without a cursor, we cannot fire a query.

We fired our cursor & now we want arguments.

The way we have passed the arguments in the method’s parameters, you have to pass them in the same sequence here.

Name, Email, City, SID.

Then you have to write your query here.

What is Update’s query? Update “Table name” set name, we want the name dynamic, email dynamic, we can update the city as well, and all the 3 fields should be updated according to the ID.

So where SID = % S.

Now we have to pass the parameters according to the update query we’ve written.

That is why I have passed the arguments & parameters in this sequence only.

We need the name, email, city & update it according to the student ID.

So we passed the Student ID in the end.

Now what do we have to do? We have to execute our query.

How will we do it?

Cursor . (dot) execute query & arguments.

Then we have to commit.

Connection . (dot) commit. Why do we have to commit?

To update the data in the database, meaning to save it.

Then I’ll write Print so that we can know…

Data Updated, okay?

Then we have to close the connection.

Next we have to take a user Input according to this.

Let’s take an input.

First of all we have to show all of our data.

For that, we will call the ShowAll method & we’ll get all the data.

Now we have to take an input. We want the name, city, email & Student ID.

First off, we will have to send the Student ID. Why?

Because our data will be updated over that only.

I’ll take SID in which I’ll take only integer input.

I’ll write here “Enter your ID.”

Then we wanted a name so I took N.

Input from the user “Enter your Name”.

Next up is Email.

Input “Enter your Email”.

Thereafter it’s City. C for city.

Input “Enter your City”.

Now why did I take all the 3 inputs?

Because we don’t know what the user will update.

But it is not compulsory that you have to update all the three.

So I took all the 3 inputs as I don’t know what the user will update.

Afterwards we will call our UpdateData method.

According to our parameter, this parameter, we will pass our parameters below.

So Name, Email, City & SID.

The data should be seen after getting updated.

So we will call the ShowAllData method again..

I’ll write here “Show All Data from Database”.

And here I’ll write “For Showing Updated Data”.

And this method is For Updating.

Now we will run it.

We got the data first of all.

We had 2 data in our database & we got both of them.

It asked for ID first. Suppose I wrote 2.

I want to keep the name Sunit only.

But I want to change the Email ID.

Right now, our email ID is S @ gmail.com, but I want to change it to S123 @ gmail.com

I’ll leave the city as Ahmedabad only.

Okay, let’s change it to Rajkot.

Clear?

As soon as I run it, it’ll show me Data Updated.

And it also showed us the updated data below.

Until now we had this data but after the update, it changed to this.

So this is how you can update your data in your database.

It will be updated in your database as well.

I’ll refresh...see? Our database has been updated.

Now we have to delete the data.

Let’s see how to delete it.

I’ll markdown first.

Delete Data from Database.

We’ll make another method.

I made a method called DeleteData.

The data will be deleted according to the ID only, so we will pass only SID.

Then we have to create a connection.

I’ll run Create Connection here.

After that, we have to establish a cursor.

Let’s create a cursor here…

Conn. (dot) cursor.

Next we have to send an argument as we have passed a parameter in the method.

SID. And then we have to write our query.

We’ll write a query of Delete. Delete from “table name” where SID = %S.

Then we will write Cursor . (dot) execute, we will pass a query and arguments.

Thereafter we need a commit.

Commit is necessary because we are bringing certain changes in the database table.

Connection . (dot) commit.

I’ll print Data Deleted here.

At last, Connection Close.

Before running it, the data must be seen first.

Why? Because we should atleast get to see what data is available in our database.

Through which method will it be seen?

It'll be seen through the ShowAllData method.

Next we need an input. Why do we need an input?

Because we passed an SID argument in our Delete Data method.

SID. Input in the Integer.

Enter your ID.

Now we will call our Delete Data method, in which I’ll pass SID.

After that, we will again call the ShowAllData method so that we atleast get to know after the update, how much data is left.

I’ll write here “Calling Delete Method”.

Let’s run it first.

We got the data. I’ll write the ID, suppose 3.

And Enter.

A message popped up saying Data Deleted and only 1 data will be visible.

Let’s refresh & check.

You can see we have only 1 remaining data in our database.

So this is how you can Update & Delete in the database using Python.

Our next topic will be Tkinter with GUI framework where we will see how to make a GUI based App.

See you in the next video everyone.