rdbms-subsetter

I've never had a tool I really liked that would extract a chunk of a large production database for testing purposes while respecting the database's foreign keys. This past week I finally got to write one: rdbms-subsetter.

rdbms-subsetter postgresql://user:passwd@host/source_db postgresql://user:passwd@host/excerpted_db 0.001

Getting it to respect referential integrity "upward" - guaranteeing every needed parent record would be included for each child row - took less than a day. Trying to get it to also guarantee referential integrity "downward" - including all child records for each parent record - was a Quixotic idea that had me tilting at windmills for days. It's important, because parent records without child records are often useless or illogical. Yet trying to pull them all in led to an endlessly propagating process - percolation, in chemical engineering terms - that threatened to make every test database a complete (but extremely slow) clone of production. After all, if every row in parent table P1 demands rows in child tables C1, C2, and C3, and those child rows demand new rows in parent tables P2 and P3, which demand more rows in C1, C2, and C3, which demand more rows in their parent tables... I felt like I was trying to cut a little sweater out of a big sweater without snipping any yarns.

So I can't guarantee child records - instead, the final process prioritizes creating records that will fill out the empty child slots in existing parent records. But there will almost inevitably be some child slots left open when the program is done.

I've been using it against one multi-GB, highly interconnected production data warehouse, so it's had some testing, but your bug reports are welcome.

Like virtually everything else I do, this project depends utterly on SQLAlchemy.

I developed this for use at 18F, and my choice of a workplace where everything defaults to open was wonderfully validated when I asked about the procedure for releasing my 18F work to PyPI. The procedure is - and I quote -

Just go for it.

%sql: To Pandas and Back

A Pandas DataFrame has a nice to_sql(table_name, sqlalchemy_engine) method that saves itself to a database.

The only trouble is that coming up with the SQLAlchemy Engine object is a little bit of a pain, and if you're using the IPython %sql magic, your %sql session already has an SQLAlchemy engine anyway. So I created a bogus PERSIST pseudo-SQL command that simply calls to_sql with the open database connection:

%sql PERSIST mydataframe

The result is that your data can make a very convenient round-trip from your database, to Pandas and whatever transformations you want to apply there, and back to your database:

In [1]: %load_ext sql

In [2]: %sql postgresql://@localhost/
Out[2]: u'Connected: @'

In [3]: ohio = %sql select * from cities_of_ohio;
246 rows affected.

In [4]: df = ohio.DataFrame()

In [5]: montgomery = df[df['county']=='Montgomery County']

In [6]: %sql PERSIST montgomery
Out[6]: u'Persisted montgomery'

In [7]: %sql SELECT * FROM montgomery
11 rows affected.
Out[7]: 
[(27L, u'Brookville', u'5,884', u'Montgomery County'),
 (54L, u'Dayton', u'141,527', u'Montgomery County'),
 (66L, u'Englewood', u'13,465', u'Montgomery County'),
 (81L, u'Germantown', u'6,215', u'Montgomery County'),
 (130L, u'Miamisburg', u'20,181', u'Montgomery County'),
 (136L, u'Moraine', u'6,307', u'Montgomery County'),
 (157L, u'Oakwood', u'9,202', u'Montgomery County'),
 (180L, u'Riverside', u'25,201', u'Montgomery County'),
 (210L, u'Trotwood', u'24,431', u'Montgomery County'),
 (220L, u'Vandalia', u'15,246', u'Montgomery County'),
 (230L, u'West Carrollton', u'13,143', u'Montgomery County')]

ddlgenerator

I've had it on github for a while, but I finally released ddlgenerator to PyPI.

I've been frustrated for years that there was no good open-source way to set up RDBMS tables from flat data files. Sure, you could import the data - after setting up the DDL by hand. ddlgenerator handles that; in fact, you can go from zero, setting up and populating a table in a single line. Nothing up my sleeve:

$ psql -c "SELECT * FROM knights"
ERROR:  relation "knights" does not exist
LINE 1: SELECT * FROM knights
                      ^
$ ddlgenerator --inserts postgresql knights.yaml | psql
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
$ psql -c "SELECT * FROM knights"
    name    |         dob         |   kg    | brave 
------------+---------------------+---------+-------
 Lancelot   | 0471-01-09 00:00:00 | 82.0000 | t
 Gawain     |                     | 69.2000 | t
 Robin      | 0471-01-09 00:00:00 |         | f
 Reepacheep |                     |  0.0691 | t

This is a fairly complex tool so I'm sure you'll be using the bug tracker. But I hope you'll enjoy it nonetheless!

I'm available

I'm available for hire! If you need a database expert with lots of programming skill, or a Python programmer with deep database experience, please check out:

Resume / portfolio

But: you must be telecommute-friendly, or in the Dayton area. I'm sorry, but I'm not available to relocate.

released: %sql magic for IPython

Inspired and informed by discussions with the IPython developers at PyCon 2013, I've released ipython-sql, a %sql magic for IPython.

With this, I really think the IPython Notebook will become the most amazing database tool ever. In fact, virtually every computing problem will become a lot more workable when manipulated via the IPython Notebook - you can remember, inspect, and annotate all the steps to investigate whatever your issue is. All hail the Notebook! The FSF really chose well in choosing Fernando Perez for their 2012 award.

Analytic functions

A life history of unforgettable moments of clarity:

1983: Jesus
1994: Special relativity
2011: Analytic functions

I've known for a long time that I really should make use of analytic database functions. I think I've RTFMed a half-dozen times over the years, sometimes fumbling through an example or two, but never really getting them. Tom Kyte's appearance at the Dayton-Oracle User Group finally made it clear to me.

Fortunately, (a different delivery of) Tom's talk was recorded. Go, watch it! You don't have to live without analytic functions one more day!

In my case, I'd been tormented by questions about "the most recent record where..." in a MySQL database; the complex classic SQL queries I was using performed horribly in MySQL. MySQL doesn't have analytic functions, so I converted the reporting database to PostgreSQL. It paid off enormously; analytic functions cut several queries from multiple days to a few minutes. I am a Believer.

On the error message soapbox again

Q. What's wrong with this message?


A. The window that it pops up in doesn't support copy-and-paste (on Fedora 13, at least). If you want to websearch for this message, you have to type it in by hand.

Hey, Oracle - fix it, please? It's the sort of usability problem that I wouldn't nag you about if you were a little FOSS project or a mom-and-pop software shop, but you're a big company with fifty bazillion programmers and entire committees on usability, so I know you can do it.

I'm sure that's the fault of whatever Java GUI library underlies your graphical tools like dbca... so go on and fix the library, and the whole world will benefit!

Also, in keeping with my fixation with good error messages, I implore you to think about error message usability ahead of time. I suspect that error messages get the short end of developer attention because they never appear in sales presentations. Well, actually, they do appear in sales presentations, they're just not supposed to... (although, frankly, watching somebody work through an error is a million times more interesting than watching a polished sales pitch)

Q. What else is wrong?

A. The workaround to this error is to disable SELinux. For the ultimate bestest securest database EVAR, doesn't that seem a little cheesy?

Q. Anything else?

A. Until you actually achieve SELinux compatibility, how about trapping the error with a message that describes the workaround? Failing that, mentioning in the Installation Guide that SELinux should be turned off is a next-best solution... no, come to think of it, forget I said that. "It's on page 732 of the docs" is actually a terrible excuse for leaving a bug unfixed. I don't know how "document it" became an acceptable alternative to "fix it".

Q. Are you finished yet?

A. Almost. After more than 10 years of DBA work, I actually don't know what the proper channel is for making suggestions to Oracle Corp. I end up ranting about it on my blog, approximately the equivalent of wandering into the street and yelling about it. Is that silly or what?

I'm Catherine Devlin, and I approved this message.

Oracle at Ohio LinuxFest

It's not official yet - so you can't find it at the Ohio LinuxFest website - but it looks like Oracle will be a sponsor and exhibitor this year. They're planning to do an Oracle-on-Linux installfest. If you'd like to get your first taste of Oracle on Linux, sign up for LinuxFest (it's free) and prepare to have a blast.

If you're already pretty good at Oracle-on-Linux and would like to help others get started, send me email! I hope to gather a small group of volunteers to help out at the installfest.

calm down

Yes, yes. Oracle is buying Sun, which owns MySQL and Java. No, this is not the end of MySQL. You're being silly.

Oracle is about as open-source friendly as a huge proprietary software company can be, and has been since before it was cool. Oracle adores Linux, and started pushing it vigorously since about, hm, 2002? Oracle has been Java-crazy since that time, too. Oracle's marketing strategy has long been against lock-in - it wants to plug easily into a thriving open-standards economy, not to enclose and lock a walled garden. It's also been very easygoing about licensing, eager to see casual, non-paying users gaining familiarity with its products, knowing that those are the seeds that later big-money sales will come from. It doesn't try to catch and squeeze little fish, it feeds them fish food and waits for them to grow into whales. In short, you over there, slapping MySQL on your Linux box for your brother's home business? Oracle doesn't want to shut you down. Oracle loves you, has always loved you, and wants your love and trust for when you get big.

In fact, if anything, I'm a little disappointed that Oracle's (superb) marketing power, name recognition, and corporate respect will all benefit MySQL and Java... which is all fine and good, except that I'd rather see that gust of wind behind PostgreSQL and Python. (OTN's PyCon sponsorship warmed my heart, to be sure, but I wish there was a way to make ORACLE + PYTHON stop-the-presses news all around techland.)

sqlpython 1.6.1 puts the "python" in "sqlpython"

I've been asked several times, "Why is it called sqlpython?"

My answer used to be, "Ask Luca [Canali]; he wrote it."

Not anymore. Now, witness the power of this fully armed and operational hybrid SQL/Python working environment.

[EDIT: As of sqlpython 1.6.2, you use quit(), exit(), or Ctrl-D/Ctrl-Z to return from
interactive Python mode. See docs.]

0:testschema@eqtest> select title, author from play;

TITLE           AUTHOR
--------------- -----------
Timon of Athens Shakespeare
Twelfth Night   Shakespeare
The Tempest     Shakespeare
Agamemnon       Aeschylus

4 rows selected.

0:testschema@eqtest> py import urllib
0:testschema@eqtest> py current_season = urllib.urlopen('http://cincyshakes.com/').read()
0:testschema@eqtest> py
Now accepting python commands; end with `end py`
>>> r[-1]
[('Timon of Athens', 'Shakespeare'), ('Twelfth Night', 'Shakespeare'), ('The Tempest', 'Shakespeare'), ('Agamemnon', 'Aeschylus')]
>>> for row in r[-1]:
...     print '%s by %s' % (row.title, row.author)
Timon of Athens by Shakespeare
Twelfth Night by Shakespeare
The Tempest by Shakespeare
Agamemnon by Aeschylus
>>> [row.title for row in r[-1] if row.title in current_season]
['Timon of Athens', 'Twelfth Night']
>>> binds['nowplaying'] = [row.title for row in r[-1] if row.title in current_season][0]
>>> end py
0:testschema@eqtest> print
:nowplaying = Timon of Athens
0:testschema@eqtest> select title, author from play where title = :nowplaying;

TITLE           AUTHOR
--------------- -----------
Timon of Athens Shakespeare

1 row selected.

A history of result sets from each query is exposed to the python session as the list `r`; the most recent result set is `r[-1]`. Bind variables are exposed as the dictionary `binds`. All variables are retained each time the python environment is entered (whether interactively, or with one-line `py` statements).

Resultsets in `r` are read-only, but `binds` can be written as well as read, and will be working bind variables in the SQL environment.

Oh, the possibilities...

Senora 1.0.1, and mutual FOSS goading

