Forms based on SQL data

Introduction

The most common task of web forms is to input from and output data to a SQL database. Here are Deform based recipes for common use cases.

Selection widget with SQL vocabulary

websauna.system.form.sqlalchemy.UUIDModelSel provides a Colander schema type for matching (uuid, name) tuples to form elements and then back SQLAlchemy objects. Furthermore a helper function websauna.system.form.sqlalchemy.convert_query_to_tuples() allows us to fill in a value vocabulary for select, radio or checkbox widget from a model query.

Example 1: manual form - pick multiple

Assume models.py:

class Customer(Organization):
    """A customer belonging to one user."""

    __tablename__ = "customer"

    id = Column(UUID(as_uuid=True),
                primary_key=True,
                server_default=sqlalchemy.text("gen_random_uuid()"),)

    name = Column(String(256))
    phone_number = Column(String(256))
    business_id = Column(String(256))

    user_id = Column(ForeignKey("users.id"), nullable=False)
    user = relationship(User,
                        backref=backref("own_customers",
                                        lazy="dynamic",
                                        cascade="all, delete-orphan",
                                        single_parent=True,),
                        uselist=False)

And now we want to user to select from his or her customers on a Deform form:

import colander
import deform
from websauna.system.form.schema import CSRFSchema
from websauna.system.form.sqlalchemy import UUIDModelSet, convert_query_to_tuples
from websauna.system.core.route import simple_route

from .models import Customer


def customers_by_user_widget(node: colander.SchemaNode, kw: dict):
    """Populate selection widget vocabulary with user's customers.

    :param node: The currrent :py:class:`colandar.SchemaNode` for which we are evaluation this.

    :param kw: ``schema.bind()`` arguments passed around.
    """
    request = kw["request"]
    user = request.user
    dbsession = request.dbsession
    query = dbsession.query(Customer).filter(Customer.user == user)
    vocab = convert_query_to_tuples(query, first_column="id", second_column="name")
    return deform.widget.SelectWidget(values=vocab)



class ChooseCustomer(CSRFSchema):
    """A form with a widget to choose one of existing customers of a user."""

    customer =  colander.SchemaNode(

            # Convert selection widget UUIDs back to Customer objects
            UUIDModelSet(model=Customer, match_column="id"),

            title="Choose your customers",

            # A SelectWidget with values lazily populated
            widget=customers_by_user_widget)


@simple_route("/choose-customer",
    route_name="choose_customer",
    renderer='nordledger/choose_customer.html')
def choose_customer(request: Request):
    """Render an invoice creation form."""

    schema = ChooseCustomer()
    schema = schema.bind(request=request)
    form = deform.Form(schema, buttons=("submit",))
    rendered_form = form.render()

    return locals()

Example 2: admin interface - pick one

../../_images/admin-choose-branding.png

Below is an example how to create a relation picker in admin interface.

models.py:

import typing as t
import sqlalchemy as sa
from sqlalchemy import orm
from sqlalchemy.orm import Session
from pyramid_sms.utils import normalize_us_phone_number

from websauna.system.model.json import NestedMutationDict
from websauna.system.model.columns import JSONB
from websauna.system.model.columns import UUID
from websauna.system.model.columns import UTCDateTime
from websauna.system.model.meta import Base
from websauna.utils.time import now
from websauna.system.user.models import User

from typing import Iterable


class Branding(Base):
    """Describe branding info of the site."""

    __tablename__ = "branding"

    #: Internal id
    id = sa.Column(UUID(as_uuid=True), primary_key=True, server_default=sa.text("gen_random_uuid()"))

    #: Human readable name of the organization. Used in footer, such.
    name = sa.Column(sa.String(256))

    #: Misc. bag of branding variables
    branding_data = sa.Column(NestedMutationDict.as_mutable(JSONB), default=dict)

    def __str__(self):
        return self.name or "-"


