Python Update MySQL Table

This article demonstrates how to issue a MySQL UPDATE query from python to update 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.

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.connector  this 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 a cursor.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.rowcount method to get the number of rows affected. here it will return 1 because we are updating one row.

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.rowcount we 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, I have created a Python Database programming Quiz and Exercise Project.

That’s it. Folks Let me know your comments and questions in the section below.