Microservice using Python, Flask and SQLite Database

In this article, I will develop a Microservice Application using Python Language with Flask Framework and SQLite Database and expose all CRUD (Create, Read, Update, Delete) Operations as the REST APIs.

Flask Framework will serve as back-end server and I will be using Relational Database known as SQLite Database, for persisting(storing) the data.

By the end of this article, you would learn about how to:

• Use SQLAlchemy to work with Relational Database and persist the data.
• Create and Expose REST APIs for CRUD Operations using Flask Framework.
• How to handle HTTP Requests through REST Controller.

Overview

We will build a Python Flask Rest CRUD API for a Todo Task Application in that:

• Each Todo Task has id, title, description, creation date, due date, status and comments.
• APIs help to Create, Read, Update, Delete Todo Tasks.

Below mentioned are the REST APIs for CRUD Operations.

If you have no time to read this article, but want to try the code for yourself, GitHub location is provided here. Go ahead and clone the code repository.

Definitions

Python is an interpreted, object-oriented, high-level programming language with dynamic semantics. Its high-level built in data structures, combined with dynamic typing and dynamic binding, make it very attractive for Rapid Application Development, as well as for use as a scripting or glue language to connect existing components together. Python supports modules and packages, which encourages program modularity and code reuse.

Flask is a web framework, it’s a Python module that lets you develop web applications easily. It’s has a small and easy-to-extend core: it’s a microframework that doesn’t include an ORM (Object Relational Manager) or such features. Flask is a web application framework written in Python. Flask is based on the Werkzeg WSGI toolkit and the Jinja2 template engine.

SQLAlchemy is a library that facilitates the communication between Python programs and databases. SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL.

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. SQLite is the most used database engine in the world. SQLite is built into all mobile phones.

Microservices is an architecture that allows the developers to develop and deploy services independently. Each service running has its own process and this achieves the lightweight model to support business applications. One of the most popular types of APIs for building Microservices applications is known as “RESTful API” or “REST API”.

REpresentational State Transfer (REST) is an architectural style that defines a set of constraints to be used for creating web services. REST API is a way of accessing web services in a simple and flexible way without having any processing. All communication done via REST API uses only HTTP request.

Application Programming Interface (API) is a software intermediary that allows two applications to talk to each other. To simplify, an API delivers a user requests to a system and sends the system’s response back to a user.

Prerequisites

There are some prerequisites that are required for creating the Microservice Using Python, Flask and SQLite Database Application.

Familiarity with Technology and Frameworks

It is assumed that you have prior knowledge or familiarity with Python Language, Flask Framework, working with RDBMS databases and basic SQL commands, because I will not be covering the basics of these in this article.

If you are not familiar, then it is advised to get the basic knowledge of these before continuing.

Install Python

Install latest version for your platform from here. Select the latest version of Python 3 and download the Windows Installer. Click on the downloaded .exe and follow the on-screen instructions to complete the download.

Integrated Development Environment (IDE) for Code Development

You can use any Text Editor or IDE of your choice. I will be using the Visual Studio Code.

If you wish to use the Visual Studio Code, download the latest version from here. Click on the downloaded .exe and complete the installation.

Install the Python Extension for the VS Code

Install the Python extension for VS Code from the Visual Studio Marketplace.

1. You can browse and install extensions from within VS Code. Bring up the Extensions view by clicking on the Extensions icon in the Activity Bar on the side of VS Code or the View: Extensions command (Ctrl+Shift+X).
2. Browse for Python and click on Install button.

Create Base Project

An important concept while working with Python projects is a virtual environment. It allows you to create an isolated Python environment, where all required packages for the project are installed.

Create a Project Environment

1. Open command prompt and go to the folder where you want to create the Python Project and type following command:

mkdir microservice-python-flask-sqlite
cd microservice-python-flask-sqlite

2. In that folder, use the following commands (one after another) to create and activate a virtual environment named .venv:

py -3 -m venv .venv
.venv\Scripts\activate

If the activate command generates the message Activate.ps1 is not digitally signed. You cannot run this script on the current system., then you need to temporarily change the PowerShell execution policy to allow scripts to run (see About Execution Policies in the PowerShell documentation):

Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope Process

3. Open the project folder in VS Code by running code ., or by running VS Code and using the File > Open Folder command.
4. In VS Code, open the Command Palette (View > Command Palette or (Ctrl+Shift+P)). Then select the Python: Select Interpreter command:

   

5. The command presents a list of available interpreters that VS Code can find automatically, including virtual environments. Select the folder that starts with .venv

   

