Python MySQL - Update Query

The UPDATE query in SQL is used to modify existing records in a table. It allows you to update specific columns' values in one or more rows of a table. It's important to note that the UPDATE query affects only the data, not the structure of the table.

Syntax

UPDATE tablename
SET column1 = "new_value", column2 = "new_value"
WHERE condition;

• tablename: The name of the table where the data will be updated.
• SET: Specifies the columns and their new values.
• WHERE: Determines which rows will be updated. If omitted, all rows in the table will be updated.

DATABASE IN USE:

Example 1: Update the Age of a Student

In this example, we'll update the age of a student named "Rishi Kumar" in the STUDENT table. We will change his age to 23:

import mysql.connector # Connecting to the Database mydb = mysql.connector.connect( host="localhost", database="College", user="root", # Replace with your MySQL username password="your_password" # Replace with your MySQL password ) # Create a cursor object cs = mydb.cursor() # SQL query to update the age of a student statement = "UPDATE STUDENT SET AGE = 23 WHERE Name = 'Rishi Kumar'" # Execute the update statement cs.execute(statement) # Commit the changes to the database mydb.commit() # Disconnecting from the database mydb.close() 

Output:

Explanation:

• UPDATE query modifies the AGE column for the student with the name "Rishi Kumar".
• mydb.commit() ensures that the changes are saved to the database.
• connection is closed with mydb.close() to release resources.

Example 2: Correcting the Spelling of a Student's Name

In this example, we will correct the spelling of the student's name from "SK Anirban" to "S.K. Anirban" in the STUDENT table:

import mysql.connector # Connecting to the Database mydb = mysql.connector.connect( host="localhost", database="College", user="root", password="your_password" # Replace with your MySQL password ) # Create a cursor object cs = mydb.cursor() # SQL query to update the name of the student statement = "UPDATE STUDENT SET Name = 'S.K. Anirban' WHERE Name = 'SK Anirban'" # Execute the update statement cs.execute(statement) # Commit the changes to the database mydb.commit() # Disconnecting from the database mydb.close() 

Output:

Explanation:

• UPDATE query changes the name "SK Anirban" to "S.K. Anirban".
• mydb.commit() ensures the changes are saved.
• The connection is closed after the operation.