Plotting Graphs from MySQL Database using Pandas DataFrame

Combine the querying power of relational databases with the analytical charting of Pandas. By connecting Python to MySQL through SQLAlchemy and executing queries via pd.read_sql(), you can extract records, aggregate metrics in the database engine, and render immediate visualizations such as Pie, Bar, Stacked Bar, Line, Density, and Scatter plots.

The 3-Step Database Plotting Pipeline 🔝

Generating charts directly from database tables follows a structured 3-tier workflow:

1. Database Connection: Establish an authenticated connection engine using SQLAlchemy.
2. SQL Extraction: Execute a SQL query (SELECT ... GROUP BY) using pd.read_sql() to let the database handle heavy filtering and aggregation.
3. Visualization: Call the appropriate Pandas plotting method (df.plot.bar(), df.plot.pie(), etc.) on the resulting DataFrame.

Step 1: Connect to MySQL with SQLAlchemy 🔝

In modern Python data stacks, SQLAlchemy is the standard database abstraction library used by Pandas. Construct a connection URL containing your username, password, host, and database name:

from sqlalchemy import create_engine

# Connection string format: mysql+mysqldb://<user>:<password>@<host>/<database>
my_conn = create_engine("mysql+mysqldb://root:password@localhost/my_tutorial")

Tip for practice: For testing without a live MySQL server, you can use SQLite with create_engine("sqlite:///sample.db") or an in-memory SQLite connection.

Step 2: Query database records into a DataFrame 🔝

Pass your SQL query and the engine object to pd.read_sql(). Performing aggregations like SQL GROUP BY directly inside the database minimizes memory usage in Python:

query = "SELECT class1, COUNT(*) as number FROM student GROUP BY class1"
df = pd.read_sql(query, my_conn)
print(df)

Step 3A: Plotting a Pie Chart from SQL aggregations 🔝

Represent class proportions as a pie chart with percentage callouts:

import pandas as pd
import matplotlib.pyplot as plt
from sqlalchemy import create_engine

my_conn = create_engine("mysql+mysqldb://root:password@localhost/my_tutorial")
query = "SELECT class1, COUNT(*) as number FROM student GROUP BY class1"
df = pd.read_sql(query, my_conn)

labels_list = df["class1"].tolist()

df.plot.pie(
    y="number",
    labels=labels_list,
    autopct="%1.0f%%",
    figsize=(5.5, 5),
    title="Student Distribution by Class"
)
plt.ylabel("")  # Hide default y-label
plt.tight_layout()
plt.show()

Step 3B: Vertical and Horizontal Bar charts 🔝

To compare class counts, render vertical or horizontal bar charts:

# Vertical bar chart
df.plot.bar(x="class1", y="number", title="Students by Class", figsize=(6, 3.5))
plt.show()

# Horizontal bar chart
df.plot.barh(x="class1", y="number", title="Students by Class (Horizontal)", figsize=(6, 3))
plt.show()

Step 3C: Multi-Metric Bar charts (AVG and MAX) 🔝

Query multiple aggregate columns simultaneously in SQL to plot multi-bar comparisons:

query_multi = "SELECT class1, MAX(mark) as max_mark, AVG(mark) as avg_mark FROM student GROUP BY class1"
df_multi = pd.read_sql(query_multi, my_conn)

df_multi.plot.bar(
    x="class1",
    y=["avg_mark", "max_mark"],
    figsize=(6.5, 4),
    title="Average and Maximum Marks per Class"
)
plt.ylabel("Score")
plt.tight_layout()
plt.show()

Step 3D: Stacked Bar charts with SQL CASE statements 🔝

Combine SQL CASE expressions with conditional aggregation to generate pivoting datasets directly from SQL:

query_case = """
SELECT class1,
SUM(CASE WHEN gender = 'male' THEN 1 ELSE 0 END) AS Male,
SUM(CASE WHEN gender = 'female' THEN 1 ELSE 0 END) AS Female
FROM student
GROUP BY class1
"""
df_gender = pd.read_sql(query_case, my_conn)

df_gender.plot.bar(
    x="class1",
    stacked=True,
    figsize=(6, 4),
    title="Gender Composition by Class"
)
plt.ylabel("Number of Students")
plt.tight_layout()
plt.show()

Step 3E: Trend Line and Density curves 🔝

For time-series or mark distributions, execute queries and pass results to Line or Density functions:

# Continuous probability distribution of student marks
query_marks = "SELECT mark FROM student"
df_marks = pd.read_sql(query_marks, my_conn)

df_marks.plot.density(figsize=(6, 3.5), title="Overall Mark Density Distribution")
plt.show()

Step 3F: Correlation Scatter plots 🔝

Examine bivariate relationships (such as average class mark vs class size) using scatter plots:

query_scatter = "SELECT AVG(mark) as avg_mark, COUNT(*) as student_count FROM student GROUP BY class1"
df_scatter = pd.read_sql(query_scatter, my_conn)

df_scatter.plot.scatter(
    x="avg_mark",
    y="student_count",
    title="Average Score vs Class Size",
    figsize=(6, 3.5)
)
plt.show()

Save database charts to image files 🔝

Capture the underlying Matplotlib Figure object to export report assets:

ax = df.plot.pie(y="number", labels=labels_list, autopct="%1.0f%%")
fig = ax.get_figure()
fig.savefig("student_distribution.png", dpi=300, bbox_inches="tight")
plt.close()
print("Exported student_distribution.png successfully!")

Practical workflow: store sales & fulfillment dashboard 🔝

In retail analytics, inventory managers track sales performance across categories from an order database table. In this workflow, we query order totals and generate a two-panel dashboard:

import sqlite3
import pandas as pd
import matplotlib.pyplot as plt

# Connect to local database engine
conn = sqlite3.connect(":memory:")

# Populate demo orders
orders = pd.DataFrame({
    "order_id": range(1, 9),
    "category": ["Electronics", "Apparel", "Electronics", "Home", "Apparel", "Home", "Electronics", "Home"],
    "amount": [240, 85, 410, 130, 95, 180, 320, 210]
})
orders.to_sql("orders", conn, index=False)

# Query totals and averages
q = """
SELECT category,
COUNT(*) as order_count,
SUM(amount) as total_revenue,
AVG(amount) as avg_order_val
FROM orders
GROUP BY category
ORDER BY total_revenue DESC
"""
df_report = pd.read_sql(q, conn)
print(df_report)

assert len(df_report) == 3
assert df_report["total_revenue"].sum() == 1670

# Render two-panel dashboard
fig, axes = plt.subplots(1, 2, figsize=(10, 4))
df_report.plot.bar(x="category", y="total_revenue", ax=axes[0], title="Total Revenue ($)")
df_report.plot.pie(y="order_count", labels=df_report["category"], ax=axes[1], autopct="%1.0f%%", title="Order Share")
axes[1].set_ylabel("")
plt.tight_layout()
plt.show()

Expected console output:

      category  order_count  total_revenue  avg_order_val
0  Electronics            3            970     323.333333
1         Home            3            520     173.333333
2      Apparel            2            180      90.000000

Exercise: customer order value and tier distribution 🔝

Given the following database table schema for customer accounts, perform these tasks:

1. Create an in-memory SQL database table customers with tier and balance columns.
2. Execute a SQL query using GROUP BY tier calculating both total_balance and customer_count.
3. Assert that the total combined balance equals $16,500.
4. Plot a horizontal bar chart of total_balance by tier with grid=True.

Exercise solution 🔝

import sqlite3
import pandas as pd
import matplotlib.pyplot as plt

conn = sqlite3.connect(":memory:")
cust_df = pd.DataFrame({
    "id": range(1, 6),
    "tier": ["Gold", "Silver", "Gold", "Bronze", "Silver"],
    "balance": [6000, 3500, 4500, 1000, 1500]
})
cust_df.to_sql("customers", conn, index=False)

query = """
SELECT tier,
COUNT(*) as customer_count,
SUM(balance) as total_balance
FROM customers
GROUP BY tier
ORDER BY total_balance DESC
"""
result_df = pd.read_sql(query, conn)
print(result_df)

assert result_df["total_balance"].sum() == 16500

result_df.plot.barh(
    x="tier",
    y="total_balance",
    grid=True,
    figsize=(6.5, 3.5),
    title="Account Balance by Customer Tier"
)
plt.xlabel("Total Balance ($)")
plt.tight_layout()
plt.show()

Expected console output:

     tier  customer_count  total_balance
0    Gold               2          10500
1  Silver               2           5000
2  Bronze               1           1000

Practice in Google Colab 🔝

Run live SQL extraction and plotting examples in our interactive Google Colab notebook:

Open in Google Colab View on GitHub
Run database-driven visualization examples interactively, execute SQL aggregate queries with SQLAlchemy, and test multi-metric charts directly in Google Colab with pre-populated SQLite tables. All sample datasets are embedded directly in the notebook.

Further Reading & Related Tutorials 🔝

• Pandas Plot Overview — Master visualization pillar covering all Pandas chart types.
• Pandas read_sql() — Complete guide to loading database queries and tables into DataFrames.
• Pandas with MySQL — Establishing database connections, inserting rows, and executing queries.
• SQL GROUP BY Queries — Database aggregate queries for count, sum, average, and max.
• SQL CASE Expressions — Conditional SQL logic for categorization and pivoting.
• Pandas Section Home — Master index of all Pandas tutorials, pillars, and guides.

Reference: Pandas read_sql official documentation.

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: