Categories: Python
Tags:

Create Your Own SQL to JSON Data API with Python

APIs are one of the most common ways applications exchange data. A frontend application, mobile app, dashboard, or another backend system can request data from an API and receive it in a structured format such as JSON.

Normally, when we talk about creating an API in Python, frameworks such as Flask, FastAPI, or Django immediately come to mind. These frameworks are excellent for production applications, but they are not mandatory for learning how an API actually works.

In this chapter, we will build a small SQL-to-JSON Data API using Python without Flask.

We will use:

  • Python
  • MySQL
  • mysql.connector
  • Python’s json module
  • Python’s built-in http.server
  • The Northwind database

Our API will connect to MySQL, execute SQL queries, convert the returned rows into JSON, and send that JSON response to a client through an HTTP endpoint.

The basic architecture will look like this:

Client → Python API → MySQL → Python → JSON Response

1. What Are We Going to Build?

Suppose our Northwind database contains a customers table.

Instead of opening MySQL Workbench and running:

SELECT * FROM customers;

we want another application to request:

http://localhost:8000/api/customers

and receive something like:

[
    {
        "CustomerID": "ALFKI",
        "CompanyName": "Alfreds Futterkiste",
        "ContactName": "Maria Anders"
    },
    {
        "CustomerID": "ANATR",
        "CompanyName": "Ana Trujillo Emparedados y helados",
        "ContactName": "Ana Trujillo"
    }
]

This is already the fundamental idea behind a data API.

The difference is that we are not using Flask or FastAPI. Python’s standard library will handle the HTTP request.

2. Understanding the Architecture

Before writing code, understand the complete flow.

When somebody opens:

http://localhost:8000/api/customers

Python receives an HTTP request.

Our Python program then:

  1. Receives the request.
  2. Identifies the requested endpoint.
  3. Connects to MySQL.
  4. Executes an SQL query.
  5. Reads the database rows.
  6. Converts the rows into Python dictionaries.
  7. Converts those dictionaries into JSON.
  8. Sends JSON back to the client.

The important point is that SQL produces database records, while JSON provides a format that applications can easily consume.

3. Install MySQL Connector

First, make sure Python is installed.

Then install the MySQL connector:

pip install mysql-connector-python

You can verify the installation with:

pip show mysql-connector-python

We will import it in Python as:

import mysql.connector

We will also use:

import json

for JSON conversion.

Finally, we need:

from http.server import BaseHTTPRequestHandler, HTTPServer

These modules allow us to create a basic HTTP server without Flask.

4. Create the Python File

Create a file called:

sql_json_api.py

Our initial imports will be:

import mysql.connector
import json

from http.server import BaseHTTPRequestHandler, HTTPServer

Now we have everything required to build our basic API.

5. Connect Python to the Northwind Database

Create a database connection function:

Replace:

def get_connection():
    return mysql.connector.connect(
        host="localhost",
        user="root",
        password="YOUR_PASSWORD",
        database="northwind"
    )
YOUR_PASSWORD

with your MySQL password.

If your Northwind database has a different name, change:

database="northwind"

accordingly.

Keeping the connection inside a function is useful because we can call it whenever an API endpoint needs database access.

6. Create a Function to Execute SQL

Now let’s create a reusable SQL function.

def execute_query(query):
    connection = get_connection()
    cursor = connection.cursor(dictionary=True)

    cursor.execute(query)

    results = cursor.fetchall()

    cursor.close()
    connection.close()

    return results

The important part here is:

cursor = connection.cursor(dictionary=True)

Normally MySQL Connector can return rows as tuples.

For example:

(
    "ALFKI",
    "Alfreds Futterkiste",
    "Maria Anders"
)

But dictionary=True allows us to receive:

{
    "CustomerID": "ALFKI",
    "CompanyName": "Alfreds Futterkiste",
    "ContactName": "Maria Anders"
}

This is extremely convenient because dictionaries can be directly converted into JSON.

7. Create the HTTP Request Handler

Now we create our API handler:

class APIHandler(BaseHTTPRequestHandler):

    def do_GET(self):
        if self.path == "/api/customers":
            self.get_customers()
        else:
            self.send_error(404, "Endpoint not found")

The do_GET() method is automatically called when a client sends a GET request.

For example:

GET /api/customers

The code checks:

self.path

If it matches:

/api/customers

we call:

self.get_customers()

