Python MySQL - Delete Data in MySQL

What You'll Learn Here

What is DELETE — removing data from MySQL tables
DELETE syntax — how to write DELETE statements
Single row deletion — removing specific records
Multiple row deletion — removing many records at once
Safety measures — preventing accidental data loss
Best practices — safe deletion guidelines

What is DELETE?

The DELETE statement is used to remove existing data from a MySQL table. It's the most dangerous operation because once data is deleted, it's usually gone forever.

⚠️ WARNING: DELETE is permanent! Unlike INSERT or UPDATE, there's no easy way to undo a DELETE. Always be careful when deleting data.

Think of DELETE like throwing away a file from your computer. Once you empty the recycle bin, it's gone. DELETE does the same thing with your database records.

When to Use DELETE

# ============================================================
# REAL-WORLD SCENARIOS FOR DELETE
# ============================================================

# 1. Remove a user who closed their account
DELETE FROM students WHERE student_id = 5;

# 2. Delete old or archived records
DELETE FROM orders WHERE order_date < '2023-01-01';

# 3. Remove duplicate records
DELETE FROM students WHERE student_id IN (...);

# 4. Clean up test data
DELETE FROM students WHERE is_test = 1;

# 5. Remove inactive users
DELETE FROM students WHERE last_login < '2022-01-01';

# ============================================================
# IMPORTANT: DELETE vs TRUNCATE
# ============================================================

# DELETE - removes rows one by one (can use WHERE)
DELETE FROM students WHERE age > 30;

# TRUNCATE - removes all rows at once (faster, no WHERE)
TRUNCATE TABLE students;

# Use TRUNCATE when you want to delete ALL rows
# Use DELETE when you want to delete SPECIFIC rows

Key point: DELETE removes data. TRUNCATE removes all data. Both are permanent operations.

Quick Check: What is the difference between DELETE and TRUNCATE? (Answer: DELETE can use WHERE to delete specific rows; TRUNCATE deletes ALL rows)

DELETE Syntax

Basic DELETE Syntax

# ============================================================
# BASIC DELETE SYNTAX
# ============================================================

DELETE FROM table_name WHERE condition;

# ============================================================
# REAL EXAMPLES
# ============================================================

# 1. Delete a specific student
DELETE FROM students WHERE student_id = 5;

# 2. Delete students younger than 18
DELETE FROM students WHERE age < 18;

# 3. Delete students with a specific name
DELETE FROM students WHERE first_name = 'Test';

# 4. Delete students who haven't logged in recently
DELETE FROM students WHERE last_login < '2023-01-01';

# ============================================================
# IMPORTANT: ALWAYS USE WHERE!
# ============================================================

# ❌ DANGEROUS - Deletes ALL rows!
DELETE FROM students;

# ✅ SAFE - Deletes only matching rows
DELETE FROM students WHERE student_id = 5;

# ============================================================
# IN PYTHON
# ============================================================

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="secret",
    database="myapp_db"
)

cursor = connection.cursor()

# Delete a specific student
query = "DELETE FROM students WHERE student_id = %s"
cursor.execute(query, (5,))
connection.commit()

print(f"Deleted {cursor.rowcount} row(s)")

cursor.close()
connection.close()

DELETE structure:

DELETE FROM — specifies the table
WHERE — specifies which rows to delete
Important! Without WHERE, ALL rows are deleted
Always test with SELECT first

Quick Check: What happens if you forget the WHERE clause in DELETE? (Answer: ALL rows in the table will be deleted)

Deleting Single Rows

Removing Specific Records

# ============================================================
# DELETING A SINGLE ROW
# ============================================================

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="secret",
    database="myapp_db"
)

cursor = connection.cursor()

# ============================================================
# METHOD 1: Delete by ID (Safest)
# ============================================================

def delete_student(student_id):
    """Delete a student by their ID"""
    query = "DELETE FROM students WHERE student_id = %s"
    cursor.execute(query, (student_id,))
    connection.commit()
    return cursor.rowcount

# Delete student with ID 5
rows_deleted = delete_student(5)
if rows_deleted > 0:
    print(f"✅ Deleted student ID {5}")
else:
    print(f"⚠️ No student found with ID {5}")

# ============================================================
# METHOD 2: Delete by unique email
# ============================================================

def delete_student_by_email(email):
    """Delete a student by their email"""
    query = "DELETE FROM students WHERE email = %s"
    cursor.execute(query, (email,))
    connection.commit()
    return cursor.rowcount

# Delete student with specific email
rows_deleted = delete_student_by_email("test@email.com")
if rows_deleted > 0:
    print(f"✅ Deleted student with email test@email.com")
else:
    print(f"⚠️ No student found with that email")

# ============================================================
# METHOD 3: Delete with confirmation
# ============================================================

def safe_delete_student(student_id):
    """Delete a student with confirmation"""
    # First, check if student exists
    cursor.execute("SELECT * FROM students WHERE student_id = %s", (student_id,))
    student = cursor.fetchone()
    
    if not student:
        print(f"⚠️ Student ID {student_id} not found")
        return 0
    
    # Show what will be deleted
    print(f"About to delete: {student}")
    
    # Ask for confirmation
    confirm = input(f"Are you sure you want to delete student ID {student_id}? (y/n): ")
    if confirm.lower() == 'y':
        cursor.execute("DELETE FROM students WHERE student_id = %s", (student_id,))
        connection.commit()
        print(f"✅ Deleted student ID {student_id}")
        return cursor.rowcount
    else:
        print(f"❌ Deletion cancelled")
        return 0

# Use the safe delete
safe_delete_student(10)

cursor.close()
connection.close()

Single row deletion tips:

Use a unique identifier (like student_id) in WHERE
Always commit after DELETE
Check cursor.rowcount to see if anything was deleted
Consider confirming before deleting

Quick Check: What should you use in the WHERE clause to delete a single row? (Answer: A unique identifier like student_id or primary key)

Deleting Multiple Rows

Removing Many Records at Once

# ============================================================
# DELETING MULTIPLE ROWS
# ============================================================

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="secret",
    database="myapp_db"
)

cursor = connection.cursor()

# ============================================================
# EXAMPLE 1: Delete by age range
# ============================================================

def delete_by_age_range(min_age, max_age):
    """Delete students in an age range"""
    query = "DELETE FROM students WHERE age BETWEEN %s AND %s"
    cursor.execute(query, (min_age, max_age))
    connection.commit()
    return cursor.rowcount

# Delete students aged 18-20
rows_deleted = delete_by_age_range(18, 20)
print(f"✅ Deleted {rows_deleted} students aged 18-20")

# ============================================================
# EXAMPLE 2: Delete by condition
# ============================================================

def delete_inactive_students():
    """Delete students who haven't logged in recently"""
    query = "DELETE FROM students WHERE last_login < '2023-01-01'"
    cursor.execute(query)
    connection.commit()
    return cursor.rowcount

# Delete inactive students
rows_deleted = delete_inactive_students()
print(f"✅ Deleted {rows_deleted} inactive students")

# ============================================================
# EXAMPLE 3: Delete using IN operator
# ============================================================

def delete_by_ids(student_ids):
    """Delete students with specific IDs"""
    placeholders = ', '.join(['%s'] * len(student_ids))
    query = f"DELETE FROM students WHERE student_id IN ({placeholders})"
    cursor.execute(query, student_ids)
    connection.commit()
    return cursor.rowcount

# Delete students with IDs 10, 12, 15
rows_deleted = delete_by_ids([10, 12, 15])
print(f"✅ Deleted {rows_deleted} students")

# ============================================================
# SAFETY: Check before deleting
# ============================================================

def safe_delete_with_check(condition_query, delete_query, params):
    """Check what will be deleted before actually deleting"""
    # First, see what will be deleted
    cursor.execute(condition_query, params)
    rows = cursor.fetchall()
    
    if not rows:
        print("⚠️ No rows found to delete")
        return 0
    
    print(f"⚠️ About to delete {len(rows)} rows:")
    for row in rows:
        print(f"   {row}")
    
    confirm = input("Are you sure you want to delete these rows? (y/n): ")
    if confirm.lower() == 'y':
        cursor.execute(delete_query, params)
        connection.commit()
        print(f"✅ Deleted {cursor.rowcount} rows")
        return cursor.rowcount
    else:
        print("❌ Deletion cancelled")
        return 0

# Example usage
safe_delete_with_check(
    "SELECT * FROM students WHERE age > 30",
    "DELETE FROM students WHERE age > 30",
    ()
)

