Python MySQL – Update Data in a Table

Summary: In this tutorial, you will learn how to update data in a table using MySQL Connector/Python API.

This tutorial picks up where the Inserting Data Into a Table tutorial left off.

To update data in a table in Python, you follow these steps:

• First, connect to the MySQL server by creating a new MySQLConnection object.
• Next, create a new MySQLCursor object from the MySQLConnection object.
• Then, call the execute() method of the MySQLCursor object.
• After that, call the commit() method of the MySQLConnection object after calling the execute() method to apply the changes to the database.
• Finally, close the cursor and database connection.

The following program defines a function called update_book() to update the title of a book by book_id:

from mysql.connector import MySQLConnection, Error
from config import read_config

def update_book(book_id, title):
    # read database configuration
    config = read_config()

    # prepare query and data
    query = """ UPDATE books
                SET title = %s
                WHERE id = %s """

    data = (title, book_id)

    affected_rows = 0  # Initialize the variable to store the number of affected rows

    try:
        # connect to the database
        with MySQLConnection(**config) as conn:
            # update book title
            with conn.cursor() as cursor:
                cursor.execute(query, data)

                # get the number of affected rows
                affected_rows = cursor.rowcount

            # accept the changes
            conn.commit()

    except Error as error:
        print(error)

    return affected_rows  # Return the number of affected rows

if __name__ == '__main__':
    affected_rows = update_book(37, 'The Giant on the Hill *** TEST ***')
    print(f'Number of affected rows: {affected_rows}')
Code language: Python (python)

How it works.

First, import the necessary modules including MySQLConnection and Error are classes from the mysql.connector module, and read_config is a function from a custom module config.

from mysql.connector import MySQLConnection, Error
from config import read_configCode language: JavaScript (javascript)

Second, define a function named update_book that takes two parameters: book_id and title. The function reads the database configuration and prepares an SQL query to update the title of a book with the specified ID:

def update_book(book_id, title):
    # read database configuration
    config = read_config()

    # prepare query and data
    query = """ UPDATE books
                SET title = %s
                WHERE id = %s """

    data = (title, book_id)
Code language: PHP (php)

Third, open a database connection (MySQLConnection) with the configuration from the configuration file and create a cursor (cursor). Inside the nested with statement, update the book title, and return the number of affected rows obtained from cursor.rowcount:

    affected_rows = 0  # Initialize the variable to store the number of affected rows

    try:
        # connect to the database
        with MySQLConnection(**config) as conn:
            # update book title
            with conn.cursor() as cursor:
                cursor.execute(query, data)

                # get the number of affected rows
                affected_rows = cursor.rowcount

            # accept the changes
            conn.commit()
Code language: PHP (php)

Fourth, display the error if it occurs during the update:

    except Error as error:
        print(error)
Code language: JavaScript (javascript)

Fifth, return the number of affected rows:

return affected_rows  # Return the number of affected rowsCode language: PHP (php)

Finally, call the update_book() function to update the book with id 37 with the new title:

if __name__ == '__main__':
    affected_rows = update_book(37, 'The Giant on the Hill *** TEST ***')
    print(f'Number of affected rows: {affected_rows}')
Code language: PHP (php)

Running the update

First, open the Command Prompt on Windows or Terminal on Unix-like systems:

mysql -u root -p

Second, switch the current database to pub:

USE pub;Code language: PHP (php)

Third, retrieve the book with ID 37:

SELECT * FROM books
WHERE id = 37;Code language: SQL (Structured Query Language) (sql)

Output:

+----+------------------------+---------------+
| id | title                  | isbn          |
+----+------------------------+---------------+
| 37 | The Giant on the Hill  | 1235644620578 |
+----+------------------------+---------------+
1 row in set (0.01 sec)Code language: JavaScript (javascript)

Fourth, open the second terminal and execute the update.py module (note that you need to activate the virtual environment if relevant):

python update.pyCode language: SQL (Structured Query Language) (sql)

Output:

Number of affected rows: 1Code language: JavaScript (javascript)

Finally, go back to the mysql terminal and retrieve the book with id 37 to verify the update:

SELECT * FROM books
WHERE id = 37;

Output:

+----+------------------------------------+---------------+
| id | title                              | isbn          |
+----+------------------------------------+---------------+
| 37 | The Giant on the Hill *** TEST *** | 1235644620578 |
+----+------------------------------------+---------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

The output indicates that the update.py program has updated the book ID 37 successfully.

Summary

• Use the execute() method of the cursor object to run an update statement and call the commit of the connection object to apply the changes to the database.