CRUD Operations, Query Filtering & Pagination0%
Database Schema Migrations with Flask-Migrate

CRUD Operations, Query Filtering & Pagination

Beginner12 min read•Updated: Jul 11, 2026
Study MaterialsCheatsheet

CRUD Operations, Query Filtering & Pagination

The fundamental responsibility of any web application backend is executing CRUD operations: Creating, Reading, Updating, and Deleting records. Flask-SQLAlchemy manages transactions and database operations through db.session.

1. Creating Records (db.session.add)

To insert records into the database:

  1. 1
    Instantiate the model class.
  2. 2
    Add it to the active transaction with db.session.add().
  3. 3
    Commit the transaction with db.session.commit().
Python
from models import db, Course
 
@app.route("/courses/new", methods=["POST"])
def create_course():
data = request.get_json()
new_course = Course(
title=data["title"],
slug=data["slug"],
price=data.get("price", 49.99),
is_published=True
)
try:
db.session.add(new_course)
db.session.commit()
return {"status": "Created", "id": new_course.id}, 201
except Exception as e:
# Essential: rollback the session on error to avoid leaving it in an invalid state!
db.session.rollback()
return {"error": "Failed to create course", "details": str(e)}, 400

2. Reading & Querying Records

In modern SQLAlchemy (2.0 style supported in Flask-SQLAlchemy 3.x), query records using db.select() or model query shortcuts:

Querying All Records:

Python
# Fetch all published courses
courses = Course.query.filter_by(is_published=True).all()

Querying a Single Record:

Python
# By Primary Key (returns None if missing)
course = Course.query.get(10)
 
# Or 404 Helper (aborts with HTTP 404 automatically if not found!)
course = Course.query.get_or_404(10, description="Course not found")
 
# First matching record
course = Course.query.filter_by(slug="flask-complete-course").first_or_404()

Advanced Filtering & Sorting:

Python
# Complex filters with operators (<, >, !=, like)
cheap_courses = Course.query.filter(
Course.price < 50.00,
Course.title.ilike("%python%")
).order_by(Course.created_at.desc()).limit(10).all()

3. Updating Records

To update an existing record, fetch the instance, mutate its Python attributes, and commit:

Python
@app.route("/courses/<int:course_id>/price", methods=["PATCH"])
def update_price(course_id):
course = Course.query.get_or_404(course_id)
new_price = request.json.get("price")
# Mutate attribute
course.price = new_price
# SQLAlchemy tracks the modification automatically!
db.session.commit()
return {"status": "Price updated", "new_price": float(course.price)}

4. Deleting Records (db.session.delete)

Python
@app.route("/courses/<int:course_id>", methods=["DELETE"])
def delete_course(course_id):
course = Course.query.get_or_404(course_id)
db.session.delete(course)
db.session.commit()
return {"message": "Course permanently deleted"}, 200

5. Built-in Database Pagination (paginate())

Fetching thousands of database rows at once exhausts server memory and crashes browser clients. Flask-SQLAlchemy provides native pagination via .paginate():

Visual Architecture & Process Flow

How data and code flow step-by-step

Flowchart
Step 1
# pagination.items
Step 2
current page's list of Course objects

In Jinja2:

HTML
{% for course in pagination.items %}
<h3>{{ course.title }}</h3>
{% endfor %}
 
<div class="pagination-controls">
{% if pagination.has_prev %}
<a href="?page={{ pagination.prev_num }}">Previous</a>
{% endif %}
<span>Page {{ pagination.page }} of {{ pagination.pages }}</span>
{% if pagination.has_next %}
<a href="?page={{ pagination.next_num }}">Next</a>
{% endif %}
</div>
Interactive Quiz

Practice Quiz

Question 1
What does get_or_404(pk) do if no record matches the primary key?
Question 2
Why is db.session.rollback() essential inside an except block?
Question 3
How do you access the list of records on the current page from a Flask-SQLAlchemy Pagination object?
Question 4
When updating an existing model instance's attributes, why is db.session.add(instance) typically unnecessary?
Question 5
Which query method performs case-insensitive wildcard pattern matching in SQLAlchemy?
Next Lesson

Database Schema Migrations with Flask-Migrate

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous LessonNext Lesson
Database Relationships (One-to-Many & Many-to-Many)Database Schema Migrations with Flask-Migrate

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

PreviousNext
PrevNext