This article demonstrates how to issue a MySQL UPDATE query from python to update the MySQL table’s data.
Goals of this lesson. You’ll learn the following MySQL UPDATE operations from Python
- Update single row, multiple rows, single column, and multiple columns.
- Use a python variable in a parameterized query to update table rows.
- Also, Update a column with date-time and timestamp values
- Also, understand the role of commit and rollback in the update operation.
Further reading
- Try to solve Python MySQL Exercise
- Also, read Python MySQL Tutorial (Complete Guide)
Prerequisite
Before executing the following program, make sure you have the following in place −
- Username and password that you need to connect MySQL
- MySQL database table name which you want to update.
For this article, I am using a Laptop table present in my MySQL server.
If no table is present in your MySQL server you can refer to our article to create a MySQL table from Python.
Also, you can download Laptop table creation with data in MySQL file which contains SQL queries for table creation along with data so you can use this table for your UPDATE operations.
Note: We are using MySQL Connector Python to update the MySQL table.
Python MySQL Update Example to update a record of a table
To perform a SQL UPDATE query from Python, you just need to follow these simple steps: –
- Install MySQL Connector Python using pip.
- Create a MySQL database connection in python
- Prepared the Update statement query (here you must know the table’s columns details)
- Execute the UPDATE query, using
cursor.execute() - Close the cursor object and database connection
- Catch any SQL exceptions that may occur during this process
Let’s see the program now. In this program, we are updating a Laptop table by changing the value of the Price column of the laptop whose id is 1.
import mysql.connector
import mysql.connector
from mysql.connector import Error
try:
connection = mysql.connector.connect(host='localhost',
database='electronics',
user='pynative',
password='pynative@#29')
cursor = connection.cursor()
print("Before updating a record ")
sql_select_query = """select * from Laptop where id = 1"""
cursor.execute(sql_select_query)
record = cursor.fetchone()
print(record)
# Update single record now
sql_update_query = """Update Laptop set Price = 7000 where id = 1"""
cursor.execute(sql_update_query)
connection.commit()
print("Record Updated successfully ")
print("After updating record ")
cursor.execute(sql_select_query)
record = cursor.fetchone()
print(record)
except mysql.connector.Error as error:
print("Failed to update table record: {}".format(error))
finally:
if (connection.is_connected()):
connection.close()
print("MySQL connection is closed")
You should get the following output after the execution of the above code: –
Before updating a record (1, 'Lenovo ThinkPad P71', 6459.0, datetime.date(2019, 8, 14)) Record Updated successfully After updating record (1, 'Lenovo ThinkPad P71', 7000.0, datetime.date(2019, 8, 14)) MySQL connection is closed
Refer to Fetch rows from MySQL table in Python to check the data that you just updated.
Let’s Understand the above example now
-
import mysql.connectorthis line imports MySQL Connector Python module in your program so you can use this modules API to connect MySQL - Next, We used the mysql.connector.connect() method to connect the MySQL Database from Python.
- Then we prepared SQL UPDATE query to update a “Price” column of a Laptop table. The update query contains the column value to be updated.
- we used a
connection.cursor()method to get cursor object from the connection. this method creates a new MySQLCursor object. using acursor.execute()we can execute the operation stored in the UPDATE query. - Make changes persistent into a database using
connection.commit(). - After a successful update operation, use a
cursor.rowcountmethod to get the number of rows affected. here it will return 1 because we are updating one row.
- commit() to make changes persistent in the database.
- rollback() rollback or reverse the changes if any database error occurred.
Use a Python variable in an Update query to update MySQL table
Sometimes we need input from the user, for example when users updating their password or any other details through User Interface.
- It is always best practice to use parameterized query and prepared statement i.e. placeholders ( %s ) inside any SQL statements that contain input from users.
- This helps us prevent SQL injection and other SQL issues.
Read more on What is a Parameterized Query and its performance benefits.
Let’ s see the example program now.
import mysql.connector
from mysql.connector import Error
def updateLaptopPrice(id, price):
try:
connection = mysql.connector.connect(host='localhost',
database='electronics',
user='pynative',
password='pynative@#29')
cursor = connection.cursor()
sql_update_query = """Update laptop set price = %s where id = %s"""
inputData = (price, id)
cursor.execute(sql_update_query, inputData)
connection.commit()
print("Record Updated successfully ")
except mysql.connector.Error as error:
print("Failed to update record to database: {}".format(error))
finally:
if (connection.is_connected()):
cursor.close()
connection.close()
print("MySQL connection is closed")
updateLaptopPrice(7500, 1)
updateLaptopPrice(5000, 2)Output: –
Record Updated successfully MySQL connection is closed Record Updated successfully MySQL connection is closed
Let’s understand the above program: –
- We used the prepared statement to accept user input using a placeholder, i.e. we put two placeholders in update query one for “Price” column and other is for the” id” column
- We then added those two columns value in the input tuple in sequential order and passed SQL update query and input tuple t0
cursor.execute()method, remember tuple contains user data in the sequential order of placeholders. - In the end, we are committing our changes to the database using
connection.commit()
Python MySQL update Multiple Rows data in a single query
It is possible to update multiple rows in a single SQL Query. I have prepared an example which demonstrates the same. you can also call it a bulk update. We can use a cursor.executemany() method of cursor object to update multiple tables rows.
import mysql.connector
from mysql.connector import Error
try:
connection = mysql.connector.connect(host='localhost',
database='electronics',
user='pynative',
password='pynative@#29')
cursor = connection.cursor()
sql_update_query = """Update Laptop set Price = %s where id = %s"""
# multiple records to be updated in tuple format
records_to_update = [(3000, 3), (2750, 4)]
cursor.executemany(sql_update_query, records_to_update)
connection.commit()
print(cursor.rowcount, "Records of a laptop table updated successfully")
except mysql.connector.Error as error:
print("Failed to update records to database: {}".format(error))
finally:
if (connection.is_connected()):
connection.close()
print("MySQL connection is closed")Output:
2 Records of a laptop table updated successfully connection is closed
Let’s understand the above program
cursor = connection.cursor()method allows a prepared statement to execute a SQL query.- We defined a SQL update query with two placeholders (“Price” and “Id” column ). and prepared a list of records to be updated. this List contains tuple for each row. here we created two tuples so we are updated two rows.
- We used the
cursor.executemany()method to update multiple rows of a database table. - using
cursor.rowcountwe can find how many rows are actually updated successfully.
The syntax of the executemany() method
cursor.executemany(operation, seq_of_params)This method prepares a database operation (i.e. query or command) and executes it against all parameter sequences in the sequence “seq_of_params” argument.
Python update multiple Columns of MySQL table
We can also update multiple columns of MySQL table in a single query. prepare a parameterized query using a placeholder to update multiple columns. let see this with an example program.
import mysql.connector
from mysql.connector import Error
try:
connection = mysql.connector.connect(host='localhost',
database='electronics',
user='pynative',
password='pynative@#29')
cursor = connection.cursor()
sql_update_query = """Update Laptop set Name = %s, Price = %s where id = %s"""
name = "HP Pavilion"
price = 2200
id = 4
input = (name, price, id)
cursor.execute(sql_update_query, input)
connection.commit()
print("Multiple column updated successfully ")
except mysql.connector.Error as error:
print("Failed to update record to database: {}".format(error))
finally:
if (connection.is_connected()):
connection.close()
print("MySQL connection is closed")Output:
Multiple column updated successfully MySQL connection is closed
Python Update Datetime and timestamp column of a MySQL table
Suppose you have a date column in a MySQL table and you want to update a datetime.datetime() object into this column. then how to prepare an update query to update the datetime column of a table. let’s see this with an example.
import mysql.connector
from mysql.connector import Error
from datetime import datetime
try:
connection = mysql.connector.connect(host='localhost',
database='electronics',
user='pynative',
password='pynative@#29')
cursor = connection.cursor()
sql_update_query = """Update Laptop set Purchase_date = %s where id = %s"""
current_Date = datetime.now()
formatted_date = current_Date.strftime('%Y-%m-%d %H:%M:%S')
id = 2
input = (formatted_date, id)
cursor.execute(sql_update_query, input)
connection.commit()
print("Purchased Date Updated successfully ")
except mysql.connector.Error as error:
print("Failed to update purchased date {}".format(error))
finally:
if (connection.is_connected()):
connection.close()
print("connection is closed")You should get the following output after the execution of the above code.
Purchased Date Updated successfully connection is closed
Next Steps:
To practice what you learned in this article, Please solve a Python Database Exercise project to Practice and master the Python Database operations.
