Building a RESTful API with Flask, Flask-RESTful, SQLAlchemy and pytest

Fantasy Sports offer all kinds of statistics and data to sort through. However, finding a free, reliable, easily accessible source for all of this data can be hard. Instead, let’s implement our own Fantasy Football API using Flask, Flask-RESTful, Flask-SQLAlchemy and pytest! Buckle up, this is going to be a long post.

Bob's Burgers Buckle Up

Note: This post is strictly a tutorial on building a REST API using Python. I will not cover how to make a frontend application in this post. Despite no frontend, there will be A LOT of code to read. Lastly, this post assumes you have some knowledge of Python, RESTful API design and development, and writing tests. You don’t need to be an expert on any of these topics, but some of the content might go over your head if they are brand new to you.

What is REST

Before we dig into any code let’s discuss what REST is. Per its Wikipedia definition, REST, or REpresentational State Transfer, is a software architectural style that defines a set of constraints to be used for creating Web services. Web services that conform to the REST architectural style, called RESTful Web services, provide interoperability between computer systems on the internet.

REST, to me, boils down to a set of conventions using the HTTP protocol to provide the standard CRUD (Create, Read, Update, Delete) behaviors on objects and collections of those objects. The standard HTTP verbs map to CRUD as so:

| CRUD        | HTTP Verb   |
| ----------- | ----------- |
| Create      | POST        |
| Read        | GET         |
| Update      | PUT/PATCH   |
| Delete      | DELETE      |

CRUD actions are typically performed on resources. Resources typically represent the nouns in our API, such as Players, Teams, Seasons, and Stats. CRUD represents the verbs we do with these nouns. This paradigm is essential in creating clean, pragmatic RESTful APIs.

Initial Setup

If you completed one of my previous projects from my other Python posts you should already have Python 3 and pipenv installed. However, if this is your first Python project with me then you will need to install Python 3 as well as pipenv.

Once Python and pipenv are both installed we should create our project folder by entering the following in a terminal window:

# This assumes you have a "Desktop" directory.
# Feel free to put your project folder anywhere you see fit
$ cd ~/Desktop
$ mkdir flask_rest_api && cd flask_rest_api

Open the flask_rest_api directory in your favorite code editor and create a new Pipfile:

name = "pypi"
url = ""
verify_ssl = true

black = "==20.8b1"
pytest = "==6.1.2"
pytest-cov = "==2.10.1"
pytest-sugar = "==0.9.4"

flask = "==1.1.2"
flask-restful = "==0.3.8"
flask-sqlalchemy = "==2.4.4"
marshmallow = "==3.9.1"

python_version = "3.8"

Please note, the above Pipfile calls for Python 3.8. Ensure you have at least Python 3.8 installed before proceeding.

Now, go back to the flask_rest_api directory in your terminal window and install everything using pipenv. Once pipenv has finished installing all of our dependencies we can activate our virtualenv and start working.

# Install all dependencies in our Pipfile
$ pipenv install --dev

# Then activate our virtualenv
$ pipenv shell
Project Structure

We now have a Pipfile and Pipfile.lock in our project’s directory. Let’s create a few more directories and files, laying out our project structure like so:

├── football_api/
│   ├──
│   ├──
│   ├──
│   ├──
│   └── models/
|       └──
│   └── resources/
|       └──
│   └── schemas/
|       └──
├── tests/
|   └── integration/
├── Pipfile
├── Pipfile.lock
└── fantasy_football.db <--- We will download this file below.

Above, we see a file named fantasy_football.db. This file is our SQLite database containing a small amount of football stats data to test our API with. You can download this test database here.

We now have all of our libraries installed, a test database, and a solid project structure. Let’s write some code.

Flask-SQLAlchemy and our Data Models

Flask-SQLAlchemy is an extension for Flask that adds support for SQLAlchemy to a Flask application. It aims to simplify using SQLAlchemy with Flask by providing useful defaults and extra helpers that make it easier to accomplish common tasks. Open and create our SQLAlchemy instance. This object is used to control the SQLAlchemy integration to our Flask application:

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

Next, we should define our Flask-SQLAlchemy Models. Models represent our table definitions. Each Model typically consists of Columns, and these Columns can be marked as primary keys, define relationships, and more.

