Python: MySQL Operators in E-Commerce

# ============================================================
# OPERATORS IN E-COMMERCE
# ============================================================

# 1. Comparison: Find expensive products
SELECT * FROM products WHERE price > 500;

# 2. Logical: Find products in Electronics under $1000
SELECT * FROM products 
WHERE category = 'Electronics' AND price < 1000;

# 3. Arithmetic: Calculate discounted price
SELECT product_name, price, price * 0.9 AS discounted_price
FROM products;

# 4. Special: Search products containing 'Phone'
SELECT * FROM products WHERE product_name LIKE '%Phone%';

# 5. Special: Find products in specific price range
SELECT * FROM products WHERE price BETWEEN 100 AND 500;

# 6. Special: Find customers from specific cities
SELECT * FROM customers WHERE city IN ('Mumbai', 'Delhi');

# ============================================================
# COMPARISON OPERATORS
# ============================================================

# =  equal to
# !=  not equal to
# >  greater than
# <  less than
# >=  greater than or equal to
# <=  less than or equal to

# ============================================================
# E-COMMERCE EXAMPLES
# ============================================================

import mysql.connector

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

cursor = connection.cursor()

# 1. Equal to - Find specific product
cursor.execute("SELECT * FROM products WHERE category = 'Electronics'")
products = cursor.fetchall()
print("Electronics Products:")
for p in products:
    print(f"  {p[1]} - ${p[4]}")

# 2. Not equal to - Find products not in Electronics
cursor.execute("SELECT * FROM products WHERE category != 'Electronics'")
products = cursor.fetchall()
print("\nNon-Electronics Products:")
for p in products:
    print(f"  {p[1]} - ${p[4]}")

# 3. Greater than - Find expensive products
cursor.execute("SELECT * FROM products WHERE price > 500")
products = cursor.fetchall()
print("\nProducts over $500:")
for p in products:
    print(f"  {p[1]} - ${p[4]}")

# 4. Less than - Find affordable products
cursor.execute("SELECT * FROM products WHERE price < 100")
products = cursor.fetchall()
print("\nProducts under $100:")
for p in products:
    print(f"  {p[1]} - ${p[4]}")

# 5. Greater than or equal - Products $100 and above
cursor.execute("SELECT * FROM products WHERE price >= 100")
products = cursor.fetchall()
print("\nProducts $100 and above:")
for p in products:
    print(f"  {p[1]} - ${p[4]}")

# 6. Less than or equal - Products $200 and below
cursor.execute("SELECT * FROM products WHERE price <= 200")
products = cursor.fetchall()
print("\nProducts $200 and below:")
for p in products:
    print(f"  {p[1]} - ${p[4]}")

cursor.close()
connection.close()

# ============================================================
# LOGICAL OPERATORS
# ============================================================

# AND - both conditions must be true
# OR - at least one condition must be true
# NOT - reverses the condition

# ============================================================
# AND OPERATOR - Multiple filters
# ============================================================

# Electronics under $500
cursor.execute(
    "SELECT * FROM products WHERE category = 'Electronics' AND price < 500"
)
products = cursor.fetchall()
print("Electronics under $500:")
for p in products:
    print(f"  {p[1]} - ${p[4]}")

# Customers from Mumbai with orders
cursor.execute("""
    SELECT c.first_name, c.last_name, c.city, COUNT(o.order_id) as order_count
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    WHERE c.city = 'Mumbai' AND o.status = 'completed'
    GROUP BY c.customer_id
""")
results = cursor.fetchall()
print("\nMumbai customers with completed orders:")
for r in results:
    print(f"  {r[0]} {r[1]} - {r[2]} orders")

# ============================================================
# OR OPERATOR - Multiple alternatives
# ============================================================

# Products from Electronics OR Footwear
cursor.execute(
    "SELECT * FROM products WHERE category = 'Electronics' OR category = 'Footwear'"
)
products = cursor.fetchall()
print("\nElectronics or Footwear products:")
for p in products:
    print(f"  {p[1]} - {p[2]}")

# Customers from Mumbai OR Delhi
cursor.execute(
    "SELECT * FROM customers WHERE city = 'Mumbai' OR city = 'Delhi'"
)
customers = cursor.fetchall()
print("\nCustomers from Mumbai or Delhi:")
for c in customers:
    print(f"  {c[1]} {c[2]} - {c[4]}")