cursor.close()
connection.close()

Multiple row deletion tips:

Be extra careful with multiple row deletions
Always test with SELECT first
Use transactions for safety
Consider backup before major deletions

Quick Check: How can you test which rows will be deleted before running DELETE? (Answer: Use a SELECT statement with the same WHERE clause)

Deleting All Rows

Clearing an Entire Table

⚠️ WARNING: This section shows how to delete ALL rows. This is DANGEROUS and should only be done with extreme caution!

# ============================================================
# DELETING ALL ROWS
# ============================================================

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="secret",
    database="myapp_db"
)

cursor = connection.cursor()

# ============================================================
# METHOD 1: DELETE ALL (SLOW but can use WHERE)
# ============================================================

def delete_all_students():
    """Delete all students - BAD IDEA unless you're sure!"""
    # Show warning
    print("⚠️⚠️⚠️ WARNING: This will delete ALL students! ⚠️⚠️⚠️")
    
    # Show count
    cursor.execute("SELECT COUNT(*) FROM students")
    count = cursor.fetchone()[0]
    print(f"📊 There are {count} students in the table")
    
    # Ask for confirmation
    confirm = input("Are you REALLY sure? Type 'YES' to continue: ")
    if confirm == 'YES':
        cursor.execute("DELETE FROM students")
        connection.commit()
        print(f"✅ Deleted ALL {cursor.rowcount} students")
        return cursor.rowcount
    else:
        print("❌ Deletion cancelled")
        return 0

# ============================================================
# METHOD 2: TRUNCATE (FASTER but can't use WHERE)
# ============================================================

def truncate_students():
    """Truncate the students table - even faster!"""
    print("⚠️⚠️⚠️ WARNING: This will TRUNCATE the entire table! ⚠️⚠️⚠️")
    
    confirm = input("Are you REALLY sure? Type 'YES' to continue: ")
    if confirm == 'YES':
        cursor.execute("TRUNCATE TABLE students")
        connection.commit()
        print("✅ Table truncated (all rows removed)")
        return True
    else:
        print("❌ Truncation cancelled")
        return False

# ============================================================
# COMPARISON: DELETE vs TRUNCATE
# ============================================================

print("\n📊 DELETE vs TRUNCATE:")
print("┌─────────────────┬─────────────────┬─────────────────┐")
print("│ DELETE           │ TRUNCATE        │ DIFFERENCE      │")
print("├─────────────────┼─────────────────┼─────────────────┤")
print("│ Can use WHERE   │ No WHERE        │ DELETE is safer │")
print("│ Slower          │ Faster          │ TRUNCATE is faster")
print("│ Can rollback    │ Cannot rollback │ DELETE is safer │")
print("│ Returns rowcount│ No rowcount     │ DELETE gives info")
print("└─────────────────┴─────────────────┴─────────────────┘")

# ============================================================
# SAFER APPROACH: Delete in batches
# ============================================================

def delete_in_batches(batch_size=1000):
    """Delete all rows in batches (safer for large tables)"""
    total_deleted = 0
    
    while True:
        cursor.execute(f"DELETE FROM students LIMIT {batch_size}")
        connection.commit()
        rows_deleted = cursor.rowcount
        
        if rows_deleted == 0:
            break
        
        total_deleted += rows_deleted
        print(f"Deleted {rows_deleted} rows (Total: {total_deleted})")
    
    print(f"✅ Finished. Deleted {total_deleted} rows total")
    return total_deleted

cursor.close()
connection.close()

Deleting all rows - key points:

DELETE FROM table — deletes all rows (slow, can be rolled back)
TRUNCATE TABLE — deletes all rows (fast, cannot be rolled back)
Always confirm before deleting all rows
Consider batching for large tables

Quick Check: What is the difference between DELETE FROM table and TRUNCATE TABLE? (Answer: DELETE can be rolled back and is slower; TRUNCATE is faster but cannot be rolled back)

Safety Measures

Protecting Your Data

# ============================================================
# SAFETY MEASURES FOR DELETE
# ============================================================

print("=" * 60)
print("SAFETY MEASURES FOR DELETE OPERATIONS")
print("=" * 60)

# ============================================================
# 1. ALWAYS TEST WITH SELECT FIRST
# ============================================================

