CRUD Operations

With models defined and a schema that can evolve safely, Bookshelf is finally ready for the four operations every real view function performs on data: Create, Read, Update, and Delete. This lesson walks through all four against the Book model, inside real Flask view functions rather than isolated shell snippets.

Create: add() Then commit()

Creating a new row is a two-step pattern in SQLAlchemy: build the Python object, stage it with db.session.add(), then write it with db.session.commit(). Here's a view that adds a new book to one of Bookshelf's shelves:

@app.route('/shelves/<int:shelf_id>/books/new', methods=['POST'])
def create_book(shelf_id):
    book = Book(
        title=request.form['title'],
        author=request.form['author'],
        pages=request.form.get('pages', type=int),
        shelf_id=shelf_id,
        user_id=current_user.id
    )
    db.session.add(book)     # stages the new row, nothing written yet
    db.session.commit()      # writes it, and book.id is now populated
    return redirect(url_for('book_detail', book_id=book.id))

db.session.add(book) only stages the new Book instance in the current session — nothing has touched the actual database file yet, and if the request ended here, book would never be saved at all. db.session.commit() is the step that actually writes the staged object as a real row and starts a fresh transaction for whatever comes next. It's only after commit() runs that book.id is populated with the primary key the database just assigned.

Read: query.all() and query.get()

Reading rows back uses each model's .query attribute, which Flask-SQLAlchemy adds automatically. Two of the most common entry points:

@app.route('/books')
def book_list():
    books = Book.query.all()          # every Book row
    return render_template('books/list.html', books=books)


@app.route('/books/<int:book_id>')
def book_detail(book_id):
    book = Book.query.get(book_id)    # a single row by primary key
    if book is None:
        abort(404)
    return render_template('books/detail.html', book=book)

Update: Change an Attribute, Then commit()

Updating an existing row needs no special "save" call on the object at all — fetch it with a query, change whatever attributes need changing, and commit:

@app.route('/books/<int:book_id>/edit', methods=['POST'])
def update_book(book_id):
    book = Book.query.get(book_id)
    if book is None:
        abort(404)

    book.status = request.form['status']
    book.rating = request.form.get('rating', type=int)
    db.session.commit()   # no book.save() call needed

    return redirect(url_for('book_detail', book_id=book.id))

This is a genuinely different mental model from Django's explicit instance.save(). SQLAlchemy's session automatically tracks every object it has already loaded from the database — the moment book.status = ... runs, the session already knows that book is "dirty" and needs writing. db.session.commit() then flushes every tracked change in the session at once, whether that's one book or a dozen objects modified across the same request.

Delete: session.delete() Then commit()

Deleting mirrors creating: stage the removal with db.session.delete(), then make it permanent with commit():

@app.route('/books/<int:book_id>/delete', methods=['POST'])
def delete_book(book_id):
    book = Book.query.get(book_id)
    if book is None:
        abort(404)

    db.session.delete(book)
    db.session.commit()

    return redirect(url_for('book_list'))

rollback(): Undoing an Uncommitted Change

Sometimes something goes wrong partway through a request, after changes have already been staged or made but before commit() ever runs — a validation check fails, or an unrelated exception interrupts the view function. db.session.rollback() discards every uncommitted change in the current session, resetting it back to a clean state:

@app.route('/books/<int:book_id>/edit', methods=['POST'])
def update_book(book_id):
    book = Book.query.get(book_id)
    if book is None:
        abort(404)

    try:
        book.pages = request.form.get('pages', type=int)
        db.session.commit()
    except Exception:
        db.session.rollback()   # undo the staged change, session is clean again
        raise

    return redirect(url_for('book_detail', book_id=book.id))

This is exactly the tool worth reaching for from inside the 500 error handler covered back in Error Handling: a request that blows up mid-transaction can leave the session holding half-applied changes, and rolling back guarantees the next request starts from a clean slate rather than inheriting a corrupted session state.