class Organization(Base):
    """A utility company."""

    __tablename__ = "organization"

    #: Internal id
    id = sa.Column(UUID(as_uuid=True), primary_key=True, server_default=sa.text("gen_random_uuid()"))

    #: Human readable name
    name = sa.Column(sa.String(256))

    #: Branding for this organization
    branding_id = sa.Column(sa.ForeignKey("branding.id"), nullable=True)
    branding = orm.relationship(Branding,
                                    uselist=False,
                                    backref=orm.backref("organizations",
                                        lazy="dynamic",
                                        cascade="all, delete-orphan",
                                        single_parent=False,),)

    #: Substring startswith expression used to evaluate when we should populate app info with this organization
    #: Example: https://foobar.com
    matching_rule = sa.Column(sa.String(256))

    def __str__(self):
        return self.name or "-"

admins.py:

from pyramid.security import Deny, Allow, Everyone
from websauna.system.admin.modeladmin import ModelAdmin, model_admin
from websauna.system.crud import Base64UUIDMapper

from .models import Organization
from .models import Branding


@model_admin(traverse_id="organization")
class Organization(ModelAdmin):
    """Manage user owned accounts and their balances."""

    title = "Organizations"

    model = Organization

    # UserOwnedAccount.id attribute is uuid type
    mapper = Base64UUIDMapper(mapping_attribute="id")

    class Resource(ModelAdmin.Resource):

        def get_title(self):
            return self.get_object().name


@model_admin(traverse_id="branding")
class Branding(ModelAdmin):
    """Manage user owned accounts and their balances."""

    title = "Brandings"

    model = Branding

    # UserOwnedAccount.id attribute is uuid type
    mapper = Base64UUIDMapper(mapping_attribute="id")

    class Resource(ModelAdmin.Resource):

        def get_title(self):
            return self.get_object().name

adminviews.py:

import colander
import deform
import deform.widget

from websauna.system.form.sqlalchemy import UUIDForeignKeyValue
from websauna.system.core.viewconfig import view_overrides

from .models import Branding


@colander.deferred
def branding_selector_widget(node: colander.SchemaNode, kw: dict) -> deform.widget.Widget:
    """Populate selection widget vocabulary with possible branding choice.

    :param node: The currrent :py:class:`colandar.SchemaNode` for which we are evaluation this.

    :param kw: ``schema.bind()`` arguments passed around.
    """
    request = kw["request"]
    dbsession = request.dbsession
    query = dbsession.query(Branding).all()
    vocab = [("", "[No branding]")]
    for branding in query:
        vocab.append((uuid_to_slug(branding.id), branding.name))
    return deform.widget.SelectWidget(values=vocab)


@view_overrides(context=admins.Organization.Resource)
class OrganizationEdit(adminviews.Edit):

    includes =  [
        "name",
        "matching_rule",
        colander.SchemaNode(UUIDForeignKeyValue(model=Branding, match_column="id"), name="branding", widget=branding_selector_widget, missing=None)
    ]
    form_generator = SQLAlchemyFormGenerator(includes=includes)

Example 3: stock group picker

Websauna comes with a group vocabulary you can use to refer to groups.

models.py:

import sqlalchemy as sa
from sqlalchemy import orm

from websauna.system.user.models import Group


class Organization(Base):
    """A company."""

    __tablename__ = "organization"

    # ...

    #: Management group of this organization
    group_id = sa.Column(sa.ForeignKey("group.id"), nullable=True)
    group = orm.relationship(
        Group,
        uselist=False,
        backref=orm.backref(
            "organizations",
             lazy="dynamic",
             single_parent=False,
        ),
    )

adminviews.py:

from websauna.system.user.models import Group
from websauna.system.form.fields import defer_widget_values
from websauna.system.form.sqlalchemy import UUIDForeignKeyValue

from websauna.system.user.schemas import optional_group_vocabulary

from . import admins

@view_overrides(context=admins.OrganizationAdmin)
class OrganizationAdd(adminviews.Add):
    """Automatically set the organization parent."""

    includes = [
        "name",

        # Pick one user group who manages this organization
        colander.SchemaNode(UUIDForeignKeyValue(model=Group, match_column="uuid"), name="group", widget=defer_widget_values(deform.widget.SelectWidget, optional_group_vocabulary, css_class="groups"), missing=None),
    ]
    form_generator = SQLAlchemyFormGenerator(includes=includes)

For more information see