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¶
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