[albatross-users] More SQL goodness

[Michael C. Neel]

A while back, I posted my alx-sql tag, which has served me greatly so
far, but I got tired of cluttering up a template with a lot of basic
queries for lookup tables in select boxes.  That, and not being able to
mix optionexpr with <al-option> tags bugged me.  So here is my
alx-sqlselect and alx-sqloption tags, which if you find useful feel free
to use =D

A few notes, first I have a pretty standard way of creating lookup
tables in my apps, and these tags are written with that in mind, they
have some flexability but you still may need to alter the code to work
with your db scheme.  Also, this is still in development, and may have
an issue or two, but so far it's been working fine here.  My globals.py
defines db, which is used below (another convention of mine).  For
reference, my lookup tables look like:

CREATE TABLE lookup (
  lookup_id int(10) unsigned zerofill NOT NULL auto_increment,
  ordering int(10) unsigned NOT NULL default '0',
  code varchar(10) NOT NULL default '',
  text varchar(255) NOT NULL default '',
  deleted enum('Y','N') NOT NULL default 'N',
  PRIMARY KEY  (lookup_id)
) TYPE=MyISAM COMMENT='Lookup Table for a generic lookup'

The tag in use looks like:

              <alx-sqlselect name="staff_id">
                <al-option value="xxx">Pick one...</al-option>
                <alx-sqloption table="staff" index="staff_id"
text="name">
                <al-option value="0">Other</al-option>
              </alx-sqlselect>

You can also have a where and an order argument.  Notice that the
al-option can be mixed in, and the alx-option tag is and empty tag (i.e.
no </alx-option>).  Also, since the alx-sqlselect is sub classed from
the standard al-select, all normal options (like nameexpr) will still
work, and just like before and optionexpr will override the
al-option/alx-sqloption tags (so there would be no point in doing that,
use al-select instead).  Here is the python code:

import albatross
from globals import *

class SqlSelect(albatross.tags.Select):
    name = "al-sqlselect"

    def append(self, tag):
        if isinstance(tag, SqlOption) or isinstance(tag,
albatross.tags.Option):
            self.options.append(tag)
        else:
            EnclosingTag.append(self, tag)

class SqlOption(albatross.EmptyTag):
    name = 'al-sqloption'

    def to_html(self, ctx, select_value):
        if self.has_attrib('table'):
            table = self.get_attrib('table')

            # set defaults
            index = "%s_id" % table
            text = "text"
            order = "ordering"
            where = "deleted = 'N'"

            if self.has_attrib('index'):
                index = self.get_attrib('index')
            if self.has_attrib('text'):
                text = self.get_attrib('text')
            if self.has_attrib('order'):
                order = self.get_attrib('order')
            if self.has_attrib('where'):
                where = self.get_attrib('where')

            c = db.cursor()
            c.execute("""SELECT %s, %s FROM %s WHERE %s ORDER BY %s""" %
(index, text, table, where, order))

            for (value, label) in c.fetchall():
                ctx.write_content('<option')
                self.write_attribs_except(ctx, 'index', 'text',
'order','where','table')

                value = str(value)
                if type(select_value) is type(''):
                    if value == select_value:
                        ctx.write_content(' selected')
                elif type(select_value) in (type([]), type(())):
                    if value in select_value:
                        ctx.write_content(' selected')

                ctx.write_content(' value="%s">' %
albatross.tags.escape(value))
                ctx.write_content(albatross.tags.escape(label))


Hope this is helpful to somebody out there, and have a great weekend!

Mike