.. _tw_cookbook_dynamicselect:

How To Create A Database-Driven Select Field
============================================

Most developers create database tables which allow select fields in
their forms to be dynamically updated when a change occurs in the
database.  The challenge is that widgets are stateless, and therefore
select fields expect to have the same options from the time they are
instantiated.  However, ToscaWidgets does allow widget parameters to
change "on the fly." Here is a description of one way a developer
might update these parameters and allow dynamic select options.

Consider the following model snippet::

 from sqlalchemy import Table, Column, types
 from sqlalchemy.orm import mapper

 genera_table = Table("genera", metadata,
    Column("id", types.Integer, primary_key=True),
    Column("name", types.String(100), nullable=False),
    Column("description", types.Text, nullable=True),
    )

 class Genera(object):
    pass

 mapper(Genera, genera_table)

The trick here is to override the update_params method of
SingleSelectField to query the database for the records, and then add
them to the 'options' parameter before processing the rest of the
TW.::
 
 from mypackage.model import Genera
 from tw.forms import SingleSelectField
 from tg2 import DBSession
 
 class MySelect(SingleSelectField):
    def update_params(self, d):
        rows = DBSession.query(Genera).fetchall()
        rows= [(row['id'], row['name']) for row in rows]
        d['options']= rows
        SingleSelectField.update_params(self, d)
        return d

A more intelligent solution would be to cache the rows and then
refresh them every so often.::
 
 from mypackage.model import Genera
 from tw.forms import SingleSelectField
 from tg2 import DBSession
 import time

 timestamp = time.time()
 options = []
 
 class MySelect(SingleSelectField):
    def update_params(self, d):
        global timestamp
        global options
   
        #refresh once a minute at the most:
        if time.time() - timestamp > 60:
            rows = DBSession.query(Genera).fetchall()
            options = [(row['id'], row['name']) for row in rows]
   
        d['options'] = options
        SingleSelectField.update_params(self, d)
        return d
