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.