# ============================================================
# NOT OPERATOR - Excluding conditions
# ============================================================

# Products not in Electronics
cursor.execute("SELECT * FROM products WHERE NOT category = 'Electronics'")
products = cursor.fetchall()
print("\nProducts not in Electronics:")
for p in products:
    print(f"  {p[1]} - {p[2]}")

# Orders not completed
cursor.execute("SELECT * FROM orders WHERE NOT status = 'completed'")
orders = cursor.fetchall()
print("\nOrders not completed:")
for o in orders:
    print(f"  Order {o[0]} - Status: {o[5]}")

# ============================================================
# COMBINING AND + OR
# ============================================================

# Electronics OR Footwear products under $200
cursor.execute("""
    SELECT * FROM products 
    WHERE (category = 'Electronics' OR category = 'Footwear') 
    AND price < 200
""")
products = cursor.fetchall()
print("\nElectronics or Footwear under $200:")
for p in products:
    print(f"  {p[1]} - ${p[4]}")

# ============================================================
# ARITHMETIC OPERATORS
# ============================================================

# +  addition
# -  subtraction
# *  multiplication
# /  division
# %  modulo (remainder)

# ============================================================
# E-COMMERCE EXAMPLES
# ============================================================

# 1. Calculate discounted price (10% off)
cursor.execute("""
    SELECT product_name, price, price * 0.9 AS discounted_price
    FROM products
""")
products = cursor.fetchall()
print("Products with 10% discount:")
for p in products:
    print(f"  {p[0]} - Original: ${p[1]}, Discounted: ${p[2]}")

# 2. Calculate total revenue per product
cursor.execute("""
    SELECT p.product_name, 
           SUM(oi.quantity * oi.unit_price) AS total_revenue
    FROM products p
    JOIN order_items oi ON p.product_id = oi.product_id
    GROUP BY p.product_id
""")
results = cursor.fetchall()
print("\nRevenue per product:")
for r in results:
    print(f"  {r[0]} - ${r[1]}")

# 3. Apply tax calculation (18% GST)
cursor.execute("""
    SELECT product_name, price, 
           price * 0.18 AS tax,
           price + (price * 0.18) AS price_with_tax
    FROM products
""")
products = cursor.fetchall()
print("\nProduct prices with 18% tax:")
for p in products:
    print(f"  {p[0]} - Base: ${p[1]}, Tax: ${p[2]}, Total: ${p[3]}")

# 4. Calculate average order value
cursor.execute("""
    SELECT AVG(total_amount) AS avg_order_value,
           SUM(total_amount) AS total_revenue,
           COUNT(order_id) AS total_orders
    FROM orders
    WHERE status = 'completed'
""")
result = cursor.fetchone()
print(f"\nOrder Statistics:")
print(f"  Average order value: ${result[0]}")
print(f"  Total revenue: ${result[1]}")
print(f"  Total orders: {result[2]}")

# 5. Update stock after sale (simulate)
cursor.execute("""
    SELECT product_name, stock_quantity, 
           stock_quantity - 5 AS updated_stock
    FROM products
""")
products = cursor.fetchall()
print("\nStock after selling 5 units:")
for p in products:
    print(f"  {p[0]} - Current: {p[1]}, After sale: {p[2]}")

# ============================================================
# SPECIAL OPERATORS
# ============================================================

# LIKE - pattern matching
# IN - matches any value in a list
# BETWEEN - checks if value is in a range
# IS NULL - checks for null values

# ============================================================
# LIKE OPERATOR - Pattern matching
# ============================================================

# Products starting with 'i'
cursor.execute("SELECT * FROM products WHERE product_name LIKE 'i%'")
products = cursor.fetchall()
print("Products starting with 'i':")
for p in products:
    print(f"  {p[1]}")

# Products containing 'Phone'
cursor.execute("SELECT * FROM products WHERE product_name LIKE '%Phone%'")
products = cursor.fetchall()
print("\nProducts containing 'Phone':")
for p in products:
    print(f"  {p[1]}")

# Customers with names ending in 'a'
cursor.execute("SELECT * FROM customers WHERE first_name LIKE '%a'")
customers = cursor.fetchall()
print("\nCustomers with names ending in 'a':")
for c in customers:
    print(f"  {c[1]} {c[2]}")

