Python Flask SQLAlchemy CRUD Example (2024)

In this article, we'll demonstrate how to create a Python SQL CRUD example by using Flask 2 and SQLAlchemy which will create below rest endpoints for POST, GET, PUT, DELETE.

Python POST, GET, PUT, DELETE Rest Api's

POST - Create Employee Record
GET - List all employees
GET - Get employees by it's id
PUT - Update/Edit selected employee details
DELETE - Remove selected employee record

Q: What is Flask Python?
Ans:

A Python module that makes it simple to create web applications is called Flask. Its core is compact and simple to extend; it's a microframework that has lots of features, such as url routing and a template engine.

What is virtual environment in Python?

A virtual environment virtualenv is used to create independent, separate virtual environments with their own dependencies for multiple project. It generates a new folder for the new virtual environment which has its own libraries folder, pip, and Python interpreter for the Python version used to activate the environment.

Follow the steps listed below to begin the project:

Q: What is Flask-SQLAlchemy?
Ans:

SQLAlchemy is an ORM library helps convert Python classes/models into database tables and objects into rows and fields. A Flask addon called Flask-SQLAlchemy allows in integrating SQLAlchemy with Flask applications.

Install SQLAlchemy

We're going to utilize an ORM as it keeps the code much clearer and simpler. Additionally, the ORM library like SQLAlchemy assists us with a number of potential problems while using SQL, including multi-threading support, database migrations and creating default tables according to models etc.

Use pip install pymysql command to install SQLAlchemy into Python project.

PS D:\PythonCrudExample> pip install pymysql
Collecting pymysql
  Downloading PyMySQL-1.0.3-py3-none-any.whl (43 kB)
     ---------------------------------------- 43.7/43.7 kB 2.1 MB/s eta 0:00:00
Installing collected packages: pymysql
Successfully installed pymysql-1.0.3

[notice] A new release of pip is available: 23.0.1 -> 23.1.2
[notice] To update, run: python.exe -m pip install --upgrade pip

Project Structure

According to the project's use case or requirements, we can create files and folders.

Checkout our related posts :

Python Docker Restful API

Python and Flask Virtual Environment Setup

Why do we pass __name__ to the Flask class?

Python Flask SQLAlchemy Model

Create db.py file and initialize the SQLAlchemy as given below:

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

Create model folder and __init__.py file with below code.

from models.employee import EmployeeModel

Now create employee.py file under model folder. Initialize employee fields with its data type and it's constraints.

from db import db

class EmployeeModel(db.Model):
    __tablename__ = "employees"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), unique=True, nullable=False)
    role = db.Column(db.String(80), unique=True, nullable=False)

    def __init__(self, name, role, id=None):
        if id:
            self.id = id
        self.name = name
        self.role = role

Python Flask - Create Marshmallow Schemas

Python Marshmallow library is used for object/data validation and serialization/deserialization. Marshmallow can also be used with Flask-SQLAlchemy.

Create a new schemas.py file under project to define the model schema for validating the input data.

from marshmallow import Schema, fields

class EmployeeSchema(Schema):
    id = fields.Str(dump_only=True)
    name = fields.Str(required=True)
    role = fields.Str(required=True)

class EmployeeUpdateSchema(Schema):
    name = fields.Str()
    role = fields.Str()

Python Flask Resources using Blueprint

Install the Flask Smorest using pip install flask-smorest command in order to utilize blueprint functionality.
Create the blueprint for the employee functionality inside the resources/employee.py file
Import the blueprint from flask-smorest.
Add MethodViews for GET, POST, PUT and DELETE endpoints.

from flask.views import MethodView
from flask_smorest import Blueprint, abort
from sqlalchemy.exc import SQLAlchemyError
from db import db
from models import EmployeeModel
from schemas import EmployeeSchema, EmployeeUpdateSchema

empblueprint = Blueprint("Employees", "employees", description="TechGeekNext - Employee CRUD operations")


