Update record in MySQL table using python
Update record in MySQL table using python is the fourth article in this series of Python and MySQL, In the previous article, we learn how to install python package MySQL client to make a connection between Python and MySQL. Insert record in MySQL table using python, Delete record from MySQL table and now we are going to learn how to update records in MySQL table using python.
Mysql syntax to update a record is as follow
UPDATE TableName Set ColumnName = newValue, ColumnName = newValue [ where <condition> ]
If a condition is missing from the Update command- Update command edits the whole table with new values. Thus condition was introduced in the UPDATE command.
UPDATE student SET name= ‘rakeshkumar’ where name like ‘%rakesh%’;
The above MySQL command change only those rows from the table that have “rakesh” in its userName.
The same we are going to implement in this python program to edit a record in the STUDENT table. The table structure of the table student is as follows
Student table contains the following records.
So the command that we will use to update a record will be
UPDATE student SET name= ‘rakeshkumar’ where name like ‘%rakesh%’;
The command will erase a single record from the table whose admission no is 111.
Python Program to Update a record in MySQL Table
import MySQLdb
db = MySQLdb.connect("localhost", "root", "", "binarynote")
cursor = db.cursor()
name = input("Enter current name : ")
new_name = input("Enter new name :")
sql = "update user set uname={} where name like '%{}';".format(name, new_name)
cursor.execute(sql)
db.commit()
db.close()
print("Row deleted successfully")
Since we are making changes in the database, it is compulsory to save that changes using the commit() method. If you forgot to fire this method all your efforts to make changes in the database will not take place.
