Deleting Rows From A MySQL Database Table Using Python And PyMySQL

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')

 


Copyright 2020 © pythontic.com