Python MySQL - DML Statements (E-Commerce Platform)

What You'll Learn Here

INSERT — adding products, customers, and orders
UPDATE — modifying prices, stock, and order status
DELETE — removing reviews and cancelled orders
SELECT — querying data for dashboards and reports
Python implementation — executing DML from Python

What are DML Statements?

DML (Data Manipulation Language) statements are used to add, modify, delete, and retrieve data from your database tables. They work with the data itself, not the structure.

INSERT

Adds new rows to a table

UPDATE

Modifies existing rows

DELETE

Removes rows from a table

SELECT

Retrieves data from tables

DML in E-Commerce

# ============================================================
# DML STATEMENTS IN E-COMMERCE
# ============================================================

# 1. INSERT - Add a new product
INSERT INTO products (product_name, price, stock_quantity) 
VALUES ('iPhone 15', 999.99, 50);

# 2. UPDATE - Change product price
UPDATE products SET price = 899.99 WHERE product_id = 1;

# 3. DELETE - Remove a cancelled order
DELETE FROM orders WHERE order_id = 5 AND status = 'cancelled';

# 4. SELECT - Get all electronics products
SELECT * FROM products WHERE category = 'Electronics';

Key point: DML statements work with the data in your tables. They are the most commonly used SQL operations.

Quick Check: What does DML stand for? (Answer: Data Manipulation Language)

INSERT - Adding Data

Inserting E-Commerce Data from Python

# ============================================================
# INSERT STATEMENTS FROM PYTHON
# ============================================================

import mysql.connector

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

cursor = connection.cursor()

# ============================================================
# 1. INSERT A SINGLE PRODUCT
# ============================================================

query = """
    INSERT INTO products (product_name, category, price, stock_quantity)
    VALUES (%s, %s, %s, %s)
"""
values = ("iPhone 15 Pro", "Electronics", 999.99, 50)

cursor.execute(query, values)
connection.commit()

print(f"Product added! ID: {cursor.lastrowid}")

# ============================================================
# 2. INSERT A CUSTOMER
# ============================================================

query = """
    INSERT INTO customers (first_name, last_name, email, phone, city)
    VALUES (%s, %s, %s, %s, %s)
"""
values = ("Rahul", "Sharma", "rahul@email.com", "9876543210", "Mumbai")

cursor.execute(query, values)
connection.commit()

print(f"Customer added! ID: {cursor.lastrowid}")

# ============================================================
# 3. INSERT AN ORDER
# ============================================================

query = """
    INSERT INTO orders (customer_id, total_amount, status)
    VALUES (%s, %s, %s)
"""
values = (1, 1099.98, 'pending')

cursor.execute(query, values)
order_id = cursor.lastrowid
connection.commit()

print(f"Order added! ID: {order_id}")

# ============================================================
# 4. INSERT ORDER ITEMS
# ============================================================

query = """
    INSERT INTO order_items (order_id, product_id, quantity, unit_price, total_price)
    VALUES (%s, %s, %s, %s, %s)
"""
items = [
    (order_id, 1, 1, 999.99, 999.99),
    (order_id, 7, 1, 69.99, 69.99)
]

cursor.executemany(query, items)
connection.commit()

print(f"Added {cursor.rowcount} order items")

# ============================================================
# 5. INSERT A REVIEW
# ============================================================

query = """
    INSERT INTO reviews (customer_id, product_id, rating, comment)
    VALUES (%s, %s, %s, %s)
"""
values = (1, 1, 5, "Amazing phone! Great battery life.")

cursor.execute(query, values)
connection.commit()

print(f"Review added! ID: {cursor.lastrowid}")

cursor.close()
connection.close()

INSERT key points:

INSERT INTO — specifies the table
VALUES — provides the data
executemany() — inserts multiple rows
lastrowid — gets the auto-generated ID

Quick Check: How do you get the ID of a newly inserted row? (Answer: cursor.lastrowid)

