Creating an API REST with Python, Flask and SQLite3

In this Python programming tutorial you will learn how to create a REST API using Flask, SQLite 3 (for data), and JSON for data communication.

In the end you will have an API that you can consume from any client that could be an Android application, a browser with JavaScript or even another server language.

I will show you how to create an API that communicates using JSON and saves the data in SQLite 3. The API will be created with Flask, and we will use the Python programming language to handle all the data.

API description

We are going to use the 4 most used HTTP verbs: GET, POST, PUT and DELETE, which will be related to the CRUD of the database.

What we are going to manage will be a database of games, which have a name, price and rate (or rating). We will also have several operations that we are going to expose through the API created with Flask:

• Get all the games
• Create a new game
• Update a game
• Delete a game
• Get a game by ID

First we are going to create the CRUD that is related to the database using Python, and later we are going to expose all these functions with Flask in the API, encoding as JSON.

SQLite3 database

The structure of the database is as seen below. It is a single table, although from this tutorial we can create more tables, relationships, and so on.

CREATE TABLE IF NOT EXISTS games(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  price REAL NOT NULL,
  rate INTEGER NOT NULL
)

Then we see the connection file to the database:

import sqlite3
DATABASE_NAME = "games.db"


def get_db():
    conn = sqlite3.connect(DATABASE_NAME)
    return conn


def create_tables():
    tables = [
        """CREATE TABLE IF NOT EXISTS games(
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
    price REAL NOT NULL,
    rate INTEGER NOT NULL
            )
            """
    ]
    db = get_db()
    cursor = db.cursor()
    for table in tables:
        cursor.execute(table)

In this SQLite3 connection file with Python we see that the database will be called games.db. In addition, we have two functions: one of them is to obtain the database, and the other (create_tables) is to create the tables within the database only if they do not exist.

Take a good look at these functions, as we are going to import them from other files. Now that we have the database defined, let’s see the CRUD of games with the SQLite3 database.

Game controller: database connection

Before exposing the database in the API, we are going to create a game controller that will take care of all the operations to save, update, delete and get the game data.

All these functions are inside a file called game_controller.py and it looks like this:

from db import get_db


def insert_game(name, price, rate):
    db = get_db()
    cursor = db.cursor()
    statement = "INSERT INTO games(name, price, rate) VALUES (?, ?, ?)"
    cursor.execute(statement, [name, price, rate])
    db.commit()
    return True


def update_game(id, name, price, rate):
    db = get_db()
    cursor = db.cursor()
    statement = "UPDATE games SET name = ?, price = ?, rate = ? WHERE id = ?"
    cursor.execute(statement, [name, price, rate, id])
    db.commit()
    return True


def delete_game(id):
    db = get_db()
    cursor = db.cursor()
    statement = "DELETE FROM games WHERE id = ?"
    cursor.execute(statement, [id])
    db.commit()
    return True


def get_by_id(id):
    db = get_db()
    cursor = db.cursor()
    statement = "SELECT id, name, price, rate FROM games WHERE id = ?"
    cursor.execute(statement, [id])
    return cursor.fetchone()


def get_games():
    db = get_db()
    cursor = db.cursor()
    query = "SELECT id, name, price, rate FROM games"
    cursor.execute(query)
    return cursor.fetchall()

In the file we see several functions. The insert_game function receives the game data and inserts it into the database (INSERT); all this using prepared statements to avoid SQL injections in this API that we are creating with Python and Flask.

We also see other methods such as update_game that performs the UPDATE operation to update a game, delete_game that deletes a game (DELETE) from its id, get_by_id that returns a game from its id (using the SELECT operation).

Finally we look at the get_games function that returns all existing games.

Note that all functions use the database and a cursor to perform all operations.

Now that we have the CRUD of the operations with the database, it is time to expose everything in the API with Flask.

Creating the API with Flask and Python

The first thing we do in the API is create the Flask app and import the games controller. We also import a function from the database because we need to create the tables when starting the application:

from flask import Flask, jsonify, request
import game_controller
from db import create_tables

app = Flask(__name__)

Now we define the routes with the GET, PUT, POST and DELETE http verbs:

@app.route('/games', methods=["GET"])
def get_games():
    games = game_controller.get_games()
    return jsonify(games)


@app.route("/game", methods=["POST"])
def insert_game():
    game_details = request.get_json()
    name = game_details["name"]
    price = game_details["price"]
    rate = game_details["rate"]
    result = game_controller.insert_game(name, price, rate)
    return jsonify(result)


@app.route("/game", methods=["PUT"])
def update_game():
    game_details = request.get_json()
    id = game_details["id"]
    name = game_details["name"]
    price = game_details["price"]
    rate = game_details["rate"]
    result = game_controller.update_game(id, name, price, rate)
    return jsonify(result)


@app.route("/game/<id>", methods=["DELETE"])
def delete_game(id):
    result = game_controller.delete_game(id)
    return jsonify(result)


@app.route("/game/<id>", methods=["GET"])
def get_game_by_id(id):
    game = game_controller.get_by_id(id)
    return jsonify(game)

Each path exposes a game controller function that we saw earlier, which in turn interacts with the SQLite3 database. It is important to highlight a few things. For example, when updating and inserting a game we read the JSON of the request with get_json and we access the dictionary.

In the case of deleting or obtaining by ID we read the variable id from the path as <variable> and receiving it in the method.

Also note that this API with Python communicates through JSON, so all responses are made according to what the jsonify function returns.

Finally we create the Flask app to start the server and listen to requests:

if __name__ == "__main__":
    create_tables()
    """
    Here you can change debug and port
    Remember that, in order to make this API functional, you must set debug in False
    """
    app.run(host='0.0.0.0', port=8000, debug=False)

Optional: add CORS

If you are going to consume this API from a domain other than where the API is listening, you need to enable CORS. Just add the following code snippet in the API (in the repository you will find the code already added, which you can remove if you want):

"""
Enable CORS. Disable it if you don't need CORS
"""
@app.after_request
def after_request(response):
    response.headers["Access-Control-Allow-Origin"] = "*" # <- You can change "*" for a domain for example "http://localhost"
    response.headers["Access-Control-Allow-Credentials"] = "true"
    response.headers["Access-Control-Allow-Methods"] = "POST, GET, OPTIONS, PUT, DELETE"
    response.headers["Access-Control-Allow-Headers"] = "Accept, Content-Type, Content-Length, Accept-Encoding, X-CSRF-Token, Authorization"
    return response

Putting it all together

The full code of this API created with Flask and SQLite3 is like this:

"""
    API REST con Python 3 y SQLite 3
    By Parzibyte: 
    ** https://parzibyte.me/blog **
"""
from flask import Flask, jsonify, request
import game_controller
from db import create_tables

app = Flask(__name__)


@app.route('/games', methods=["GET"])
def get_games():
    games = game_controller.get_games()
    return jsonify(games)


@app.route("/game", methods=["POST"])
def insert_game():
    game_details = request.get_json()
    name = game_details["name"]
    price = game_details["price"]
    rate = game_details["rate"]
    result = game_controller.insert_game(name, price, rate)
    return jsonify(result)


@app.route("/game", methods=["PUT"])
def update_game():
    game_details = request.get_json()
    id = game_details["id"]
    name = game_details["name"]
    price = game_details["price"]
    rate = game_details["rate"]
    result = game_controller.update_game(id, name, price, rate)
    return jsonify(result)


@app.route("/game/<id>", methods=["DELETE"])
def delete_game(id):
    result = game_controller.delete_game(id)
    return jsonify(result)


@app.route("/game/<id>", methods=["GET"])
def get_game_by_id(id):
    game = game_controller.get_by_id(id)
    return jsonify(game)


if __name__ == "__main__":
    create_tables()
    """
    Here you can change debug and port
    Remember that, in order to make this API functional, you must set debug in False
    """
    app.run(host='0.0.0.0', port=8000, debug=False)

If you want to see the code for the other files and the full repository, visit my GitHub. There you can download and test all the open source code.

Remember that to start the server and the API you must execute:

python main.py

Or failing that:

python3 main.py

Testing the API

After cloning and running the repository you can run the tests using Postman or any language of your choice. Later I will bring examples of consumption with JavaScript Frameworks or with pure JavaScript.

Meanwhile I leave you a capture testing the API:

If you like Python, I invite you to read more content on that topic on my blog. Or learn more about Flask.