Flask CRUD Operations

CRUD stands for Create, Read, Update, and Delete — the four fundamental operations every database-driven application performs. With Flask-SQLAlchemy, each operation maps to clean Python code. No raw SQL is required.

The CRUD Flow Diagram

CREATE  →  Add a new row to the table
READ    →  Fetch one or many rows
UPDATE  →  Modify an existing row
DELETE  →  Remove a row permanently

The Model Used in These Examples

class Post(db.Model):
    id      = db.Column(db.Integer, primary_key=True)
    title   = db.Column(db.String(200), nullable=False)
    content = db.Column(db.Text, nullable=False)
    author  = db.Column(db.String(80), nullable=False)

CREATE — Adding a New Record

@app.route('/post/new', methods=['GET', 'POST'])
def new_post():
    if request.method == 'POST':
        post = Post(
            title   = request.form.get('title'),
            content = request.form.get('content'),
            author  = request.form.get('author')
        )
        db.session.add(post)
        db.session.commit()
        return redirect(url_for('all_posts'))
    return render_template('new_post.html')

The three-step pattern for creating a record:

1. Create a model object:   post = Post(title='...', ...)
2. Add to session:          db.session.add(post)
3. Commit to database:      db.session.commit()

The session is a staging area. You can add multiple objects before committing — they all go to the database in one transaction.

READ — Fetching Records

# Fetch all posts
@app.route('/posts')
def all_posts():
    posts = Post.query.all()
    return render_template('posts.html', posts=posts)

# Fetch one post by ID
@app.route('/post/<int:post_id>')
def view_post(post_id):
    post = Post.query.get_or_404(post_id)
    return render_template('post.html', post=post)

# Filter posts by author
@app.route('/posts/by/<author>')
def posts_by_author(author):
    posts = Post.query.filter_by(author=author).all()
    return render_template('posts.html', posts=posts)

Common Query Methods

UPDATE — Modifying a Record

@app.route('/post/<int:post_id>/edit', methods=['GET', 'POST'])
def edit_post(post_id):
    post = Post.query.get_or_404(post_id)
    if request.method == 'POST':
        post.title   = request.form.get('title')
        post.content = request.form.get('content')
        db.session.commit()
        return redirect(url_for('view_post', post_id=post.id))
    return render_template('edit_post.html', post=post)

Update pattern:

1. Fetch the existing record:   post = Post.query.get_or_404(id)
2. Change its attributes:       post.title = new_title
3. Commit:                      db.session.commit()

You do not call db.session.add() again for updates. SQLAlchemy tracks objects fetched from the database and detects changes automatically.

DELETE — Removing a Record

@app.route('/post/<int:post_id>/delete', methods=['POST'])
def delete_post(post_id):
    post = Post.query.get_or_404(post_id)
    db.session.delete(post)
    db.session.commit()
    return redirect(url_for('all_posts'))

Delete pattern:

1. Fetch the record:     post = Post.query.get_or_404(id)
2. Mark for deletion:    db.session.delete(post)
3. Commit:               db.session.commit()

The delete route uses methods=['POST'] only. Delete actions should never happen on a GET request because browsers and crawlers can trigger GET requests automatically.

Bulk Operations

# Add multiple records at once
posts = [
    Post(title='First', content='Content 1', author='Alice'),
    Post(title='Second', content='Content 2', author='Bob'),
]
db.session.add_all(posts)
db.session.commit()

# Delete all records matching a filter
Post.query.filter_by(author='Bob').delete()
db.session.commit()

Summary

SQLAlchemy CRUD follows a consistent session-based pattern: create objects and add them to the session, fetch and modify objects then commit, or mark objects for deletion then commit. The session batches all changes and sends them to the database in a single transaction when you call db.session.commit(). This keeps your data consistent even if multiple operations happen together.