@empblueprint.route("/employee/<string:emp_id>")
class Employee(MethodView):
    @empblueprint.response(200, EmployeeSchema)
    def get(self, emp_id):
        return EmployeeModel.query.get_or_404(emp_id)


    def delete(self, emp_id):
        emp = EmployeeModel.query.get_or_404(emp_id)
        db.session.delete(emp)
        db.session.commit()
        return {"message": "Employee deleted."}

    @empblueprint.arguments(EmployeeUpdateSchema)
    @empblueprint.response(200, EmployeeSchema)
    def put(self, emp_data, emp_id):
        emp = EmployeeModel.query.get(emp_id)

        if emp:
            emp.name = emp_data["name"]
            emp.role = emp_data["role"]
        else:
            emp = EmployeeModel(id=emp_id, **emp_data)

        db.session.add(emp)
        db.session.commit()

        return


@empblueprint.route("/employees")
class GetEmployees(MethodView):
    @empblueprint.response(200, EmployeeSchema(many=True))
    def get(self):
        return EmployeeModel.query.all()


@empblueprint.route("/employee")
class CreateEmp(MethodView):
    @empblueprint.arguments(EmployeeSchema)
    @empblueprint.response(201, EmployeeSchema)
    def post(self, emp_data):
        emp = EmployeeModel(**emp_data)
        try:
            db.session.add(emp)
            db.session.commit()
        except SQLAlchemyError:
            abort(500, message="An error occurred while creating an employee.")

        return

Flask application factory

We have been directly creating the app variable in the Flask app called app.py.
We create a create_app() method that returns an app using the app factory pattern. So that we can configure the app before receiving it back, we can give configuration values to the function in this manner.
Provide the Swagger documentation details.
Configure the database connection with the app.
Connect the database using the db.init_app() method.
Register the Employee Blueprint.

from flask import Flask
from flask_smorest import Api
from db import db
from resources.employee import empblueprint as EmployeeBlueprint

def create_app():
    # creates an application instance app using Flask() class
    app = Flask(__name__)

    # swagger documentation details
    app.config["PROPAGATE_EXCEPTIONS"] = True
    app.config["API_TITLE"] = "Employee Crud Example"
    app.config["API_VERSION"] = "v1"
    app.config["OPENAPI_VERSION"] = "3.0.3"
    app.config["OPENAPI_URL_PREFIX"] = "/"
    app.config["OPENAPI_SWAGGER_UI_PATH"] = "/swagger-ui"
    app.config[
        "OPENAPI_SWAGGER_UI_URL"
    ] = "https://cdn.jsdelivr.net/npm/swagger-ui-dist/"

    # Database configuration - connection
    app.config["SQLALCHEMY_DATABASE_URI"] = "mysql+pymysql://root:root@localhost/employeetestdb"

    app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False

    # Connect the database to the app instance
    db.init_app(app)

    api = Api(app)

    with app.app_context():
        db.create_all()

    #register employee blueprint
    api.register_blueprint(EmployeeBlueprint)

    return app

Python Flask CRUD Example - POST, PUT, GET, DELETE endpoints

Open the database and create the schema using below SQL command.

CREATE SCHEMA `employeetestdb` ;

Run the python application using flask run command.

Create New Employee
Open Postman, use POST method with end point http://localhost:5000/employee and provide Employee details to create new employee record.
List Employees
Use GET method with end point http://localhost:5000/employees to get all employees.
Get Employee
Use GET method with end point http://localhost:5000/employee/1 to get employee by id.
Edit Employee
Use PUT method with end point http://localhost:5000/employee/2 where 2 is the id of the employee and provide employee details as body in Postman to edit.
Delete Employee
Use DELETE method with end point http://localhost:5000/employee/2 where 2 is the id of the employee.

We could also perform all above operations from swagger ui http://localhost:5000/swagger-ui.
We can see that the table will be created once the application started and updated using all above operations.