python_diagramm_sql.py

import mysql.connector
import numpy as np
from sqlalchemy import create_engine
import os
import pandas as pd
import matplotlib.pyplot as plt





# Create SQLAlchemy engine for connecting to MySQL
engine = create_engine("mysql+mysqlconnector://root:password@localhost/tgbigdad")




# Function to plot average salary by gender
def plot_avg_salary(conn):
    query = "SELECT sex, AVG(salary) AS AvgSalary FROM employees GROUP BY sex"
    df = pd.read_sql_query(query, conn)
    plt.figure()
    plt.bar(df["sex"], df["AvgSalary"], color=["blue","pink"])
    plt.title("Average salary by gender")
    plt.ylabel("Salary")
    plt.savefig("Graphs/avg_salary_per_sex.png", dpi=300, bbox_inches="tight")
    plt.show()






# Function to plot the distribution of employees by gender
def plot_gender_distribution(conn):
    query = "SELECT sex, COUNT(*) AS Count FROM employees GROUP BY sex"
    df = pd.read_sql_query(query, conn)
    print(df)  

    plt.figure()
    plt.pie(df["Count"], labels=df["sex"], autopct='%1.1f%%', colors=["blue", "pink"])
    plt.title("Employee Distribution by Gender")

    if not os.path.exists("Graphs"):
        os.makedirs("Graphs")
    plt.savefig("Graphs/employee_gender_distribution2.png", dpi=300, bbox_inches="tight")
    plt.show()







# Function to plot average salary for each job position
def plot_avg_salary_per_job(engine):
    query = "SELECT position, AVG(salary) AS AvgSalary FROM employees GROUP BY position"
    df = pd.read_sql_query(query, engine)
    plt.figure(figsize=(14,8))
    plt.bar(df["position"], df["AvgSalary"], color="purple")
    plt.xticks(rotation=45, ha="right")
    plt.title("Average salary by job position")
    plt.ylabel("Salary")
    plt.subplots_adjust(bottom=0.2) 
    if not os.path.exists("Graphs"):
        os.makedirs("Graphs")
    plt.savefig("Graphs/avg_salary_per_job.png", dpi=300, bbox_inches="tight")
    plt.show()






# Function to plot total revenue per product
def plot_product_sales(engine):
    query = """
    SELECT p.name_prd, SUM(w.total_sales) AS TotalSalesSum
    FROM Products p
    JOIN works_with w ON p.product_id = w.product_id
    GROUP BY p.name_prd
    ORDER BY TotalSalesSum DESC
    """
    df = pd.read_sql_query(query, engine)

    plt.figure(figsize=(14, 8))
    plt.bar(df["name_prd"], df["TotalSalesSum"], color="teal")
    plt.title("Total Revenue per Product", fontsize=16)
    plt.xlabel("Product", fontsize=14)
    plt.ylabel("Total Revenue (Euro €)", fontsize=14)
    plt.xticks(rotation=45, ha="right")
    plt.ylim(0, df["TotalSalesSum"].max() * 1.1)
    plt.yticks(np.arange(0, df["TotalSalesSum"].max() * 1.1, 1000))
    plt.tight_layout()
    if not os.path.exists("Graphs"):
        os.makedirs("Graphs")
    plt.savefig("Graphs/product_sales.png", dpi=300, bbox_inches="tight")
    plt.show()





# Function to plot monthly sales trends
def plot_monthly_sales(engine):
    query = """
    SELECT 
        YEAR(w.sale_date) AS sale_year, 
        MONTH(w.sale_date) AS sale_month,
        SUM(w.total_sales) AS TotalSalesSum
    FROM works_with w
    GROUP BY sale_year, sale_month
    ORDER BY sale_year, sale_month;
    """
    df = pd.read_sql_query(query, engine)

    df["month_year"] = df["sale_month"].astype(str).str.zfill(2) + "/" + df["sale_year"].astype(str)

    plt.figure(figsize=(14, 7))
    plt.plot(df["month_year"], df["TotalSalesSum"], marker="o", color="blue", linewidth=2)
    plt.title("Total Sales per Month", fontsize=16)
    plt.xlabel("Month / Year", fontsize=14)
    plt.ylabel("Total Revenue (€)", fontsize=14)
    plt.xticks(rotation=45, ha="right")
    plt.grid(True, linestyle="--", alpha=0.7)

    if not os.path.exists("Graphs"):
        os.makedirs("Graphs")

    plt.tight_layout()
    plt.savefig("Graphs/monthly_sales_line.png", dpi=300, bbox_inches="tight")
    plt.show()




# Function to compare the highest and lowest salaries in the company
def plot_salary_extremes(engine):
    query = """
   SELECT 
    
    (SELECT CONCAT(first_name, ' ', last_name) FROM employees ORDER BY salary DESC LIMIT 1) AS TopPaidEmployee,
    (SELECT salary FROM employees ORDER BY salary DESC LIMIT 1) AS TopSalary,
    (SELECT CONCAT(first_name, ' ', last_name) FROM employees ORDER BY salary ASC LIMIT 1) AS LowestPaidEmployee,
    (SELECT salary FROM employees ORDER BY salary ASC LIMIT 1) AS LowestSalary;

    """
    df = pd.read_sql_query(query, engine)

    names = [df["TopPaidEmployee"][0], df["LowestPaidEmployee"][0]]
    salaries = [df["TopSalary"][0], df["LowestSalary"][0]]

    plt.figure(figsize=(8, 6))
    plt.bar(names, salaries, color=["green", "red"])
    plt.title("Salaries: Highest Paid vs Lowest Paid", fontsize=16)
    plt.ylabel("Salary (€)", fontsize=14)
    plt.tight_layout()

    if not os.path.exists("Graphs"):
        os.makedirs("Graphs")
    plt.savefig("Graphs/salary_extremes.png", dpi=300, bbox_inches="tight")
    plt.show()





# Run all plotting functions
plot_gender_distribution(engine)
plot_avg_salary(engine)
plot_avg_salary_per_job(engine)
plot_product_sales(engine)
plot_monthly_sales(engine)
plot_salary_extremes(engine)