Using Pyramid with Deform to Edit/Create records in SQLAlchemy backed database

While working with Pyramid it was worth taking a good look at deform. During my brief look before, the one thing that appeared to be missing was a method to quickly integrate with SQLAlchemy backed data. For a handful of forms, manually creating the appstruct to be passed to the Form wouldn’t be difficult, but, for some more significant applications, it could be quite cumbersome. The following is a one page application that allows you to create a new user, or, edit an existing user by specifying the userid in the URL. The two pieces that do the real work are record_to_appstruct and merge_session_with_post. record_to_appstruct takes the class returned from SQLAlchemy and converts it to an appstruct that deform likes. Once we get the validated data, we merge the record with the post items using merge_session_with_post, merge the record and present a screen with the post data.

This is more a proof of concept, but, works well enough that we were able to convert quite a few forms and work more closely with Deform and Pyramid as we do more development.

__init.py__:


from pyramid.configuration import Configurator
from paste.deploy.converters import asbool

from defo.models import initialize_sql

def app(global_config, **settings):
    """ This function returns a WSGI application.
    
    It is usually called by the PasteDeploy framework during 
    ``paster serve``.
    """
    db_string = settings.get('db_string')
    if db_string is None:
        raise ValueError("No 'db_string' value in application configuration.")
    db_echo = settings.get('db_echo', 'false')
    initialize_sql(db_string, asbool(db_echo))
    config = Configurator(settings=settings)
    config.begin()
    config.add_static_view('static', 'defo:static')
    config.add_route('home', '/', view='defo.views.edit',
                     view_renderer='test.mako')
    config.add_route('homeid', '/:id', view='defo.views.edit',
                     view_renderer='test.mako')
    config.end()
    return config.make_wsgi_app()

views.py:

from defo.models import DBSession
from defo.models import MyModel

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

from sqlalchemy import *
from sqlalchemy.databases import mysql
from sqlalchemy.orm import relation, backref, synonym
from sqlalchemy.orm.exc import NoResultFound

import deform
import colander
from webhelpers import constants

class UserSchema(colander.Schema):
    username = colander.SchemaNode(colander.String())
    contact = colander.SchemaNode(colander.String())
    email = colander.SchemaNode(colander.String())
    company = colander.SchemaNode(colander.String())
    addr1 = colander.SchemaNode(colander.String())
    addr2 = colander.SchemaNode(colander.String(), missing=u'',)
    city = colander.SchemaNode(colander.String())
    state = colander.SchemaNode(colander.String())
    zip = colander.SchemaNode(colander.String())
    country = colander.SchemaNode(
        colander.String(),
        widget = deform.widget.SelectWidget(values=constants.country_codes()),
    )
    phone = colander.SchemaNode(colander.String())

class AuthUser(Base):
    __tablename__ = 'auth_users'

    id = Column(mysql.BIGINT(20, unsigned=True), primary_key=True, autoincrement=True)
    username = Column(Unicode(80), nullable=False)
    email = Column(Unicode(80), nullable=False)
    contact = Column(Unicode(80), nullable=False)
    company = Column(Unicode(80), nullable=False)
    addr1 = Column(Unicode(80), nullable=False)
    addr2 = Column(Unicode(80))
    city = Column(Unicode(80), nullable=False)
    state = Column(Unicode(80), nullable=False)
    zip = Column(Unicode(80), nullable=False)
    country = Column(Unicode(80), nullable=False)
    phone = Column(Unicode(80), nullable=False)

def record_to_appstruct(self):
    return dict([(k, self.__dict__[k]) for k in sorted(self.__dict__) if '_sa_' != k[:4]])

def merge_session_with_post(session, post):
    for key,value in post:
        setattr(session, key, value)
    return session

def edit(request):
    dbsession = DBSession()
    if request.matchdict.has_key('id'):
        record = dbsession.query(AuthUser). \
                 filter_by(id=request.matchdict['id']).first()
    else:
        record = AuthUser()

    schema = UserSchema()
    form = deform.Form(schema, buttons=('submit',))
    if request.POST:
        try:
            appstruct = form.validate(request.POST.items())
        except deform.ValidationFailure, e:
            return {'form':e.render()}
        record = merge_session_with_post(record, request.POST.items())
        dbsession.merge(record)
        dbsession.flush()
        return {'formdata':appstruct}
    else:
        appstruct = record_to_appstruct(record)
    return {'form':form.render(appstruct=appstruct)}

test.mako:

<html>
<head>
  <title>
    Deform Demo Site
  </title>
  <!-- Meta Tags -->
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  <!-- CSS -->
  <link rel="stylesheet" href="/static/css/form.css" type="text/css" />
  <link rel="stylesheet" href="/static/css/theme.css" type="text/css" />
  <!-- JavaScript -->
  <script type="text/javascript"
          src="/static/scripts/jquery-1.4.2.min.js"></script>
  <script type="text/javascript"
          src="/static/scripts/deform.js"></script>
