Python Project – Sales Data Analytics

Program 1

import mysql.connector
from tabulate import tabulate

# Database connection
def connect_db():
    return mysql.connector.connect(
        host="localhost",
        user="root",
        password="root",
        database="sales_db"
    )

# 1. View Total Revenue
def view_total_revenue(conn):
    cursor = conn.cursor()
    query = """
        SELECT SUM(p.price * s.quantity) AS total_revenue
        FROM sales s
        JOIN products p ON s.product_id = p.product_id
    """
    cursor.execute(query)
    result = cursor.fetchone()
    print("\nTotal Revenue: ₹", result[0] if result[0] else 0)

# 2. View Top Selling Products
def top_selling_products(conn):
    cursor = conn.cursor()
    query = """
        SELECT p.product_name, SUM(s.quantity) AS total_sold
        FROM sales s
        JOIN products p ON s.product_id = p.product_id
        GROUP BY p.product_name
        ORDER BY total_sold DESC
        LIMIT 5
    """
    cursor.execute(query)
    rows = cursor.fetchall()
    print("\nTop Selling Products:")
    print(tabulate(rows, headers=["Product Name", "Total Sold"], tablefmt="pretty"))

# 3. View Monthly Sales Report
def monthly_sales_report(conn):
    cursor = conn.cursor()
    query = """
        SELECT DATE_FORMAT(sale_date, '%Y-%m') AS month, SUM(p.price * s.quantity) AS revenue
        FROM sales s
        JOIN products p ON s.product_id = p.product_id
        GROUP BY month
        ORDER BY month
    """
    cursor.execute(query)
    rows = cursor.fetchall()
    print("\nMonthly Sales Report:")
    print(tabulate(rows, headers=["Month", "Revenue"], tablefmt="pretty"))

# 4. City-wise Customer Count
def city_wise_customer_count(conn):
    cursor = conn.cursor()
    query = """
        SELECT city, COUNT(*) AS customer_count
        FROM customers
        GROUP BY city
        ORDER BY customer_count DESC
    """
    cursor.execute(query)
    rows = cursor.fetchall()
    print("\nCity-wise Customer Count:")
    print(tabulate(rows, headers=["City", "Customer Count"], tablefmt="pretty"))

# 5. Add New Customer
def add_new_customer(conn):
    cursor = conn.cursor()
    print("\nEnter Customer Details:")
    customer_id = int(input("Customer ID: "))
    name = input("Name: ")
    email = input("Email: ")
    city = input("City: ")
    registration_date = input("Registration Date (YYYY-MM-DD): ")

    query = """
        INSERT INTO customers (customer_id, name, email, city, registration_date)
        VALUES (%s, %s, %s, %s, %s)
    """
    cursor.execute(query, (customer_id, name, email, city, registration_date))
    conn.commit()
    print("Customer added successfully!")

# 6. Add New Product
def add_new_product(conn):
    cursor = conn.cursor()
    print("\nEnter Product Details:")
    product_id = int(input("Product ID: "))
    product_name = input("Product Name: ")
    category = input("Category: ")
    price = float(input("Price: "))

    query = """
        INSERT INTO products (product_id, product_name, category, price)
        VALUES (%s, %s, %s, %s)
    """
    cursor.execute(query, (product_id, product_name, category, price))
    conn.commit()
    print("Product added successfully!")

# 7. Add New Sale
def add_new_sale(conn):
    cursor = conn.cursor()
    print("\nEnter Sale Details:")
    sale_id = int(input("Sale ID: "))
    customer_id = int(input("Customer ID: "))
    product_id = int(input("Product ID: "))
    quantity = int(input("Quantity: "))
    sale_date = input("Sale Date (YYYY-MM-DD): ")

    query = """
        INSERT INTO sales (sale_id, customer_id, product_id, quantity, sale_date)
        VALUES (%s, %s, %s, %s, %s)
    """
    cursor.execute(query, (sale_id, customer_id, product_id, quantity, sale_date))
    conn.commit()
    print("Sale record added successfully!")

# Main menu
def main():
    conn = connect_db()
    while True:
        print("\n------ Sales Data Analytics Menu ------")
        print("1. View Total Revenue")
        print("2. View Top Selling Products")
        print("3. View Monthly Sales Report")
        print("4. View City-wise Customer Count")
        print("5. Add New Customer")
        print("6. Add New Product")
        print("7. Add New Sale")
        print("8. Exit")
        print("----------------------------------------------")
        choice = input("Enter your choice (1-8): ")

        if choice == '1':
            view_total_revenue(conn)
        elif choice == '2':
            top_selling_products(conn)
        elif choice == '3':
            monthly_sales_report(conn)
        elif choice == '4':
            city_wise_customer_count(conn)
        elif choice == '5':
            add_new_customer(conn)
        elif choice == '6':
            add_new_product(conn)
        elif choice == '7':
            add_new_sale(conn)
        elif choice == '8':
            print("********Exiting the program. Thank you!*******")
            break
        else:
            print("xxxxx  Invalid choice. Please try again. xxxxx")

    conn.close()

# Main Calling

main()