Deleting records from a Microsoft SQL Server (MSSQL) database is a common operation in application development. Whether you're cleaning up test data or removing user entries, the DELETE statement allows you to remove specific records from a table. In this tutorial, you'll learn how to use DELETE in Python with MSSQL using the pyodbc library — including safety best practices, parameterized queries, and full working examples.

✅ Prerequisites

Before you get started, ensure the following are ready:

Software Required

• Python 3.7+

• Microsoft SQL Server (Express, Developer, or Full)

• ODBC Driver 17 or 18 for SQL Server

Python Library

Install pyodbc if you haven’t already:

pip install pyodbc

Example Table Used

We’ll work with this sample Employees table:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Email NVARCHAR(100),
    Age INT,
    HireDate DATE,
    Salary DECIMAL(10, 2),
    IsActive BIT
);

Step 1: Connect to the Database

import pyodbc

conn = pyodbc.connect(
    "DRIVER={ODBC Driver 17 for SQL Server};"
    "SERVER=localhost;"
    "DATABASE=EmployeeDB;"
    "UID=your_username;"
    "PWD=your_password;"
)
cursor = conn.cursor()

Step 2: Delete a Specific Record

Example: Delete Employee by ID

employee_id = 101
cursor.execute("DELETE FROM Employees WHERE EmployeeID = ?", (employee_id,))
conn.commit()
print(f"Employee with ID {employee_id} has been deleted.")

The conn.commit() is essential — it finalizes the deletion.

Step 3: Delete with Multiple Conditions

cursor.execute("""
    DELETE FROM Employees
    WHERE IsActive = 0 AND Salary < ?
""", (30000,))
conn.commit()
print("Inactive employees with low salary have been deleted.")

Always confirm conditions carefully to avoid deleting more data than intended.

Step 4: Prevent SQL Injection

Never do this:

# UNSAFE - vulnerable to SQL injection
employee_id = input("Enter ID: ")
cursor.execute(f"DELETE FROM Employees WHERE EmployeeID = {employee_id}")

Use parameterized queries like this instead:

employee_id = int(input("Enter ID: "))
cursor.execute("DELETE FROM Employees WHERE EmployeeID = ?", (employee_id,))
conn.commit()

⚠️ Step 5: Delete All Records (Use with Caution!)

cursor.execute("DELETE FROM Employees")
conn.commit()
print("All employee records have been deleted.")

Tip: Use TRUNCATE TABLE Employees for faster deletion without logging, but be aware it cannot be rolled back or used with WHERE.

Full Working Example

import pyodbc

def delete_employee_by_id(emp_id):
    try:
        conn = pyodbc.connect(
            "DRIVER={ODBC Driver 17 for SQL Server};"
            "SERVER=localhost;"
            "DATABASE=EmployeeDB;"
            "UID=your_username;"
            "PWD=your_password;"
        )
        cursor = conn.cursor()

        cursor.execute("SELECT * FROM Employees WHERE EmployeeID = ?", (emp_id,))
        row = cursor.fetchone()

        if row:
            cursor.execute("DELETE FROM Employees WHERE EmployeeID = ?", (emp_id,))
            conn.commit()
            print(f"Deleted employee: {row.FirstName} {row.LastName}")
        else:
            print(f"No employee found with ID {emp_id}")

    except pyodbc.Error as e:
        print("Database error:", e)
    finally:
        cursor.close()
        conn.close()

# Example usage
delete_employee_by_id(102)

Tips for Deleting Data in Python with MSSQL

⚠️ Common Pitfalls

Conclusion

Deleting records in MSSQL using Python is straightforward and powerful when used correctly. Always use parameterized queries, add safety checks, and remember to commit your changes. Deletion is irreversible, so take precautions like running a SELECT first, and always back up critical data.