</head>
<body>
this is a test template in mako
<p>
% if form:
  ${form|n}
% else:
We got our form data: ${formdata}
% endif
<script type="text/javascript">
   deform.load()
</script>
</body>
</html>

Tags: , , ,

8 Responses to “Using Pyramid with Deform to Edit/Create records in SQLAlchemy backed database”

  1. Eric Says:

    Hi,
    As the number one site on Google for Pyramid and Deform… I am very new to website development in Python (or actually anything more than Notepad) and am working with Pyramid because Python is my favorite language. I’m struggling with how to handle forms and would like to know if you think it is a viable approach for people well^n below Chris M’s level? I panicked when I read that I would have to convert all of the Chameleon templates to Mako, and while I’ve started building my own Mako template library, my head exploded when I looked at the Chameleon syntax…

    If not Deform, do you have a recommendation? My next choice would probably be pyramid_simpleform, I found a bunch of errors when I tried it but Dan Jacob fixed them immediately. After that it looks like doing it myself with Formencode, et. al.

    Thanks for any advice,
    Eric

  2. cd34 Says:

    I use mako templating and use deform. Unless you’re changing the style of the deform templates, it will render its portion and insert it into your mako (or jinja2) template without you needing to change the forms. If you want to modify the actual look of the forms, you would need to use Chameleon or Mako. I have contemplated converting the forms over to mako and emulating the TableForm layout that ToscaWidgets uses which provides a more compact data entry screen. If you’re happy with the List Form layout, you can just modify the .css that is used to style it differently without having to touch any of the Chameleon templates.

    Most of the form libraries are very disconnected from the SQLAlchemy schema, which means you have a lot of double entry. If your data entry isn’t tightly tied to your database schema, then deform or simpleform would probably be my recommendation. If you do a lot of data entry that is highly coupled to your database, you might also take a look at http://docs.formalchemy.org/pyramid_formalchemy/ which uses your SQLAlchemy schema to generate forms. You can of course specify included or omitted fields.

  3. Eric Says:

    Thanks, I’ll take a look at FormalAlchemy. I used ToscaWidgets when I was working with TG2.1 and one of my concerns is that when something stopped working (Datepicker as I recall) I had no good way to troubleshoot it since I didn’t understand all the interactions. After posting this I went back to pyramid_simpleforms, found some more documentation issues and while I don’t appear to have it totally working it seems simple enough that I have a chance of troubleshooting on my own. With that simplicity probably comes a need to do more work with the other elements of putting together a page but I need to learn sometime…

    Thanks again,
    Eric

  4. Roberto Allende Says:

    Excellent post!

  5. jdnavarro Says:

    With deform it seems there is always some code duplication between the colander schema definition and the class that assembles the data from the database. In the case of SQLAlchemy have you come across any other pattern to have a more “DRYer” solution? I’m tempted to look into something related with the SQLAlchemy mapper and creating colander schemas imperatively. Have you considered those?

  6. cd34 Says:

    Deform was inspired by Formish and both are written to use object stores – like ZODB, so, they write the appstruct right to their schemaless storage with their primary key. Once you move to a schema based backend, you have to do some manipulation. If you’re looking to tie your forms more closely to SQLAlchemy, FormAlchemy is very good and well integrated with Pyramid. Form Schema definitions can be as few as 3-4 lines specifying the submit action text, included fields and the class name.

    I thought about using mappers with Deform, but, hadn’t had the chance. With the current system, related rows that use Integers as IDs cause a problem, but I haven’t had time to delve into that.

  7. skurylo Says:

    There is a bug, I think
    record = merge_session_with_post(record, request.POST.items())
    should be:
    record = merge_session_with_post(record, appstruct)

    Otherwise you’re including the original post items which haven’t had their type changed by deform.

    Also I changed record_to_appstruct to be recursive, so that my joined table objects would work:

    def record_to_appstruct(self):
    “””
    Convert a sqlalchemy record into a appstruct for deform
    “””
    new_dict = {}
    for k in sorted(self.__dict__):
    # Ignore sqlalchemy keys
    if ‘_sa_’ != k[:4]:
    try:
    new_dict[k] = record_to_appstruct(self.__dict__[k])
    except AttributeError:
    new_dict[k] = self.__dict__[k]
    return new_dict

  8. cd34 Says:

    That is a good point, and would work properly with the password verification field with deform. Since the code hack was written a two days after Pyramid was released, it was just a quick hack to migrate some Pylons code and to try Deform.

    Thanks for the code update.

Leave a Reply

You must be logged in to post a comment.