UPDATE - Modifying Data

Updating E-Commerce Data from Python

# ============================================================
# UPDATE STATEMENTS FROM PYTHON
# ============================================================

import mysql.connector

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

cursor = connection.cursor()

# ============================================================
# 1. UPDATE PRODUCT PRICE
# ============================================================

cursor.execute("""
    UPDATE products 
    SET price = 899.99 
    WHERE product_id = 1
""")
connection.commit()
print(f"Updated {cursor.rowcount} product(s)")

# ============================================================
# 2. UPDATE PRODUCT STOCK
# ============================================================

cursor.execute("""
    UPDATE products 
    SET stock_quantity = stock_quantity - 1 
    WHERE product_id = 1 AND stock_quantity > 0
""")
connection.commit()
print(f"Updated {cursor.rowcount} product(s)")

# ============================================================
# 3. UPDATE ORDER STATUS
# ============================================================

cursor.execute("""
    UPDATE orders 
    SET status = 'completed', payment_status = 'paid' 
    WHERE order_id = 1
""")
connection.commit()
print(f"Updated {cursor.rowcount} order(s)")

# ============================================================
# 4. UPDATE WITH CALCULATIONS
# ============================================================

# Apply 10% discount to electronics
cursor.execute("""
    UPDATE products 
    SET price = price * 0.90 
    WHERE category = 'Electronics' AND price > 500
""")
connection.commit()
print(f"Updated {cursor.rowcount} product(s)")

# ============================================================
# 5. UPDATE WITH PARAMETERIZED QUERY
# ============================================================

def update_customer_phone(customer_id, new_phone):
    query = "UPDATE customers SET phone = %s WHERE customer_id = %s"
    cursor.execute(query, (new_phone, customer_id))
    connection.commit()
    return cursor.rowcount

rows = update_customer_phone(1, "9876543211")
print(f"Updated {rows} customer(s)")

cursor.close()
connection.close()

UPDATE key points:

UPDATE table — specifies the table
SET column = value — defines the change
WHERE — specifies which rows to update
Always use WHERE — prevents updating all rows

Quick Check: What happens if you forget WHERE in UPDATE? (Answer: ALL rows in the table are updated)

DELETE - Removing Data

Deleting E-Commerce Data from Python

⚠️ WARNING: DELETE is permanent!

Always use WHERE clause and consider soft deletion (marking as deleted) for important data.

# ============================================================
# DELETE STATEMENTS FROM PYTHON
# ============================================================

import mysql.connector

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

cursor = connection.cursor()

# ============================================================
# 1. DELETE A REVIEW
# ============================================================

cursor.execute("DELETE FROM reviews WHERE review_id = 1")
connection.commit()
print(f"Deleted {cursor.rowcount} review(s)")

# ============================================================
# 2. DELETE CANCELLED ORDERS
# ============================================================

cursor.execute("DELETE FROM orders WHERE status = 'cancelled'")
connection.commit()
print(f"Deleted {cursor.rowcount} order(s)")

# ============================================================
# 3. DELETE WITH PARAMETERIZED QUERY
# ============================================================

def delete_order(order_id):
    query = "DELETE FROM orders WHERE order_id = %s"
    cursor.execute(query, (order_id,))
    connection.commit()
    return cursor.rowcount

rows = delete_order(5)
print(f"Deleted {rows} order(s)")

# ============================================================
# 4. SOFT DELETE - Mark as deleted instead of removing
# ============================================================

# Add a deleted flag to products table
cursor.execute("""
    ALTER TABLE products 
    ADD COLUMN is_deleted BOOLEAN DEFAULT 0
""")
connection.commit()
print("Added is_deleted column")

# Soft delete a product
cursor.execute("""
    UPDATE products 
    SET is_deleted = 1 
    WHERE product_id = 1
""")
connection.commit()
print(f"Soft deleted {cursor.rowcount} product(s)")

