Categories: Full-Stack Development
Tags:

Read Data from APIs and Create Charts with JavaScript

In the previous chapter, we created our own SQL-to-JSON API using Python. Our Python program connects to the Northwind database, runs an SQL query, and makes the result available through an API endpoint.

Now we are going one step further.

Instead of simply displaying API data as text, we will read the API data with JavaScript and turn it into useful visualizations.

We will create two simple examples:

  1. Products in Each Category → Pie Chart
  2. Customers in Each Country → Line Chart

The goal is not to teach complicated programming. The goal is to understand one simple idea:

Database → Python API → JSON → JavaScript → Chart

1. What We Already Have

Our Python program is already connected to the Northwind database.

The basic connection looks like this:

import mysql.connector
import json
from http.server import HTTPServer, BaseHTTPRequestHandler

Python connects to MySQL:

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

Then we run SQL:

cursor.execute("SELECT * FROM customers")

Finally, Python converts the result into JSON.

This means our database information can now be requested by another application.

That other application can be a website.

2. What Is an API Endpoint?

An API endpoint is simply a URL where we can request some data.

For example:

http://localhost:8000/api/customers

Our browser can visit this address and receive customer data.

Now imagine that we create another endpoint:

http://localhost:8000/api/products

This endpoint could return information about products.

We could create another one:

http://localhost:8000/api/customers-country

This could return the number of customers in each country.

So we can think of endpoints as different doors to different sets of data.

3. Different Endpoints Can Have Different SQL Queries

This is an important concept.

We don’t have to use the same SQL query for every API endpoint.

For example:

/api/products

could use:

SELECT * FROM products;

While:

/api/customers-country

could use:

SELECT Country, COUNT(*) AS TotalCustomers
FROM customers
GROUP BY Country;

The API simply runs the appropriate SQL query and returns the result as JSON.

This is where things become interesting.

We can take that JSON data and create charts.

4. Our First Example: Products by Category

Let’s start with a simple business question:

How many products are available in each category?

The Northwind database has a products table and a categories table.

We can use SQL to count products belonging to each category.

For example:

SELECT
    c.CategoryName,
    COUNT(p.ProductID) AS TotalProducts
FROM categories c
JOIN products p
ON c.CategoryID = p.CategoryID
GROUP BY c.CategoryName;

The result might look conceptually like:

Category              Products
--------------------------------
Beverages                 12
Condiments                 8
Confections               13
Dairy Products            10
Grains/Cereals             7

We don’t have to manually create these numbers.

MySQL calculates them for us.

5. Create the Products API Endpoint

We can create another function in our Python program:

def get_products():

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

    cursor = db.cursor(dictionary=True)

    cursor.execute("""
        SELECT
            c.CategoryName,
            COUNT(p.ProductID) AS TotalProducts
        FROM categories c
        JOIN products p
        ON c.CategoryID = p.CategoryID
        GROUP BY c.CategoryName
    """)

    return cursor.fetchall()

The important part for students is simply the SQL.

We are asking MySQL:

“Give me each category and count how many products belong to it.”

6. Connect the Query to an Endpoint

Our Python API can now provide this data through an endpoint such as:

http://localhost:8000/api/products

The browser can request this URL.

Instead of returning a webpage, our Python program returns JSON.

The response might look like:

[
    {
        "CategoryName": "Beverages",
        "TotalProducts": 12
    },
    {
        "CategoryName": "Condiments",
        "TotalProducts": 8
    }
]

This is perfect for JavaScript.

JavaScript doesn’t need to know anything about MySQL.

It simply asks the API for data.

7. JavaScript Reads the API

Our JavaScript can make a request using fetch():

let response = await fetch(
    "http://localhost:8000/api/products"
);

let data = await response.json();

There are two simple steps here.

First:

fetch(...)

means:

“Go and get the data.”

Then:

response.json()

means:

“Turn the response into JavaScript data.”

That’s it.

We now have our database information inside JavaScript.

8. Turn the Data into a Pie Chart

For the chart, we can use a JavaScript charting library such as Chart.js.

The important thing is that we don’t need to manually draw a pie chart.

We simply give the chart:

  • Category names
  • Number of products

For example, our JavaScript data might contain:

Beverages → 12
Condiments → 8
Confections → 13

Chart.js can turn this information into a visual pie chart.

Conceptually:

             Products
                |
        ┌───────┴───────┐
        ↓               ↓
    Category          Number
        ↓               ↓
    Beverages            12
    Condiments             8
    Confections           13

The database provides the numbers.

Python provides the API.

JavaScript provides the chart.

9. Why a Pie Chart?

A pie chart is useful when we want to understand how a total is divided between categories.

For example, a business owner might quickly see:

  • Which category has many products
  • Which category has fewer products
  • How the product catalog is distributed

This is much easier to understand visually than reading a list of numbers.

Instead of:

Beverages: 12
Condiments: 8
Confections: 13
Dairy Products: 10

we can present the same information visually.

This is one of the reasons APIs are useful for dashboards.

10. Second Example: Customers by Country

Now let’s ask another business question:

How many customers do we have in each country?

Our SQL query can be:

SELECT
    Country,
    COUNT(*) AS TotalCustomers
FROM customers
GROUP BY Country;

The result might look like:

Country             Customers
----------------------------
USA                     13
Germany                 11
France                  11
Brazil                   9
UK                       7

Again, we are allowing MySQL to do the calculation.

Python will simply make this information available through our API.

11. Create Another API Endpoint

We can create:

http://localhost:8000/api/customers-country

This endpoint can execute:

SELECT
    Country,
    COUNT(*) AS TotalCustomers
FROM customers
GROUP BY Country;

The API could return:

