Summary: in this tutorial, you will learn how to delete data from a table in the MySQL database using Python.
This tutorial picks up where Updating Data in a Table tutorial left off.
To delete data from a table from a Python program, you follow these steps:
- First, connect to the database by creating a new
MySQLConnection
object. - Next, create a new cursor object from the Connection object.
- Then, call the
execute()
method of the cursor object. - After that, commit the changes by calling the
commit()
method of theMySQLConnection
object. - Finally, close the cursor and database connection by calling
close()
method of the corresponding object.
Deleting program
The The following example shows you how to delete a book specified by book id:
from mysql.connector import MySQLConnection, Error
from config import read_config
def delete_book(book_id):
# read database configuration
config = read_config()
# prepare query and data
query = "DELETE FROM books WHERE id = %s"
data = (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 = delete_book(37)
print(f'Number of affected rows: {affected_rows}')
Code language: Python (python)
How it works.
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: Python (python)
Second, define a function named delete_book
that takes one parameter book_id
. The function reads the database configuration and prepares an SQL query to delete the book with the specified ID:
def delete_book(book_id):
# read database configuration
config = read_config()
# prepare query and data
query = "DELETE FROM books WHERE id = %s"
data = (book_id, )
Code language: Python (python)
Third, open a database connection (MySQLConnection
) with the configuration from the configuration file and create a cursor (cursor). Inside the nested with
statement, delete the book specified by the ID 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: Python (python)
Fourth, display the error if it occurs during the delete:
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 delete_book()
function to delete the book with id 37:
if __name__ == '__main__':
affected_rows = delete_book(37)
print(f'Number of affected rows: {affected_rows}')
Code language: Python (python)
Summary
- Call the
execute()
method of a cursor object to run theDELETE
statement to delete data in a table and thecommit()
method of a connection object to apply the changes to the database.