# Query that excludes soft-deleted products
cursor.execute("SELECT * FROM products WHERE is_deleted = 0")
products = cursor.fetchall()
print(f"Active products: {len(products)}")

cursor.close()
connection.close()

DELETE key points:

DELETE FROM table — specifies the table
WHERE — specifies which rows to delete
Always use WHERE — prevents deleting all rows
Soft delete — mark as deleted instead of removing

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

SELECT - Reading Data

Querying E-Commerce Data from Python

# ============================================================
# SELECT STATEMENTS FROM PYTHON
# ============================================================

import mysql.connector

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

cursor = connection.cursor()

# ============================================================
# 1. GET ALL PRODUCTS
# ============================================================

cursor.execute("SELECT * FROM products")
products = cursor.fetchall()

print("All Products:")
for p in products:
    print(f"  {p[1]} - ${p[4]} (Stock: {p[5]})")

# ============================================================
# 2. GET PRODUCTS BY CATEGORY
# ============================================================

cursor.execute("SELECT * FROM products WHERE category = 'Electronics'")
electronics = cursor.fetchall()

print("\nElectronics Products:")
for p in electronics:
    print(f"  {p[1]} - ${p[4]}")

# ============================================================
# 3. GET CUSTOMER ORDERS WITH DETAILS
# ============================================================

query = """
    SELECT o.order_id, o.order_date, o.total_amount, o.status,
           c.first_name, c.last_name
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    WHERE c.customer_id = 1
    ORDER BY o.order_date DESC
"""
cursor.execute(query)
orders = cursor.fetchall()

print("\nCustomer Orders:")
for o in orders:
    print(f"  Order {o[0]} - {o[1]} - ${o[2]} - {o[3]}")

# ============================================================
# 4. GET TOP SELLING PRODUCTS
# ============================================================

cursor.execute("""
    SELECT p.product_name, SUM(oi.quantity) as total_sold,
           SUM(oi.quantity * oi.unit_price) as revenue
    FROM products p
    JOIN order_items oi ON p.product_id = oi.product_id
    GROUP BY p.product_id
    ORDER BY total_sold DESC
    LIMIT 5
""")
top_products = cursor.fetchall()

print("\nTop Selling Products:")
for p in top_products:
    print(f"  {p[0]} - {p[1]} units sold - ${p[2]} revenue")

# ============================================================
# 5. GET PRODUCT REVIEWS
# ============================================================

cursor.execute("""
    SELECT r.rating, r.comment, c.first_name, c.last_name
    FROM reviews r
    JOIN customers c ON r.customer_id = c.customer_id
    WHERE r.product_id = 1
    ORDER BY r.review_date DESC
""")
reviews = cursor.fetchall()

print("\nProduct Reviews:")
for r in reviews:
    print(f"  {'*' * r[0]} - {r[1][:50]}... - {r[2]} {r[3]}")

cursor.close()
connection.close()

SELECT key points:

SELECT columns — specifies what to retrieve
FROM table — specifies the source
WHERE — filters results
JOIN — combines related tables
ORDER BY — sorts results
LIMIT — limits number of results

Quick Check: What does JOIN do in a SELECT query? (Answer: It combines data from multiple related tables)

E-Commerce Python Examples

Complete E-Commerce Data Operations

# ============================================================
# COMPLETE E-COMMERCE DATA OPERATIONS
# ============================================================

import mysql.connector

