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
    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.

6 comments:

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...

Catherine,

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.

Regards

Ravi

--- FROM TURBOGEARS ---
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="kap_r...@hotmail.com", text="Some Text") ---> This worked 100%
inserting the row into the table.

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

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'kap_r...@hotmail.com', 'name': u'Ravi'}

--- FROM PYTHON CLI ---

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='kap_r...@hotmail.com'
>>> 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 = comments_table.select()
>>> rows = sel.execute()
>>> print rows.fetchall()

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

AS YOU CAN SEE, THE INSERT WAS SUCCESSFUL

Catherine said...

Ravi,

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'

with

>>> 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

sqlalchemy.convert_unicode=True

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.

See http://www.sqlalchemy.org/docs/dbengine.html

and

http://trac.turbogears.org/ticket/1215

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.