We need four models, Player, Season, Stats, and Team. All of our models should be added to the football_api/models package. To keep this post a bit shorter I am only going to post code regarding Players. All code related to the Season, Stats and Team objects can be found on GitHub. Our Player model should look as follows:

# football_api/models/
from football_api.database import db

class Player(db.Model):
    Player Flask-SQLAlchemy Model

    Represents objects contained in the players table

    __tablename__ = "players"

    player_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(), nullable=False)
    position = db.Column(db.String(), nullable=False)

    stats = db.relationship("Stats", back_populates="player")

    def __repr__(self):
        return (
            f"**Player** "
            f"player_id: {self.player_id} "
            f"name: {} "
            f"position: {self.position}"
            f"**Player** "

The above Model defines each column found in the players table, defines any necessary relationships, and concludes by overriding the __repr__ built-in function, letting us define how we want our Player objects to look when logged.

I’d like to comment on this line above: __tablename__ = "players". Sometimes the names of our tables don’t sync up with our actual Model names. Why is this? Well, I prefer to name my database tables the plural of the noun they contain. For example, the table containing Player data is named players. Our Models however, represent an instance of a Player in our database, thus I prefer to name the Models singular when it makes sense. We need to explicitly set our Model’s tablename for such an occurrence, as Flask-SQLAlchemy assumes the Model name and table name are the same.

Note: The remaining Flask-SQLAlchemy Models for Season, Stats, and Team can be found here.

We now have an easily accessible SQLAlchemy instance and all of our data Models defined, thus concluding all of our boilerplate code to work with Flask-SQLAlchemy. Onto object serialization!

Marshmallow – Simplified Object Serialization

Before we dig into our API implementation we will implement our Marshmallow schemas. Marshmallow is an ORM/ODM/framework-agnostic library for converting complex datatypes, such as objects, to and from native Python datatypes. There are many reasons to use Marshmallow. We will be using Marshmallow for the marshalling and unmarshalling of our data, as well as some very basic data validation.

Similar to our Models, we once again need four Schemas for Player, Season, Stats, and Team. All of our Schemas should be added to the schemas package. The Marshmallow Schema representing a Player, named PlayerSchema below, should look as follows:

# football_api/schemas/
from marshmallow import Schema, fields, post_load
from football_api.models.player import Player

class PlayerSchema(Schema):
    Player Marshmallow Schema

    Marshmallow schema used for loading/dumping Players

    name = fields.String(allow_none=False)
    position = fields.String(allow_none=False)
    player_id = fields.Integer()

    def make_player(self, data, **kwargs):
        return Player(**data)

We define all fields that make up the PlayerSchema similarly to the way in which the fields were defined for the Flask-SQLAlchemy Player Model. With our schemas in place we now have the ability to easily dump, load, and validate our data coming and going from our API! Additionally, each of our schemas containts a @post_load decorated method. Each of these decorated methods is called when deserializing data, loading and returning the corresponding Model object for each Schema.

Note: The remaining Marshmallow Schemas for Season, Stats, and Team can be found here.

Up to this point we have our data Models, and Marshmallow Schemas. It is now time to write our API Resources.

Implementing our Flask-RESTful API Resources

When it comes to web development in Python there are two frameworks ahead of the pack: Django and Flask. Django’s old saying used to be “Batteries included”, meaning Django comes with everything you need to quickly get your web application up and running. Django has a certain way of doing things, and for the most part you must follow its guidelines. Flask, however, markets itself as a “micro framework” with very few guidelines. Flask keeps its core simple yet extensible. We are building a simple RESTful API, thus Flask is perfect for our needs.

Per its official definition, Flask is a lightweight WSGI web application framework. It is designed to make getting started quick and easy. Flask offers suggestions, but doesn’t enforce any dependencies or a project layout. It is up to us, the developers, to choose the tools and libraries we want to use. We already chose two of those tools in Flask-SQLAlchemy and Marshmallow. Next is Flask-RESTful.

Flask has everything we need out of the box to start building our API. However, I design APIs a certain, [pragmatic way]), and Flask-RESTful has a lot in common with how I prefer to design and develop my RESTful APIs. As mentioned above, Flask is very extensible, and Flask-RESTful is an extension for Flask that adds support for quickly building REST APIs. It is a lightweight abstraction that works with your existing ORM/libraries. Flask-RESTful encourages best practices with minimal setup.