Otherwise, we return a 404 error.

8. Create the Customers Endpoint

Now create the function that retrieves data from MySQL.

def get_customers(self):

    query = """
        SELECT
            CustomerID,
            CompanyName,
            ContactName
        FROM customers
    """

    results = execute_query(query)

    self.send_json(results)

The SQL query retrieves three columns from the customers table.

The returned Python object might look like:

[
    {
        "CustomerID": "ALFKI",
        "CompanyName": "Alfreds Futterkiste",
        "ContactName": "Maria Anders"
    },
    {
        "CustomerID": "ANATR",
        "CompanyName": "Ana Trujillo Emparedados y helados",
        "ContactName": "Ana Trujillo"
    }
]

Now we need to convert it into JSON and send it to the browser.

9. Convert SQL Data into JSON

Create a reusable function:

def send_json(self, data):

    json_data = json.dumps(
        data,
        default=str
    )

    self.send_response(200)

    self.send_header(
        "Content-Type",
        "application/json"
    )

    self.send_header(
        "Content-Length",
        str(len(json_data.encode("utf-8")))
    )

    self.end_headers()

    self.wfile.write(
        json_data.encode("utf-8")
    )

The most important line is:

json.dumps(data)

This converts Python objects into JSON.

The:

default=str

option is useful for MySQL data types such as dates and timestamps.

For example, MySQL might return a Python datetime object.

JSON cannot directly serialize every Python date object, so:

default=str

converts unsupported values into strings.

10. Add a Northwind View

One of the advantages of this approach is that your API does not have to retrieve data only from tables.

It can also retrieve data from SQL views.

Suppose we create a view in MySQL:

CREATE VIEW customer_summary AS
SELECT
    CustomerID,
    CompanyName,
    Country
FROM customers;

Now we can create another API endpoint:

/api/customer-summary

Add this to do_GET():

elif self.path == "/api/customer-summary":
    self.get_customer_summary()

Then create:

def get_customer_summary(self):

    query = """
        SELECT *
        FROM customer_summary
    """

    results = execute_query(query)

    self.send_json(results)

Now the API can expose the result of a database view.

This is particularly useful because complicated SQL logic can remain inside the database view while Python simply consumes the result.

11. Complete API Code

At this point, we can combine everything into one Python file.

import mysql.connector
import json

from http.server import BaseHTTPRequestHandler, HTTPServer


def get_connection():

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


def execute_query(query):

    connection = get_connection()

    cursor = connection.cursor(
        dictionary=True
    )

    cursor.execute(query)

    results = cursor.fetchall()

    cursor.close()
    connection.close()

    return results


class APIHandler(BaseHTTPRequestHandler):

    def do_GET(self):

        if self.path == "/api/customers":

            self.get_customers()

        elif self.path == "/api/customer-summary":

            self.get_customer_summary()

        else:

            self.send_error(
                404,
                "Endpoint not found"
            )

    def get_customers(self):

        query = """
            SELECT
                CustomerID,
                CompanyName,
                ContactName
            FROM customers
        """

        results = execute_query(query)

        self.send_json(results)

    def get_customer_summary(self):

        query = """
            SELECT *
            FROM customer_summary
        """

        results = execute_query(query)

        self.send_json(results)

    def send_json(self, data):

        json_data = json.dumps(
            data,
            default=str
        )

        self.send_response(200)

        self.send_header(
            "Content-Type",
            "application/json"
        )

        self.send_header(
            "Content-Length",
            str(len(json_data.encode("utf-8")))
        )

        self.end_headers()

        self.wfile.write(
            json_data.encode("utf-8")
        )


server = HTTPServer(
    ("localhost", 8000),
    APIHandler
)

print(
    "API running at http://localhost:8000"
)

server.serve_forever()

12. Run the API

Open your terminal in the folder containing the Python file.

Run:

python sql_json_api.py

You should see:

API running at http://localhost:8000

Now open your browser.

Visit:

http://localhost:8000/api/customers

The browser should display JSON containing the Northwind customer records.

You can also test:

http://localhost:8000/api/customer-summary

This endpoint retrieves data from the SQL view.

13. Why This Is an API

You may wonder whether this is really an API because we did not use Flask.

Yes.

An API is not defined by Flask.

Flask is simply a framework that makes building web APIs easier.

In our example, Python’s:

HTTPServer

is handling HTTP requests.

