Python MySQL Update Operation

Python MySQL Update Operation: In this part of tutorial, we will learn that how can we update a table present in SQL database through our Python program. As like SQL, we can modify, rewrite or update any specific field in a table using Python program. We just need to specify the primary ID of the specific field which we want to update in the table.

Syntax: Following is the syntax that we will use in our Python program to update any specific field of table:

UPDATE-SET

In the above given syntax, we have to give two arguments to update the table. Following are the two arguments which we need to specify while using UPDATE-SET method:

1. Table Name: We have to write the name of the specific table that we want to update after 'UPDATE' keyword to define the table in our Python program.

2. Column Name: After 'SET' keyword we have to write column name from where we want to enter the particular field. We can also use more than one column name after SET keyword to edit more than one field of table at once.

Now, we will use this update method on our 'StudentTable' to edit the Name and Stream Name columns in two different examples. We will use the following syntax with the UPDATE-SET method in these two examples:

> update StudentTable set ColumnName = 'NewEntry' where id = ID

Consider the following two examples:

Example 1: Updating the name where ID = 2402 from StudentTable using UPDATE-SET method:

 # import the mysql.connector module
 import mysql.connector
 # create the connection object with database
 MyConnectionObject = mysql.connector.connect(host = "localhost", user = "root",passwd = "2401", database = "OurPython1stDB")
 # print the defined connection object
 print(MyConnectionObject)
 # define the cursor object with cursor() function
 CursorObject = MyConnectionObject.cursor()
 # print the defined cursor object
 print(CursorObject)
 # use UPDATE-SET method in try and catch method
 try: 
     CursorObject.execute("update Studenttable set name = 'Delta' where id = 2402") # Name column where ID = 2402 updated in the table
     MyConnectionObject.commit() # it commits the updation in the StudentTable
     print(“The Name field of StudentTable where id = 2402 is successfully updated.")
 except: 
     MyConnectionObject.rollback()
 # print the updated table using fetchall() function on cursorobject
 CursorObject.execute("SELECT * FROM StudentTable")
 ResultOutput = CursorObject.fetchall()
 # print the updted table with for loop
 for z in ResultOutput:
   print(z)
 # close the connection using close() function
 MyConnectionObject.close() 

Output:

 <mysql.connector.connection.MySQLConnection object at 0x000002A192C87CD0>
 MySQLCursor: (Nothing executed yet)
 The Name field of StudentTable where id = 2402 is successfully updated.
 ('Jonas', 2401, 'Science', 'A', 'Physics')
 ('Delta', 2402, 'Science', 'A', 'Physics')
 ('Lyon', 2509, 'Science', 'B', 'Maths')
 ('James', 2601, 'Science', 'E', 'Chemistry')
 ('Peter', 2703, 'Science', 'C', 'Biology')
 ('Jonathan', 2901, 'Science', 'E', 'Biology') 

Example 2: Updating the Major name from StudentTable where ID = 2901 using UPDATE-SET method:

 # import the mysql.connector module
 import mysql.connector
 # create the connection object with database
 MyConnectionObject = mysql.connector.connect(host = "localhost", user = "root",passwd = "2401", database = "OurPython1stDB")
 # print the defined connection object
 print(MyConnectionObject)
 # define the cursor object with cursor() function
 CursorObject = MyConnectionObject.cursor()
 # print the defined cursor object
 print(CursorObject)
 # use UPDATE-SET method in try and catch method
 try: 
     CursorObject.execute("update Studenttable set major = 'Biotechnology' where id = 2901") # Major column where ID = 2901 updated in the table
     MyConnectionObject.commit() # it commits the updation in the StudentTable
     print("The major field of StudentTable where id = 2901 is successfully updated.")
 except: 
     MyConnectionObject.rollback()
 # print the updated table using fetchall() function on cursorobject
 CursorObject.execute("SELECT * FROM StudentTable")
 ResultOutput = CursorObject.fetchall()
 # print the updted table with for loop
 for z in ResultOutput:
   print(z)
 # close the connection using close() function
 MyConnectionObject.close() 

Output:

 <mysql.connector.connection.MySQLConnection object at 0x000001F62D187CD0>
 MySQLCursor: (Nothing executed yet)
 The Stream field of StudentTable where id = 2901 is successfully updated.
 ('Jonas', 2401, 'Science', 'A', 'Physics')
 ('Delta', 2402, 'Science', 'A', 'Physics')
 ('Lyon', 2509, 'Science', 'B', 'Maths')
 ('James', 2601, 'Science', 'E', 'Chemistry')
 ('Peter', 2703, 'Science', 'C', 'Biology')
 ('Jonathan', 2901, 'Science', 'E', 'Biotechnology') 

Explanation:

In the above two examples, we have first imported mysql.connector module in our Python program to access MySQL database. Then, we have defined a connection object named 'MyConnectionObject' in the program. We have used connect() method on 'MyConnectionObject' with specifying the database.

Then, we have printed the connection object we have defined. After that, we have used cursor() function to define a cursor object in the program with name CursorObject. After that we have printed the cursor object. Then, we used Try and catch method in our program to use the UPDATE-SET method. Then we used the execute() function with the CursorObject and after that we have used CURSER-SET method with the where clause i.e., where ID = 2402 or 2901.

Using where clause after SET keyword specifically indicates which field we want to update in the table. In the first example we updated name of student have ID = 2402 and in second example we update stream of student have ID = 2901. Then, we used commit() function with connection object to commit this updation in the table in database.

After that, we have closed 'try and catch' method using the rollback() function with MyConnectionObject in catch statement. Then, we have used fetchall() function with the cursor object to define the ResultOutput through which we will print the updated table in the output of the program.

We used ResultOutput inside the for loop in our program so that we can easily print updated table as output of program. Then, we closed the connection with database using close() function with MyConnectionObject. Output will be printed after Python successfully run the program and access the database.

• We can use UPDATE-SET method in any Python program like this we have used in above two examples to update any field from a given SQL table. We just have to specify the table name and column name where we want to make the change.