angle-uparrow-clockwisearrow-counterclockwisearrow-down-uparrow-leftatcalendarcard-listchatcheckenvelopefolderhouseinfo-circlepencilpeoplepersonperson-fillperson-plusphoneplusquestion-circlesearchtagtrashx

Flask SQLAlchemy CRUD application with WTForms QuerySelectField and QuerySelectMultipleField

WTForms QuerySelectField and QuerySelectMultipleField make it easy to manage SQLAlchemy relationship data.

8 March 2021 Updated 8 March 2021
post main image
https://unsplash.com/@helpdeskheroes

For a new Flask application using WTForms and SQLAlchemy, I had many relationships between tables and was looking for the easiest way to manage these tables. The most obvious choice is to use the QuerySelectField and QuerySelectMultipleField present in the wtforms-sqlalchemy package. Since I haven't used them before, I created a small application to play with.

Below I show you the code (development on Ubuntu 20.04). If you want to see it in action, you can download the Docker image at the bottom of this post.

Summary of the application

This is a CRUD application that demonstrates the QuerySelectField and QuerySelectMultipleField. To reduce the code I added Bootstrap-Flask. The database is SQLite. I am not using Flask-SQLAlchemy but a proprietary implementation.

There are three tables:

  • Friend
  • City
  • Hobby

Friend-City is a many-to-one relationship:

A friend can live in only one city, and a city can have many friends.

Friend-Hobby is a many-to-many relationship:

A friend may have many hobbies, and a hobby may be practiced by many friends.

In the Friend form:

  • the QuerySelectField is used to select a single city
  • the QuerySelectMultipleField is used to select a zero or more hobbies

I more or less duplicated the code for the Create, Edit and Delete operations. This leaves some rooms for experimentation. I did not use the query_factory field in the form with QuerySelectField and QuerySelectMultipleField. Instead I added this to the view function, like:

	form.city.query = app_db.session.query(City).order_by(City.name)

Create virtual environment

Go to your development directory, create a virtual environment for a new directory, for example flaskquery, activate it and enter the directory:

cd <your-development-directory>
python3 -m venv flaskquery
source flaskquery/bin/activate
cd flaskquery
mkdir project
cd project
mkdir app

The project directory is 'project' and our application is in the app directory.

Install packages

To minimize the code I will be using Bootstrap-Flask. The best part is the form rendering with just a single statement. Further we use SQLAlchemy and for the database SQLite. I do not use Flask-SQLAlchemy, I explained this in a previous post. For migrations we use Alembic, I cannot live without it.

pip install flask
pip install flask-wtf
pip install bootstrap-flask
pip install sqlalchemy
pip install wtforms-sqlalchemy
pip install alembic

Project directory

For your reference, here is the tree dump of the project directory for the completed project.

.
├── alembic
│   ├── env.py
│   ├── README
│   ├── script.py.mako
│   └── versions
│       ├── 1c20e6a53339_create_db.py
│       └── d821ac509404_1e_revision.py
├── alembic.ini
├── app
│   ├── blueprints
│   │   └── manage_data
│   │       ├── forms.py
│   │       └── views.py
│   ├── factory.py
│   ├── factory.py_first_version
│   ├── model.py
│   ├── service_app_db.py
│   ├── service_app_logging.py
│   ├── services.py
│   └── templates
│       ├── base.html
│       ├── home.html
│       ├── item_delete.html
│       ├── item_new_edit.html
│       └── items_list.html
├── app.db
├── app.log
├── config.py
├── requirements.txt
└── run.py

Start with a minimal app

In the project directory we create a run.py file with the following contents:

# run.py

from app import factory

app = factory.create_app()

if __name__ == '__main__':
    app.run(host= '0.0.0.0')

Note that we using a factory.py file instead of an __init__.py file. Avoid the __init__.py. When your application grows you may run into circular imports.

We put a config.py in the project directory to store our configuration variables:

# config.py

import os

project_dir = os.path.abspath(os.path.dirname(__file__))

class Config(object):
    DEBUG = False
    TESTING = False    