Martin Drautzburg just published version 1.0.1 of Senora, a command-line client for Oracle that makes a very attractive alternative to Oracle's SQL*Plus.

The timing - seven weeks before I present a review of Senora, sqlpython, and others at IOUG Collaborate - is not a coincidence. I asked Martin to review my draft paper submission for the conference. He graciously did, and supplied several crucial corrections and additions regarding Senora.

He also mentioned that he'd been continuing Senora development for in-house use, but that it had been years since he'd released the updates. My upcoming review spurred him to do the release.

And what a release it is - it's full of awesome! Mighty new flag options! Multiple sessions! Automatic generation of Senora commands from SQL scripts!

It sent me to scheming about how I can copy this fresh goodness to sqlpython.

Plus, of course, the paper reminded me painfully of some of sqlpython's shortcomings. I'm starting to use the sqlpython trac seriously now, as a token to myself of my goodwill. This will probably lead to a spurt of Embarrassment-Driven Development before the conference.

FOSS people know what the ancient Romans knew: money is a feeble motivator compared to glory. (Half the time, people only want money so they can buy things that will make them feel glorious...)

sqlpython: getting crowded in here

I've come across two other SQL command-line clients lately, both written in Python:

pysql, like sqlpython, is for Oracle only.

sqlcmd is intended to work across all sorts of database backends (Oracle, postgreSQL, MySQL, etc.) seamlessly.

I intend to publicly review them soon. In the meantime, thought I'd let you know of their existence.

I can plot that data in two keystrokes

(plus a carriage return)

One of the ideas I most gleefully stole from YASQL for sqlpython is special terminators, sequences like \g and \c that replace a SELECT statement's ending semicolon. When a query ends with a special terminator, the output is specially formatted: \c gives CSV, \h gives HTML, \t gives transposed (columns as rows / rows as columns), etc. Type help terminators for details.

sqlpython 1.5.0 is out today, with the most demented special output format yet: CHARTS! Instant ad-hoc grapical goodness direct from your query, no tedious mucking around in spreadsheets or exporting to another program. Just terminate your query with \l (line graph), \L (scatter graph - no lines), \p (pie chart), or \b (bar graph).





Also, as of 1.5.0, it's pretty easy to define your own special terminators and formats. Just install sqlpython in uncompressed form (easy_install -UZ will do that), open up output_templates.py, and follow the pattern.

Dayton Oracle SIG

At last, it's time for DAY-O to meet!

Meeting of Dayton-Oracle SIG (DAY-O)

March 10, 2008, 5:30 pm

A hands-on, cooperative, laboratory-style meeting. Bring a wireless-enabled laptop if you have one. This meeting's topic will be Flashback.

Mary-Ann's Kitchen

33 N Ludlow St

Dayton OH 45402 USA

(937) 222-3663

SQL*Plus alternatives (at InOUG)

I spoke last Thursday at the Indiana Oracle Users Group on "The Command Line Lives: SQL*Plus and Alternatives".

I still need to get the full, S5 format presentation posted somewhere; for now, you can see the raw text of the presentation here.

It's an interesting topic, and one I'd like to get drawn more deeply into. Some nice things have been done in producing open-source command-line alternatives to SQL*Plus, including

• gqlplus
• Senora
• YASQL
• sqlpython

