Python: Creating REST APIs using Flask and SQLAlchemy

-

In the previous article, I covered database access from the Python application using the SQLAlchemy package. In this article, we will implement the REST APIs in Python using the Flask framework. 

What is Flask?

Flask is a popular microweb framework that is written in Python. It is designed to be lightweight and modular, which makes it easy to create web applications quickly. It provides all those essentials needed to build a web app, such as routing, request handling, and templates while allowing developers the freedom to choose additional libraries and tools based on their needs. Following are some of the important Flask objects:

The request Object

The request object in Flask is used to handle incoming request data. This contains all the data sent by the client in an HTTP request including form data, query parameters, headers, cookies, etc.

Following are some common attributes and methods of the request object:

  • request.method: The HTTP method used for the request (e.g., GET, POST).

  • request.form: Access form data sent with a POST request.

  • request.args: Access query parameters in the URL.

  • request.json: Access JSON data sent with the request.

  • request.headers: Access the headers of the request.

The jsonify function

The jsonify function in Flask is used to generate a JSON response. It converts the data read from the database or like from dictionaries into a JSON-formatted string and sets the appropriate response headers.

The Flask-Cors extension

Since we will be creating REST APIs, we must also use the Cross-Origin Resource Origin (CORS) extension for the Flask. This extension is provided in the flask-cors extension.      

The Flask-SQLAlchemy extension

Flask-SQLAlchemy is a Flask extension that integrates SQLAlchemy, a powerful SQL toolkit, and the Object-Relational Mapping (ORM) library for Python. It simplifies database interaction and helps manage database connections in Flask applications.

Figure 1 shows an implementation of the application.



Figure 1: The Application 

As shown in Figure 1, the Flask offers route endpoints for HTTP requests for GET, POST, PUT, and DELETE. Flask extensions like CORS and SqlAlchemy must be configured at the application level configuration. This configuration will make sure that the Database access and CORS are configured at the application level so that all endpoints will have access to it.

Let's implement the API. I have implemented the code for this article using PyCharm, you can use other IDE like VSCode.

We will be creating an application for recording the Expenses, so in MySql create a database named Home and a table named Expenses as shown in Listing 1


Create Database Home;

use Home;

Create Table Expenses (
 ExpensesId int Primary Key,
 ExpensesType varchar(100) Not Null,
 VendorName varchar(100) Not Null,
 AmountPaid int not null,
 PaidBy varchar(100) Not Null
);

Listing 1: The Database and Table


Step 1: Open PyCharm and create a new project. Name this project as flaskAPI. For this project install the following packages:

pip install flask

pip install flask_cors

pip install flask_sqlalchemy

pip install sqlalchemy

Step 2: In the project add a folder named models. In this folder, we will add the Expenses class and we will export it as a package. In this folder add a new file named expenses.py. In this file add the code for the Expenses class as shown in Listing 2:


from sqlalchemy import Column, Integer, String

from dblogic.dbapplogic import Base
# The Model class is a subclass of the Base class, which is the declarative base class
# provided by SQLAlchemy. This class represents the Expenses table in the database.

class Expenses(Base):
    __tablename__ = 'Expenses'
    ExpensesId = Column(Integer, primary_key=True)
    ExpensesType = Column(String(100), nullable=False)
    VendorName = Column(String(100), nullable=False)
    AmountPaid = Column(Integer, nullable=False)
    PaidBy = Column(String(100), nullable=False)

    def __repr__(self):
        return (f'<Expenses(ExpensesType={self.ExpensesType}, '
                f'VendorName={self.VendorName}, '
                f'AmountPaid={self.AmountPaid}, PaidBy={self.PaidBy})>')

Listing 2: The Expenses class

We have the Expenses class that maps with each of the columns of the Expenses Table. Since we will be exporting the Expenses class as a package from the models folder, in this folder add a new file named __init__.py and add the line in it as shown in Listing 3.

__all__ = ["expenses"]

Listing 3: The export package

Step 3: In the project add a new folder named dbaccess. In this folder, add a new file named dboparatons.py. In this file, we will add code for the DbOperations class.  This class will have a parameterized constructor that accepts the session_factory object that represents the SqlAlchemy session object which is used to manage database interactions and transactions. This class contains methods to perform CRUD operations with the database server using the methods of session objects like add(), query(), delete(), commit(), etc. One of the important methods of the session object is the refresh() method. The refresh() method of the SQLAlchemy Session object is used to refresh an object’s attributes from the database. This is useful when you want to ensure that the attributes of an object reflect the current state of the database, especially after changes have been made by other transactions or sessions. The commit() method commits the transaction. Listing 4 shows the code for the DbOperations class.


# define the db operations class

