CRUD - Delete Record

Deleting records should be done carefully (data loss is permanent).

Example

python

with app.app_context():
    user = User.query.get(1)
    if user:
        db.session.delete(user)
        db.session.commit()

Soft deletes (common pattern)

Many applications avoid hard delete.

Instead they add a flag:

• is_deleted

And filter it out by default.

This helps:

• auditing
• recovery

Cascades

If other tables depend on the record (foreign keys), deleting may:

• fail due to integrity constraints, or
• delete child records (if cascade is configured)

Understand your relationships before enabling cascade delete.

Two ways to delete, with different consequences

orm_delete.py

victim = db.session.scalar(sa.select(User).where(User.name == "bob"))
db.session.delete(victim)
db.session.commit()

bulk_delete.py

result = db.session.execute(sa.delete(User).where(User.name == "cy"))
db.session.commit()
result.rowcount          # 1

Measured: 3 rows, ORM-deleting bob left 2; the bulk delete matched 1 row and left 1.

diagram Diagram mermaid

  flowchart TD
  A["session.delete(obj)"] --> A1["loads the object first"]
  A1 --> A2["cascades run
events fire
related objects handled"]
  B["sa.delete(Model).where(...)"] --> B1["one DELETE statement"]
  B1 --> B2["no objects loaded
no ORM cascades
rowcount tells you how many"]

The ORM form is the safe default: it knows about relationships and will cascade according to how you configured them. The bulk form is for deleting many rows at once, and it does exactly what the SQL says — nothing more.

Deleting something that is not there

missing.py

victim = db.session.scalar(sa.select(User).where(User.id == 999))
db.session.delete(victim)      # AttributeError if victim is None

scalar() returns None when nothing matches, so guard it. In a view, the idiomatic form turns the miss into a 404:

view.py

from flask import abort
 
user = db.session.get(User, user_id)
if user is None:
    abort(404)
db.session.delete(user)
db.session.commit()

db.session.get(Model, pk) is the right call for a primary-key lookup — it checks the identity map first and only queries if it must.

Delete belongs behind POST, never a plain link

A <a href="/delete/5"> can be followed by a crawler, a link prefetcher, or a browser preloading what it thinks you will click. Use a form that POSTs (with the CSRF token), or DELETE from JavaScript. This is a real cause of data loss, not a formality.

Soft delete is often what you actually want

soft.py

class User(db.Model):
    deleted_at: Mapped[datetime | None]
 
user.deleted_at = datetime.now(timezone.utc)
db.session.commit()

Nothing is destroyed, the row can be restored, and references from other tables stay valid. The cost is that every query must now exclude soft-deleted rows — easy to forget, so centralise it in one helper rather than repeating the filter.

See it move

sketch ORM delete against bulk delete p5.js Pause Restart

session.delete loads the object and runs cascades. sa.delete issues one statement and reports rowcount, with no ORM involvement.

Check yourself

pch.quizTag pch.quizDefaultTitle

1. What does db.session.delete(obj) do that sa.delete(Model).where(...) does not?

   A it is faster on large numbers of rows B it loads the object and runs ORM cascades and events for related rows C it can be rolled back D it reports how many rows were removed
   pch.quizShowAnswer

   B — it loads the object and runs ORM cascades and events for related rows — The ORM form knows about relationships. The bulk form is one statement with no objects loaded, which is why it is fast and why it reports rowcount instead.

2. Why should a delete action never be a plain link?

   A links cannot carry a record id B crawlers, prefetchers and link preloading can follow a GET and destroy data without a user ever clicking C links are slower than forms D browsers block GET requests to delete endpoints
   pch.quizShowAnswer

   B — crawlers, prefetchers and link preloading can follow a GET and destroy data without a user ever clicking — This is a real cause of data loss. Use a form that POSTs with a CSRF token, or send DELETE from JavaScript.

3. db.session.scalar(select(User).where(User.id == 999)) returns None and you pass it to session.delete. What happens?

   A nothing; deleting None is a no-op B an AttributeError, so the lookup must be guarded — in a view, with abort(404) C the first user is deleted instead D an IntegrityError
   pch.quizShowAnswer

   B — an AttributeError, so the lookup must be guarded — in a view, with abort(404) — scalar returns None when nothing matches. Use db.session.get(Model, pk) for a primary-key lookup and check for None before acting.