PF Flask DB Create, Update, Read, Delete (CRUD), Pagination Example by ORM

In this section we will first create model, after that will do CRUD operation

  • Create Person model

class Person(AppModel):
    first_name = app_db.Column(app_db.String(150), nullable=False)
    last_name = app_db.Column(app_db.String(150))
    email = app_db.Column(app_db.String(120), nullable=False)
    age = app_db.Column(app_db.Integer)
    income = app_db.Column(app_db.Float, default=0)


with app.app_context():
    app_db.create_all()
  • Here :

    • db.create_all() : Create all schema during the application start.


  • Create : Instantiate person model and save to database

@app.route('/create')
def create():
    person = Person(first_name="First Name", last_name="Last Name", email="hmtmcse.com@gmail.com", age=22, income=500)
    person.save()
    response = "Data successfully Inserted"
    return response


  • Update : For update a model, we have to pull data from database then we are updating properties, then save to database.

@app.route('/update')
def update():
    person = Person.query.filter_by(id=1).first()
    if person:
        person.first_name = "FName Update"
        person.last_name = "LName Update"
        person.save()
    return "Data has been updated."


  • Delete : Delete like update operation, first get data from database then call delete action.

@app.route('/delete')
def delete():
    person = Person.query.filter_by(id=1).first()
    if person:
        person.delete()
    return "Record has been deleted"


  • Read list of inserted data : Below codes pull all data from database and list them.

@app.route('/list')
def list():
    response = ""
    persons = Person.query.all()
    for person in persons:
        response += person.first_name + " " + person.last_name + " " + person.email + "<br>"
    return response


  • Bulk Create : Create 20 person and add to database

@app.route('/create-bulk')
def create_bulk():
    total_record = 20
    person = Person(first_name="Flask", last_name="DB", email="flask-db@email.loc")
    for index in range(total_record):
        person.add(
            Person(
                first_name="First Name " + str(index),
                last_name="Last Name " + str(index),
                email="email-" + str(index) + "@email.loc",
                age=1 * index,
                income=100 * index)
        )
    person.save()
    response = str(total_record) + " Records successfully Inserted"
    return response
  • Codes

    • db.session.add_all(person_list) : This function can add multiple record at a time

  • Here


  • Read list of inserted data with pagination: Below codes pull all data from database and list them with pagination.

@app.route('/pagination')
@app.route('/pagination/<int:per_page>')
@app.route('/pagination/<int:page>/<int:per_page>')
def pagination(page: int = 1, per_page: int = 5):
    response = {
        "page": 0,
        "pages": 0,
        "per_page": 0,
        "totalItem": 0,
        "items": [],
    }
    persons = Person.query.paginate(page, per_page, error_out=False)
    if persons:
        response["page"] = persons.page
        response["pages"] = persons.pages
        response["per_page"] = persons.per_page
        response["totalItem"] = persons.total
        for person in persons.items:
            response["items"].append(person.first_name + " " + person.last_name + " " + person.email)
    return response
{
    "items": [
        "First Name 0 Last Name 0 email-0@email.loc",
        "First Name 1 Last Name 1 email-1@email.loc",
        "First Name 2 Last Name 2 email-2@email.loc",
        "First Name 3 Last Name 3 email-3@email.loc",
        "First Name 4 Last Name 4 email-4@email.loc"
    ],
    "page": 1,
    "pages": 4,
    "per_page": 5,
    "totalItem": 20
}