How to Update a Value in a MySQL Table in Python



Python


In this article, we show how to update a value in a MySQL table in Python.

Say, for instance, you have a table of Students. This table specifically has the columns of firstname, lastname, and grade. Let's say, we entered 'Dave Matthews' as one of the students in our title, but then realize that the student's name is actually 'Dave Matthewson'. We don't have to delete the record and create a new record. We can simply just update the value in the table to the correct value. This is called updating a value.

If you need to know how to install MySQL, see How to Install MySQL in Python 3.

So, the general code to update a value in a MySQL table in Python is shown below.



So, the code above updates the table, table_name and sets column_name1 equal to value where column_name2 is equal to some value.

Let's go back to our previous example that we were discussing before about the Students table. Let's say we have a MySQL composed of students in a school. We have 3 columns in the table: firstname, lastname, and grade. Let's say we entered into the table the student 'Dave Matthews' only to realize that his name is really 'Dave Matthewson'.

We don't have to delete the record and create a new one. We can simply update the student's last name, so that it rightly reflects his correct last name, which is Matthewson.

Below is the full code to update the table, named Students, where the last name is Mathews and have it changed to Matthewson.



So, this is all the code that is needed to update the student's last name to Matthewson in the Students table.

So we first must import MySQLdb. Once that is imported, we gain connection to the MySQL database using the MySQLdb.connect() function. To see a full-length article on connecting to a MySQL database, see How to Connect to a MySQL Database in Python.

We then have to create a cursor for the table.

Next, we execute our function to update the last name 'Matthews' to 'Matthewson' in the table using the cursor.execute() function. Inside of this function, we place in the line, "UPDATE Students SET lastname='Matthewson' WHERE lastname='Matthews'". So the whole line of code, is, cursor.execute("UPDATE Students SET lastname='Matthewson' WHERE lastname='Matthews'")

We then close the database once we've done what we've needed to.

If the data has been successfully inserted into the MySQL table, a '1' should be returned in the Python shell.

The above code looks at the column, lastname, and looks for the last name, "Matthews" and changes it to "Matthewson". However, what if there is another student with the last name, Matthews. This code will also change that last name, even if that name is correct for the student. To be more specific, you may want to add more precise code.

The code, below, now also changes the last name to Matthewson where the first name is Dave and the last name is Matthewson. Our code before changed all last names, Matthews, to Matthewson. This new code only changes the last name to Matthewson if the first name is Dave.



The AND operator means that both statements must be true in order for the statement to execute. The code above will update the last name to Matthewson only if the last name is Matthews and the first name is Dave.

And this is how data can be updated in a MySQL table in Python.


Related Resources

How to Show All Tables of a MySQL Database in Python

How to Count the Number of Rows in a MySQL Table in Python



HTML Comment Box is loading comments...