6. Run Terminal: Create New Terminal (Ctrl+Shift+`)) from the Command Palette, which creates a terminal and automatically activates the virtual environment by running its activation script.

Install Flask and SQLAlchemy

Use the inbuilt Python package manager tool pip for installing dependencies. In your VS Code terminal enter the following one after another.

pip install flask
pip install flask-sqlalchemy

Setting up an initial Flask server

• Create a new Python file in project root directory with a preferred name. Let us call it app.py
• Import Flask class and create its instance for this application

1from flask import Flask
2app = Flask(__name__)
3@app.route('/')
4def hello():
5    return 'Hello!'

• Set the FLASK_APP Environment variable using the command:

$env:FLASK_APP="app.py"

• Start the server using the command:

flask run

• When you navigate to http://localhost:5000/ you should see ‘Hello!’ on the browser

  

Change the port

As we have seen, default port for Python Flask Application Server is 5000. However, we can change the port.

Start the server using the command:

flask run --port 8080 

This would start the Python Flask Application Server on the port 8080.

Now that we have base flask app setup complete, lets dive in and start designing our Todo Task Application project.

Enhance the Existing Application to Expose REST APIs

It is now time to enhance our project to include REST API endpoints, so that they can be exposed for consumption by other applications.

Adding Database

We will first setup the database for persisting the data. I will be using Relational Database known as SQLite Database. You can extend this to use any other Relational Database and only Database configurations needs to be changed.

For completing this setup, we will need to update/create few files. Let us go ahead and complete that.

Create /__init__.py

In the Project root folder create the file __init__.py. This will initialize the SQLite Database using the SQLAlchemy Module.

1from sqlalchemy import create_engine, MetaData
2from sqlalchemy.ext.declarative import declarative_base
3
4SQLALCHEMY_DATABASE_URI= "sqlite:///todo-app.sqlite"
5engine = create_engine(SQLALCHEMY_DATABASE_URI, echo = True)
6meta = MetaData()
7Base = declarative_base()

Create /models.py

This file will hold all the models that we will creating for this project

 1from . import engine, Base
 2from sqlalchemy import Column, Integer, String, Date, ForeignKey
 3from sqlalchemy.orm import relationship
 4
 5class Tasks(Base):
 6    __tablename__="tasks"
 7
 8    systemTaskId = Column(Integer, primary_key = True)
 9    title = Column(String)
10    description = Column(String)
11    status = Column(String)
12    dueDate = Column(Date)
13    creationDate = Column(Date)
14    todoTaskCommentsSet = relationship("TodoTaskComments", backref = "task", lazy='subquery', cascade="all, delete-orphan")
15
16class TodoTaskComments(Base):
17    __tablename__="todoTaskComments"
18
19    systemTodoTaskCommentsId = Column(Integer, primary_key = True)
20    taskComments = Column(String)
21    creationDate = Column(Date)
22    task_systemTaskId = Column(Integer, ForeignKey('tasks.systemTaskId'))
23
24Base.metadata.create_all(engine)

• Each class will represent the entity model.
• Each class contains: tablename, Column and Column type
• relationship represents the One To Many Relationship
• Base.metadata.create_all will create all the tables and its dependencies in the database, at the application startup.

Update the /app.py

Update the app.py to include the SQLAlchemy Session for handling the persistance.

1from flask import Flask
2from . import engine
3from sqlalchemy.orm import sessionmaker
4
5app = Flask(__name__)
6Session = sessionmaker(bind= engine)

Now when we stop and restart the Server, we can see on the console that, 2 tables are created.

Add REST API Endpoint for CREATE Operation

For creating this endpoint, we need to update the app.py file

 1from flask import Flask, request, jsonify, abort
 2from .models import Tasks, TodoTaskComments
 3from datetime import datetime,date
 4from . import engine
 5import json
 6from sqlalchemy.orm import sessionmaker
 7
 8app = Flask(__name__)
 9Session = sessionmaker(bind= engine)
10
11@app.route('/todo-app/tasks/', methods=['POST'])
12def create_task():
13    record = json.loads(request.data)
14    task = Tasks(title = record.get('title'), description = record.get('description'), status = record.get('status'), 
15    dueDate = datetime.strptime(record.get('dueDate'), '%Y-%m-%d').date(), creationDate = date.today())
16    
17    with Session() as session:
18        session.add(task)
19        session.commit()
20        session.refresh(task)
21        
22    return __jsonResponse(task, None), 201
23
24def __jsonResponse(task, todoTaskCommentsList):
25    todoTaskCommentSet = []
26    taskDict = {
27        'systemTaskId' : task.systemTaskId,
28        'title' : task.title,
29        'description' : task.description,
30        'status' : task.status,
31        'dueDate' : task.dueDate,
32        'creationDate' : task.creationDate,
33        'todoTaskCommentSet' : todoTaskCommentSet,
34        }
35    
36    if (todoTaskCommentsList is not None):
37        for todoTaskComment in todoTaskCommentsList:
38            todoTaskCommentDict = {
39                'systemTodoTaskCommentsId': todoTaskComment.systemTodoTaskCommentsId,
40                'taskComments': todoTaskComment.taskComments,
41                'creationDate': todoTaskComment.creationDate,
42                'task_systemTaskId': todoTaskComment.task_systemTaskId
43            }
44
45            taskDict["todoTaskCommentSet"].append(todoTaskCommentDict)
46       
47    return taskDict

• create_task() is the method that will be called for creating new task.
• __jsonResponse() is the custom private method created for returning the json response from rest api methods.
• with Session() as session: with establish the Session with the sessionmaker for the current transaction.
• After the session.commit() or session.flush(), sometimes data will not be refreshed with the latest database value. This is where session.refresh() comes in handy.

Now, Open any REST client and provide values, as per the below table and see the response from the server.

Add REST API Endpoint for GET All Operation

For creating this endpoint, we need to update the app.py file

 1@app.route('/todo-app/tasks/', methods=['GET'])
 2def get_all():
 3    with Session() as session:
 4        data = []
 5        taskList = session.query(Tasks).all()
 6        
 7        for task in taskList:
 8            todoTaskCommentsList = session.query(TodoTaskComments).filter(TodoTaskComments.task_systemTaskId == task.systemTaskId)
 9            data.append(__jsonResponse(task, todoTaskCommentsList))
10        
11    return data

Now, Open any REST client and provide values, as per the below table and see the response from the server.

Add REST API Endpoint for GET Operation

For creating this endpoint, we need to update the app.py file

1@app.route('/todo-app/tasks/<int:id>', methods=['GET'])
2def get_one_task(id):
3    with Session() as session:
4        task = session.query(Tasks).get(id)
5
6    if task is None:
7        abort(404)
8
9    return jsonify(__jsonResponse(task, task.todoTaskCommentsSet))

Now, Open any REST client and provide values, as per the below table and see the response from the server.

Add REST API Endpoint for UPDATE Operation

For creating this endpoint, we need to update the app.py file

 1@app.route('/todo-app/tasks/', methods=['PUT'])
 2def update_task():
 3    record = json.loads(request.data)
 4    print(record)
 5    
 6    with Session() as session:
 7        data = []
 8        task = session.query(Tasks).get(record.get('systemTasksId'))
 9        if task is None:
10            abort(404)
11
12        task.title = record.get('title')
13        task.description = record.get('description')
14        task.status = record.get('status')
15        task.dueDate = datetime.strptime(record.get('dueDate'), '%Y-%m-%d').date()
16
17        for taskComment in record['todoTaskCommentsSet']:
18            todoTaskCommentsSet = TodoTaskComments(taskComments = taskComment['taskComments'], creationDate = date.today(), task_systemTaskId=task.systemTaskId)
19            session.add(todoTaskCommentsSet)
20        
21        session.commit()
22        session.refresh(task)
23
24        data.append(__jsonResponse(task, task.todoTaskCommentsSet))
25    return data

Now, Open any REST client and provide values, as per the below table and see the response from the server.

Add REST API Endpoint for DELETE Operation

For creating this endpoint, we need to update the app.py file

 1@app.route("/todo-app/tasks/<int:id>", methods=["DELETE"])
 2def delete_one_task(id):
 3    with Session() as session:
 4        task = session.query(Tasks).get(id)
 5        if task is None:
 6            abort(404)
 7        
 8        session.delete(task)
 9        session.commit()
10    return jsonify({'result': True})

Now, Open any REST client and provide values, as per the below table and see the response from the server.

Response from the Server

Add REST API Endpoint for DELETE ALL Operation

For creating this endpoint, we need to update the app.py file

 1@app.route("/todo-app/tasks/", methods=["DELETE"])
 2def delete_all():
 3    with Session() as session:
 4        taskList = session.query(Tasks).all()
 5        if taskList is None:
 6            abort(404)
 7
 8        for task in taskList:
 9            session.delete(task)
10            session.commit()
11
12    return jsonify({'result': True})

Now, Open any REST client and provide values, as per the below table and see the response from the server.

Response from the Server

Conclusion

With these endpoints’ setup, we have come to an end of this article and we have enabled CRUD (Create, Read, Update, Delete) Operations using below REST API Endpoints and they are ready to be consumed by any frontend or other microservice applications.

Through this article, you have learned about how to:

• Use SQLAlchemy to work with Relational Database and persist the data.
• Create and Expose REST APIs for CRUD Operations using Flask Framework.
• Handle HTTP Requests through REST Controller.

Complete code for this project can be found at GitHub here.

Instructions on how to clone the code repository and run the project are provided on the GitHub project page.