INTERFACING PYTHON WITH MYSQL - GROUP BY OPERATIONS

# Python program to demonstrate GROUP BY operations with MySQL

import mysql.connector

def connect_database():
    """Establish connection to MySQL database"""
    try:
        connection = mysql.connector.connect(
            host='localhost',
            database='sales_db',
            user='root',
            password='password'
        )
        return connection
    except mysql.connector.Error as error:
        print(f"Error connecting to MySQL: {error}")
        return None

def create_sample_data():
    """Create sample sales data"""
    connection = connect_database()
    if connection is None:
        return
    
    cursor = connection.cursor()
    
    try:
        # Create sales table
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS sales (
                sale_id INT PRIMARY KEY,
                product VARCHAR(50),
                category VARCHAR(30),
                quantity INT,
                price DECIMAL(10,2),
                sale_date DATE
            )
        """)
        
        # Insert sample data
        sample_data = [
            (1, 'Laptop', 'Electronics', 2, 50000.00, '2024-01-15'),
            (2, 'Mouse', 'Electronics', 5, 1500.00, '2024-01-16'),
            (3, 'Book', 'Education', 10, 500.00, '2024-01-17'),
            (4, 'Pen', 'Education', 20, 50.00, '2024-01-18'),
            (5, 'Phone', 'Electronics', 3, 30000.00, '2024-01-19'),
            (6, 'Notebook', 'Education', 15, 100.00, '2024-01-20'),
            (7, 'Tablet', 'Electronics', 1, 25000.00, '2024-01-21'),
            (8, 'Pencil', 'Education', 25, 20.00, '2024-01-22')
        ]
        
        cursor.executemany(
            "INSERT IGNORE INTO sales VALUES (%s, %s, %s, %s, %s, %s)",
            sample_data
        )
        
        connection.commit()
        print("Sample sales data created!")
        
    except mysql.connector.Error as error:
        print(f"Error creating sample data: {error}")
    
    finally:
        cursor.close()
        connection.close()

def display_all_data():
    """Display all sales data"""
    connection = connect_database()
    if connection is None:
        return
    
    cursor = connection.cursor()
    
    try:
        cursor.execute("SELECT * FROM sales")
        records = cursor.fetchall()
        
        print("\n--- All Sales Data ---")
        print("ID\tProduct\t\tCategory\tQty\tPrice\t\tDate")
        print("-" * 70)
        for record in records:
            print(f"{record[0]}\t{record[1]:<12}\t{record[2]:<12}\t{record[3]}\t{record[4]}\t{record[5]}")
            
    except mysql.connector.Error as error:
        print(f"Error displaying data: {error}")
    
    finally:
        cursor.close()
        connection.close()

def group_by_operations():
    """Demonstrate various GROUP BY operations"""
    connection = connect_database()
    if connection is None:
        return
    
    cursor = connection.cursor()
    
    try:
        # 1. Count products by category
        print("\n1. COUNT of products by category:")
        cursor.execute("SELECT category, COUNT(*) as product_count FROM sales GROUP BY category")
        results = cursor.fetchall()
        print("Category\t\tCount")
        print("-" * 25)
        for row in results:
            print(f"{row[0]:<15}\t{row[1]}")
        
        # 2. Total quantity sold by category
        print("\n2. SUM of quantity by category:")
        cursor.execute("SELECT category, SUM(quantity) as total_quantity FROM sales GROUP BY category")
        results = cursor.fetchall()
        print("Category\t\tTotal Qty")
        print("-" * 25)
        for row in results:
            print(f"{row[0]:<15}\t{row[1]}")
        
        # 3. Average price by category
        print("\n3. AVERAGE price by category:")
        cursor.execute("SELECT category, AVG(price) as avg_price FROM sales GROUP BY category")
        results = cursor.fetchall()
        print("Category\t\tAvg Price")
        print("-" * 30)
        for row in results:
            print(f"{row[0]:<15}\t{row[1]:.2f}")
        
        # 4. Maximum and minimum price by category
        print("\n4. MAX and MIN price by category:")
        cursor.execute("SELECT category, MAX(price) as max_price, MIN(price) as min_price FROM sales GROUP BY category")
        results = cursor.fetchall()
        print("Category\t\tMax Price\tMin Price")
        print("-" * 40)
        for row in results:
            print(f"{row[0]:<15}\t{row[1]}\t\t{row[2]}")
        
        # 5. Total sales value by category
        print("\n5. Total sales value by category:")
        cursor.execute("SELECT category, SUM(quantity * price) as total_sales FROM sales GROUP BY category")
        results = cursor.fetchall()
        print("Category\t\tTotal Sales")
        print("-" * 30)
        for row in results:
            print(f"{row[0]:<15}\t{row[1]:.2f}")
        
        # 6. GROUP BY with HAVING clause
        print("\n6. Categories with total sales &gt; 50000:")
        cursor.execute("""
            SELECT category, SUM(quantity * price) as total_sales 
            FROM sales 
            GROUP BY category 
            HAVING SUM(quantity * price) &gt; 50000
        """)
        results = cursor.fetchall()
        print("Category\t\tTotal Sales")
        print("-" * 30)
        for row in results:
            print(f"{row[0]:<15}\t{row[1]:.2f}")
        
        # 7. Multiple column GROUP BY
        print("\n7. Sales summary by category and date:")
        cursor.execute("""
            SELECT category, sale_date, COUNT(*) as items, SUM(quantity * price) as daily_sales
            FROM sales 
            GROUP BY category, sale_date
            ORDER BY category, sale_date
        """)
        results = cursor.fetchall()
        print("Category\t\tDate\t\tItems\tDaily Sales")
        print("-" * 50)
        for row in results:
            print(f"{row[0]:<15}\t{row[1]}\t{row[2]}\t{row[3]:.2f}")
            
    except mysql.connector.Error as error:
        print(f"Error in GROUP BY operations: {error}")
    
    finally:
        cursor.close()
        connection.close()

// Main program
if __name__ == "__main__":
    print("=== MySQL Database - GROUP BY Operations ===")
    
    // Create sample data
    create_sample_data()
    
    // Display all data
    display_all_data()
    
    // Demonstrate GROUP BY operations
    group_by_operations()
    
    print("\n=== GROUP BY Operations completed ===")
}