Get Started: Python MySQL Update Table

February 13, 2022

In This Article, You Will Know About Python MySQL Update Table.

Python MySQL Update Table – Before moving ahead, let’s know a bit about Python MySQL Order By.

Update Table

Use “UPDATE” statement to update or change existing value in the table.

Example: Use “UPDATE” statement to change the name from “Akshay” to “Alex”.

				
					import mysql.connector
my_user_details = mysql.connector.connect(host="localhost",
                                          username="your_MySQL_username",
                                          password="Your_MySQL_password",
                                          database="YOUR_MYSQL_DATABASE_NAME")
database_for_mysql = my_user_details.cursor()

database_for_mysql.execute("UPDATE STU_INFORMATION SET NAME = 'Alex' WHERE NAME = 'Akshay' ")

my_user_details.commit()

As a result, it returned updated name that is ‘Alex’ from ‘Akshay’.

Note: The statement: my_user_details.commit() is required to make the changes, otherwise no changes are made to the table.

Notice: The WHERE clause specifies which record that should be updated or changed. If you omit the WHERE clause, all records will be updated!

Example: Use “UPDATE” statement to change the name for all students’ names.

				
					import mysql.connector
my_user_details = mysql.connector.connect(host="localhost",
                                          username="your_MySQL_username",
                                          password="Your_MySQL_password",
                                          database="YOUR_MYSQL_DATABASE_NAME")
database_for_mysql = my_user_details.cursor()

database_for_mysql.execute("UPDATE STU_INFORMATION SET NAME = 'John'")

my_user_details.commit()

As a result, it returned updated name ‘John’ for all students’ name.

Note: To get back all names as it was before update “name” of all students with the help of “roll_no” for each “name”.

Example: Use “UPDATE” statement to change the name from “John” to “Alex”.

				
					import mysql.connector
my_user_details = mysql.connector.connect(host="localhost",
                                          username="your_MySQL_username",
                                          password="Your_MySQL_password",
                                          database="YOUR_MYSQL_DATABASE_NAME")
database_for_mysql = my_user_details.cursor()

database_for_mysql.execute("UPDATE STU_INFORMATION SET name = 'Alex'  WHERE roll_no = 5202")

my_user_details.commit()

As a result, it returned updated name that is “Alex” for first student’s name.

Note: Repeat the above code with changing roll_no value in each row with the old name for each student to update the name value.

Example: Use “UPDATE” statement to change the roll_no from 5202 to 5201.

				
					import mysql.connector
my_user_details = mysql.connector.connect(host="localhost",
                                          username="your_MySQL_username",
                                          password="Your_MySQL_password",
                                          database="YOUR_MYSQL_DATABASE_NAME")
database_for_mysql = my_user_details.cursor()

database_for_mysql.execute("UPDATE STU_INFORMATION SET roll_no = 5201 WHERE roll_no = 5202")

my_user_details.commit()