class ECommerceData:
    """E-Commerce data operations using DML"""
    
    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 Exception as e:
            print(f"Connection failed: {e}")
            return False
    
    # ============================================================
    # PRODUCT OPERATIONS
    # ============================================================
    
    def add_product(self, name, category, price, stock):
        """Add a new product"""
        query = """
            INSERT INTO products (product_name, category, price, stock_quantity)
            VALUES (%s, %s, %s, %s)
        """
        self.cursor.execute(query, (name, category, price, stock))
        self.connection.commit()
        return self.cursor.lastrowid
    
    def update_product_price(self, product_id, new_price):
        """Update product price"""
        query = "UPDATE products SET price = %s WHERE product_id = %s"
        self.cursor.execute(query, (new_price, product_id))
        self.connection.commit()
        return self.cursor.rowcount
    
    def update_product_stock(self, product_id, quantity_change):
        """Update product stock"""
        query = """
            UPDATE products 
            SET stock_quantity = stock_quantity + %s 
            WHERE product_id = %s AND stock_quantity + %s >= 0
        """
        self.cursor.execute(query, (quantity_change, product_id, quantity_change))
        self.connection.commit()
        return self.cursor.rowcount
    
    def get_products(self, category=None, min_price=None, max_price=None):
        """Get products with filters"""
        conditions = []
        params = []
        
        if category:
            conditions.append("category = %s")
            params.append(category)
        if min_price:
            conditions.append("price >= %s")
            params.append(min_price)
        if max_price:
            conditions.append("price <= %s")
            params.append(max_price)
        
        query = "SELECT * FROM products"
        if conditions:
            query += " WHERE " + " AND ".join(conditions)
        
        self.cursor.execute(query, params)
        return self.cursor.fetchall()
    
    # ============================================================
    # ORDER OPERATIONS
    # ============================================================
    
    def create_order(self, customer_id, items):
        """Create a new order with items"""
        self.connection.start_transaction()
        
        try:
            # Calculate total
            total = sum(item['price'] * item['quantity'] for item in items)
            
            # Create order
            query = """
                INSERT INTO orders (customer_id, total_amount, status)
                VALUES (%s, %s, 'pending')
            """
            self.cursor.execute(query, (customer_id, total))
            order_id = self.cursor.lastrowid
            
            # Add order items
            query = """
                INSERT INTO order_items (order_id, product_id, quantity, unit_price, total_price)
                VALUES (%s, %s, %s, %s, %s)
            """
            item_data = []
            for item in items:
                item_data.append((
                    order_id,
                    item['product_id'],
                    item['quantity'],
                    item['price'],
                    item['price'] * item['quantity']
                ))
            
            self.cursor.executemany(query, item_data)
            
            # Update stock
            for item in items:
                self.cursor.execute("""
                    UPDATE products 
                    SET stock_quantity = stock_quantity - %s 
                    WHERE product_id = %s
                """, (item['quantity'], item['product_id']))
            
            self.connection.commit()
            return {"success": True, "order_id": order_id}
            
        except Exception as e:
            self.connection.rollback()
            return {"success": False, "error": str(e)}
    
    def update_order_status(self, order_id, status):
        """Update order status"""
        query = "UPDATE orders SET status = %s WHERE order_id = %s"
        self.cursor.execute(query, (status, order_id))
        self.connection.commit()
        return self.cursor.rowcount
    
    def get_orders_by_customer(self, customer_id):
        """Get all orders for a customer"""
        query = """
            SELECT o.order_id, o.order_date, o.total_amount, o.status,
                   GROUP_CONCAT(CONCAT(p.product_name, ' (', oi.quantity, ')')) as items
            FROM orders o
            JOIN order_items oi ON o.order_id = oi.order_id
            JOIN products p ON oi.product_id = p.product_id
            WHERE o.customer_id = %s
            GROUP BY o.order_id
            ORDER BY o.order_date DESC
        """
        self.cursor.execute(query, (customer_id,))
        return self.cursor.fetchall()
    
    # ============================================================
    # REVIEW OPERATIONS
    # ============================================================
    
    def add_review(self, customer_id, product_id, rating, comment):
        """Add a product review"""
        query = """
            INSERT INTO reviews (customer_id, product_id, rating, comment)
            VALUES (%s, %s, %s, %s)
        """
        self.cursor.execute(query, (customer_id, product_id, rating, comment))
        self.connection.commit()
        return self.cursor.lastrowid
    
    def get_product_reviews(self, product_id):
        """Get all reviews for a product"""
        query = """
            SELECT r.rating, r.comment, c.first_name, c.last_name
            FROM reviews r
            JOIN customers c ON r.customer_id = c.customer_id
            WHERE r.product_id = %s
            ORDER BY r.review_date DESC
        """
        self.cursor.execute(query, (product_id,))
        return self.cursor.fetchall()
    
    # ============================================================
    # ANALYTICS OPERATIONS
    # ============================================================
    
    def get_sales_summary(self):
        """Get sales summary by category"""
        query = """
            SELECT p.category,
                   COUNT(DISTINCT o.order_id) as order_count,
                   SUM(oi.quantity) as units_sold,
                   SUM(oi.quantity * oi.unit_price) as revenue
            FROM products p
            JOIN order_items oi ON p.product_id = oi.product_id
            JOIN orders o ON oi.order_id = o.order_id
            WHERE o.status = 'completed'
            GROUP BY p.category
            ORDER BY revenue DESC
        """
        self.cursor.execute(query)
        return self.cursor.fetchall()
    
    def get_customer_summary(self):
        """Get customer summary"""
        query = """
            SELECT c.customer_id, c.first_name, c.last_name,
                   COUNT(o.order_id) as order_count,
                   COALESCE(SUM(o.total_amount), 0) as total_spent
            FROM customers c
            LEFT JOIN orders o ON c.customer_id = o.customer_id
            GROUP BY c.customer_id
            ORDER BY total_spent DESC
        """
        self.cursor.execute(query)
        return self.cursor.fetchall()
    
    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": "ecommerce_db"
}

