Sunday, November 05, 2006

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

    [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

    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
    from turbogears.database import bind_meta_data
    from sqlalchemy.ext.assignmapper import assign_mapper
    emp_table = Table("emp", metadata, autoload=True)
    class Emp(object):
    assign_mapper(session.context, Emp, emp_table)
  4. To, add

    import model
    then add to the Root class

    def emps(self):
    emps_list =
    return dict(emps=emps_list)
  5. Copy templates/welcome.kid to templates/emps.kid, and replace the document body with

    <li py:for="emp in emps">
    ${emp.ename} : ${emp.job}
  6. From the command prompt, run
  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

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.


Steve Allen said...

Cool--you've just removed the last obstacle for my jump to TurboGears!

mike bayer said...

hey catherine -

im glad to see you working with SA (also thanks for the oracle patch, ill have a look at that soon)...and you are correct that SA is a little more "database centric" than the typical scripting language database tool. while im mainly a developer, i have about 5 years of oracle experience, and database-centric types and DBAs are definitely one of my "target audiences" I hope to attract to Python.

so thanks for the interest and keep the suggestions coming....

- mike

kap_ravi said...


Thats just great the job with SA+TG+Oracle. A new alternative for the J2EE stuff.

But I have got stuck with a pretty simple problem. If you can help me out I would be thankful to you.

I have configured Turbogears and created a quick-start web site with
SQL Alchemy as the database modeler. The kid template pages where I
showed some data from the database are working perfectly. But the part
of the controller where I am trying to do an INSERT into a table of my
oracle database using the values of the bind variables of a remote
form that I have shown, is not working.

Apparently there is some problem with the UNICODE data that sqlAlchemy
sends to oracle from the turbo gears framework. But I have tried the
same from the python command line with named parameters and its works
without any issues.

I furnish below all the code parts related to the issue and any kind
help is appreciated.



The method in the controller which is in-charge of doing the INSERT
into the database using the received parameters

def add(self, _name, _email, _text, _somedate, *args):
#ins_comment = model.comments_table.insert().execute(name="Ravi",
email="", text="Some Text") ---> This worked 100%
inserting the row into the table.

print _name, _email, _text, _somedate ---> This prints the values

ins_comment = model.comments_table.insert().execute(email=_email,
name=_name, text=_text) ---> This is not doing the insert into the
oracle table correctly but throwing the following error.

SQLError: (NotSupportedError) Variable_TypeByValue(): unhandled data
type unicode 'INSERT INTO comments (email, name, text) VALUES
(:email, :name, :text)' {'text': u'This is a comment', 'email':
u'', 'name': u'Ravi'}


Python 2.4.3 (#69, Mar 29 2006, 17:35:34) [MSC v.1310 32 bit (Intel)]
on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> from sqlalchemy import *
>>> db = create_engine('oracle://scott:tigress@xe')
>>> metadata = BoundMetaData(db)
>>> comments_table = Table("comments", metadata, autoload=True)
>>> v_email=''
>>> v_name='Ravi Kasibhatla'
>>> v_text='This is a comment from Python CLI'
>>> ins = comments_table.insert()
>>> ins.execute(email=v_email, name=v_name, text=v_text)

sqlalchemy.engine.base.ResultProxy object at 0x00D503D0
>>> sel =
>>> rows = sel.execute()
>>> print rows.fetchall()

[('', 'Ravi Kasibhatla', 'This is a comment from
Python CLI', datetime.datetime(2007, 4, 25, 11, 24, 13))]


Catherine said...


Hmm... good question! It's not so simple after all... it seems to trace down to cx_Oracle itself.

If you execute SQL through a cx_Oracle cursor that includes bind variables, and have a unicode value in the dictionary of bind variables, you get precisely this error, even with TurboGears nowhere in sight.

curs.execute('SELECT * FROM dual WHERE dummy = :dum', {'dum':u'X'})

cx_Oracle.NotSupportedError: Variable_TypeByValue(): unhandled data type unicode

I think the reason you can insert successfully separately from TG is that, when you do it by hand, you're using ordinary strings, not unicode ones. Replace your

>>> v_name='Ravi Kasibhatla'


>>> v_name=u'Ravi Kasibhatla'

and you'll see the error.

I'm still working on this. Stay tuned...

Catherine said...

I got it!

In your dev.cfg (or prod.cfg, or whatever), try setting


I suspect that I have the problem because I didn't set up my Oracle database to handle unicode. TG handles all strings as unicode, so it needs to be converted before it can be shipped up to Oracle.



which mentions the (otherwise undocumented, AFAIK) fact that you can pass "extra" parameters to SQLAlchemy's create_engine call by using sqlalchemy.parameter in your .cfg file.

Graham said...

Thanks Catherine : a great jumpstart for myself. I have 13 years Oracle experience as an Oracle DBA but I haven't done very much in the way of development. I'll post now and then to inform you of my progress as a Turbogears newbie if that's OK.