Extensible CheckBox List

Introducing a brand-new widget for TurboGears: Extensible CheckBox List!

To use it:

1. easy_install xcbl
2. In your controller.py,
   from xcbl.widgets import ExtensibleCheckBoxList
3. In your form's WidgetsList,
   pets = ExtensibleCheckBoxList(options=['cats','dogs','turtles'])
   (exactly the same as you would for an ordinary CheckBox List widget)

The result - a CheckBox List with an "other" field that lets the user hand-enter new options - as many as they want.

Unfortunately, the new items will appear beneath the "Other:" box, not above it, unless you upgrade your MochiKit to 1.4. Details on how to do that and more are here.

Developing it was lots of fun - it really shows how easily a relative amateur can contribute to TurboGears. I hope to write up a "Developing and Contributing TurboGears Widgets for Dummies" document soon, based on the experience.

Unicode error with TurboGears and Oracle

Thanks to kap_ravi's comment, I tracked down a surprisingly big and poorly-documented stumbling block for folks using TG with Oracle.

I've updated the original TG+Oracle blog post to reflect it, but the gist of it is that you're likely to get

SQLError: (NotSupportedError) Variable_TypeByValue(): unhandled data
type unicode

errors unless you include

sqlalchemy.convert_unicode = True

in your .cfg files.

Configuring Oracle for proper Unicode use may avoid the need for this; I'm not sure. My dreadfully mononational working environment has prevented me from developing any Unicode experience.

Excel reports from TurboGears

Last week, I had the chance to design, build, and deploy a TurboGears application to outside customers for the first time ever. It was awesome! It was a simple survey form - well, simple in concept, but various questions were dependent in various ways on the answers to other questions, so I did have to get into the JavaScript, praise be to MochiKit.

The application owner wanted to see his survey results in (hmm, can you guess?) MS Excel. "No problem", I said, and then was surprised at how much trouble I had figuring out how to do that straight from TG. AFAIK, TurboGears doesn't have a handy way to output CSV. I could certainly produce a webpage with CSV data, and even use the default method in my controller to serve it with a .CSV extension ("http://myserver/report.csv"), but it still had webpage-type headers and thus Excel still didn't know what to do with it. And no, I wasn't about to tell him to cut-and-paste.

What I eventually hit on was to provide the results as a simple HTML table, then take advantage of the fact that Excel can open a webpage that contains (only) a table rather nicely. Then I gave him a batchfile that simply said

"c:\Program Files\Microsoft Office\Office11\excel.exe" http://myserver/report

... double-clicking it gets a live view of the data.

If he'd been cooler, the batchfile could just as well have said
"c:\Program Files\OpenOffice.org 2.2\program\scalc.exe" http://myserver/report
- I tried that, too, it works.

Actually, I didn't serve it under http://myserver/report, I served it under https://myserver/reportWithHideousGUIDblahblah4242rtfm22hike. That was my quick-and-dirty way to provide some basic security - the report URL is unguessable, so unless he shares the link, it should be safe. On the other hand, anybody who can sniff his request can extract the magic URL and use it. Can anybody comment on how much of a risk that is? There's no real sensitivity to the data in this case, but it would be nice to know if this the-URL-is-the-password scheme is a worthwhile shortcut or is terribly dumb.

TurboGears and Oracle

This weekend saw the fulfillment of a lifelong dream - I got TurboGears working against an Oracle database!

For general information, I recommend the ToDo list tutorial and Splee's post on SQLAlchemy/TG. But there are some particulars you'll need to know to work with Oracle... so here's a super-basic example to demonstrate.

1. After installing TurboGears, run at the command prompt:
   

   tg-admin quickstart --sqlalchemy

2. In dev.cfg, replace

   sqlalchemy.dburi="sqlite:///devdata.sqlite"

   with

   sqlalchemy.dburi="oracle://scott:tiger@orcl"

   
   [EDIT April 25, 2007:] Unless you've specifically configured your Oracle database to support Unicode (and maybe even if you have - I'm still fuzzy on this part), you'll also need to set
   

   sqlalchemy.convert_unicode=True

   
   If you decide to leave it out, then start getting
   

   SQLError: (NotSupportedError) Variable_TypeByValue(): unhandled data
   type unicode

   you'll know you needed this parameter set.
   
3. Add the following to model.py:
   

   from turbogears.database import bind_meta_data
   bind_meta_data()
   from sqlalchemy.ext.assignmapper import assign_mapper
   emp_table = Table("emp", metadata, autoload=True)
   class Emp(object):
       pass
   assign_mapper(session.context, Emp, emp_table)

4. To controllers.py, add

   
   import model

   then add to the Root class

   
       @expose(template="myProjectName.templates.emps")
       def emps(self):
           emps_list = model.Emp.select()
           return dict(emps=emps_list)

5. Copy templates/welcome.kid to templates/emps.kid, and replace the document body with

   
     <ul>
       <li py:for="emp in emps">
         ${emp.ename} : ${emp.job}
       </li>
     </ul>

6. From the command prompt, run
   

   python start-myProjectName.py

7. Point a browser at http://localhost:8080/emps

There - you're listing data from SCOTT.EMP!

You can, of course, manually define the columns of your tables; using autoload is simply more convenient and error-proof. It'll only work against tables that have a primary key, though. If you don't use autoload, you don't need to call bind_meta_data in model.py.

TurboGears has recently added SQLAlchemy, as an alternative to SQLObject, for its database-access layer. I don't know much about their relative merits, but it seems like SQLAlchemy may be more friendly to a database-centered (as opposed to object-programming-centered) point of view. In any case, SQLAlchemy has Oracle support, whereas SQLObject's Oracle support still hasn't been integrated into the main codebase. Thus, I'm using the SQLAlchemy flavor of TurboGears.