Unfortunately, the only one whose development currently seems to be active is sqlpython, and there's just me and Luca Canali working on that. I've just added functionality to sqlpython to let it fail over to sessions of the other tools (when "set failover true" is issued; doesn't work on Windows). That way, the exciting features of Senora and YASQL can be used from within sqlpython, instead of trying to remember which tool has which feature. It's quirky, though, since a full-fledged independent session of each tool is actually maintained "under the covers".

I'm dreaming of expanding sqlpython to the point where it incorporates all Senora and YASQL's features natively. Tragically, my dreams cannot yet be downloaded from the Cheese Shop.

cleansing table and column names

What kind of joker thought "BASE POC'S" would make a good column name? Sheesh, MS Access. (Not to mention apostrophe abuse. See Bob's Quick Guide to the Apostrophe, You Idiots.)

To my surprise, I googled "script rename invalid columns oracle" and got nothing useful. So here's a chance to play with Oracle regular expressions.

You may be thinking, "If they're illegal column names, how can they get in the database in the first place?" With "quotation marks", that's how. In fact, you can

CREATE TABLE "!!!My Rockin' Table!!!"
  ( "*^@(@  @!!!" VARCHAR2(30) 
                  CONSTRAINT " -- d00d --" PRIMARY KEY,
    "miXiNG CASE is TEH KEWList" VARCHAR2(30) ); 

and, not only will all your hideous names be used verbatim, but they will be case-sensitive. If you take advantage of this feature, you should be slapped. I guess the reason Oracle makes this possible in the first place is so that an import can be accepted from a source - like MS Access - regardless of how awful its names are.

Anyway, here's a PL/SQL cure for bad table and column names.
(link to code)

/*
Renames tables and columns to be Oracle-legal.

SET LINESIZE 200 before running to avoid unwanted page breaks.
Run from an account with privileges to
  - access v$reserved words 
  - alter the appropriate tables

Thanks to Eddie Awad for his description of v$reserved words:
http://awads.net/wp/2007/01/10/what-does-reserved-y-really-mean/

Catherine Devlin, catherinedevlin.blogspot.com, 11 May 2007, v0.1
*/
CREATE OR REPLACE PROCEDURE cleanse_names IS
  new_obj_name VARCHAR2(30);
  CURSOR tbl_names IS
    SELECT owner,
           table_name
    FROM   all_tables
    WHERE  dropped = 'NO';
  CURSOR col_names IS
    SELECT owner,
           table_name,
           column_name
    FROM   all_tab_columns
    JOIN   all_tables USING (table_name, owner)
    WHERE  dropped = 'NO';

  PROCEDURE do(this IN VARCHAR2) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(this || ';');
    -- Uncomment if you're feeling decisive
    -- EXECUTE IMMEDIATE(this);
  END do;
  FUNCTION legalize(name IN VARCHAR2, replacement_letter IN VARCHAR2)
  RETURN varchar2
  IS
    new_name VARCHAR2(30);
    is_reserved NUMBER(11,0);
  BEGIN    
    -- must begin with a letter
    new_name := REGEXP_REPLACE(name,'^[^[:alpha:]]',replacement_letter);
    -- and contain only letters, numbers, #,_,$
    new_name := REGEXP_REPLACE(new_name,'[^[:alnum:]#_$]','_');
    -- and mixing case is evil
    new_name := UPPER(new_name);
    -- and we must respect Oracle reserved words
    SELECT count(*)
    INTO   is_reserved
    FROM   v$reserved_words
    WHERE  keyword=new_name
    AND    (reserved='Y' OR res_semi='Y');
    IF is_reserved > 0
    THEN
      new_name := SUBSTR(new_name, 1, 29) || '_';
    END IF;
    RETURN new_name;
  END legalize;
BEGIN
    DBMS_OUTPUT.PUT_LINE('set scan off');
    FOR col IN col_names
    LOOP
      new_obj_name := legalize(col.column_name, 'C');
      IF new_obj_name != col.column_name
      THEN
        do('ALTER TABLE ' || col.owner ||
           '."' || col.table_name ||
           '" RENAME COLUMN "' || col.column_name ||
           '" TO ' || new_obj_name);
      END IF;
    END LOOP;
    FOR tbl IN tbl_names
    LOOP
      new_obj_name := legalize(tbl.table_name, 'T');
      IF new_obj_name != tbl.table_name
      THEN
        do('ALTER TABLE ' || tbl.owner ||
           '."' || tbl.table_name ||
           '" RENAME TO ' || new_obj_name);
      END IF;
    END LOOP;
END cleanse_names;

good errors vs. bad errors

One of the things I love most about PostgreSQL is its beautiful error messages. For instance, let's say I've inexplicably forgotten to add my home state to a table of states, thus violating a foreign key constraint when I try to insert a Minnesotan city. Here it is in Oracle:

SQL> insert into city values ('International Falls','MN');
insert into city values ('International Falls','MN')
*
ERROR at line 1:
ORA-02291: integrity constraint (MYSCHEMA.FK_CITY_STATE) violated - parent key not found

OK, that doesn't suck. In this case, it's pretty easy to figure out the problem, especially since I gave my constraint a meaningful name. However, there are cases where the foreign key is not so obvious - maybe it involves multiple columns - and I'm sometimes sent off to look up details on what FK_CITY_STATE involves. Since DESCRIBE won't work on a constraint, that means either an intricate query joining several V$ tables, or a graphical tool like Toad or SQL Developer.

Now I'll make the same mistake in psql.

mydb=# insert into city values (2,'International Falls','MN');
ERROR: insert or update on table "city" violates foreign key constraint "fk_city_state"
DETAIL: Key (state)=(MN) is not present in table "state".

Now that's clarity! It's one of the things I love about PostgreSQL. After all, the database has all this detail easily accessible to it; why shouldn't it deliver it to me along with the error message? It's even more important when an error like this is thrown from somewhere deep inside some code, where it may not be obvious to me which statement or which row is responsible for it - having the offending value displayed is incredibly convenient. It's considerate programming - no more, no less.

I'll say this for Oracle, though - Oracle's standardized ABC-12345 error codes are really handy for looking them up, for searches on Metalink or Google.

In any case, either of these SQL environments have good error reporting. But what about Oracle Enterprise Manager?


Where am I supposed to go with this? Shall we go search on Metalink for "An error has occurred"? Does someone at Oracle imagine that this message is, in some mysterious way, useful? Maybe there's some logfile somewhere on the server that will tell me more... maybe... somewhere. Maybe. Somewhere.

Seriously, folks. OEM has been around for a long time now. It still throws lots of errors, often for no apparent fault of the DBA (I got this one simply by trying to access the "Administration" tab.) But I would forgive throwing lots of errors, if the errors were clear, and if they included information to facilitate finding out more. This, however, I cannot forgive, and it is all too typical of my experiences with OEM. (Well, a few years back, the errors tended to be screenfuls of Java error-stack vomit; now most of those seem to have been "cleaned up" into these poker-faced non-messages. That's progress? It's like Windows 3.1 all over again.)

So, if you see me rolling my eyes next time an Oracle rep shows a slide telling us how wonderful lovely OEM makes DBAs' jobs so much easier... this is why.

Feeling the love from Oracle

The Oracle Technology Network has rolled out two new features deeply gratifying to the PyOraGeek...

• The Scripting Language Technology Center is devoted to articles about Python and Ruby. Keep an eye on it - more articles are in the pipeline! (Including one by me... oops, now I mentioned it publicly, now I really have to get cracking on it.)
• They established an OTN Forum page just for Python.

Thank you, OTN! This attention is important for more than just the benefits to those of us already hooked on Python. Oracle's interest carries a lot of weight; when Oracle embraced Linux several years ago, it was a big step forward in establishing Linux's credibility in the corporate world. I think we can expect a boost, too.

Presenting at Dayton-Cincinnati Code Camp

The following topics you submitted have been selected to be included in this year’s Code Camp:

· Data in Python, from ASCII to ZODB

Yay! Be in West Chester, OH (i.e., Northern Cincinnati, basically) on March 24 for a fun, free day of Code Camp. Most of the content is .NET, but they're very gracious about encouraging other content, too.

My talk is going to be a broad survey of ways you can get data of various sorts into and out of Python programs - not just RDBMS, but everything from the simplest to the most elaborate of data infrastructures. It's a lot to take on in an hour, but I think it'll be good for raising awareness of the breadth of possibilities - even for non-Python programmers (you poor things). I know I've learned quite a bit already just outlining the talk.

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.