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.
Was this tutorial helpful?