class DbOperations:
    # code for methods to perform CRUD operations on Expenses table using Expenses class
    def __init__(self, session_factory):
        self.session_factory = session_factory

    def add_expense(self, expense):
        from models.expenses import Expenses
        session = self.session_factory()
        session.add(expense)
        session.commit()
        session.refresh(expense)  # Re-associate the object with the session
        session.close()
        return expense

    def get_expense(self, expense_id):
        from models.expenses import Expenses
        session = self.session_factory()
        expense = session.query(Expenses).filter_by(ExpensesId=expense_id).first()
        if expense is None:
            session.close()
            raise Exception("No expense found with id: " + str(expense_id))
        session.refresh(expense)  # Re-associate the object with the session
        session.close()
        return expense

    def get_all_expenses(self):
        from models.expenses import Expenses
        session = self.session_factory()
        expenses = session.query(Expenses).all()
        for expense in expenses:
            session.refresh(expense)  # Re-associate each object with the session
        session.close()
        return expenses

    def update_expense(self, expense_id, new_expense):
        from models.expenses import Expenses
        session = self.session_factory()
        expense = session.query(Expenses).filter_by(ExpensesId=expense_id).first()
        if expense is None:
            session.close()
            raise Exception("No expense found with id: " + str(expense_id))
        expense.ExpensesType = new_expense.ExpensesType
        expense.VendorName = new_expense.VendorName
        expense.AmountPaid = new_expense.AmountPaid
        expense.PaidBy = new_expense.PaidBy
        session.commit()
        session.refresh(expense)  # Re-associate the object with the session
        session.close()
        return expense

    def delete_expense(self, expense_id):
        from models.expenses import Expenses
        session = self.session_factory()
        expense = session.query(Expenses).filter_by(ExpensesId=expense_id).first()
        if expense is None:
            session.close()
            raise Exception("No expense found with id: " + str(expense_id))
        session.delete(expense)
        session.commit()
        session.close()
        return expense

Listing 4: DbOperations class

As shown in Listing 3 of Step 2, we need to expose the DbOperations class as a package by adding __init__.py file in dbaccess folder with code as shown in Listing 5.

__all__= ["dboperations"]

Listing 5: Export as the package         

Step 4: In the project, add a new folder named dblogic. In this folder, add a new Python file named dbapplogic.py. In this file, we will add code for the DbLogic class. This class accesses the DbOperations class to perform CRUD operations. The DbLogic class has a constructor that contains code to use SqlAlchemy package methods e.g. create_engine(), and sessionmaker() to connect to the MySQL database and perform operations. Listing 6 shows the code for the DbLogic class.


from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import declarative_base

from dbaccess.dboperations import DbOperations

Base = declarative_base()
class DbLogic:

    def __init__(self):
        from models. Expenses import Expenses
        self.DATABASE_CONNECTION = 'mysql+pymysql://root:root123@localhost/Home'
        self.engine = create_engine(self.DATABASE_CONNECTION)
        self.Session = sessionmaker(bind=self.engine)
        self.dboperations = DbOperations(self.Session)
        self.expenses = Expenses

    def add_expense(self, expense):
        return self.dboperations.add_expense(expense)

    def get_expense(self, expense_id):
        return self.dboperations.get_expense(expense_id)

    def get_all_expenses(self):
        return self.dboperations.get_all_expenses()

    def update_expense(self, expense_id, new_expense):
        return self.dboperations.update_expense(expense_id, new_expense)

    def delete_expense(self, expense_id):
        return self.dboperations.delete_expense(expense_id)

Listing 6: The DbLogic class      

Listing 6 shows methods added in the DbLogic class that are used to access methods from the DbOperations class to perform CRUD operations. As shown in Listing 3 of Step 2, we need to expose the DbLogic class as a package by adding __init__.py file in the dblogic folder with code as shown in Listing 7.

__all__= ["dbapplogic"]

Listing 7: Export as the package  

So far we have added logic and data access classes to the project. Now it's time to add the code for the API endpoint. In the project, add a new file named api.py.  In this file, we will import Flask, request, and jsonify objects and methods from the Flask package. We need to import the CORS method from the flask_cors package and the SQLAlchemy object from the flask_sqlalchemy package. To create API using the Flask we need to define the Flask app object and configure the CORS, DB Connection, and the SqlAlchemy object so that the Flask framework knows has the CORS check as well as the database connection information with it. Finally, we need to define the API endpoint routes using the route() method of the Flask app object. The code for the API is shown in Listing 8.


from flask import Flask, request, jsonify
from flask_cors import CORS
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
# Initializes Cross Origin Resource sharing for the application
CORS(app)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:root123@localhost/Home'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# Integrates SQLAlchemy with Flask. This handles setting up one or
# more engines, associating tables and models with specific engines,
# and cleaning up connections and sessions after each request.
db = SQLAlchemy(app)

def expense_to_dict(expense):
    return {
        'ExpensesId': expense.ExpensesId,
        'ExpensesType': expense.ExpensesType,
        'VendorName': expense.VendorName,
        'AmountPaid': expense.AmountPaid,
        'PaidBy': expense.PaidBy
    }