# ============================================================
# IN OPERATOR - Multiple values
# ============================================================

# Products in specific categories
cursor.execute(
    "SELECT * FROM products WHERE category IN ('Electronics', 'Footwear')"
)
products = cursor.fetchall()
print("\nProducts in Electronics or Footwear:")
for p in products:
    print(f"  {p[1]} - {p[2]}")

# Customers from specific cities
cursor.execute(
    "SELECT * FROM customers WHERE city IN ('Mumbai', 'Delhi', 'Bangalore')"
)
customers = cursor.fetchall()
print("\nCustomers from Mumbai, Delhi, or Bangalore:")
for c in customers:
    print(f"  {c[1]} {c[2]} - {c[4]}")

# ============================================================
# BETWEEN OPERATOR - Range
# ============================================================

# Products in price range
cursor.execute("SELECT * FROM products WHERE price BETWEEN 100 AND 500")
products = cursor.fetchall()
print("\nProducts between $100 and $500:")
for p in products:
    print(f"  {p[1]} - ${p[4]}")

# Orders with total between $100 and $500
cursor.execute("SELECT * FROM orders WHERE total_amount BETWEEN 100 AND 500")
orders = cursor.fetchall()
print("\nOrders between $100 and $500:")
for o in orders:
    print(f"  Order {o[0]} - ${o[3]}")

# ============================================================
# IS NULL - Check for null values
# ============================================================

# Products with no description
cursor.execute("SELECT * FROM products WHERE description IS NULL")
products = cursor.fetchall()
print("\nProducts without description:")
for p in products:
    print(f"  {p[1]}")

# Orders without tracking number
cursor.execute("SELECT * FROM orders WHERE tracking_number IS NULL")
orders = cursor.fetchall()
print("\nOrders without tracking number:")
for o in orders:
    print(f"  Order {o[0]}")

# ============================================================
# E-COMMERCE OPERATORS IN PYTHON
# ============================================================

import mysql.connector

class ECommerceOperators:
    """E-Commerce operations using MySQL operators"""
    
    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
    
    # ============================================================
    # COMPARISON OPERATORS
    # ============================================================
    
    def get_products_by_price(self, operator, price):
        """Get products using comparison operators"""
        query = f"SELECT * FROM products WHERE price {operator} %s"
        self.cursor.execute(query, (price,))
        return self.cursor.fetchall()
    
    def get_products_by_category(self, category):
        """Get products in a category"""
        query = "SELECT * FROM products WHERE category = %s"
        self.cursor.execute(query, (category,))
        return self.cursor.fetchall()
    
    # ============================================================
    # LOGICAL OPERATORS
    # ============================================================
    
    def get_products_by_category_and_price(self, category, min_price, max_price):
        """Get products with AND operator"""
        query = """
            SELECT * FROM products 
            WHERE category = %s AND price BETWEEN %s AND %s
        """
        self.cursor.execute(query, (category, min_price, max_price))
        return self.cursor.fetchall()
    
    def get_products_by_categories(self, categories):
        """Get products with OR/IN operator"""
        placeholders = ', '.join(['%s'] * len(categories))
        query = f"SELECT * FROM products WHERE category IN ({placeholders})"
        self.cursor.execute(query, categories)
        return self.cursor.fetchall()
    
    # ============================================================
    # ARITHMETIC OPERATORS
    # ============================================================
    
    def get_discounted_products(self, discount_percent):
        """Get products with calculated discount"""
        query = f"""
            SELECT product_name, price, 
                   price * {discount_percent/100} AS discount,
                   price - (price * {discount_percent/100}) AS discounted_price
            FROM products
        """
        self.cursor.execute(query)
        return self.cursor.fetchall()
    
    def get_category_revenue(self):
        """Get revenue by category with SUM"""
        query = """
            SELECT p.category, 
                   SUM(oi.quantity * oi.unit_price) AS total_revenue
            FROM products p
            JOIN order_items oi ON p.product_id = oi.product_id
            GROUP BY p.category
            ORDER BY total_revenue DESC
        """
        self.cursor.execute(query)
        return self.cursor.fetchall()
    
    # ============================================================
    # SPECIAL OPERATORS
    # ============================================================
    
    def search_products(self, search_term):
        """Search products using LIKE"""
        query = "SELECT * FROM products WHERE product_name LIKE %s"
        self.cursor.execute(query, (f"%{search_term}%",))
        return self.cursor.fetchall()
    
    def get_products_by_price_range(self, min_price, max_price):
        """Get products using BETWEEN"""
        query = "SELECT * FROM products WHERE price BETWEEN %s AND %s"
        self.cursor.execute(query, (min_price, max_price))
        return self.cursor.fetchall()
    
    def get_products_in_stock(self):
        """Get products in stock (not null)"""
        query = "SELECT * FROM products WHERE stock_quantity IS NOT NULL"
        self.cursor.execute(query)
        return self.cursor.fetchall()
    
    # ============================================================
    # COMBINED OPERATORS
    # ============================================================
    
    def get_sales_analytics(self):
        """Sales analytics using multiple operators"""
        query = """
            SELECT 
                p.category,
                COUNT(DISTINCT o.order_id) AS order_count,
                SUM(oi.quantity) AS total_units,
                SUM(oi.quantity * oi.unit_price) AS total_revenue,
                AVG(oi.unit_price) AS avg_price,
                MAX(oi.unit_price) AS max_price,
                MIN(oi.unit_price) AS min_price
            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
            HAVING total_revenue > 100
            ORDER BY total_revenue DESC
        """
        self.cursor.execute(query)
        return self.cursor.fetchall()
    
    def display_results(self, results, title="Results"):
        """Display results"""
        if not results:
            print(f"\n{title}: No results")
            return
        print(f"\n{title}: {len(results)} rows")
        print("-" * 60)
        for row in results:
            print(f"  {row}")
    
    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 = ECommerceOperators(db_config)