print("\n1. TEST WITH SELECT FIRST")
print("   # See what will be deleted")
print("   SELECT * FROM students WHERE age > 30")
print("   # Then delete")
print("   DELETE FROM students WHERE age > 30")

# ============================================================
# 2. USE TRANSACTIONS
# ============================================================

print("\n2. USE TRANSACTIONS")
print("   # Start transaction")
print("   connection.start_transaction()")
print("   # Delete")
print("   cursor.execute('DELETE FROM students WHERE student_id = 5')")
print("   # Verify")
print("   if okay: connection.commit()")
print("   else: connection.rollback()")

# ============================================================
# 3. BACKUP BEFORE DELETING
# ============================================================

print("\n3. BACKUP BEFORE DELETING")
print("   # Backup the table")
print("   CREATE TABLE students_backup AS SELECT * FROM students")
print("   # Or backup specific data")
print("   CREATE TABLE students_to_delete AS SELECT * FROM students WHERE age > 30")

# ============================================================
# 4. USE SOFT DELETE
# ============================================================

print("\n4. USE SOFT DELETE")
print("   # Instead of deleting, mark as deleted")
print("   ALTER TABLE students ADD COLUMN is_deleted BOOLEAN DEFAULT 0")
print("   # 'Delete' by updating")
print("   UPDATE students SET is_deleted = 1 WHERE student_id = 5")
print("   # Filter out deleted records")
print("   SELECT * FROM students WHERE is_deleted = 0")

# ============================================================
# 5. DOUBLE CHECK
# ============================================================

print("\n5. DOUBLE CHECK YOUR WHERE CLAUSE")
print("   # Always double check your WHERE clause")
print("   # Make sure you're deleting the right rows")

# ============================================================
# 6. LIMIT DELETIONS
# ============================================================

print("\n6. LIMIT DELETIONS FOR SAFETY")
print("   # Delete in small batches")
print("   DELETE FROM students WHERE age > 30 LIMIT 100")

# ============================================================
# IMPLEMENTATION: SafeDelete Class
# ============================================================

class SafeDelete:
    """Helper class for safe deletion operations"""
    
    def __init__(self, cursor, connection):
        self.cursor = cursor
        self.connection = connection
    
    def preview(self, query, params=None):
        """Preview what will be deleted"""
        select_query = query.replace("DELETE", "SELECT *")
        self.cursor.execute(select_query, params)
        return self.cursor.fetchall()
    
    def delete_with_confirmation(self, delete_query, params=None, message="Are you sure?"):
        """Delete with user confirmation"""
        # Preview
        rows = self.preview(delete_query, params)
        
        if not rows:
            print("No rows found to delete")
            return 0
        
        print(f"About to delete {len(rows)} rows")
        for row in rows[:5]:  # Show first 5
            print(f"  {row}")
        if len(rows) > 5:
            print(f"  ... and {len(rows) - 5} more")
        
        confirm = input(f"{message} (y/n): ")
        if confirm.lower() == 'y':
            self.cursor.execute(delete_query, params)
            self.connection.commit()
            return self.cursor.rowcount
        else:
            print("Deletion cancelled")
            return 0

Safety measures summary:

Test with SELECT — verify what will be deleted
Use transactions — commit or rollback
Backup — save data before deleting
Soft delete — mark instead of actually deleting
Double check — verify your WHERE clause
Limit deletions — delete in batches

Quick Check: What is a soft delete? (Answer: Marking records as deleted instead of actually removing them)

Real-World Example: User Management

Building a User Management System with Safe Deletion

# ============================================================
# USER MANAGEMENT WITH SAFE DELETION
# ============================================================

import mysql.connector