The main API building block provided by Flask-RESTful that I love most is Resources. Flask-RESTful Resources give us quick access to the basic HTTP verbs/methods (GET, PUT, POST, PATCH, DELETE) just by defining them as methods in our Resources. Our API needs a few Resources, all centered around our Player, Season, Stats and Team objects. Let’s take a look at the PlayersResource implementation:

import logging

from flask import request
from flask_restful import Resource, abort
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm.exc import NoResultFound

from football_api.database import db
from football_api.models.player import Player
from football_api.schemas.player_schema import PlayerSchema

PLAYERS_ENDPOINT = "/api/players"
logger = logging.getLogger(__name__)

class PlayersResource(Resource):
    def get(self, id=None):
        PlayersResource GET method. Retrieves all players found in the Football
        Stats database, unless the id path parameter is provided. If this id
        is provided then the player with the associated player_id is retrieved.

        :param id: Player ID to retrieve, this path parameter is optional
        :return: Player, 200 HTTP status code
        if not id:
            position = request.args.get("position")
                f"Retrieving all players, optionally filtered by position={position}"

            return self._get_all_players(position), 200"Retrieving player by id {id}")

            return self._get_player_by_id(id), 200
        except NoResultFound:
            abort(404, message="Player not found")

    def _get_player_by_id(self, player_id):
        player = Player.query.filter_by(player_id=player_id).first()
        player_json = PlayerSchema().dump(player)

        if not player_json:
            raise NoResultFound()"Player retrieved from database {player_json}")
        return player_json

    def _get_all_players(self, position):
        if position:
            players = Player.query.filter_by(position=position).all()
            players = Player.query.all()

        players_json = [PlayerSchema().dump(player) for player in players]"Players successfully retrieved.")
        return players_json

    def post(self):
        PlayersResource POST method. Adds a new Player to the database.

        :return: Player.player_id, 201 HTTP status code.
        player = PlayerSchema().load(request.get_json())

        except IntegrityError as e:
                f"Integrity Error, this team is already in the database. Error: {e}"

            abort(500, message="Unexpected Error!")
            return player.player_id, 201

