Python SQL Server: Deleting Data

Summary: in this tutorial, you will learn how to delete data from an SQL Server table in Python.

This tutorial begins where the paginating data tutorial left off.

Deleting a row from a table

Step 1. First, create a delete.py file within the project directory and define the delete_customer() function:

from connect import create_connection

def delete_customer(id: int) -> int:
    # Connect to the SQL Server
    conn = create_connection()
    if conn is None:
        return 0
    
    # Delete the customer    
    with (conn, conn.cursor() as cursor):
        cursor.execute("DELETE FROM Customers WHERE CustomerID = %s", (id,))
        conn.commit()
        return cursor.rowcountCode language: Python (python)

How it works.

First, import the create_connection function from the connect module.

Second, define a function delete_customer that accepts a customer ID and returns the number of rows deleted.

def delete_customer(id: int) -> int:Code language: Python (python)

Third, connect to SQL Server by calling the create_connection() function and return 0 if the connection fails:

conn = create_connection()
if conn is None:
    return 0Code language: Python (python)

Fourth, manage the Connection and Cursor objects using the with statement:

with (conn, conn.cursor() as cursor):Code language: Python (python)

This eliminates the need to close the Cursor and Connection objects manually.

Fifth, execute a DELETE statement with the id parameter:

cursor.execute("DELETE FROM Customers WHERE CustomerID = %s", (id,))Code language: Python (python)

Sixth, delete the data permanently by calling the commit() method of the Connection object:

conn.commit()Code language: CSS (css)

Finally, return the number of rows deleted:

return cursor.rowcountCode language: Python (python)

Step 2. Modify the main.py module to use the delete_customer function:

import logging, sys
from delete import delete_customer

# config logging to console
logging.basicConfig(
    stream=sys.stdout, 
    encoding='utf-8', 
    format='%(levelname)s:%(message)s',
    level=logging.DEBUG
)

# Delete customer with id 1
try:
    row_deleted = delete_customer(1)
    logging.info(f'{row_deleted} row(s) deleted.')
except Exception as e:
    logging.error(f"Error fetching customers: {e}")
       Code language: Python (python)

How it works.

First, import the delete_customer function from the delete module:

from delete import delete_customerCode language: Python (python)

Second, call the delete_customer function to delete the customer with ID 1:

try:
    row_deleted = delete_customer(1)
    logging.info(f'{row_deleted} row(s) deleted.')
except Exception as e:
    logging.error(f"Error fetching customers: {e}")Code language: Python (python)

Step 3. Open your terminal and run the main.py module to delete the customer with ID 1:

python main.pyCode language: Python (python)

Output:

INFO:1 row(s) deleted.Code language: Python (python)

The output indicates that one row was deleted.

Download the project source code

Summary

Execute the DELETE statement to delete data from a table in Python.

Was this tutorial helpful?