class UserManager:
    """Manage users with safe deletion features"""
    
    def __init__(self, db_config):
        self.db_config = db_config
        self.connection = None
        self.cursor = None
    
    def connect(self):
        try:
            self.connection = mysql.connector.connect(**self.db_config)
            self.cursor = self.connection.cursor()
            return True
        except mysql.connector.Error as e:
            print(f"Connection failed: {e}")
            return False
    
    def get_user(self, user_id):
        """Get a user by ID"""
        query = "SELECT * FROM students WHERE student_id = %s"
        self.cursor.execute(query, (user_id,))
        return self.cursor.fetchone()
    
    def delete_user(self, user_id, confirm=False):
        """Delete a user (with confirmation)"""
        # Check if user exists
        user = self.get_user(user_id)
        if not user:
            print(f"User ID {user_id} not found")
            return 0
        
        print(f"About to delete user: {user}")
        
        if confirm:
            query = "DELETE FROM students WHERE student_id = %s"
            self.cursor.execute(query, (user_id,))
            self.connection.commit()
            print(f"✅ Deleted user ID {user_id}")
            return self.cursor.rowcount
        else:
            print("❌ Deletion requires confirmation")
            return 0
    
    def delete_inactive_users(self, days, confirm=False):
        """Delete users inactive for more than X days"""
        query = "DELETE FROM students WHERE last_login < DATE_SUB(CURDATE(), INTERVAL %s DAY)"
        
        if confirm:
            self.cursor.execute(query, (days,))
            self.connection.commit()
            print(f"✅ Deleted {self.cursor.rowcount} inactive users")
            return self.cursor.rowcount
        else:
            print("❌ Deletion requires confirmation")
            return 0
    
    def soft_delete_user(self, user_id):
        """Soft delete a user (mark as deleted)"""
        query = "UPDATE students SET is_deleted = 1 WHERE student_id = %s"
        self.cursor.execute(query, (user_id,))
        self.connection.commit()
        print(f"✅ Soft deleted user ID {user_id}")
        return self.cursor.rowcount
    
    def restore_user(self, user_id):
        """Restore a soft-deleted user"""
        query = "UPDATE students SET is_deleted = 0 WHERE student_id = %s"
        self.cursor.execute(query, (user_id,))
        self.connection.commit()
        print(f"✅ Restored user ID {user_id}")
        return self.cursor.rowcount
    
    def bulk_delete(self, user_ids, confirm=False):
        """Delete multiple users at once"""
        if not user_ids:
            return 0
        
        placeholders = ', '.join(['%s'] * len(user_ids))
        query = f"DELETE FROM students WHERE student_id IN ({placeholders})"
        
        if confirm:
            self.cursor.execute(query, user_ids)
            self.connection.commit()
            print(f"✅ Deleted {self.cursor.rowcount} users")
            return self.cursor.rowcount
        else:
            print("❌ Deletion requires confirmation")
            return 0
    
    def close(self):
        if self.cursor:
            self.cursor.close()
        if self.connection:
            self.connection.close()

# ============================================================
# DEMONSTRATION
# ============================================================

db_config = {
    "host": "localhost",
    "user": "root",
    "password": "secret",
    "database": "myapp_db"
}

manager = UserManager(db_config)
if manager.connect():
    
    # 1. Get user before deletion
    user = manager.get_user(5)
    if user:
        print(f"User before: {user}")
    
    # 2. Delete with confirmation
    # This would only work with confirm=True
    # rows = manager.delete_user(5, confirm=True)
    
    # 3. Soft delete
    rows = manager.soft_delete_user(5)
    print(f"Soft deleted {rows} user(s)")
    
    # 4. Restore
    rows = manager.restore_user(5)
    print(f"Restored {rows} user(s)")
    
    # 5. Bulk delete
    # rows = manager.bulk_delete([10, 12, 15], confirm=True)
    
    manager.close()

This example shows:

User lookup before deletion
Confirmation-based deletion
Soft delete (marking instead of removing)
Restoring soft-deleted users
Bulk deletion with confirmation

Quick Check: What is the advantage of soft delete over hard delete? (Answer: Soft delete allows you to recover data if needed)

Best Practices

Safe Deletion Guidelines

# ============================================================
# BEST PRACTICES FOR DELETE
# ============================================================

print("1. ALWAYS USE WHERE CLAUSE")
print("   - Without WHERE, ALL rows are deleted")
print("   - This is the most common and dangerous mistake")

print("\n2. TEST WITH SELECT FIRST")
print("   - Use SELECT with the same WHERE clause")
print("   - Verify you're deleting the right rows")

print("\n3. USE TRANSACTIONS")
print("   - Start a transaction before DELETE")
print("   - Commit only when you're sure")
print("   - Rollback if something goes wrong")

print("\n4. CHECK ROW COUNT")
print("   - Always check cursor.rowcount")
print("   - Verify the expected number of rows deleted")

print("\n5. USE PARAMETERIZED QUERIES")
print("   - Always use %s placeholders")
print("   - Never build queries with string concatenation")
print("   - Protects against SQL injection")