if store.connect():
    
    print("=" * 60)
    print("E-COMMERCE OPERATORS DEMONSTRATION")
    print("=" * 60)
    
    # 1. Comparison operators
    print("\n1. COMPARISON OPERATORS")
    products = store.get_products_by_price(">", 500)
    print(f"Products over $500: {len(products)}")
    
    # 2. Logical operators
    print("\n2. LOGICAL OPERATORS")
    products = store.get_products_by_category_and_price("Electronics", 100, 500)
    print(f"Electronics between $100-$500: {len(products)}")
    
    # 3. Arithmetic operators
    print("\n3. ARITHMETIC OPERATORS")
    discounted = store.get_discounted_products(20)
    print(f"Products with 20% discount: {len(discounted)}")
    
    # 4. Special operators
    print("\n4. SPECIAL OPERATORS")
    products = store.search_products("Phone")
    print(f"Products containing 'Phone': {len(products)}")
    
    # 5. Combined operators
    print("\n5. SALES ANALYTICS")
    analytics = store.get_sales_analytics()
    print("Sales by category:")
    for row in analytics:
        print(f"  {row[0]}: {row[1]} orders, {row[2]} units, ${row[3]} revenue")
    
    store.close()

# ============================================================
# BEST PRACTICES FOR OPERATORS
# ============================================================

print("1. USE PARAMETERIZED QUERIES")
print("   - Always use %s placeholders")
print("   - Even with operators, protect against injection")

print("\n2. USE INDEXES ON OPERATOR COLUMNS")
print("   - Columns used with =, >, <, LIKE should be indexed")
print("   - BETWEEN and IN benefit from indexes")

print("\n3. AVOID FUNCTIONS ON INDEXED COLUMNS")
print("   - WHERE YEAR(date) = 2024 is slow")
print("   - Use date BETWEEN '2024-01-01' AND '2024-12-31'")

print("\n4. USE LIKE CAREFULLY")
print("   - 'Phone%' is faster than '%Phone%'")
print("   - Leading % prevents index usage")

print("\n5. USE IN OVER MULTIPLE OR CONDITIONS")
print("   - IN is cleaner and often faster")
print("   - WHERE city IN ('Mumbai', 'Delhi')")

print("\n6. USE BETWEEN OVER >= AND <=")
print("   - BETWEEN is cleaner and more readable")
print("   - WHERE price BETWEEN 100 AND 500")

print("\n7. USE EXPLAIN TO ANALYZE")
print("   - Check how operators affect performance")
print("   - Identify slow queries")

print("\n8. BE CONSISTENT")
print("   - Use same operator style throughout")
print("   - Maintain readability")