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.
Table of Contents
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()