print("\n6. BACKUP BEFORE DELETING")
print("   - For major deletions, backup the table")
print("   - CREATE TABLE backup_students AS SELECT * FROM students")

print("\n7. CONSIDER SOFT DELETE")
print("   - Add an is_deleted column")
print("   - Update instead of delete")
print("   - Allows recovery of data")

print("\n8. LOG YOUR DELETIONS")
print("   - Keep track of who deleted what")
print("   - When and why data was deleted")

print("\n9. USE LIMIT FOR LARGE DELETIONS")
print("   - Delete in small batches")
print("   - Prevents long-running transactions")

print("\n10. GET CONFIRMATION")
print("   - Always confirm before deleting")
print("   - Especially for important data")

Summary of best practices:

Always use WHERE — never delete all rows unintentionally
Test with SELECT — verify before you delete
Use transactions — commit or rollback safely
Check rowcount — verify how many rows were deleted
Parameterized queries — prevent SQL injection
Backup first — for major deletions
Soft delete — mark instead of removing
Log deletions — keep track of what was removed
Get confirmation — double-check before deleting

Quick Check: What is the most important rule for DELETE statements? (Answer: Always include a WHERE clause and test with SELECT first)

Try It Yourself

Experiment with DELETE statements in the editor below.

Loading Pyodide... 0%

Python Code Editor

# Python MySQL DELETE - Practice

print("=" * 40)
print("DELETE DATA - PRACTICE")
print("=" * 40)

# Simulated Database for learning
class SimulatedDB:
    students = [
        {"id": 1, "first_name": "Rahul", "last_name": "Sharma", "age": 22, "email": "rahul@email.com"},
        {"id": 2, "first_name": "Priya", "last_name": "Patel", "age": 25, "email": "priya@email.com"},
        {"id": 3, "first_name": "Amit", "last_name": "Singh", "age": 24, "email": "amit@email.com"},
        {"id": 4, "first_name": "Sneha", "last_name": "Reddy", "age": 23, "email": "sneha@email.com"},
        {"id": 5, "first_name": "Vikram", "last_name": "Kumar", "age": 26, "email": "vikram@email.com"},
        {"id": 6, "first_name": "Anjali", "last_name": "Nair", "age": 21, "email": "anjali@email.com"},
        {"id": 7, "first_name": "Ravi", "last_name": "Desai", "age": 27, "email": "ravi@email.com"},
        {"id": 8, "first_name": "Meera", "last_name": "Iyer", "age": 23, "email": "meera@email.com"}
    ]
    
    @classmethod
    def delete(cls, condition_func):
        """Delete students matching a condition"""
        count = 0
        remaining = []
        for student in cls.students:
            if condition_func(student):
                count += 1
            else:
                remaining.append(student)
        cls.students = remaining
        return count
    
    @classmethod
    def display_all(cls, title="All Students"):
        """Display all students"""
        if not cls.students:
            print(f"\n{title}: No students!")
            return
        print(f"\n{title}:")
        print("-" * 55)
        for s in cls.students:
            print(f"  ID: {s['id']:1} | {s['first_name']:8} {s['last_name']:8} | Age: {s['age']:2} | {s['email']}")
        print(f"  Total: {len(cls.students)} students")

print("\n1. SINGLE ROW DELETE")
print("-" * 40)

SimulatedDB.display_all("Before deletion")

print("\n🔄 Deleting student with ID 5...")
count = SimulatedDB.delete(lambda s: s["id"] == 5)
print(f"Deleted {count} student(s)")

SimulatedDB.display_all("After deletion")

print("\n2. MULTIPLE ROW DELETE")
print("-" * 40)

print("🔄 Deleting all students aged 23...")
count = SimulatedDB.delete(lambda s: s["age"] == 23)
print(f"Deleted {count} student(s)")

SimulatedDB.display_all("After age deletion")

print("\n3. DELETE BY CONDITION")
print("-" * 40)

print("🔄 Deleting all students older than 25...")
count = SimulatedDB.delete(lambda s: s["age"] > 25)
print(f"Deleted {count} student(s)")

SimulatedDB.display_all("After condition deletion")

print("\n4. DELETE ALL (with safety)")
print("-" * 40)