store = ECommerceData(db_config)

if store.connect():
    
    print("=" * 60)
    print("E-COMMERCE DATA OPERATIONS")
    print("=" * 60)
    
    # 1. Add a product
    print("\n1. ADDING PRODUCT")
    product_id = store.add_product("Wireless Mouse", "Electronics", 29.99, 100)
    print(f"Product added with ID: {product_id}")
    
    # 2. Update product
    print("\n2. UPDATING PRODUCT")
    rows = store.update_product_price(product_id, 24.99)
    print(f"Updated {rows} product(s)")
    
    # 3. Get products
    print("\n3. GETTING PRODUCTS")
    products = store.get_products(category="Electronics", max_price=50)
    print(f"Found {len(products)} electronics under $50:")
    for p in products:
        print(f"  {p[1]} - ${p[4]}")
    
    # 4. Create order
    print("\n4. CREATING ORDER")
    items = [
        {"product_id": product_id, "quantity": 2, "price": 24.99}
    ]
    result = store.create_order(1, items)
    print(f"Order result: {result}")
    
    # 5. Get customer orders
    print("\n5. CUSTOMER ORDERS")
    orders = store.get_orders_by_customer(1)
    for o in orders:
        print(f"  Order {o[0]} - ${o[2]} - {o[3]}")
        print(f"    Items: {o[4]}")
    
    # 6. Sales summary
    print("\n6. SALES SUMMARY")
    sales = store.get_sales_summary()
    for s in sales:
        print(f"  {s[0]}: {s[1]} orders, {s[2]} units, ${s[3]} revenue")
    
    store.close()

This example shows:

All DML operations in one class
Transaction management for complex operations
Error handling with rollback
Analytics queries for business insights
Customer and order management

Quick Check: Why use transactions for order creation? (Answer: To ensure all operations succeed or none do, preventing data inconsistency)

Best Practices

DML Best Practices

# ============================================================
# DML BEST PRACTICES
# ============================================================

print("1. ALWAYS USE WHERE CLAUSE")
print("   - In UPDATE and DELETE statements")
print("   - Prevents accidental mass updates/deletions")

print("\n2. USE PARAMETERIZED QUERIES")
print("   - Always use %s placeholders")
print("   - Protects against SQL injection")