class ConfigDevelopment(Config):
    DEBUG = True
    SECRET_KEY = 'NO8py79NIOU7694rgLKJHIGo87tKUGT97g'
    SQLALCHEMY_DB_URI = 'sqlite:///' + os.path.join(project_dir, 'app.db')
    SQLALCHEMY_ENGINE_OPTIONS = {
        'echo': True,
    }

class ConfigTesting(Config):
    TESTING = True

class ConfigProduction(Config):
    pass

Two services, logging and database

We can put everything in the factory.py file but that will get messy. So let's create separate files with classes for logging and database.

# service_app_logging.py

import logging

class AppLogging:

    def __init__(self, app=None):
        if app is not None:
            self.init_app(app)

    def init_app(self, app):
        FORMAT = '%(asctime)s [%(levelname)-5.5s] [%(funcName)30s()] %(message)s'
        logFormatter = logging.Formatter(FORMAT)
        app.logger = logging.getLogger()
        app.logger.setLevel(logging.DEBUG)

        fileHandler = logging.FileHandler('app.log')
        fileHandler.setFormatter(logFormatter)
        app.logger.addHandler(fileHandler)

        consoleHandler = logging.StreamHandler()
        consoleHandler.setFormatter(logFormatter)
        app.logger.addHandler(consoleHandler)

        return app.logger

To access the database we create a SQLAlchemy scoped_session.

# service_app_db.py

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session

class AppDb:

    def __init__(self, app=None):
        if app is not None:
            self.init_app(app)

    def init_app(self, app):
        sqlalchemy_db_uri = app.config.get('SQLALCHEMY_DB_URI')
        sqlalchemy_engine_options = app.config.get('SQLALCHEMY_ENGINE_OPTIONS')

        engine = create_engine(
            sqlalchemy_db_uri,
            **sqlalchemy_engine_options
        )
        sqlalchemy_scoped_session = scoped_session(
            sessionmaker(
                bind=engine,
                expire_on_commit=False
            )
        )

        setattr(self, 'session', sqlalchemy_scoped_session)

We use an intermediate file services.py where we instantiate the services.

# services.py

from .service_app_logging import AppLogging
from .service_app_db import AppDb

app_logging = AppLogging()
app_db = AppDb()

The application factory, first version

Now we can create the first version of our factory.py file:

# factory.py

from flask import Flask, request, g, url_for, current_app, render_template
from flask_wtf.csrf import CSRFProtect
from flask_bootstrap import Bootstrap

from .services import app_logging, app_db

def create_app():

    app = Flask(__name__)

    app.config.from_object('config.ConfigDevelopment')

    # services
    app.logger = app_logging.init_app(app)
    app_db.init_app(app)
    app.logger.debug('test debug message')

    Bootstrap(app)

    csrf = CSRFProtect()
    csrf.init_app(app)

    @app.teardown_appcontext
    def teardown_db(response_or_exception):
        if hasattr(app_db, 'session'):
            app_db.session.remove()

    @app.route('/')
    def index():
        return render_template(
            'home.html',
            welcome_message='Hello world',
        )

    return app

Note that I put a '@app.route' here for the home page.

In the templates directory we put two files, here is the base template, see also the example in the Bootstrap-Flask package.

{% from 'bootstrap/nav.html' import render_nav_item %}
{% from 'bootstrap/utils.html' import render_messages %}
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <title>QuerySelectField and QuerySelectMultipleField</title>
    {{ bootstrap.load_css() }}
</head>
<body>
    <nav class="navbar navbar-expand-lg navbar-light bg-light mb-4">
        <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarSupportedContent"
                aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
            <span class="navbar-toggler-icon"></span>
        </button>
        <div class="collapse navbar-collapse" id="navbarSupportedContent">
            <ul class="navbar-nav mr-auto">
                {{ render_nav_item('index', 'Home', use_li=True) }}
            </ul>
        </div>
    </nav>

    <main class="container">
        {{ render_messages(container=False, dismissible=True) }}
        {% block content %}{% endblock %}
    </main>

    {{ bootstrap.load_js() }}