print("⚠️ Be careful! This deletes ALL remaining students!")
confirm = input("Type 'YES' to delete all: ")
if confirm == 'YES':
    count = SimulatedDB.delete(lambda s: True)
    print(f"Deleted {count} student(s)")
    SimulatedDB.display_all("After deleting all")
else:
    print("❌ Deletion cancelled")

print("\n✅ DELETE is powerful! Always use with caution!")

🏆

You've Got It!

You now know how to safely delete data from MySQL using Python. You understand single row deletion, multiple row deletion, and important safety measures.

Quick Quiz

Test what you've learned:

1. What does the DELETE statement do?

A) Adds new data to a table B) Modifies existing data in a table C) Removes data from a table D) Creates a new table

2. What happens if you omit the WHERE clause in a DELETE?

A) Nothing changes B) ALL rows in the table are deleted C) Only the first row is deleted D) An error occurs

3. What is a soft delete?

A) Deleting data quickly B) Marking data as deleted instead of actually removing it C) Deleting data in small batches D) Using TRUNCATE instead of DELETE

4. What is the difference between DELETE and TRUNCATE?

A) They do the same thing B) DELETE can use WHERE; TRUNCATE cannot C) TRUNCATE can use WHERE; DELETE cannot D) TRUNCATE is slower than DELETE

5. What should you do before running a major DELETE?

A) Nothing, just run it B) Test with SELECT and backup data C) Restart the database D) Disconnect all users

Frequently Asked Questions

What happens if I forget the WHERE clause in DELETE?

If you forget the WHERE clause, ALL rows in the table will be deleted. This is a very common and dangerous mistake. Always double-check your DELETE statements before running them.

Can I undo a DELETE?

If you use transactions, you can rollback a DELETE before committing. If you've already committed, you'll need to restore from backup. That's why it's important to backup data before major deletions and use soft delete when possible.

What is the difference between DELETE and DROP?

DELETE removes rows (data) from a table but keeps the table structure. DROP removes the entire table (both data AND structure). DROP is permanent and cannot be rolled back.

What is a common interview question about DELETE?

Common questions include: "What happens if you run DELETE without WHERE?" "What is the difference between DELETE and TRUNCATE?" and "How would you safely delete data from a production database?"

How can I recover deleted data?

If you used a transaction and haven't committed, you can rollback. If you've committed, you'll need to restore from a backup. That's why soft delete (marking as deleted) is often preferred for important data.

Where to Go From Here

Now that you know how to safely delete data, check out these related topics:

Previous: Update Data Next: Parameterized Queries →

Python

Python: Delete Data in MySQL

What is DELETE?

When to Use DELETE

DELETE Syntax

Basic DELETE Syntax

Deleting Single Rows

Removing Specific Records

Deleting Multiple Rows

Removing Many Records at Once

Deleting All Rows

Clearing an Entire Table

Safety Measures

Protecting Your Data

Real-World Example: User Management

Building a User Management System with Safe Deletion

Best Practices

Safe Deletion Guidelines

Try It Yourself

You've Got It!

Quick Quiz

Frequently Asked Questions

What happens if I forget the WHERE clause in DELETE?

Can I undo a DELETE?

What is the difference between DELETE and DROP?

What is a common interview question about DELETE?

How can I recover deleted data?

Where to Go From Here

Update Data

Transactions

Error Handling

Python

Python: Delete Data in MySQL

What is DELETE?

When to Use DELETE

DELETE Syntax

Basic DELETE Syntax

Deleting Single Rows

Removing Specific Records

Deleting Multiple Rows

Removing Many Records at Once

Deleting All Rows

Clearing an Entire Table

Safety Measures

Protecting Your Data

Real-World Example: User Management

Building a User Management System with Safe Deletion

Best Practices

Safe Deletion Guidelines

Try It Yourself

You've Got It!

Quick Quiz

Frequently Asked Questions

What happens if I forget the WHERE clause in DELETE? ▼

Can I undo a DELETE? ▼

What is the difference between DELETE and DROP? ▼

What is a common interview question about DELETE? ▼

How can I recover deleted data? ▼

Where to Go From Here

Update Data

Transactions

Error Handling

What happens if I forget the WHERE clause in DELETE?

Can I undo a DELETE?

What is the difference between DELETE and DROP?

What is a common interview question about DELETE?

How can I recover deleted data?