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 theMySQLConnection
object. - Then, call the
execute()
method of theMySQLCursor
object. - After that, call the
commit()
method of theMySQLConnection
object after calling theexecute()
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_config
Code 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 rows
Code 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.py
Code language: SQL (Structured Query Language) (sql)
Output:
Number of affected rows: 1
Code 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.