The above code contains two methods, a get method and a post method. The constant PLAYERS_ENDPOINT is later used when defining our Flask-RESTful resource upon API startup. The methods for this Resource are all invoked via the /api/players endpoint (http://localhost:5000/api/players, for example).

The POST method reads a JSON request, and converts the incoming JSON to a Player model via our PlayerSchema and its load method. Once we have a valid Player Model we add it to the database using Flask-SQLAlchemy, and finish up by returning the new Player’s Id, and a successful 201 HTTP status code. If the Player is already in the database we catch an IntegrityError, log it, and return a 500 HTTP status code to the user.

The GET method has a bit more going on, but it is still rather simple. First, as previously mentioned, this method will be accessed via the api/players endpoint. This endpoint can also take an optional id parameter in the URL path like so: api/players/1. The former endpoint will return all players in our database, while the latter endpoint containing the path parameter will return just the player with the player_id that matches the supplied id. If an id parameter is provided but a matching Player cannot be found our method returns a 404 NOT FOUND HTTP status code. If an id is not provided, meaning all players should be returned we also check for the optional position query parameter. If the position query parameter is provided then the players with the provided position will only be returned. Such a request might look something like this: http://localhost:5000/players?position=QB. This request would return all players that play the QB (quarterback) position in our database.

That quickly sums up a Flask-RESTful Resource. We have a POST, and a GET method and could easily add a DELETE, PUT, or PATCH method!

Note: The remaining Flask-RESTful Resources for Season, Stats, and Team can be found here.

Running our API

We have written our Flask-SQLAlchemy Models, Marshmallow Schemas, and Flask-RESTful Resources. All that is left to do is run our API and try it out. Let’s write a simple Python script that initializes our Flask application, defines our Flask-RESTful API and is runnable from the command line:

import logging
import sys
from os import path


from flask import Flask
from flask_restful import Api

from football_api.constants import PROJECT_ROOT, FANTASY_FOOTBALL_DATABASE
from football_api.database import db
from football_api.resources.players_resource import PlayersResource, PLAYERS_ENDPOINT
from football_api.resources.seasons_resource import SeasonsResource, SEASONS_ENDPOINT
from football_api.resources.stats_resources import (
from football_api.resources.teams_resource import TeamsResource, TEAMS_ENDPOINT

def create_app(db_location):
    Function that creates our Flask application.
    This function creates the Flask app, Flask-RESTful API,
    and Flask-SQLAlchemy connection

    :param db_location: Connection string to the database
    :return: Initialized Flask app
    # This configures our logging, writing all logs to the file "football_api.log"
        format="%(asctime)s %(name)-12s %(levelname)-8s %(message)s",
        datefmt="%m-%d %H:%M",
        handlers=[logging.FileHandler("football_api.log"), logging.StreamHandler()],

    app = Flask(__name__)
    app.config["SQLALCHEMY_DATABASE_URI"] = db_location

    # The lines below assume you grabbed the other models, schemas and resources
    # not covered in this blog post. Please see my GitHub repo to find the code
    api = Api(app)
    api.add_resource(PlayersResource, PLAYERS_ENDPOINT, f"{PLAYERS_ENDPOINT}/<id>")
    api.add_resource(SeasonsResource, SEASONS_ENDPOINT)
    api.add_resource(StatsResource, STATS_ENDPOINT)
    api.add_resource(StatsPlayerResource, STATS_PLAYER_ENDPOINT)
    api.add_resource(StatsSeasonResource, STATS_SEASON_ENDPOINT)
    api.add_resource(TeamsResource, TEAMS_ENDPOINT, f"{TEAMS_ENDPOINT}/<id>")
    return app

if __name__ == "__main__":
    app = create_app(f"sqlite:////{PROJECT_ROOT}/{FANTASY_FOOTBALL_DATABASE}")

The above script assumes you downloaded the Models, Schemas and Resources omitted from this post for our Season, Stats and Teams objects.

Before we can run the API we have one last finishing touch to make. I like to create a file in my projects when I have a few constant variables that need to be accessed from various places in the code. Open the file I had us make when creating the project layout and add these couple of constants:

from pathlib import Path

PROJECT_ROOT = Path(__file__).parent.parent

FANTASY_FOOTBALL_DATABASE = "fantasy_football.db"

We can now start our API from the command line using our script. Run the following command from our project’s root directory to start the API:

$ python football_api/

Assuming the API started successfully you should now be able to test it out in your favorite browser, or via a curl command:

$ curl http://localhost:5000/api/players/1
    "name": "Alvin Kamara",
    "player_id": 1,
    "position": "RB"

Our API is up and running and seems to be in working order! Pat yourself on the back, that was a lot of work! However, we’re not done yet…

Testing our API using pytest

Testing our code could make for its very own blog post. However, what good is all of this code if we don’t write tests for it now? Everything that’s not tested will break, so we really should write some tests. I strongly prefer pytest for all my Python testing needs. pytest leads to short, easy-to-read tests, and often lend themselves better to both reusability and extensibility.

Before we start writing our tests we will need to do some setup. pytest offers a tool called fixtures which are functions attached to tests which run before our test functions are run. These fixtures can be defined in an individual test file, or in a file. Any fixture defined in a file can be used by all tests in the same directory or sub-directoy the lives in.

Our tests would benefit from a fixture that copies our SQLite database file to the pytest tmp_path directory, and creates a Flask test_client for our testing needs:

from shutil import copy

import pytest
from football_api.api import create_app
from football_api.constants import FANTASY_FOOTBALL_DATABASE, PROJECT_ROOT

def client(tmpdir):
    copy(f"{PROJECT_ROOT}/{FANTASY_FOOTBALL_DATABASE}", tmpdir.dirpath())

    temp_db_file = f"sqlite:///{tmpdir.dirpath()}/{FANTASY_FOOTBALL_DATABASE}"

    app = create_app(temp_db_file)
    app.config["TESTING"] = True

    with app.test_client() as client:
        yield client

If you are curious about the use of the built-in pytest fixture tmpdir let me explain. This fixture gives us access to a temporary directory which pytest will automatically cleanup for us upon test completion. Any files or data that get placed into this directory will only persist for the life of the test using this fixture. This is very handy, and makes for a great spot to place a test database, as our tests will be messing around with our data.

pytest expects all test files to start with either test_ or end with I prefer the prefix, so let’s create a new test file,, in our tests/integration folder and write our tests:

from football_api.resources.players_resource import PLAYERS_ENDPOINT


def test_players_post(client):
    new_player_json = {"name": "D.K. Metcalf", "position": "WR"}
    response ="{PLAYERS_ENDPOINT}", json=new_player_json)
    assert response.status_code == 201

def test_players_post_error(client):
    missing_pos_json = {"name": "Deandre Hopkins"}
    response ="{PLAYERS_ENDPOINT}", json=missing_pos_json)
    assert response.status_code == 500

