2024-11-06 Building a Flask CRUD Webapp with SQLAlchemy and Bootstrap
I just created a simple CRUD webapp using Flask. It has form validation, but the data is in memory. I'd like to proceed with adding database support, in particular, using SQLAlchemy, so that I can do more experiments on SQLite, MySQL, and PostgreSQL.
I'd like to use the SQLAlchemy package for database access from our Flask app. Like installing Flaks-WTForms to use WTForms, I'll also install Flask-SQLAlchemy to use SQLAlchemy.
Setup
Following the previous post, first install `Flask-SQLAlchemyh. Make sure you have entered the virtual environment.
pip install Flask-SQLAlchemyHowever, I'd like to make sure which version of SQLAlchemy is used by the installed Flask-SQLAlchemy:
$ pip list | grep SQLAlchemy
Flask-SQLAlchemy 3.1.1
SQLAlchemy 2.0.36To use it, first create the file models.py to place data model related code:
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import String
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
class Base(DeclarativeBase):
pass
db = SQLAlchemy(model_class=Base)
class Note(db.Model):
id: Mapped[int] = mapped_column(primary_key=True)
title: Mapped[str] = mapped_column(String(100))
text: Mapped[str]Some examples can be found here.
The app.py file needs to be modified too. Remove the data model related part and adjust the routes to access the database instead:
from flask import Flask, render_template, request
from flask import redirect, url_for
from forms import NoteForm
from models import db, Note
app = Flask(__name__)
app.config['SECRET_KEY'] = '123454321'
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///noter.db"
db.init_app(app)
@app.route('/')
def home():
return render_template('home.html')
@app.route('/notes')
def note_index():
notes = db.session.execute(db.select(Note)).scalars()
return render_template('note-index.html', notes=notes)
@app.route('/notes/<int:note_id>')
def note_view(note_id):
note = db.get_or_404(Note, note_id)
return render_template('note-view.html', note=note)
@app.route('/notes/<int:note_id>/edit', methods=["GET", "POST"])
def note_edit(note_id):
note = db.get_or_404(Note, note_id)
form = NoteForm(obj=note)
if request.method == 'POST':
if form.validate_on_submit():
note.title = form.title.data
note.text = form.text.data
db.session.commit();
return redirect(url_for('note_view', note_id=note.id))
return render_template('note-edit.html', note=note, form=form)
@app.route('/notes/<int:note_id>/delete')
def note_delete(note_id):
note = db.get_or_404(Note, note_id)
db.session.delete(note);
db.session.commit();
return redirect(url_for('note_index'))
if __name__ == '__main__':
with app.app_context():
db.create_all()Other than the database initialization part, the new code is not that different from the old one. Before launching the server, we need to initialize the database first. We can run:
python app.pywhich just runs db.create_all() inside an app context app.app_context(). This will initiate the database tables defined via db if they do not exist yet.
Now we can launch the server as before:
flask run --debugBut visiting the page at http://127.0.0.1:5000/notes , we only see an empty note list:
Of course, the newly created database (in the SQLite file instance/noter.sqlite) has no data yet. Since we haven't done note creation yet, to keep things going, we can use flask shell to enter into a "shell" and run python code there, for example, to populate the database with some notes:
$ flash shell
...
>>> from models import Note
>>> note = Note(title="README", text="Check this out")
>>> db.session.add(note)
>>> db.session.commit()
...
>>> list(db.session.execute(db.select(Note)).scalars())
[<Note 1>, <Note 2>, <Note 3>]We manually add some notes into the database. Refresh the page and see if the notes show up:
Styling with Bootstrap
There are many approaches to adding styling to websites. Here I just use Bootstrap for its ease of use. Go to Get started and see what need to be added to our templates/base.html:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>{% block title %}{% endblock %}</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
<main class="py-3">
{% block body %}{% endblock %}
</main>
</body>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js"></script>
</html>This adds a CSS style sheet and a JavaScript code into the base template. We then change the home page home.html as:
{% extends 'base.html' %}
{% block title %}Home{% endblock %}
{% block body %}
<main class="container">
<div class="bg-body-tertiary p-5 rounded">
<h1>Hello, World!</h1>
<a class="btn btn-lg btn-primary" href="/notes">My notes »</a>
</div>
</main>
{% endblock %}The home page looks like:
Below are the changes to different template files:
note-index.html
{% extends 'base.html' %}
{% block title %}Notes{% endblock %}
{% block body %}
<div class="container">
<h1>My Notes</h1>
<ul class="list-group mb-3">
{% for note in notes %}
<li class="list-group-item">
<a href="{{ url_for('note_view', note_id=note.id) }}">
{{ note.title }}
</a>
</li>
{% endfor %}
</ul>
<p><a class="btn btn-outline-secondary" href="/">Back</a></p>
</div>
{% endblock %}
note-view.html
{% extends 'base.html' %}
{% block title %}Note{% endblock %}
{% block body %}
<div class="container">
<h1>Note [{{ note.id }}]</h1>
<ul class="list-group mb-3">
<li class="list-group-item">title: {{ note.title }}</li>
<li class="list-group-item">text: {{ note.text }}</li>
<li class="list-group-item"><a href="{{ url_for('note_edit', note_id=note.id) }}">edit</a></li>
</ul>
<p><a class="btn btn-outline-secondary" href="/notes">Back</a></p>
</div>
{% endblock %}
note-edit.html
{% extends 'base.html' %}
{% block title %}Note{% endblock %}
{% block body %}
<div class="container">
<h1>Note [{{ note.id }}] - Edit</h1>
<form method="post" action="{{ url_for('note_edit', note_id=note.id) }}">
{{ form.csrf_token }}
<div class="mb-3">
{{ form.title.label(class='form-label') }}
{{ form.title(class='form-control', size=20) }}
</div>
{% if form.title.errors %}
<ul class="errors">
{% for error in form.title.errors %}
<li>{{ error }}</li>
{% endfor %}
</ul>
{% endif %}
<div class="mb-3">
{{ form.text.label(class='form-label') }}
{{ form.text(class='form-control', rows=3, cols=5) }}
</div>
{% if form.text.errors %}
<ul class="errors">
{% for error in form.text.errors %}
<li>{{ error }}</li>
{% endfor %}
</ul>
{% endif %}
<div class="mb-3">
<a class="btn btn-outline-secondary me-2" href="{{ url_for('note_view', note_id=note.id) }}">Cancel</a>
<a class="btn btn-danger me-2" href="{{ url_for('note_delete', note_id=note.id) }}">Delete</a>
<button class="btn btn-primary" type="submit">Submit</button>
</div>
</form>
</div>
{% endblock %}
Creating Notes
Lastly, let's see how to add note creation function. The code below uses what has been covered so far with slight difference. Again, let's do it "outside in" by modifying note-index.html first:
...
<p>
<a class="btn btn-outline-secondary me-2" href="/">Back</a>
<a class="btn btn-primary" href="{{ url_for('note_create') }}">New Note</a>
</p>
...To make it work, we need to add a route note_create in app.py, which mimics note_edit:
@app.route('/notes/create', methods=["GET", "POST"])
def note_create():
form = NoteForm()
if request.method == 'POST':
if form.validate_on_submit():
note = Note(
title=form.title.data,
text=form.text.data)
db.session.add(note)
db.session.commit();
return redirect(url_for('note_view', note_id=note.id))
return render_template('note-create.html', form=form)With this, we can see the new list page already:
But to create a new note, the missing template note-create.html is needed. But it is similar to note-edit.html too:
{% extends 'base.html' %}
{% block title %}Create Note{% endblock %}
{% block body %}
<div class="container">
<h1>Note - New</h1>
<form method="post" action="{{ url_for('note_create') }}">
{{ form.csrf_token }}
<div class="mb-3">
{{ form.title.label(class='form-label') }}
{{ form.title(class='form-control', size=20) }}
</div>
{% if form.title.errors %}
<ul class="errors">
{% for error in form.title.errors %}
<li>{{ error }}</li>
{% endfor %}
</ul>
{% endif %}
<div class="mb-3">
{{ form.text.label(class='form-label') }}
{{ form.text(class='form-control', rows=3, cols=5) }}
</div>
{% if form.text.errors %}
<ul class="errors">
{% for error in form.text.errors %}
<li>{{ error }}</li>
{% endfor %}
</ul>
{% endif %}
<div class="mb-3">
<a class="btn btn-outline-secondary me-2" href="{{ url_for('note_index') }}">Cancel</a>
<button class="btn btn-primary" type="submit">Submit</button>
</div>
</form>
</div>
{% endblock %}Then we can click New Note and see:
Enter both input fields and click Submit and see whether it leads to the page viewing the newly created note.