Build a Flask API with SQLite and JWT: Complete Tutorial

I am building an API because I don’t have time or the energy to build out a full application for my crew. Before I jumped off the deep end on building a Flask API, I first did the blog tutorial found in the documentation. The reasoning was to get use to the syntax and to quickly get up to speed. I found this really helpful when I did the tutorial on building RESTful APIs later.

The idea was that maybe I could tack on the social media posts to the content calendar within Google Sheets, so I don’t have to manually copy and paste what they output every friggin’ day of the week. I actually didn’t go this route, but I did help me understand how to implement APIs, troubleshoot them and error handling when I connect to one.

So I did what anyone else would do, I found a tutorial on LinkedIn Learning called, “Building RESTful APIs with Flask” that got straight to the point plus provided other tools to test it. Since I have a proclivity for thriftiness, I used my library’s access to take it for free.

HTTP Verbs

I vaguely remember the verbs for these and have built a few things that connect to an API, but I have never built one myself. For example, I called the Hashnode API to bring in my 3 most current blog posts into my portfolio website.

It makes it easier for me to remember them by comparing HTTP verbs to database CRUD verbs, as my background has been in data and digging around in databases.

HTTP response status codes

HTTP response status codes indicate whether a specific HTTP request has been successfully completed. Responses are grouped in five classes:

1. Informational responses (100 – 199)

2. Successful responses (200 – 299)

3. Redirection messages (300 – 399)

4. Client error responses (400 – 499)

5. Server error responses (500 – 599)

Simple API

Make sure to create a new environment and activate it, before installing Flask:

python3 -m venv venv
source venv/bin/activate
pip install Flask

Then create the app.py file, and import Flask with jsonify and request:

from flask import Flask, jsonify, request

app = Flask(__name__)

# ---------------------
#   DEFINE ENDPOINTS
# ---------------------
@app.route('/', methods=['GET', 'POST'])
def home():
    if (request.method == 'GET'):
        data = "hello world"
        return jsonify( {'data': data})


@app.route('/home/<int:num>', methods = ['GET'])
def display_result(num):
    return jsonify({'data': num**2})

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

First run Flask using this: python3 app.py, then open or create a split terminal to run a curl on localhost.

For the home function:

• Use this command: curl http://127.0.0.1:5000/, and it’ll return the message.

For the display_result function:

• Use this command: curl http://127.0.0.1:5000/home/3 and it’ll return 9, which is the square root of 3

The other thing to use is Postman.

Endpoints with parameters

This is where Postman comes in handy if you don’t want to type the url with the parameters.

@app.route('/parameters')
def parameters(): 
    name= request.args.get('name')
    age = int(request.args.get('age'))
    if age < 18: 
        return jsonify(message=f"Sorry you are not old enough, {name}"), 401
    else: 
        return jsonify(message=f"Hello {name}, you are old enough.")

If you want to use curl in Bash/terminal:

• Test both messages by just changing the age parameter: curl http://127.0.0.1:5000/parameters?name=Bryce&age=23

Pretty Endpoint parameters

To make the url less ugly and more modern and not hardcoded using rules eg. http://127.0.0.1:5000/Bryce/23 by using url variables with conversion filters:

1. Define the variables and data type in the url @app.route('/url_variables/<string:name>/<int:age>')

