Creating Database-powered RESTFUL APIs with Flask and MySQL

Introduction

In software engineering, API (Application Programming Interface) is part of a computer software application that allows for communication and sharing of data to other computer software or the underlying operating systems. With APIs, computer programs with differing architectures have an ‘interface’ of communication that allows for data sharing and transfer.

Information transfer between different computer programs proves to be the essence of robust applications. APIs make that possible. APIs are used especially when there’s a large amount of data to share with the public. It would be unforgiving to make users download heavy files through FTP (File transfer protocol).

Flask is a web framework for Python that provides functionality for building web applications, including managing HTTP requests and rendering templates.

In this guide, you’ll create a RESTFUL API that serves data directly from a MySQL database using the Flask web framework.

Prerequisites

For this guide you’ll need the following to get along:

Basic knowledge of Python programming
Basic knowledge of MySQL
Python and Flask installed on your computer
Installations:
If you don’t already have Python installed on your system, head up to the Python Official Website and start your download.
To confirm if the installation was successful, open the command line and run this command:
```
  python --version
```
The output should be a version number. After successfully installing Python, run this command in your terminal:
```
  pip install flask
```
This will install the flask framework.

In this guide, you’ll use the pymysql database connector which allows you to connect to MySQL. Install it using this command:
```
  pip install pymysql
```
Have the MySQL database server installed on your computer. If you don’t have MySQL, Download MySQL according to your system requirements.

Understanding RESTFUL APIs

A REST (REpresentational State Transfer) is a backend architectural style that defines a set of best practices to implement APIs. REST implementation induces performance, reliability, portability and simplicity. For an API to be considered RESTFUL, it must follow the design principles of the REST architecture. An API is not RESTFUL if there’s a violation of any of the following constraints (except the last one which is optional).

RESTFUL API Constraints :

Uniform interface: There’s a definite interface, characterized by methods, requests and responses, in which the client and server can interact.
Stateless: The server is independent of the client. There’s no storing of client information on the server. Request parameters, including authentication codes, are included when making requests to the server.
Cacheable: All resource representations (responses) from the server are cacheable.
Client-Server: There is no form of database connectedness between server and client. The server doesn’t know who’s talking to it. There’s a distinct separation between the client and server.
Layered System: Intermediary servers are put in place such as load balancers. This ensures that security policies are enforced.
Code on Demand (optional): servers can transfer logic to clients and clients can execute some form of logic on the server, this however, should be a temporary process.

In a RESTFUL API, a client communicates with the server by using HTTP methods (GET, POST, PUT and DELETE) which corresponds to the major operations performed on database objects:
GET --> READ
POST --> CREATE
PUT --> UPDATE
DELETE --> DELETE, DESTROY

HTTP Response Status

The server responds with status codes that indicate the status of the request. Below are important status codes:

200: OK
201: created (after a POST request)
204: no content (can be returned after a delete request)
400: the server doesn’t understand the request
401: unauthorized (client is not identified)
403: forbidden (client is not allowed to access a particular resource)
404: not found
500: internal server error

Understanding Endpoints

REST API is resource-based. The data to be accessed are located in specific URLs called endpoints. Here is an example of an endpoint:

https://scifistories.com/api/v1/authors/