The endpoint:

/api/customers

accepts a request and returns structured JSON data.

Therefore, another application can consume the endpoint.

For example, JavaScript could request:

fetch("http://localhost:8000/api/customers")
    .then(response => response.json())
    .then(data => {
        console.log(data);
    });

A Power BI process, another Python program, a mobile application, or a frontend application could similarly consume the endpoint.

14. Table vs View

There is an important architectural advantage to using database views.

Suppose your application needs customer information together with order information.

Instead of putting a complicated SQL query directly inside Python, you could create a view:

CREATE VIEW customer_orders AS
SELECT
    c.CustomerID,
    c.CompanyName,
    o.OrderID,
    o.OrderDate
FROM customers c
JOIN orders o
    ON c.CustomerID = o.CustomerID;

Your Python API can then simply execute:

SELECT *
FROM customer_orders;

This separates responsibilities.

MySQL handles data logic.

Python handles API logic.

JSON handles data exchange.

This separation can make the application easier to maintain.

15. Important Security Considerations

Our example is designed for learning and local development.

Do not expose this exact implementation directly to the public internet without additional security.

For example, avoid placing database credentials directly inside source code in a production application.

Instead of:

password="YOUR_PASSWORD"

a production application should use environment variables or a secure secrets system.

You should also consider:

  • Authentication
  • Authorization
  • HTTPS
  • Input validation
  • Error handling
  • Rate limiting
  • Database connection management
  • SQL injection protection
  • Pagination
  • Logging

Another important point is that we should not allow users to send arbitrary SQL such as:

/api/query?sql=DROP TABLE customers

That would create a serious security vulnerability.

The safer approach is to define specific endpoints and predefined SQL queries.

16. Adding More Endpoints

Once the basic API works, adding endpoints is straightforward.

For example:

/api/products
/api/orders
/api/customers
/api/suppliers
/api/employees

You can map each endpoint to a specific SQL query.

For example:

elif self.path == "/api/products":
    self.get_products()

Then:

def get_products(self):

    query = """
        SELECT
            ProductID,
            ProductName,
            UnitPrice
        FROM products
    """

    results = execute_query(query)

    self.send_json(results)

You have now created a small collection of database-backed API endpoints.

17. What We Have Built

Let’s summarize the complete workflow:

Browser / Application
        ↓
HTTP GET Request
        ↓
Python HTTP Server
        ↓
API Endpoint
        ↓
mysql.connector
        ↓
MySQL Northwind
        ↓
SQL Table / View
        ↓
Python Dictionary
        ↓
json.dumps()
        ↓
JSON Response
        ↓
Browser / Application

The most important lesson is that you do not need a large framework to understand the fundamentals of an API.

Using Python’s standard library, mysql.connector, and the json module, we can build a functional SQL-to-JSON API that reads data from a MySQL table or view.

Frameworks such as Flask and FastAPI become valuable when the API grows and you need routing, authentication, validation, middleware, documentation, dependency injection, asynchronous processing, and other production features.

But for learning the fundamentals, this lightweight implementation makes the process very clear.

The Northwind database is particularly useful for this type of exercise because it contains multiple related tables, allowing you to progress from simple table endpoints to SQL views, joins, filtering, pagination, and eventually complete REST-style APIs.

Your next step could be adding URL parameters such as /api/products?id=10, pagination such as /api/customers?page=2, and POST endpoints that allow applications to insert data into MySQL.

Create an index.html in the same folder:

<!DOCTYPE html>
<html>
<body>

<h2>Northwind Customers</h2>
<div id="data"></div>

<script>
fetch("http://localhost:8000/api/customers")
  .then(res => res.json())
  .then(data => {
    document.getElementById("data").innerHTML =
      data.map(x => `<p>${x.CustomerID} - ${x.CompanyName}</p>`).join("");
  });
</script>

</body>
</html>

What this does

fetch("http://localhost:8000/api/customers")

gets the JSON from your Python API.

res.json()

converts the response into JavaScript data.

data.map(...)

goes through each customer.

And:

document.getElementById("data").innerHTML

puts the results inside the <div>.

So the complete beginner flow is now:

MySQL → Python → JSON API → JavaScript → HTML <div>

One small issue: if you open index.html directly with file://, the browser may block the request because of CORS. If that happens, we can make the same tiny Python server serve the HTML file too, keeping the whole project very simple.