Overview
PyMySQL is a MySQL client library written using the Python Programming Language.
PyMySQL is used to connect to a MySQL server and perform database operations from a Python program.
PyMySQL works with the following Python implementations
- CPython
- PyPy
- IronPython
This article describes how to delete a set of rows from a MySQL database table.
Steps involved in deleting a set of rows from a MySQL Table
The SQL statement DELETE is a DML statement. DML stands for data Manipulation Language.
Unless a filter using WHERE clause is specified the DELETE statement will delete all the rows from a specified table.
Deleting a set of rows from a MySQL table involves:
Creation of a database connection by specifying the database server using IP or host name along with database user credentials
Obtaining a cursor instance from the database connection
Defining a string containing the SQL command to delete a set of rows including the WHERE clause if required
Calling the execute() method on cursor by passing the DELETE SQL string as the parameter
In the python example given here, the autocommit is made True while creating the database connection. The remaining rows from the table are printed using the SQL SELECT statement.
Example
# import the pymysql module
import pymysql
# Code for creating a connection object
databaseServer = "127.0.0.1"
databaseUser = "root"
databaseUserPwd = ""
databaseName = "test"
databaseCharSet = "utf8mb4"
dbConnection = pymysql.connect(host=databaseServer,
user=databaseUser,
password=databaseUserPwd,
db=databaseName,
charset=databaseCharSet,
autocommit=True)
try:
# Code for creating cursor from database connection
cursorInstance = dbConnection.cursor()
# SQL statement for deleting rows from a table matching a criteria
sqlDeleteRows = "Delete from tbl_items where status='expired'"
# using the cursor delete a set of rows from the table
cursorInstance.execute(sqlDeleteRows)
# Check if there are any existing items with expired status
sqlSelectRows = "select * from tbl_items"
# Execute the SQL query
cursorInstance.execute(sqlSelectRows)
#Fetch all the rows using cursor object
itemRows = cursorInstance.fetchall()
# print all the remaining rows after deleting the rows with status as "expired"
for item in itemRows:
print(item)
except Exception as ex:
print("Exception occured: %s"%ex)
finally:
dbConnection.close()Output
(1, 'butter', 50, 'fresh')
(2, 'milk', 200, 'fresh')
(3, 'yogurt', 100, 'fresh')