The endpoint consists of the base URL (https://scifistories.com) and the resource location (api/v1/authors/). Let’s assume this endpoint contains some information about all science fiction authors in this format:

[ {"Jules Verne": {
    "id": 0,
    "age": 51,
    "best_work": "Journey to the Center of the Earth"
    }
},

 {"H.G Wells": {
    "id": 1,
    "age": 62,
    "best_work": "War of the Worlds"
     }
}
]

Requesting this resource returns all the data about the science fiction authors.

What if you just need the endpoint to return information about H.G. Wells? This is where query parameters come into play. To make more specific requests, some parameters are passed to the URL. Here is an example:

https://scifiauthors.com/api/v1/authors/?id=1

A query parameter begins with a question mark. Multiple query parameters can be passed but they are joined using ‘&’.

Another way to do this is to use URL variables.

http://scifiauthors.com/api/v1/authors/<int:id>

Either way, the backend can obtain the query arguments and fetch the author having the id we passed to the URL.

Setting Up Database Model

Let’s begin setting up the database models. First, head up to the MySQL console and create a database. Name the database authors:

    CREATE DATABASE authors;

Using a code editor, create a file named model.py and copy this code into it:

from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.orm import declarative_base, sessionmaker

# Connect to the database
# Ensure to replace usr_name and usr_pwd with your username and password
engine = create_engine("mysql+pymysql://usr_name:usr_pwd@localhost/authors")

# Create an Object Relational Mapper instance to map Python classes to database tables
Base = declarative_base()

# Create Python class to map to database table
class Author(Base):
    __tablename__ = "authors"
    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False)
    age = Column(Integer, nullable=False)
    best_work = Column(String(100), nullable=False)

# create tables    
Base.metadata.create_all(bind=engine)

# Create a database session
Session = sessionmaker(bind=engine)
session = Session()

That’s a lot of information in there, so let’s break everything down.

The create_engine() function establishes a connection to the database you created earlier. The declarative_base() function enables you to turn the Python classes into database tables.

In the class definition, the columns defined represent some information about the authors' table. Each column also specifies the datatype in it. Certain constraints are added as well. For instance, the id column contains the primary_key constraint.

Run this command on the MySQL console to confirm the creation of the database table:

  USE authors;

and

  SHOW TABLES;

You should see a table named ‘authors’.

Setting up the Flask Web App

Now that you have the database set up, it’s time to create the flask app. Create a file app.py and copy this code into it:

from flask import Flask
# Instantiate flask app
app = Flask(__name__)

@app.route("/", methods=["GET"])
def home():
    return "<h1>Hello Scifi Authors!</h1>"

if __name__ == "__main__":
    app.run(debug=True)

After running the code, you should see an output similar to this:

    Running on http://127.0.0.1:5000/
    …

This means that the Flask application is running locally on your system. Follow the link http://127.0.0.1:5000 to see the application running on your browser:

You created a working web application.

So what just happened? How does Flask work? Well, when you accessed the base URL of the web application, Flask maps that URL to the function you defined and runs the function. In this case, the home() function runs.

Implementing the CRUD Operations

a. Create (POST):

Now that you have the database table set up and a running web application, it's time to begin making API requests. Create another endpoint to receive POST requests. Do this by adding this code to the app.py file:

@app.route("/add_author", methods=["POST"])
def create_author():
    if not request.json or "name" not in request.json:
        abort(404)
    else:
        author_dict = request.get_json()
        author = Author(name=author_dict["name"],
            age=author_dict["age"], best_work=author_dict["best_work"])
        session.add(author)
        session.commit()

        created_auth = {}
        created_auth["id"] = author.id
        for k, v in author_dict.items():
            created_auth[k] = v

        return jsonify({"author": created_auth}), 201

Essentially, this endpoint receives a POST request, checks if there’s a name in the JSON request, creates a new author and adds that author to the database. The endpoint returns the created author's information in JSON.

Now you can create your first author using API request. First, run the web application and open the terminal. If you’re using a Linux terminal, it’s straightforward to make POST requests:

~$ curl -i -H "Content-Type: application/json" -X POST http://127.0.0.1:5000/add_athor -d '{"name": "H.G Wells", "age": 46, "best_work": "The Time Machine"}'

If you’re on Windows, you’ll need to do a little trick. Enclose your request data within double quotes and escape all double quotes within the JSON. Like this:

curl -i -H "Content-Type: application/json" -X POST http://127.0.0.1/add_author -d "{\"name\": \"H.G Wells\", \"age\": 46, \"best_work\": \"The Time Machine\"}"

You should see the created author returned:

HTTP/1.1 201 CREATED
Server: Werkzeug/2.3.6 Python/3.11.4
Date: Tue, 25 Jul 2023 13:15:22 GMT
Content-Type: application/json
Content-Length: 111
Connection: closed

{
    "author": {
        "id": 1,
        "name": "H.G Wells",
        "age": 46,
        "best_work": "The Time Machine",
    }
}

The API request was a success! Now check the database and confirm the creation of the author:

  mysql> SELECT * FROM authors;
  +----+----------+-----+------------------+
  | id | name     | age | best_work        |
  +----+----------+-----+------------------+
  | 1  |H.G Wells | 45  | The Time Machine |
  +----+----------+-----+------------------+
  1 row in set (0.00 sec)

The table now contains the author you created. Go ahead and create another author, call this author Jules Vern:

~$ curl -i -H "Content-Type: application/json" -X POST http://127.0.0.1:5000/add_athor -d '{"name": "Jules Vern", "age": 40, "best_work": "Journey to the Center of the Earth"}'

And the response:

HTTP/1.1 201 CREATED
Server: Werkzeug/2.3.6 Python/3.11.4
Date: Tue, 25 Jul 2023 14:15:22 GMT
Content-Type: application/json
Content-Length: 130
Connection: closed

{
    "author": {
        "id": 2,
        "name": "Jules Vern",
        "age": 40,
        "best_work": "Journey to the Center of the Earth",
    }
}

b. Read (GET):

Create another endpoint to handle GET requests. Before then add these lines to your code, just before the base route that is /.

    @app.errorhandler(404)
    def not_found(error):
        return jsonify({"error": "Not found"}), 404

This code will run if there’s a 404 error in the application. It's a 'RESTFUL' manner of handling errors. Now create the endpoint for retrieving authors by their id:

@app.route("/authors/<int:id>", methods=["GET"])
def get_author(id):
    """Get author by id"""
    try:
        author = session.query(Author).filter(Author.id == id).one()
        if author.id == id:
            auth_json = {}
            auth_json["name"] = author.name
            auth_json["age"] = author.age
            auth_json["best_work"] = author.best_work

            return jsonify({"author": auth_json})
    except Exception:
        abort(404)

This endpoint receives an id, queries the database authors table, and filters out the author with that id. A new dictionary for the author is created and returned as the JSON response.

Run the application and head to the terminal to make an API GET request:

~$ curl http://127.0.0.1:5000/authors/1
{
    "author": {
        "id": 1,
        "name": "H.G Wells",
        "age": 46,
        "best_work": "The Time Machine"
    }
}

It worked perfectly. Now try to get an author that doesn’t exist

~$ curl http://127.0.0.1:5000/authors/10
{
    "error": "Not found"
}

The request returned an API-friendly error message.

c. Update (PUT):

To update the database information, you’ll need to make an endpoint that accepts PUT requests. Here’s the code to do this:

@app.route("/authors/<int:id>", methods=["PUT"])
def update_author(id):
    """update author by id"""
    if not request.json or "name" not in request.json:
        return jsonify({"error": "Invalid parameters"})
    try:
        author = session.query(Author).filter(Author.id == id).one()
        if author.id == id:
            req_parameters = request.get_json()
            auth_updated = {}
            auth_updated["id"] = id
            for key, value in req_parameters.items():
                if key in ["name", "age", "best_work"]:
                    setattr(author, key, value)
                    auth_updated[key] = value
            session.commit()

            return jsonify({"author": auth_updated})
    except Exception:
        abort(404)

This endpoint updates and returns an author identified by an id. To see this endpoint at work, Make an API call to update H.G Well’s best work:

~$ ~$ curl -i -H "Content-Type: application/json" -X PUT http://127.0.0.1:5000/authors/1 -d '{"name": "H.G Wells", "best_work": "The War of the Worlds"}'
HTTP/1.1 200 OK
Server: Werkzeug/2.3.6 Python/3.11.4
Date: Tue, 25 Jul 2023 13:15:22 GMT
Content-Type: application/json
Content-Length: 111
Connection: closed

{
    "author": {
        "id": 1,
        "name": "H.G Wells",
        "best_work": "The War of the Worlds"
    }
}

As seen, H.G Wells' best work changed from The Time Machine to The War of the Worlds. Confirm this change in the database:

  mysql> SELECT * FROM authors;
  +----+----------+-----+------------------------------------+
  | id | name     | age | best_work                          |
  +----+----------+-----+------------------------------------+
  | 1  |H.G Wells | 45  | The War of the Worlds              |
  +----+----------+-----+------------------------------------+
  | 2  |Jules Vern| 40  | Journey to the Center of the Earth |
  +----+----------+-----+-----------------------+------------+
  1 row in set (0.00 sec)

The database reflects the changes we made.

d. Delete (DELETE):

To delete a record in the database through API requests, you will also need to define an endpoint for it. Add these lines to your code:

@app.route("/authors/<int:id>", methods=["DELETE"])
def delete_author(id):
    try:
        author = session.query(Author).filter(Author.id == id).one()
        session.delete(author)
        session.commit()
        return jsonify({})

    except Exception:
        abort(404)

Run the application again and make a delete request at the terminal. It should return an empty dictionary. An empty dictionary shows that the request was successful. So let’s get rid of Jules Vern. :)

~$ curl -X DELETE http://127.0.0.1:5000/authors/2
   {}

When you query the database table, only H.G Wells’ records should be there:

  mysql> SELECT * FROM authors;
  +----+----------+-----+-----------------------+
  | id | name     | age | best_work             |
  +----+----------+-----+-----------------------+
  | 1  |H.G Wells | 45  | The War of the Worlds |
  +----+----------+-----+-----------------------+
  1 row in set (0.00 sec)

You have successfully made all four API calls. That’s basically how the CRUD operations work.

Here is the final code:

from flask import Flask
from flask import request, abort, jsonify
from model import Author, session

# Instantiate flask app
app = Flask(__name__)

@app.errorhandler(404)
def not_found(error):
    return jsonify({"error": "Not found"}), 404

@app.route("/", methods=["GET"])
def home():
    return "<h1>Hello Scifi Authors!</h1>"

@app.route("/add_author", methods=["POST"])
def create_author():
    if not request.json or "name" not in request.json:
        abort(404)
    else:
        author_dict = request.get_json()
        author = Author(name=author_dict["name"], age=author_dict["age"], best_work=author_dict["best_work"])
        session.add(author)
        session.commit()

        created_auth = {}
        created_auth["id"] = author.id
        for k, v in author_dict.items():
            created_auth[k] = v

        return jsonify({"author": created_auth}), 201

@app.route("/authors/<int:id>", methods=["GET"])
def get_author(id):
    """Get author by id"""
    try:
        author = session.query(Author).filter(Author.id == id).one()
        if author.id == id:
            auth_json = {}
            auth_json["name"] = author.name
            auth_json["age"] = author.age
            auth_json["best_work"] = author.best_work
            return jsonify({"author": auth_json})
    except Exception:
        abort(404)

@app.route("/authors/<int:id>", methods=["PUT"])
def update_author(id):
    """update author by id"""
    if not request.json or "name" not in request.json:
        return jsonify({"error": "Invalid parameters"})
    try:
        author = session.query(Author).filter(Author.id == id).one()
        if author.id == id:
            req_parameters = request.get_json()
            auth_updated = {}
            auth_updated["id"] = id
            for key, value in req_parameters.items():
                if key in ["name", "age", "best_work"]:
                    setattr(author, key, value)
                    auth_updated[key] = value
            session.commit()

            return jsonify({"author": auth_updated})
    except Exception:
        abort(404)

@app.route("/authors/<int:id>", methods=["DELETE"])
def delete_author(id):
    try:
        author = session.query(Author).filter(Author.id == id).one()
        session.delete(author)
        session.commit()
        return jsonify({})

    except Exception:
        abort(404)

if __name__ == "__main__":
    app.run(debug=True)

Conclusion

In this guide, you used the Flask framework to create RESTFUL APIs that serve content directly from a MySQL database. You also implemented the create, read, update and delete functionalities at the various endpoints that manage the specified request.

An In-depth Guide to Using Flask to Create Database-powered RESTFUL APIs

Table of contents