[
    {
        "Country": "USA",
        "TotalCustomers": 13
    },
    {
        "Country": "Germany",
        "TotalCustomers": 11
    },
    {
        "Country": "France",
        "TotalCustomers": 11
    }
]

Notice something important.

The JSON structure is extremely simple.

We have:

Country
TotalCustomers

That makes it easy for JavaScript to use.

12. Read the Customer API with JavaScript

Our JavaScript can again use:

let response = await fetch(
    "http://localhost:8000/api/customers-country"
);

let data = await response.json();

Now data contains the customer information.

We can use:

Country → X-axis
Customers → Y-axis

to create a chart.

For example:

Customers
   |
 15|
 10|    ●
  5| ●     ●
  0|________________
       USA Germany France

The actual chart library will create the visual chart for us.

13. Why Use a Line Chart?

A line chart is normally useful when values have an ordered sequence, particularly over time.

For this learning example, we can use country categories along the horizontal axis to demonstrate how JavaScript can visualize API data.

However, in a real business dashboard, customer counts by country are often better represented by a bar or column chart, because countries are categories rather than a continuous timeline.

This distinction is useful for students to learn:

The chart should match the type of data.

14. The Complete Data Journey

At this point, our application has a very interesting architecture.

For the product chart:

Northwind Database
        ↓
Products + Categories
        ↓
SQL Query
        ↓
Python
        ↓
API Endpoint
        ↓
JSON
        ↓
JavaScript
        ↓
Pie Chart

And for customers:

Northwind Database
        ↓
Customers
        ↓
SQL Query
        ↓
Python
        ↓
API Endpoint
        ↓
JSON
        ↓
JavaScript
        ↓
Chart

Students don’t need to think about everything as one huge programming problem.

Instead, think about it as small steps connected together.

15. The Most Important Idea

The most important lesson from this chapter isn’t fetch().

It isn’t Python.

It isn’t SQL.

It isn’t even the chart.

It is understanding that different technologies can work together.

MySQL stores the data.

Python retrieves and prepares the data.

The API makes the data available.

JSON carries the data.

JavaScript reads the data.

The chart turns the data into something humans can understand quickly.

This is a basic version of the same architecture used in many modern web applications and business dashboards.

16. What We Will Build Next

Once the students understand these two examples, we can gradually make the project more useful.

For example, we can create:

/api/products
/api/products/category
/api/customers
/api/customers-country
/api/orders
/api/sales

Each endpoint can have its own SQL query.

Then JavaScript can read those endpoints and create a complete dashboard.

Eventually, our Northwind project could look something like:

             Python API
                 |
      ┌──────────┼──────────┐
      ↓          ↓          ↓
   Products   Customers    Orders
      ↓          ↓          ↓
    JSON       JSON        JSON
      ↓          ↓          ↓
    Chart      Chart       Chart

And the final webpage could contain several visualizations.

Here is the Python and HTML Code you need for this short Full Stack Project

import mysql.connector
import json
from http.server import HTTPServer, BaseHTTPRequestHandler

def get_data():
    db = mysql.connector.connect(
        host="localhost", user="root",
        password="mysql", database="northwind"
    )

    cursor = db.cursor(dictionary=True)
    cursor.execute("SELECT * FROM customers")
    return cursor.fetchall()

class API(BaseHTTPRequestHandler):
    def do_GET(self):
        data = json.dumps(get_data(), default=str)

        self.send_response(200)
        self.send_header("Content-Type", "application/json")
        self.send_header("Access-Control-Allow-Origin", "*")
        self.end_headers()

        self.wfile.write(data.encode())

HTTPServer(("localhost", 8000), API).serve_forever()
<!DOCTYPE html>
<html>
<head>

    <title>Northwind Dashboard</title>

    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>

</head>

<body>

<h2>Products by Category</h2>

<canvas id="productsChart"></canvas>


<h2>Customers by Country</h2>

<canvas id="customersChart"></canvas>


<script>

async function loadProducts() {

    let response = await fetch(
        "http://localhost:8000/api/products"
    );

    let data = await response.json();

    let names = [];
    let totals = [];

    for (let item of data) {
        names.push(item.CategoryName);
        totals.push(item.TotalProducts);
    }

    new Chart(
        document.getElementById("productsChart"),
        {
            type: "pie",

            data: {
                labels: names,

                datasets: [{
                    data: totals
                }]
            }
        }
    );
}


async function loadCustomers() {

    let response = await fetch(
        "http://localhost:8000/api/customers"
    );

    let data = await response.json();

    let countries = [];
    let totals = [];

    for (let item of data) {
        countries.push(item.Country);
        totals.push(item.TotalCustomers);
    }

    new Chart(
        document.getElementById("customersChart"),
        {
            type: "line",

            data: {
                labels: countries,

                datasets: [{
                    label: "Customers",
                    data: totals
                }]
            }
        }
    );
}


loadProducts();
loadCustomers();

</script>

</body>
</html>
async function loadProducts() simply means:

Create a function that can wait for data from the API without freezing the webpage.

In our example:

async function loadProducts() {

means “Create a function named loadProducts that will get product data from the API.”

The async keyword allows us to use await inside the function:

let response = await fetch(...);

So, simply:

async = this function may need to wait for something, such as API data.

Conclusion

Today we moved from simply creating an API to actually using an API.

Our Python program connects the Northwind database to the web. Different endpoints can execute different SQL queries, and each endpoint can return useful JSON data.

JavaScript can then request that data and turn it into visual information such as charts.

The complete concept is simple:

SQL gets the information → Python creates the API → JSON carries the information → JavaScript displays it.

Once students understand this flow, they have the foundation for building simple data dashboards, reporting tools, and database-powered web applications without needing to learn a large number of technologies at once.