def test_get_all_players(client):
    response = client.get(f"{PLAYERS_ENDPOINT}")
    assert response.status_code == 200
    assert len(response.json) == NUM_PLAYERS_IN_BASE_DB

def test_get_all_players_by_position(client):
    response = client.get(f"{PLAYERS_ENDPOINT}?position=QB")

    for player in response.json:
        assert player["position"] == "QB"

def test_get_single_player(client):
    response = client.get("/api/players/1")

    assert response.status_code == 200
    assert response.json["name"] == "Alvin Kamara"

def test_get_single_player_not_found(client):
    response = client.get("/api/players/16")
    assert response.status_code == 404

Note: You might notice we are writing integration tests as opposed to unit tests. Our tests are invoking a running Flask application which connects to a database. Write tests. Not too many. Mostly Integration

The above test module thoroughly tests the PlayersResource class. Each function is an isolated test, testing a small portion of the code found in Ideally, we would run this test often when making changes to our code, ensuring any new code causes no negative anywhere. Each test passes in our client pytest fixture found in our file, allowing each test to access our database and Flask API. pytest automatically finds this fixture for us, and there is no need to import it.

We can run all tests from the command line with the following command from our project’s root directory:

$ pytest tests

If you want to run an individual test file and not all tests in a directory it can be done as so:

$ pytest tests/integration/

Lastly, we can also run an individual test in a test file:

$ pytest tests/integration/

Our Pipfile installed a couple of pytest plugins. The first worth noting is pytest-sugar, a simple plugin that prettifies our pytest output. Second is pytest-cov, a plugin which shows us how much of our code is covered by our tests. We can run our coverage report from our project’s root directory, with the following command:

$ pytest --cov football_api

If successful, we should see coverage output similar to this:

---------- coverage: platform darwin, python 3.8.2-final-0 -----------
Name                                         Stmts   Miss  Cover
football_api/                         0      0   100%
football_api/                             28      2    93%
football_api/                        3      0   100%
football_api/                         2      0   100%
football_api/models/                  0      0   100%
football_api/models/                    9      1    89%
football_api/models/                    6      0   100%
football_api/models/                    30      0   100%
football_api/models/                      9      0   100%
football_api/resources/               0      0   100%
football_api/resources/      44      0   100%
football_api/resources/      27      0   100%
football_api/resources/       46      3    93%
football_api/resources/        44      0   100%
football_api/schemas/                 0      0   100%
football_api/schemas/            9      0   100%
football_api/schemas/            7      0   100%
football_api/schemas/            34      0   100%
football_api/schemas/              9      0   100%
TOTAL                                          307      6    98%

Results (1.46s):
      20 passed

Our coverage of 98% is quite high, but is it high enough? I’ll leave that to you to decide.. If you want to see the remaining tests pertaining to Seasons, Stats and Teams they can be found on GitHub here!

Note: Some might say we did things backwards and should have written our tests first and then our API code. This is known as Test Driven Development, or TDD, and can be pretty powerful.


Thus concludes an introduction to building a Python Flask API using Flask-RESTful, Flask-SQLAlchemy and a few other useful libraries. If you’re looking for the source code found in this post, as well as the additional code for the entire API it can be found on GitHub. A lot of the topics discussed here could warrant their very own blog post, but we’re far enough down the rabbithole now where you should be able to explore on your own!



Get updates on new content straight to your inbox! Unsubscribe at anytime.

* indicates required