Python
In this tutorial we will learn to update data in MySQL database using Python.
We have covered how to insert data into MySQL table using Python and how to fetch data from MySQL table using Python in the previous tutorials. Feel free to check that out.
To understand this tutorial you need to have some prior knowledge of MySQL.
Note!
For complete MySQL tutorial click here.
To learn more about UPDATE query check out this MySQL - UPDATE Table tutorial.
To update data from a table we have the following steps.
commit()
In this tutorial we will be working with an employee table.
employee
The table already has some rows which are shown below.
mysql< SELECT * FROM employee; +------------+-----------+----------+---------------------+ | employeeid | firstname | lastname | created_at | +------------+-----------+----------+---------------------+ | e01 | John | Doe | 2018-01-01 10:20:30 | | e02 | Jane | Doe | 2018-01-01 12:13:14 | | e03 | Peter | Parker | 2018-01-02 15:16:17 | | e04 | Bruce | Banner | 2018-01-03 10:20:30 | | e05 | Bruce | Wayne | 2018-01-04 12:00:00 | | e06 | Tony | Stark | 2018-01-05 12:13:14 | | e07 | Doctor | Strange | 2018-01-05 13:14:15 | | e08 | Doctor | Who | 2018-01-05 13:14:15 | +------------+-----------+----------+---------------------+ 8 rows in set (0.00 sec)
To update records in a table we take help of the execute() method and pass SQL query and the values that we want to update.
execute()
In the following Python program we are updating the firstname of the employee having id e08.
firstname
e08
# import module import mysql.connector # import errorcode from mysql.connector import errorcode # get db connection try: cnx = mysql.connector.connect( user='yusufshakeel', password='', host='127.0.0.1', database='mydb' ) except mysql.connector.Error as err: if err.errno == errorcode.ER_ACCESS_DENIED_ERROR: print('Invalid credential. Unable to access database.') elif err.errno == errorcode.ER_BAD_DB_ERROR: print('Database does not exists') else: print('Failed to connect to database') # update data try: # cursor cnxCursor = cnx.cursor() # placeholder firstname = "Dr." employeeid = "e08" # sql query query = "UPDATE employee SET firstname = %s WHERE employeeid = %s" # data data = (firstname, employeeid) # execute cnxCursor.execute(query, data) # commit cnx.commit() # total number of rows updated print("Total rows updated: %d" % cnxCursor.rowcount) except mysql.connector.Error as err: print("Error:", err.message) except: print("Unknown error occurred!") finally: # close cursor cnxCursor.close() # close connection cnx.close()
On success the above code will give us the following output.
Total rows updated: 1
If we check the employeetable we will get the following output.
mysql> SELECT * FROM employee; +------------+-----------+----------+---------------------+ | employeeid | firstname | lastname | created_at | +------------+-----------+----------+---------------------+ | e01 | John | Doe | 2018-01-01 10:20:30 | | e02 | Jane | Doe | 2018-01-01 12:13:14 | | e03 | Peter | Parker | 2018-01-02 15:16:17 | | e04 | Bruce | Banner | 2018-01-03 10:20:30 | | e05 | Bruce | Wayne | 2018-01-04 12:00:00 | | e06 | Tony | Stark | 2018-01-05 12:13:14 | | e07 | Doctor | Strange | 2018-01-05 13:14:15 | | e08 | Dr. | Who | 2018-01-05 13:14:15 | +------------+-----------+----------+---------------------+ 8 rows in set (0.00 sec)