2. Then, make sure your method expects the same variables and that they have the data type also defined: def url_variables(name: str, age: int

# -------------------------------------------------------------
#       PARAMETERS WITH URL VARIABLES + CONVERSION FILTERS 
# -------------------------------------------------------------

@app.route('/url_variables/<string:name>/<int:age>') #flask func uses 'string' not python 'str'
def url_variables(name: str, age: int): # var rule matching, use python & specify type
    if age < 18: 
        return jsonify(message=f"Sorry you are not old enough, {name}"), 401
    else: 
        return jsonify(message=f"Hello {name}, you are old enough.")

Databases (SQLite) + ORM (SQLAlchemy)

I am going to use an ORM because it can support multiple database platforms so if I am going to sell the API code for distribution for on-premise solutions. Plus it makes it easier to control the structure and manage it using GIT, as well as being able to switch the database to something other than SQLite.

• Install the library: pip install -U Flask-SQLAlchemy

Configuration + Initialization

Add import statement for it and the data types you need for your project and configuration variables after the Flask instantiation and then initialize the database:

•             from flask_sqlalchemy import SQLAlchemy
              from sqlalchemy import Column, Integer, String, Float
              import os
  
              app = Flask(__name__)
  
              # app config section
              basedir = os.path.abspath(os.path.dirname(__file__))
              app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + os.path.join(basedir, 'planets.db')
  
              # app initialzer section
              db = SQLAlchemy(app)
  

Database models

Now these models can be entered into the same script, but you can put them into their own file and call them into the main script (recommended):

# ---------------------
#   DATABASE MODELS
# ---------------------

# you can move these to it's own file to maintain modular structure 
class User(db.Model): 
    __tablename__ = 'users' # control the table name when it gets generated by sqlalchemy
    # define fields
    id = Column(Integer, primary_key=True)
    first_name = Column(String)
    last_name = Column(String)
    email = Column(String, unique=True) # unique emails only
    password = Column(String)


class Planet(db.Model):
    __tablename__ = 'planets'
    planet_id = Column(Integer, primary_key=True)
    planet_name = Column(String, unique=True)
    planet_type = Column(String)
    home_star = Column(String)
    mass = Column(Float)
    radius = Column(Float)
    distance = Column(Float)

Creating + Drop + Seeding the DB

Add this under the database configuration at the top of the file:

# ------------------------------------
#   DATABASE CREATION + DROP + SEED
# ------------------------------------
@app.cli.command('db_create')
def db_create():
    db.create_all()
    print('Database created!')

@app.cli.command('db_drop')
def db_drop(): 
    db.drop_all()
    print('Database dropped.')

@app.cli.command('db_seed')
def db_seed():
    mercury = Planet(planet_name='Mercury',
                     planet_type='Class D',
                     home_star='Sol',
                     mass=3.258e23, 
                     radius=1516, 
                     distance=35.98e6)
    venus = Planet(planet_name='Venus',
                     planet_type='Class K',
                     home_star='Sol',
                     mass=4.867e24, 
                     radius=3760, 
                     distance=67.24e6)
    earth = Planet(planet_name='Earth',
                     planet_type='Class M',
                     home_star='Sol',
                     mass=5.972e24, 
                     radius=3959, 
                     distance=92.96e6)

    # Add these to the database
    db.session.add(mercury)
    db.session.add(venus)
    db.session.add(earth)

    # Add test user
    test_user = User(first_name="Will", 
                     last_name= 'Herschel',
                     email="will@foobar.com", 
                     password='foobar')

    db.session.add(test_user)

    # commit the seeded data:
    db.session.commit()
    print('Database seeded!')

Then use the commands below to create and seed (drop too):

• Create: flask db_create

• Seed: flask db_seed

• Drop: flask db_drop

To see the database, download DB Browser for SQLite brew install db-browser-for-sqlite

Then open it and find your .db file name, eg. planets.db to open the database in the DB Browser.

Create some queries

But need to deserialize the JSON so that it has a nice output. So need to install Marshmallow for Flask. pip install flask-marshmallow

1. Config Marshmallow: add import and create an instance of it at the top of the file (under the database instance).

from flask_marshmallow import Marchmallow

#...

# app initializer section
ma = Marchmallow(app)

2. Create the Marshmallow schemes that you need:

   
    class UserSchema(ma.SQLAlchemyAutoSchema):
        class Meta: 
            model=User
            load_instance = True
            fields = ('id', 'first_name', 'last_name', 'email')
   
    class PlanetSchema(ma.SQLAlchemyAutoSchema):
        class Meta: 
            model=Planet
            load_instance = True
            fields = ('planet_id','planet_name','planet_type','home_star','mass','radius', 'distance')
   
    # instantiate each schema
    user_schema = UserSchema()
    users_schema = UserSchema(many=True)
   
    planet_schema = PlanetSchema()
    planets_schema = PlanetSchema(many=True)
   

3. Create a new endpoint to pull all the planets using Marshmallow schema:

4.       # ------------------------------------------------
         #   Endpoint pulling data from DB + Marshmallow
         # ------------------------------------------------
         @app.route('/planets', methods=['GET'])
         def planets():
             planets_list = Planet.query.all()
             results = planets_schema.dump(planets_list) # use marshmallow to return it all
             # marshmallow >=3 returns the dumped data directly (not an object with `.data`)
             return jsonify(results)
   

Securing your API

Don’t use user management with sessions unless it’s going to be an actually application. Otherwise use JSON session tokens instead. JWT.io

Might want to do an approval workflow

Then, setup JWT using flask_jwt_extended:

from flask_jwt_extended import JWTManager, jwt_required, create_access_token

# app config section
app.config['JWT_SECRET_KEY'] = 'super-secret' # create a better secret key, this is just a placeholder

# app initializer section
jwt = JWTManager(app)

User registration

# ---------------------
#   USERS REGISTER
# ---------------------
@app.route('/register', methods=['POST'])
def register():
    email = request.form['email']
    test = User.query.filter_by(email=email).first()
    if test:
        return jsonify(message='That email already exists.'), 409
    else:
        first_name = request.form['first_name']
        last_name = request.form['last_name']
        password = request.form['password']
        user = User(first_name=first_name, last_name=last_name, email=email, password=password)
        db.session.add(user)
        db.session.commit()
        return jsonify(message="User created successfully."), 201

User Login

This code gives you the setup for both form data and JSON, you can decide which you’d rather implement for your API:

@app.route('/login', methods=['POST'])
def login():
    if request.is_json: 
        email = request.json['email']
        password = request.json['password']
    else: 
        email = request.form['email']
        password = request.form['password']

    test = User.query.filter_by(email=email, password=password).first()

    if test:    
        access_token = create_access_token(identity=email)
        return jsonify(message="Login successful", access_token=access_token)
    else: 
        return jsonify(message="Bad email or password"), 401

Setting up email

Install flask-mail.

from flask_mail import Mail, Message

# EMAIL CONFIG:
app.config['MAIL_SERVER'] = 'sandbox.smtp.mailtrap.io'
app.config['MAIL_USERNAME'] = os.environ['MAIL_USERNAME']
app.config['MAIL_PASSWORD'] = os.environ['MAIL_PASSWORD']

# INITIALIZE MAIL:
mail = Mail(app)

Sign up for MailTrap and use the sandbox to setup all the environment variables.

Then add to the configuration section of your app, the code here is from the tutorial, but if it doesn’t work just copy the code samples from MailTrap:

Build out the endpoint for sending a user’s password:

@app.route('/retrieve_password/<string:email>', methods=['GET'])
def retrieve_password(email: str):
    user = User.query.filter_by(email=email).first()
    if user: 
        msg = Message("Your password API is " + user.password, 
                      sender="admin@pinkphi.com",
                      recipients=[email])
        mail.send(msg)
        return jsonify(message="Password sent to " + email)
    else:
        return jsonify(message="That email doesn't exist")

Add environment variables by creating a .env file — need to figure this part out as I wasn’t able to get it to work.

Create, Read, Update & Delete

Create

The idea here is to first create an unprotected route to add/delete then protect it afterward, so here’s the implementation unprotected so that I can test it first:

@app.route('/add/planet', methods=['POST'])
def add_planet():
    planet_name = request.form['planet_name']
    test = Planet.query.filter_by(planet_name=planet_name).first()
    if test: 
        return jsonify("There's already a planet by that name"), 409
    else: 
        planet_type = request.form['planet_type']
        home_star = request.form['home_star']
        mass = float(request.form['mass'])
        radius = float(request.form['radius'])
        distance = float(request.form['distance'])

        new_planet = Planet(planet_name=planet_name, 
                            planet_type=planet_type,
                            home_star=home_star,
                            mass=mass,
                            radius=radius,
                            distance=distance)

        db.session.add(new_planet)
        db.session.commit()
        return jsonify(message="You added a planet"), 201

The setup in Postman to set up a new planet:

Then try to add one that is already in the database (like Mercury) and it should return 409.

To secure this just add @jwt_required after the route and we’ll need to pass in a valid JSON web token to indicate that the user is logged in:

@app.route('/add/planet', methods=['POST'])
@jwt_required() # <-- just adding this requires an access token
def add_planet():

1. First sign in AND then copy the access token:

   

2. Then add the access token to the authorization tab:

   

Read

Get a specific planet by id:

# --------------------------
#   Endpoint planet data - READ
# --------------------------
@app.route('/planet_details/<int:planet_id>', methods=["GET"])
def planet_details(planet_id:int):
    planet = Planet.query.filter_by(planet_id=planet_id).first()
    if planet:
        result = planet_schema.dump(planet)
        return jsonify(result)
    else:
        return jsonify(message="That planet does not exist"), 404

Update

You will need to use PUT, so that that you call everything correctly.

@app.route('/update_planet',methods=['PUT'])
def update_planet():
    planet_id = int(request.form['planet_id'])
    planet = Planet.query.filter_by(planet_id=planet_id).first()
    if planet: 
        planet.planet_name = request.form['planet_name']
        planet.planet_type = request.form['planet_type']
        planet.home_star = request.form['home_star']
        planet.mass = float(request.form['mass'])
        planet.radius = float(request.form['radius'])
        planet.distance = float(request.form['distance'])
        db.session.commit()
        return jsonify(message="you updated a planet"), 202
    else: 
        return jsonify(message="that planet doesn't exist"), 404
    #updating the object automatically so just commit it

The steps:

1. Looked for a planet I wanted to edit:

   

2. Then copied an existing call and just changed the url and added planet_id variable to the form so that I could change something about it:

   

   Once you get it to work, just add the jwt_required() after the app.route definition to require a login to update planets:

    @app.route('/update_planet',methods=['PUT'])
    @jwt_required()
   

Delete

Just add the JWT line and just make sure to include the access token before you make the call:

# DELETE/ DELETE
@app.route('/remove_planet/<int:planet_id>', methods=['DELETE'])
@jwt_required()
def remove_planet(planet_id: int):
    planet = Planet.query.filter_by(planet_id=planet_id).first()
    if planet:
        db.session.delete(planet)
        db.session.commit()
        return jsonify(message="You deleted a planet"), 202
    else:
        return jsonify(message="That planet does not exist"), 404

Exporting API

Make sure you are in the main project directory (so outside the API code). And make sure to freeze the requirements and save them to the pip freeze > requirements.txt

You can use PythonAnywhere, DigitalOcean, Green Unicorn and NGINX to deploy the API.

I’ll probably just use PythonAnywhere.

References

• Building RESTful APIs with Flask

• Postman

• MailTrap

• DB Browser for SQLite