print("\n3. USE TRANSACTIONS FOR RELATED OPERATIONS")
print("   - Group INSERT, UPDATE, DELETE together")
print("   - Commit or rollback as a unit")

print("\n4. CHECK ROW COUNT")
print("   - Use cursor.rowcount to verify operations")
print("   - Confirm expected number of rows affected")

print("\n5. USE SOFT DELETE WHEN POSSIBLE")
print("   - Add is_deleted column")
print("   - Update instead of delete for important data")

print("\n6. VALIDATE DATA BEFORE INSERT/UPDATE")
print("   - Check data types and constraints")
print("   - Prevent errors at database level")

print("\n7. USE BATCH OPERATIONS FOR MULTIPLE ROWS")
print("   - Use executemany() for multiple inserts")
print("   - Better performance")

print("\n8. LOG DML OPERATIONS")
print("   - Track who changed what")
print("   - Useful for auditing and debugging")

Summary of best practices:

Always use WHERE — prevent mass updates/deletions
Parameterized queries — prevent SQL injection
Transactions — group related operations
Check rowcount — verify operations worked
Soft delete — recoverable deletion
Validate data — catch errors early

Quick Check: What is the most important rule for UPDATE and DELETE? (Answer: Always include a WHERE clause)

Try It Yourself

Experiment with DML statements in the editor below.

Loading Pyodide... 0%

Python Code Editor

# Python MySQL DML - Practice

print("=" * 40)
print("DML STATEMENTS - PRACTICE")
print("=" * 40)

# Simulated E-Commerce Data
class ECommerceDB:
    products = [
        {"id": 1, "name": "iPhone 15 Pro", "category": "Electronics", "price": 999.99, "stock": 50},
        {"id": 2, "name": "MacBook Air", "category": "Electronics", "price": 1099.99, "stock": 20},
        {"id": 3, "name": "Nike Air Max", "category": "Footwear", "price": 149.99, "stock": 100}
    ]
    customers = [
        {"id": 1, "first_name": "Rahul", "last_name": "Sharma", "email": "rahul@email.com"},
        {"id": 2, "first_name": "Priya", "last_name": "Patel", "email": "priya@email.com"}
    ]
    orders = []
    order_id_counter = 1
    
    @classmethod
    def insert_product(cls, name, category, price, stock):
        """Simulate INSERT"""
        new_id = len(cls.products) + 1
        product = {"id": new_id, "name": name, "category": category, "price": price, "stock": stock}
        cls.products.append(product)
        print(f"Added product: {name} (ID: {new_id})")
        return new_id
    
    @classmethod
    def update_product_price(cls, product_id, new_price):
        """Simulate UPDATE"""
        for p in cls.products:
            if p["id"] == product_id:
                p["price"] = new_price
                print(f"Updated product {product_id} price to ${new_price}")
                return 1
        print(f"Product {product_id} not found")
        return 0
    
    @classmethod
    def delete_product(cls, product_id):
        """Simulate DELETE"""
        for p in cls.products:
            if p["id"] == product_id:
                cls.products.remove(p)
                print(f"Deleted product {product_id}")
                return 1
        print(f"Product {product_id} not found")
        return 0
    
    @classmethod
    def select_products(cls, category=None, max_price=None):
        """Simulate SELECT"""
        results = cls.products[:]
        if category:
            results = [p for p in results if p["category"] == category]
        if max_price:
            results = [p for p in results if p["price"] <= max_price]
        return results
    
    @classmethod
    def create_order(cls, customer_id, product_id, quantity):
        """Simulate order creation with transaction"""
        order = {
            "id": cls.order_id_counter,
            "customer_id": customer_id,
            "product_id": product_id,
            "quantity": quantity,
            "status": "pending"
        }
        cls.orders.append(order)
        cls.order_id_counter += 1
        
        # Update stock
        for p in cls.products:
            if p["id"] == product_id:
                p["stock"] -= quantity
                break
        
        print(f"Order {order['id']} created")
        return order["id"]

