Home > Python MySQL > Drop Table

Python MySQL Drop Table

Remove entire tables - delete tables you no longer need!

🗑️ Remove Entire Tables!

DROP TABLE removes the whole table - like throwing away an entire filing cabinet! 


import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="yourpassword",
  database="school"
)

cursor = mydb.cursor()
cursor.execute("DROP TABLE old_students")

print("✅ Table 'old_students' deleted!")
DROP
Command
Permanent
Deletion
Clean
Database

What is DROP TABLE?

DROP TABLE completely removes a table:

  • 🗑️ Delete Everything - Removes table and all its data
  • 🏗️ Remove Structure - Deletes columns, data types, everything
  • ⚠️ Permanent Action - Cannot be undone!
  • 🧹 Clean Database - Remove tables you don't need

Drop a Table

Delete a Table 

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="yourpassword",
  database="school"
)

cursor = mydb.cursor()

# Delete the 'test_table' completely
cursor.execute("DROP TABLE test_table")

print("✅ Table 'test_table' has been deleted!")

mydb.close()

⚠️ Warning!

DROP TABLE removes EVERYTHING - the table structure AND all data inside it. This cannot be undone!

Safe Drop (IF EXISTS)

Drop Table Safely 

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="yourpassword",
  database="school"
)

cursor = mydb.cursor()

# Drop table only if it exists (no error if it doesn't exist)
cursor.execute("DROP TABLE IF EXISTS old_records")

print("✅ Table 'old_records' deleted (if it existed)")

mydb.close()

Check Before Dropping

Verify Table Exists First 

import mysql.connector

def safe_drop_table(table_name):
    """Safely drop a table with confirmation"""
    try:
        mydb = mysql.connector.connect(
            host="localhost",
            user="root",
            password="yourpassword",
            database="school"
        )
        
        cursor = mydb.cursor()
        
        # Check if table exists
        cursor.execute("SHOW TABLES LIKE %s", (table_name,))
        result = cursor.fetchone()
        
        if result:
            print(f"📋 Table '{table_name}' found")
            
            # Drop the table
            cursor.execute(f"DROP TABLE {table_name}")
            print(f"✅ Table '{table_name}' has been deleted!")
        else:
            print(f"❌ Table '{table_name}' doesn't exist")
        
        mydb.close()
        
    except mysql.connector.Error as error:
        print(f"❌ Error: {error}")

# Use the function
safe_drop_table("temporary_data")

Drop Multiple Tables

Delete Several Tables 

import mysql.connector

def drop_multiple_tables(table_names):
    """Drop multiple tables safely"""
    try:
        mydb = mysql.connector.connect(
            host="localhost",
            user="root",
            password="yourpassword",
            database="school"
        )
        
        cursor = mydb.cursor()
        
        for table_name in table_names:
            try:
                cursor.execute(f"DROP TABLE IF EXISTS {table_name}")
                print(f"✅ Dropped table: {table_name}")
            except mysql.connector.Error as error:
                print(f"❌ Error dropping {table_name}: {error}")
        
        mydb.close()
        print("🧹 Cleanup complete!")
        
    except mysql.connector.Error as error:
        print(f"❌ Connection error: {error}")

# Drop several old tables
tables_to_remove = ["old_students", "temp_grades", "backup_data"]
drop_multiple_tables(tables_to_remove)

See All Tables First

List All Tables 

import mysql.connector

def show_all_tables():
    """Show all tables in the database"""
    try:
        mydb = mysql.connector.connect(
            host="localhost",
            user="root",
            password="yourpassword",
            database="school"
        )
        
        cursor = mydb.cursor()
        cursor.execute("SHOW TABLES")
        
        tables = cursor.fetchall()
        
        if tables:
            print("📚 Tables in your database:")
            for table in tables:
                print(f"  📋 {table[0]}")
        else:
            print("❌ No tables found in database")
        
        mydb.close()
        
    except mysql.connector.Error as error:
        print(f"❌ Error: {error}")

# See what tables you have before dropping any
show_all_tables()

Backup Before Dropping

Create Backup Table 

import mysql.connector

def backup_and_drop(table_name):
    """Create backup before dropping table"""
    try:
        mydb = mysql.connector.connect(
            host="localhost",
            user="root",
            password="yourpassword",
            database="school"
        )
        
        cursor = mydb.cursor()
        
        # Create backup table
        backup_name = f"{table_name}_backup"
        cursor.execute(f"CREATE TABLE {backup_name} AS SELECT * FROM {table_name}")
        print(f"💾 Backup created: {backup_name}")
        
        # Now drop the original table
        cursor.execute(f"DROP TABLE {table_name}")
        print(f"✅ Original table '{table_name}' dropped")
        print(f"📋 Your data is safe in '{backup_name}'")
        
        mydb.close()
        
    except mysql.connector.Error as error:
        print(f"❌ Error: {error}")

# Backup and drop a table
backup_and_drop("old_students")

DROP TABLE Safety Tips

⚠️ Double Check

Make sure you really want to delete the table

💾 Backup First

Create a backup copy before dropping

🔍 Use IF EXISTS

Prevents errors if table doesn't exist

📋 List Tables

See all tables before deciding what to drop

🧠 Test Your Knowledge

What does DROP TABLE do?

What does "IF EXISTS" prevent?