Categories: Python Pandas Tutorial

Tags:

Creating Business Graphs from a Live MySQL Northwind Database Using Pandas, Matplotlib, and Seaborn : Part 2

In Part 1, you successfully connected Python to a live MySQL Northwind database using the mysql-connector-python library and imported SQL query results into Pandas DataFrames.

The next step in every data analytics project is visualization. Graphs make it easier to identify trends, compare business performance, analyze customer behavior, and present insights to stakeholders. Instead of manually exporting data to Excel, Python allows us to directly query the database and generate professional charts in just a few lines of code.

In this tutorial, Ankit demonstrates how to create common business visualizations directly from a live MySQL database using Matplotlib and Seaborn.

Import Required Libraries

import pandas as pd
import mysql.connector
import matplotlib.pyplot as plt
import seaborn as sns

# Improve chart appearance
sns.set_style("whitegrid")
plt.rcParams["figure.figsize"] = (10,6)

Connect to the Northwind Database

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

1. Customer Distribution by Country (Bar Chart)

SQL Query

query = """
SELECT
Country,
COUNT(*) AS TotalCustomers
FROM customers
GROUP BY Country
ORDER BY TotalCustomers DESC;
"""

country = pd.read_sql(query, connection)

Plot

plt.figure(figsize=(12,6))

plt.bar(country["Country"],
        country["TotalCustomers"])

plt.title("Customers by Country")
plt.xlabel("Country")
plt.ylabel("Number of Customers")

plt.xticks(rotation=60)

plt.show()

Why This Chart?

A bar chart quickly highlights the countries where the company has the largest customer base. Sales and marketing teams can use this information to prioritize high-value markets.

2. Customer Distribution (Horizontal Bar Chart)

plt.figure(figsize=(10,8))

plt.barh(country["Country"],
         country["TotalCustomers"])

plt.title("Customer Distribution")

plt.xlabel("Customers")

plt.show()

Horizontal charts work well when category names are long.

3. Customer Share by Country (Pie Chart)

plt.figure(figsize=(8,8))

plt.pie(
    country["TotalCustomers"],
    labels=country["Country"],
    autopct="%1.1f%%",
    startangle=90
)

plt.title("Customer Share by Country")

plt.show()

Business Insight

Pie charts help visualize the percentage contribution of each country to the total customer base.

4. Most Expensive Products

SQL Query

query = """
SELECT
ProductName,
UnitPrice
FROM products
ORDER BY UnitPrice DESC
LIMIT 10;
"""

products = pd.read_sql(query, connection)

Plot

plt.figure(figsize=(12,6))

sns.barplot(
    data=products,
    x="UnitPrice",
    y="ProductName"
)

plt.title("Top 10 Most Expensive Products")

plt.show()

5. Product Price Distribution (Histogram)

query = """
SELECT UnitPrice
FROM products;
"""

price = pd.read_sql(query, connection)

plt.figure(figsize=(10,6))

plt.hist(
    price["UnitPrice"],
    bins=15,
    edgecolor="black"
)

plt.title("Distribution of Product Prices")

plt.xlabel("Price")

plt.ylabel("Frequency")

plt.show()

Interpretation

Histograms help identify whether most products belong to the low-price, medium-price, or premium-price categories.

6. Employee Order Count

SQL Query

query = """
SELECT

CONCAT(FirstName,' ',LastName) Employee,
COUNT(OrderID) OrdersHandled

FROM employees

JOIN orders
ON employees.EmployeeID=orders.EmployeeID

GROUP BY Employee

ORDER BY OrdersHandled DESC;
"""

employees = pd.read_sql(query, connection)

Visualization

plt.figure(figsize=(12,6))

sns.barplot(
    data=employees,
    x="Employee",
    y="OrdersHandled"
)

plt.xticks(rotation=45)

plt.title("Orders Handled by Employees")

plt.show()

Business Insight

Managers can evaluate employee workload and identify high-performing sales representatives.

7. Orders by Shipping Country

query = """

SELECT

ShipCountry,
COUNT(*) TotalOrders

FROM orders

GROUP BY ShipCountry

ORDER BY TotalOrders DESC
LIMIT 10;

"""

shipping = pd.read_sql(query, connection)

plt.figure(figsize=(12,6))

sns.barplot(
    data=shipping,
    x="ShipCountry",
    y="TotalOrders"
)