print("\n1. INSERT OPERATIONS")
print("-" * 40)

ECommerceDB.insert_product("Sony Headphones", "Electronics", 299.99, 45)
ECommerceDB.insert_product("Levi's Jeans", "Clothing", 69.99, 150)

print("\n2. SELECT OPERATIONS")
print("-" * 40)

all_products = ECommerceDB.select_products()
print("All products:")
for p in all_products:
    print(f"  {p['name']} - ${p['price']} (Stock: {p['stock']})")

electronics = ECommerceDB.select_products(category="Electronics")
print("\nElectronics products:")
for p in electronics:
    print(f"  {p['name']} - ${p['price']}")

print("\n3. UPDATE OPERATIONS")
print("-" * 40)

ECommerceDB.update_product_price(3, 129.99)
ECommerceDB.update_product_price(10, 99.99)

print("\n4. DELETE OPERATIONS")
print("-" * 40)

ECommerceDB.delete_product(2)
ECommerceDB.delete_product(10)

print("\n5. TRANSACTION - CREATE ORDER")
print("-" * 40)

order_id = ECommerceDB.create_order(1, 1, 2)
print(f"Order {order_id} created successfully")

print("\n6. FINAL PRODUCTS")
print("-" * 40)

products = ECommerceDB.select_products()
for p in products:
    print(f"  {p['name']} - ${p['price']} (Stock: {p['stock']})")

print("\n7. DML SUMMARY")
print("-" * 40)
print("""
┌─────────────────┬──────────────────────────────────────────────┐
│ DML Statement   │ Purpose                                     │
├─────────────────┼──────────────────────────────────────────────┤
│ INSERT          │ Add new data                               │
│ UPDATE          │ Modify existing data                       │
│ DELETE          │ Remove data                                │
│ SELECT          │ Retrieve data                              │
└─────────────────┴──────────────────────────────────────────────┘
""")

print("DML statements manipulate your E-Commerce data!")

========================================
DML STATEMENTS - PRACTICE
========================================

1. INSERT OPERATIONS
----------------------------------------
Added product: Sony Headphones (ID: 4)
Added product: Levi's Jeans (ID: 5)

2. SELECT OPERATIONS
----------------------------------------
All products:
iPhone 15 Pro - $999.99 (Stock: 50)
MacBook Air - $1099.99 (Stock: 20)
Nike Air Max - $149.99 (Stock: 100)
Sony Headphones - $299.99 (Stock: 45)
Levi's Jeans - $69.99 (Stock: 150)

Electronics products:
iPhone 15 Pro - $999.99
MacBook Air - $1099.99
Sony Headphones - $299.99

3. UPDATE OPERATIONS
----------------------------------------
Updated product 3 price to $129.99
Product 10 not found

4. DELETE OPERATIONS
----------------------------------------
Deleted product 2
Product 10 not found

5. TRANSACTION - CREATE ORDER
----------------------------------------
Order 1 created
Order 1 created successfully

6. FINAL PRODUCTS
----------------------------------------
iPhone 15 Pro - $999.99 (Stock: 48)
Nike Air Max - $129.99 (Stock: 100)
Sony Headphones - $299.99 (Stock: 45)
Levi's Jeans - $69.99 (Stock: 150)

7. DML SUMMARY
----------------------------------------
┌─────────────────┬──────────────────────────────────────────────┐
│ DML Statement │ Purpose │
├─────────────────┼──────────────────────────────────────────────┤
│ INSERT │ Add new data │
│ UPDATE │ Modify existing data │
│ DELETE │ Remove data │
│ SELECT │ Retrieve data │
└─────────────────┴──────────────────────────────────────────────┘

DML statements manipulate your E-Commerce data!

🏆

You've Got It!

