CRUD Operations

Reviewed & published by Brayan K

CRUD stands for Create, Read, Update, and Delete — the four fundamental operations every data-driven application performs on its records, and with Flask-SQLAlchemy each one is just a few lines of Python.

Part of the free Flask course at LearnCodingFast — hands-on lessons with worked examples and the output they print, plus practice exercises and a quick quiz.

In this lesson you'll insert new rows, query existing ones, modify records in place, and remove them, building the complete lifecycle of database-backed data.

What You'll Learn in This Lesson

• What the four CRUD operations are
• Creating rows with add and commit
• Reading rows with query, get, and filter_by
• Updating a record by changing attributes
• Deleting a record with db.session.delete
• Using get_or_404 inside routes

1 Create and Read

Create means inserting a new row: build a model instance, db.session.add() it, then db.session.commit(). Read means querying: query.all() for every row, query.get(id) for one by primary key.

The code below creates a few products, then reads them back:

# app.py — Create and Read
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///shop.db"
db = SQLAlchemy(app)

class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    price = db.Column(db.Float, nullable=False)

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

    # CREATE: stage and save two products.
    db.session.add(Product(name="Keyboard", price=49.99))
    db.session.add(Product(name="Mouse", price=24.50))
    db.session.commit()

    # READ: every row, then one row by id.
    for p in Product.query.all():
        print(p.id, p.name, p.price)

    one = Product.query.get(1)
    print("Product 1 is:", one.name)

query.get(1) looks up the row whose primary key is 1. In a route you'd often use get_or_404(1) so a missing record returns a clean 404 page. Run flask run to serve routes built on this.

2 Update a Record

Update is wonderfully simple in SQLAlchemy: fetch the object, change its attributes in plain Python, and commit(). The ORM detects the change and writes it — you never type an SQL UPDATE.

# app.py — Update an existing row
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///shop.db"
db = SQLAlchemy(app)

class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    price = db.Column(db.Float, nullable=False)

with app.app_context():
    db.create_all()
    db.session.add(Product(name="Keyboard", price=49.99))
    db.session.commit()

    # UPDATE: fetch, modify attributes, commit.
    product = Product.query.filter_by(name="Keyboard").first()
    product.price = 39.99
    db.session.commit()

    print("New price:", Product.query.get(product.id).price)

# ✅ Expected output:
# New price: 39.99

Because the object is tracked by the session, simply assigning product.price = 39.99 stages an update; commit() writes it to the database.

3 Delete a Record

Delete removes a row: fetch the object, pass it to db.session.delete(), then commit(). Here it is wired into a real route using get_or_404:

# app.py — Delete inside a route
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///shop.db"
db = SQLAlchemy(app)

class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)

# A route that deletes a product by id.
@app.route("/products/<int:product_id>/delete", methods=["POST"])
def delete_product(product_id):
    # get_or_404 returns the row or a clean 404 page.
    product = Product.query.get_or_404(product_id)
    db.session.delete(product)
    db.session.commit()
    return f"Deleted product {product_id}"

with app.app_context():
    db.create_all()
    db.session.add(Product(name="Mouse"))
    db.session.commit()
    # Simulate the delete logic directly.
    p = Product.query.get(1)
    db.session.delete(p)
    db.session.commit()
    print("Remaining products:", Product.query.all())

# ✅ Expected output:
# Remaining products: []

Delete routes use POST rather than GET so a page cannot accidentally remove data just by being loaded. Try flask run and submit a form that posts to this route.

🔬 The Four CRUD Operations

🎯 Your Turn: Fill in the Blanks

Complete the code below. Replace each ___ to create a note, update it, then delete it.

# 🎯 YOUR TURN: replace each ___ to perform full CRUD
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///notes.db"
db = SQLAlchemy(app)

class Note(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    text = db.Column(db.String(200), nullable=False)

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

    note = Note(text="First draft")
    db.session.___(note)        # CREATE: stage the new row
    db.session.commit()

    note.text = "Final version"  # UPDATE: change an attribute
    db.session.commit()

    db.session.___(note)        # DELETE: remove the row
    db.session.commit()
    print(Note.query.___())      # READ: should be an empty list

Common Errors & Quick Reference

❌ My update or delete did not stick

Every change needs db.session.commit(). Changing an attribute or calling delete() only stages the change until you commit.

❌ AttributeError: 'NoneType' object has no attribute ...

query.get(id) returns None when no row matches. Use get_or_404(id) in routes, or check for None before using the result.

🏆 Mini Challenge: Manage Users

Run a full CRUD cycle on a user record.

• Create a user named "sam"
• Read it back and print the row
• Update the username to "samantha", then delete the user

# 🏆 Mini Challenge: full CRUD on a user
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///users.db"
db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), nullable=False)

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

    user = User(username="sam")          # CREATE
    db.session.add(user)
    db.session.commit()

    print("Created:", User.query.get(user.id).username)   # READ

    user.username = "samantha"           # UPDATE
    db.session.commit()
    print("Updated:", User.query.get(user.id).username)

    db.session.delete(user)              # DELETE
    db.session.commit()
    print("Remaining:", User.query.all())

❓ Frequently Asked Questions

Lesson complete — you can manage data end to end!

You now create, read, update, and delete records with Flask-SQLAlchemy, and you know how get_or_404 protects routes against missing rows.

Practice quiz

Continue this course

• Previous: Database with SQLAlchemy
• Next: Database Migrations — Evolve your database schema safely with Flask-Migrate and Alembic
• Quick reference: Flask cheat sheet

Frequently asked questions

What does CRUD stand for?

CRUD stands for Create, Read, Update, and Delete — the four basic operations every data-driven app performs on its records. With Flask-SQLAlchemy each maps to a few lines of Python.

How do I update a record with SQLAlchemy?

Query the object you want, change its attributes in Python like obj.title = 'New', then call db.session.commit(). SQLAlchemy tracks the change and writes it on commit — no UPDATE statement needed.

How do I delete a record?

Fetch the object, pass it to db.session.delete(obj), then commit. The row is removed from the table once the session commits.

What is get_or_404 used for?

Model.query.get_or_404(id) returns the row with that primary key, or automatically raises a 404 Not Found if no such row exists — handy in routes that look up records by id.