@app.route('/expenses', methods=['POST'])
def add_expense():
    data = request.get_json()
    expense = Expenses(ExpensesId=data['ExpensesId'], ExpensesType=data['ExpensesType'], VendorName=data[
        'VendorName'],
                       AmountPaid=data['AmountPaid'], PaidBy=data['PaidBy'])
    dblogic.add_expense(expense)
    return jsonify({'message': 'Expense added successfully'})


@app.route('/expenses', methods=['GET'])
def get_all_expenses():
    expenses = dblogic.get_all_expenses()
    return jsonify([expense_to_dict(expense) for expense in expenses])

@app.route('/expenses/<int:expense_id>', methods=['GET'])
def get_expense(expense_id):
    expense = dblogic.get_expense(expense_id)
    return jsonify(expense_to_dict(expense))


@app.route('/expenses/<int:expense_id>', methods=['PUT'])
def update_expense(expense_id):
    data = request.get_json()
    new_expense = Expenses(ExpensesId=data['ExpensesId'], ExpensesType=data['ExpensesType'], VendorName=data['VendorName'],
                           AmountPaid=data['AmountPaid'], PaidBy=data['PaidBy'])
    expense = dblogic.update_expense(expense_id, new_expense)
    return jsonify(expense_to_dict(expense))


@app.route('/expenses/<int:expense_id>', methods=['DELETE'])
def delete_expense(expense_id):
    expense = dblogic.delete_expense(expense_id)
    return jsonify(expense_to_dict(expense))


if __name__ == '__main__':
    from models.expenses import Expenses
    from dblogic.dbapplogic import DbLogic
    dblogic = DbLogic()
    app.run(debug=True)

Listing 8: The API code

As shown in Listing 8, the API code imports the Expenses object and DbLogic object to access the method from them. The route() is applied for performing HTTP operations on methods like add_expense(), get_all_expenses(), etc. The method expense_to_dict() is used to define a dictionary that will be serialized in the JSON form as a response from the API.

Run the application using the following command

python api.py

This command will start the Falsk API on the default port 5000. Now you can test all APIs from the Postman, Advanced REST Client tools.

The code for this article can be downloaded from this link.

Conclusion: The Flask is one of the best frameworks that helps to build REST APIs easily in Python.                

Popular posts from this blog

Uploading Excel File to ASP.NET Core 6 application to save data from Excel to SQL Server Database

-
Image
Recently, while conducting a session on ASP.NET Core 6, my client raised a query regarding uploading excel files to the ASP.NET Core MVC application and then further saving data from excel to the SQL Server Database table. I gave him the solution and then thought to write a post on it. As we all know, the Excel Workbook is one of the most heavily used files to save data. But to persist data it is recommended that it should be stored in an SQL Server database.  If the data is stored in an SQL Server database it can be queried easily.  Figure 1, explains an implementation of the application which we are going to discuss in the article. Figure 1: The ASP.NET Core That accepts Excel files and then further store data from Excel Files in SQL Server Database The   ExcelDataReader.DataSet Library To read excel files in ASP.NET Core applications, we need to use the ExcelDataReader.DataSet package. This is a lightweight and fast library for reading Excel files in .NET applications....
Read more

ASP.NET Core 6: Downloading Files from the Server

-
Image
In this article, we will implement the file downloading feature using ASP.NET Core 6 application. In real-world applications, we can have various files like reports, spreadsheets, images, PDF documents stored on the server. (Note that all cloud-hosted applications prefer to store these files on the storage services, e.g. Blob, S3, etc.). But what if the client prefers to store files on the separate file server mapped to the web application host server? Then, in that case, we may need to download these files from the file server. In this article, we will implement exactly the same thing.   Step 1: Open Visual Studio 2022 and create a new ASP.NET Core MVC Application targetted to .NET 6. Name this application as Core6_FileDownload. In this project add a new folder and name it as ServerFiles. In this file add some images. (You can add Excel, PDF, Word, Files). Step 2: Since we need to read the ServerFiles folder using the ASP.NET Core application, we need to add the IFi...
Read more

ASP.NET Core 6: Using Entity Framework Core with Oracle Database with Code-First Approach

-
Image
In this article, we will see how to use Oracle Database with Entity Framework Core with Code-First Approach. The reason behind writing a post on this is, recently while conducting training, one of my students raised the query regarding the EF Core with Oracle. Since Oracle is one of the most powerful databases, various enterprise applications are using it for the past several years and since the .NET Core is the most popular technology for building modern apps, the scenarios for accessing the Oracle database in .NET Core apps the use Entity Framework Core is common. Oracle Data Provider for .NET Entity Framework Core The Oracle Data Provider for .NET i.e. ODP.NET is used to access data from the Oracle database in the .NET Core apps using Entity Framework Core. This uses the Oracle.EntityFrameworkCore.dll, a managed library. This data provider allows using Code-First and Database-First approaches to perform Read/Write Operations with Oracle database.    Implementation To implem...
Read more