</body>
</html>

And the home page template.

{# home.html #}

{% extends "base.html" %}

{% block content %}

    {{ welcome_message }}

{% endblock %}

First run

Go to the project directory and type:

python3 run.py

Point your browser to 127.0.0.1:5000 and you should see the message 'Hello world'. You should also see the Bootstrap menu at the top of the page. View the source code of the page and check the bootstrap files. In the project directory there also should be our log file app.log.

Add the model

We now have a running application. In the app directory we create a model.py file. We have Friends, Cities and Hobbies.

# model.py

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, ForeignKey, Integer, String, Table
from sqlalchemy.orm import relationship

Base = declarative_base()

# many-to-many link table
friend_mtm_hobby_table = Table(
    'friend_mtm_hobby',
    Base.metadata,
    Column('friend_id', Integer, ForeignKey('friend.id')),
    Column('hobby_id', Integer, ForeignKey('hobby.id'))
)

class Friend(Base):
    __tablename__ = 'friend'

    id = Column(Integer, primary_key=True)
    name = Column(String(100), server_default='')

    city_id = Column(Integer, ForeignKey('city.id'))
    city = relationship(
        'City',
        back_populates='friends',
    )

    hobbies = relationship(
        'Hobby',
        secondary=friend_mtm_hobby_table,
        back_populates='friends',
        order_by=lambda: Hobby.name,
    )

class City(Base):
    __tablename__ = 'city'

    id = Column(Integer, primary_key=True)
    name = Column(String(100), server_default='')

    friends = relationship(
        'Friend',
        back_populates='city',
        order_by=Friend.name,
    )

class Hobby(Base):
    __tablename__ = 'hobby'

    id = Column(Integer, primary_key=True)
    name = Column(String(100), server_default='')

    friends = relationship(
        'Friend',
        secondary=friend_mtm_hobby_table,
        back_populates='hobbies',
        order_by=Friend.name,
    )

Sort problem

It would be nice if SQLAlchemy returns sorted by name results. We can use this in lists.

  • Friends list: Show the friend name, the city name and the name of the hobbies
  • Cities list: show the city name and the names of all friends living in a city
  • Hobbies list: show the hobby name and the names of all friends having this hobby

For example, with a hobby we access the friends as hobby.friends. Sorting looks easy, we just add an 'order_by' clause in the relationship. However, because we refer to a class, Friend, we can only use this with classes that are loaded before.

In our model above we cannot sort the hobbies in the Friend class, because the Hobby class was not loaded before the Friend class. But we can sort the friends in the Hobby class because the Friend class was loaded before the Hobby class.

To get around this we can do one of two things:

    hobbies = relationship(
        'Hobby',
        secondary=friend_mtm_hobby_table,
        back_populates='friends',
        order_by='Hobby.name',
    )

or:

    hobbies = relationship(
        'Hobby',
        secondary=friend_mtm_hobby_table,
        back_populates='friends',
        order_by=lambda: Hobby.name,
    )

In both cases name resolution is postponed until first usage.

Use Alembic to create the database

Alembic is a great tool for database migrations. We already installed it, but we must initialize it before we can use it. Go to the project directory and type:

alembic init alembic

This will create an alembic.ini file and an alembic directory in the project directory. In alembic.ini, change the line:

sqlalchemy.url = driver://user:pass@localhost/dbname

to:

sqlalchemy.url = sqlite:///app.db

And in alembic/env.py change the line:

target_metadata = None

to:

from app.model import Base
target_metadata = [Base.metadata]

Create the first revision:

alembic revision -m "1e revision"

Run the migration:

alembic upgrade head

And the database file app.db was created in the project directory.

Use SQLite browser to view the database

Install SQLite browser:

sudo apt install sqlitebrowser

You can launch the SQLite browser by right clicking on the database. Only one table was created: alembic_version.

To create our database tables we use autogenerate:

alembic revision --autogenerate -m "create db"

Run the migration:

alembic upgrade head

Close the SQLite browser and open it again and observe that the tables have been created:

  • friend
  • city
  • hobby
  • friend_mtm_hobby

Now add a friend by using 'Execute SQL':

INSERT INTO friend (name) VALUES ('John');

Do not forget to click 'Write changes' after this! Then click 'Browse Data' and check that the inserted record is there.

Change the home page message

I want to show a message on the home page that shows all our friends. For this we change factory.py to get the friends and pass them to the template:

# factory.py

from flask import Flask, request, g, url_for, current_app, render_template
from flask_wtf.csrf import CSRFProtect
from flask_bootstrap import Bootstrap

from .services import app_logging, app_db

from .model import Friends

def create_app():

    app = Flask(__name__)

    app.config.from_object('config.ConfigDevelopment')

    # services
    app.logger = app_logging.init_app(app)
    app_db.init_app(app)
    app.logger.debug('test debug message')

    Bootstrap(app)

    csrf = CSRFProtect()
    csrf.init_app(app)

    @app.teardown_appcontext
    def teardown_db(response_or_exception):
        if hasattr(app_db, 'session'):
            app_db.session.remove()

    @app.route('/')
    def index():
        friends = app_db.session.query(Friend).order_by(Friend.name).all()
        return render_template(
            'home.html',
            welcome_message='Hello world',
            friends=friends,
        )

    return app

And we iterate our friends in the home page template:

{# home.html #}

{% extends "base.html" %}

{% block content %}

	{{ welcome_message }}

	<ul>
	{% for friend in friends %}
		<li>
			{{ friend.name }}
		</li>
	{% endfor %}
	</ul>

{% endblock %}

Refresh the page in the brower. Now the name of our friend John should be displayed on the home page.

Add a Blueprint to manage the data

To manipulate the database tables we create a Blueprint, manage_data. In this Blueprint we add the following methods for every table (object):

  • list
  • new
  • edit
  • delete

We create a blueprints directory and in this directory a 'manage_data' directory. In this directory we create two files, views.py and forms.py. We do not use the QuerySelectField / QuerySelectMultipleField query_factory parameter in the form classes but add them in the view methods.

# forms.py

from flask_wtf import FlaskForm
from wtforms import IntegerField, StringField, SubmitField
from wtforms_sqlalchemy.fields import QuerySelectField, QuerySelectMultipleField
from wtforms.validators import InputRequired, Length

from app.services import app_db
from app.model import Friend, City, Hobby

# friend
class FriendNewEditFormMixin():

    name = StringField('Name',
        validators=[ InputRequired(), Length(min=2) ])

    city = QuerySelectField('City',
        get_label='name',
        allow_blank=False,
        blank_text='Select a city',
        render_kw={'size': 1},
        )

    hobbies = QuerySelectMultipleField('Hobbies',
        get_label='name',
        allow_blank=False,
        blank_text='Select one or more hobbies',
        render_kw={'size': 10},
        )

class FriendNewForm(FlaskForm, FriendNewEditFormMixin):

    submit = SubmitField('Add')

class FriendEditForm(FlaskForm, FriendNewEditFormMixin):

    submit = SubmitField('Update')

class FriendDeleteForm(FlaskForm):

    submit = SubmitField('Confirm delete')

# city
class CityNewEditFormMixin():

    name = StringField('Name',
        validators=[ InputRequired(), Length(min=2) ])

class CityNewForm(FlaskForm, CityNewEditFormMixin):

    submit = SubmitField('Add')

class CityEditForm(FlaskForm, CityNewEditFormMixin):

    submit = SubmitField('Update')

class CityDeleteForm(FlaskForm):

    submit = SubmitField('Confirm delete')

# hobby
class HobbyNewEditFormMixin():

    name = StringField('Name',
        validators=[ InputRequired(), Length(min=2) ])

class HobbyNewForm(FlaskForm, HobbyNewEditFormMixin):

    submit = SubmitField('Add')

class HobbyEditForm(FlaskForm, HobbyNewEditFormMixin):

    submit = SubmitField('Update')

class HobbyDeleteForm(FlaskForm):

    submit = SubmitField('Confirm delete')

As stated before, there is a lot of repetition in views.py but that makes it easy to change things. Note that we share the templates between Friend, City and Hobby.

In the friend views we want to select a city and select one or more hobbies. Here we initialize the queries for the QuerySelectField and QuerySelectMultipleField. According to the documentation if any of the items in the submitted form cannot be found in the query, this will result in a validation error. And this is exactly what we want.

# views.py

from flask import Flask, Blueprint, current_app, g, session, request, url_for, redirect, \
    render_template, flash, abort

from app.services import app_db
from app.model import Friend, City, Hobby
from .forms import (
    FriendNewForm, FriendEditForm, FriendDeleteForm,
    CityNewForm, CityEditForm, CityDeleteForm,
    HobbyNewForm, HobbyEditForm, HobbyDeleteForm,
)


manage_data_blueprint = Blueprint('manage_data', __name__)


@manage_data_blueprint.route('/friends/list', methods=['GET', 'POST'])
def friends_list():

    friends = app_db.session.query(Friend).order_by(Friend.name).all()

    thead_th_items = [
        {
            'col_title': '#',
        },
        {
            'col_title': 'Name',
        },
        {
            'col_title': 'City',
        },
        {
            'col_title': 'Hobbies',
        },
        {
            'col_title': 'Delete',
        },
    ]

    tbody_tr_items = []
    for friend in friends:
        city_name = '-'
        if friend.city:
            city_name = friend.city.name
        hobby_names = '-'
        if friend.hobbies:
            hobby_names = ', '.join([x.name for x in friend.hobbies])

        tbody_tr_items.append([
            {
                'col_value': friend.id,
            },
            {
                'col_value': friend.name,
                'url': url_for('manage_data.friend_edit', friend_id=friend.id),
            },
            {
                'col_value': city_name,
            },
            {
                'col_value': hobby_names,
            },
            {
                'col_value': 'delete',
                'url': url_for('manage_data.friend_delete', friend_id=friend.id),
            }
        ])

    return render_template(
        'items_list.html',
        title='Friends',
        thead_th_items=thead_th_items,
        tbody_tr_items=tbody_tr_items,
        item_new_url=url_for('manage_data.friend_new'),
        item_new_text='New friend',
    )

@manage_data_blueprint.route('/cities/list', methods=['GET', 'POST'])
def cities_list():

    cities = app_db.session.query(City).order_by(City.name).all()

    thead_th_items = [
        {
            'col_title': '#',
        },
        {
            'col_title': 'City',
        },
        {
            'col_title': 'Friends',
        },
        {
            'col_title': 'Delete',
        },
    ]

    tbody_tr_items = []
    for city in cities:
        friend_names = ''
        if city.friends:
            friend_names = ', '.join([x.name for x in city.friends])

        tbody_tr_items.append([
            {
                'col_value': city.id,
            },
            {
                'col_value': city.name,
                'url': url_for('manage_data.city_edit', city_id=city.id),
            },
            {
                'col_value': friend_names,
            },
            {
                'col_value': 'delete',
                'url': url_for('manage_data.city_delete', city_id=city.id),
            }
        ])
    
    return render_template(
        'items_list.html',
        title='Cities',
        thead_th_items=thead_th_items,
        tbody_tr_items=tbody_tr_items,
        item_new_url=url_for('manage_data.city_new'),
        item_new_text='New city',
    )

@manage_data_blueprint.route('/hobbies/list', methods=['GET', 'POST'])
def hobbies_list():

    hobbies = app_db.session.query(Hobby).order_by(Hobby.name).all()

    thead_th_items = [
        {
            'col_title': '#',
        },
        {
            'col_title': 'Name',
        },
        {
            'col_title': 'Friends',
        },
        {
            'col_title': 'Delete',
        },
    ]

    tbody_tr_items = []
    for hobby in hobbies:
        friend_names = ''
        if hobby.friends:
            friend_names = ', '.join([x.name for x in hobby.friends])
        tbody_tr_items.append([
            {
                'col_value': hobby.id,
            },
            {
                'col_value': hobby.name,
                'url': url_for('manage_data.hobby_edit', hobby_id=hobby.id),
            },
            {
                'col_value': friend_names,
            },
            {
                'col_value': 'delete',
                'url': url_for('manage_data.hobby_delete', hobby_id=hobby.id),
            }
        ])

    return render_template(
        'items_list.html',
        title='Hobbies',
        thead_th_items=thead_th_items,
        tbody_tr_items=tbody_tr_items,
        item_new_url=url_for('manage_data.hobby_new'),
        item_new_text='New hobby',
    )

@manage_data_blueprint.route('/friend/new', methods=['GET', 'POST'])
def friend_new():

    item = Friend()
    form = FriendNewForm()
    form.city.query = app_db.session.query(City).order_by(City.name)
    form.hobbies.query = app_db.session.query(Hobby).order_by(Hobby.name)

    if form.validate_on_submit():
        form.populate_obj(item)
        app_db.session.add(item)
        app_db.session.commit()
        flash('Friend added: ' + item.name, 'info')
        return redirect(url_for('manage_data.friends_list'))

    return render_template('item_new_edit.html', title='New friend', form=form)

@manage_data_blueprint.route('/friend/edit/<int:friend_id>', methods=['GET', 'POST'])
def friend_edit(friend_id):

    item = app_db.session.query(Friend).filter(Friend.id == friend_id).first()
    if item is None:
        abort(403)

    form = FriendEditForm(obj=item)
    form.city.query = app_db.session.query(City).order_by(City.name)
    form.hobbies.query = app_db.session.query(Hobby).order_by(Hobby.name)

    if form.validate_on_submit():
        form.populate_obj(item)
        app_db.session.commit()
        flash('Friend updated: ' + item.name, 'info')
        return redirect(url_for('manage_data.friends_list'))

    return render_template('item_new_edit.html', title='Edit friend', form=form)

@manage_data_blueprint.route('/friend/delete/<int:friend_id>', methods=['GET', 'POST'])
def friend_delete(friend_id):

    item = app_db.session.query(Friend).filter(Friend.id == friend_id).first()
    if item is None:
        abort(403)

    form = FriendDeleteForm(obj=item)

    item_name = item.name
    if form.validate_on_submit():
        app_db.session.delete(item)
        app_db.session.commit()
        flash('Deleted friend: ' + item_name, 'info')
        return redirect(url_for('manage_data.friends_list'))

    return render_template('item_delete.html', title='Delete friend', item_name=item_name, form=form)

@manage_data_blueprint.route('/city/new', methods=['GET', 'POST'])
def city_new():

    item = City()
    form = CityNewForm()

    if form.validate_on_submit():
        form.populate_obj(item)
        app_db.session.add(item)
        app_db.session.commit()
        flash('City added: ' + item.name, 'info')
        return redirect(url_for('manage_data.cities_list'))

    return render_template('item_new_edit.html', title='New city', form=form)

@manage_data_blueprint.route('/city/edit/<int:city_id>', methods=['GET', 'POST'])
def city_edit(city_id):

    item = app_db.session.query(City).filter(City.id == city_id).first()
    if item is None:
        abort(403)

    form = CityEditForm(obj=item)

    if form.validate_on_submit():
        form.populate_obj(item)
        app_db.session.commit()
        flash('City updated: ' + item.name, 'info')
        return redirect(url_for('manage_data.cities_list'))

    return render_template('item_new_edit.html', title='Edit city', form=form)

@manage_data_blueprint.route('/city/delete/<int:city_id>', methods=['GET', 'POST'])
def city_delete(city_id):

    item = app_db.session.query(City).filter(City.id == city_id).first()
    if item is None:
        abort(403)

    form = CityDeleteForm(obj=item)

    item_name = item.name
    if form.validate_on_submit():
        app_db.session.delete(item)
        app_db.session.commit()
        flash('Deleted city: ' + item_name, 'info')
        return redirect(url_for('manage_data.cities_list'))

    return render_template('item_delete.html', title='Delete city', item_name=item_name, form=form)

@manage_data_blueprint.route('/hobby/new', methods=['GET', 'POST'])
def hobby_new():

    item = Hobby()
    form = HobbyNewForm()

    if form.validate_on_submit():
        form.populate_obj(item)
        app_db.session.add(item)
        app_db.session.commit()
        flash('Hobby added: ' + item.name, 'info')
        return redirect(url_for('manage_data.hobbies_list'))

    return render_template('item_new_edit.html', title='New hobby', form=form)

@manage_data_blueprint.route('/hobby/edit/<int:hobby_id>', methods=['GET', 'POST'])
def hobby_edit(hobby_id):

    item = app_db.session.query(Hobby).filter(Hobby.id == hobby_id).first()
    if item is None:
        abort(403)

    form = HobbyEditForm(obj=item)

    if form.validate_on_submit():
        form.populate_obj(item)
        app_db.session.commit()
        flash('Hobby updated: ' + item.name, 'info')
        return redirect(url_for('manage_data.hobbies_list'))

    return render_template('item_new_edit.html', title='Edit hobby', form=form)

@manage_data_blueprint.route('/hobby/delete/<int:hobby_id>', methods=['GET', 'POST'])
def hobby_delete(hobby_id):

    item = app_db.session.query(Hobby).filter(Hobby.id == hobby_id).first()
    if item is None:
        abort(403)

    form = HobbyDeleteForm(obj=item)

    item_name = item.name
    if form.validate_on_submit():
        app_db.session.delete(item)
        app_db.session.commit()
        flash('Deleted hobby: ' + item_name, 'info')
        return redirect(url_for('manage_data.hobbies_list'))

    return render_template('item_delete.html', title='Delete hobby', item_name=item_name, form=form)

Add templates for the Blueprint

We have three shared templates for list, new & edit, delete. The render_form() function of Bootstrap-Flask puts the form on the page.

{# items_list.html #}

{% extends "base.html" %}

{% block content %}

	<h1>
		{{ title }}
	</h1>
	{% if tbody_tr_items %}
	<table class="table">
		<thead>
		<tr>
			{% for thead_th_item in thead_th_items %}
			<th scope="col">
				{{ thead_th_item.col_title }}
			</th>
			{% endfor %}
		</tr>
		</thead>
		<tbody>
			{% for tbody_tr_item in tbody_tr_items %}
			<tr>
				{% for tbody_td_item in tbody_tr_item %}
				<td>
					{% if tbody_td_item.url %}
						<a href="{{ tbody_td_item.url }}">
							{{ tbody_td_item.col_value }}
						</a>
					{% else %}
						{% if tbody_td_item.col_value %}
							{{ tbody_td_item.col_value }}
						{% else %}
							-
						{% endif %}
					{% endif %}
				</td>
				{% endfor %}
			</tr>
			{% endfor %}
		</tbody>
	</table>
	{% else %}
		<p>
		No items found
		</p>
	{% endif %}
	<a class="btn btn-outline-dark" href="{{ item_new_url }}" role="button">
		{{ item_new_text }}
	</a>

{% endblock %}

New and edit operation requires only one template.

{# item_new_edit.html #}

{% from 'bootstrap/form.html' import render_form %}

{% extends "base.html" %}

{% block content %}

	<h1>
		{{ title }}
	</h1>
	{{ render_form(form) }}

{% endblock %}

And finally the delete template.

{# item_delete.html #}

{% from 'bootstrap/form.html' import render_form %}

{% extends "base.html" %}

{% block content %}

	<h1>
		{{ title }}
	</h1>
	<p>
		Confirm you want to delete: {{ item_name }}
	</p>

	{{ render_form(form) }}

{% endblock %}

Update the menu in the base template

In the base template we add navigation items for Friends, Cities and Hobbies:

{# home.html #}

{% from 'bootstrap/nav.html' import render_nav_item %}
{% from 'bootstrap/utils.html' import render_messages %}
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <title>QuerySelectField and QuerySelectMultipleField</title>
    {{ bootstrap.load_css() }}
</head>
<body>
    <nav class="navbar navbar-expand-lg navbar-light bg-light mb-4">
        <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarSupportedContent"
                aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
            <span class="navbar-toggler-icon"></span>
        </button>
        <div class="collapse navbar-collapse" id="navbarSupportedContent">
            <ul class="navbar-nav mr-auto">
                {{ render_nav_item('index', 'Home', use_li=True) }}
                {{ render_nav_item('manage_data.friends_list', 'Friends', use_li=True) }}
                {{ render_nav_item('manage_data.cities_list', 'Cities', use_li=True) }}
                {{ render_nav_item('manage_data.hobbies_list', 'Hobbies', use_li=True) }}
            </ul>
        </div>
    </nav>

    <main class="container">
        {{ render_messages(container=False, dismissible=True) }}
        {% block content %}{% endblock %}
    </main>

    {{ bootstrap.load_js() }}
</body>
</html>

Add the Blueprint to factory.py

We add a few lines to the factory.py to add the Blueprint. The final version becomes:

# factory.py

from flask import Flask, request, g, url_for, current_app, render_template
from flask_wtf.csrf import CSRFProtect
from flask_bootstrap import Bootstrap

from .services import app_logging, app_db
from .model import Friend

def create_app():

    app = Flask(__name__)

    app.config.from_object('config.ConfigDevelopment')

    # services
    app.logger = app_logging.init_app(app)
    app_db.init_app(app)
    app.logger.debug('test debug message')

    Bootstrap(app)

    csrf = CSRFProtect()
    csrf.init_app(app)

    # blueprints
    from .blueprints.manage_data.views import manage_data_blueprint
    app.register_blueprint(manage_data_blueprint, url_prefix='/manage-data')

    @app.teardown_appcontext
    def teardown_db(response_or_exception):
        if hasattr(app_db, 'session'):
            app_db.session.remove()

    @app.route('/')
    def index():
        friends = app_db.session.query(Friend).order_by(Friend.name).all()
        return render_template(
            'home.html',
            welcome_message='Hello world',
            friends=friends,
        )

    return app

Run the finished application

Again, go to the project directory and type:

python3 run.py

Point your browser to 127.0.0.1:5000. Now you should see the finished application. There are menu items for Friends, Cities and Hobbies. Clicking Friends, takes you to the list of friends. Here you can add, edit and delete friends. The same applies to cities and hobbies.

Download Docker image and run

If you want to run this application you can download the Docker image (tgz, 64 MB):

queryselectfield_100.tgz

The md5sum is:

b4f8116e6b8f30c4980a7ff96f0428a5

To load the image:

docker load < queryselectfield_100.tgz

To run:

docker run --name queryselectfield -p 5000:5000 queryselectfield:1.00

And then point your browser to 127.0.0.1:5000.

Summary

This is an example with many limitations but it shows the power of the WTForms QuerySelectField and QuerySelectMultipleField. And by including Bootstrap-Flask we can create a menu without any effort and do not have to render the forms ourselves.

Of course this is not production ready but you can refine it, add checks, more fields, etc. The QuerySelectField is great for one-to-many relationships and QuerySelectMultipleField is great for many-to-many relationships. They offer enough flexibility to build your application.

Links / credits

Alembic 1.5.5 documentation » Tutorial
https://alembic.sqlalchemy.org/en/latest/tutorial.html

Bootstrap-Flask
https://bootstrap-flask.readthedocs.io/en/stable/

Larger Applications (inluding the Circular Imports warning)
https://flask.palletsprojects.com/en/1.1.x/patterns/packages/

WTForms-SQLAlchemy
https://wtforms-sqlalchemy.readthedocs.io/en/stable/

Leave a comment

Comment anonymously or log in to comment.

Comments

Leave a reply

Reply anonymously or log in to reply.