You now understand DML statements in MySQL from Python. You can INSERT, UPDATE, DELETE, and SELECT data for your E-Commerce platform.

Quick Quiz

Test what you've learned:

1. What does INSERT do?

A) Modifies existing data B) Adds new data C) Removes data D) Retrieves data

2. What must you use with UPDATE and DELETE?

A) ORDER BY B) WHERE clause C) GROUP BY D) LIMIT

3. How do you get the ID of a newly inserted row?

A) cursor.get_id() B) cursor.lastrowid C) cursor.row_id D) cursor.insert_id

4. What is a soft delete?

A) Deleting data quickly B) Marking data as deleted instead of removing C) Deleting only one row D) Using TRUNCATE

5. Why use transactions for related operations?

A) To make code longer B) To ensure all succeed or none do C) To make it run faster D) To use less memory

Frequently Asked Questions

What is the difference between DML and DDL?

DML manipulates data (INSERT, UPDATE, DELETE, SELECT). DDL defines structure (CREATE, ALTER, DROP). DML works with data; DDL works with structure.

Why should I use parameterized queries?

Parameterized queries prevent SQL injection attacks. They separate SQL code from data, making it impossible for malicious input to change the query structure.

What is a common interview question about DML?

Common questions include: "Explain the difference between DML and DDL," "What is the difference between DELETE and TRUNCATE?" and "How do you insert multiple rows in Python?"

What is the difference between DELETE and TRUNCATE?

DELETE removes rows one by one (can use WHERE, can be rolled back). TRUNCATE removes all rows at once (faster, cannot use WHERE, resets AUTO_INCREMENT).

Will this conflict with future pure MySQL tutorials?

No. This tutorial focuses on executing DML from Python with mysql-connector. Future MySQL tutorials will focus on SQL syntax and optimization with different titles and URLs.

Where to Go From Here

Now that you understand DML statements, check out these related topics:

Previous: DDL Statements Next: Subqueries →

Python

Python: DML Statements in MySQL

What are DML Statements?

INSERT

UPDATE

DELETE

SELECT

DML in E-Commerce

INSERT - Adding Data

Inserting E-Commerce Data from Python

UPDATE - Modifying Data

Updating E-Commerce Data from Python

DELETE - Removing Data

Deleting E-Commerce Data from Python

SELECT - Reading Data

Querying E-Commerce Data from Python

E-Commerce Python Examples

Complete E-Commerce Data Operations

Best Practices

DML Best Practices

Try It Yourself

You've Got It!

Quick Quiz

Frequently Asked Questions

What is the difference between DML and DDL?

Why should I use parameterized queries?

What is a common interview question about DML?

What is the difference between DELETE and TRUNCATE?

Will this conflict with future pure MySQL tutorials?

Where to Go From Here

Subqueries

JOIN Operations

Aggregation

Python

Python: DML Statements in MySQL

What are DML Statements?

INSERT

UPDATE

DELETE

SELECT

DML in E-Commerce

INSERT - Adding Data

Inserting E-Commerce Data from Python

UPDATE - Modifying Data

Updating E-Commerce Data from Python

DELETE - Removing Data

Deleting E-Commerce Data from Python

SELECT - Reading Data

Querying E-Commerce Data from Python

E-Commerce Python Examples

Complete E-Commerce Data Operations

Best Practices

DML Best Practices

Try It Yourself

You've Got It!

Quick Quiz

Frequently Asked Questions

What is the difference between DML and DDL? ▼

Why should I use parameterized queries? ▼

What is a common interview question about DML? ▼

What is the difference between DELETE and TRUNCATE? ▼

Will this conflict with future pure MySQL tutorials? ▼

Where to Go From Here

Subqueries

JOIN Operations

Aggregation

What is the difference between DML and DDL?

Why should I use parameterized queries?

What is a common interview question about DML?

What is the difference between DELETE and TRUNCATE?

Will this conflict with future pure MySQL tutorials?