plt.title("Top Shipping Countries")

plt.xticks(rotation=45)

plt.show()

8. Monthly Orders Trend (Line Chart)

SQL Query

query = """

SELECT

MONTH(OrderDate) Month,
COUNT(*) TotalOrders

FROM orders

GROUP BY Month

ORDER BY Month;

"""

monthly = pd.read_sql(query, connection)

Plot

plt.figure(figsize=(10,5))

plt.plot(
    monthly["Month"],
    monthly["TotalOrders"],
    marker="o"
)

plt.title("Monthly Orders")

plt.xlabel("Month")

plt.ylabel("Orders")

plt.grid(True)

plt.show()

Business Insight

Line charts help identify seasonal demand and monthly business performance.

9. Supplier Distribution

query = """

SELECT

Country,
COUNT(*) Suppliers

FROM suppliers

GROUP BY Country

ORDER BY Suppliers DESC;

"""

supplier = pd.read_sql(query, connection)

plt.figure(figsize=(10,6))

sns.barplot(
    data=supplier,
    x="Country",
    y="Suppliers"
)

plt.xticks(rotation=45)

plt.title("Suppliers by Country")

plt.show()

10. Scatter Plot – Product Price vs Stock

query = """

SELECT

ProductName,
UnitPrice,
UnitsInStock

FROM products;

"""

products = pd.read_sql(query, connection)

plt.figure(figsize=(10,6))

plt.scatter(
    products["UnitPrice"],
    products["UnitsInStock"]
)

plt.xlabel("Unit Price")

plt.ylabel("Units in Stock")

plt.title("Price vs Stock Availability")

plt.show()

Interpretation

Scatter plots reveal whether expensive products generally have lower inventory levels or if there is no clear relationship between price and stock.

11. Box Plot of Product Prices

plt.figure(figsize=(8,5))

sns.boxplot(
    x=products["UnitPrice"]
)

plt.title("Product Price Distribution")

plt.show()

Why Use a Box Plot?

A box plot helps identify:

Median price
Quartiles
Price spread
High-value outlier products

12. Correlation Heatmap

Retrieve numerical product information.

query = """

SELECT

UnitPrice,
UnitsInStock,
UnitsOnOrder,
ReorderLevel

FROM products;

"""

numeric = pd.read_sql(query, connection)

plt.figure(figsize=(8,6))

sns.heatmap(
    numeric.corr(),
    annot=True,
    cmap="coolwarm"
)

plt.title("Correlation Matrix")

plt.show()

Business Insight

Correlation analysis helps determine relationships between inventory, pricing, and reorder levels.

13. Save Charts as Images

plt.savefig(
    "customer_distribution.png",
    dpi=300,
    bbox_inches="tight"
)

Charts can be exported for reports, Power BI documentation, presentations, or dashboards.

Complete Dashboard Example

import pandas as pd
import mysql.connector
import matplotlib.pyplot as plt
import seaborn as sns

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

query = """
SELECT
Country,
COUNT(*) Customers
FROM customers
GROUP BY Country
ORDER BY Customers DESC;
"""

df = pd.read_sql(query, connection)

plt.figure(figsize=(12,6))

sns.barplot(
    data=df,
    x="Country",
    y="Customers"
)

plt.title("Customer Distribution by Country")
plt.xticks(rotation=45)

plt.tight_layout()

plt.show()

connection.close()

Best Practices

Keep SQL queries efficient by selecting only the required columns.
Close the database connection after completing the analysis.
Use aliases (AS) to create meaningful column names.
Handle missing values before visualization.
Label charts clearly with descriptive titles and axis names.
Choose chart types based on the data: bar charts for comparisons, line charts for trends, pie charts for proportions, and scatter plots for relationships.
Export high-resolution images for reports and presentations.

Conclusion

In this two-part tutorial, Ankit demonstrated how to build a complete data analytics workflow using Python, MySQL Connector, Pandas, Matplotlib, and Seaborn. You learned how to connect to a live MySQL Northwind database, execute SQL queries, load results into Pandas DataFrames, perform exploratory data analysis, and create professional business visualizations such as bar charts, line charts, pie charts, histograms, scatter plots, box plots, and heatmaps. This end-to-end approach mirrors real-world analytics workflows used by data analysts and business intelligence professionals, providing a strong foundation for advanced projects, dashboard development, and data-driven decision-making.