[DB-SIG] OO wrapper for SQL?

oivvio polite oivvio@cajal.mbb.ki.se
Tue, 30 May 2000 12:03:36 +0200


Hello everybody.

I've been tinkering with a python/sql project for some time know.
Basically I use python to generate long sql statements and send them of to
the dbms.
The sql statments typically create new views or tables out of old ones.
Yesterday I went back to revise something I wrote one month or so ago and it
did not look pretty at all.

    sqlpart = ""
    for enzyme in enzymes:
        tmp = "id in (SELECT id FROM %s..%s where enzyme = '%s') AND\n"  %
(db, nomatchtable, enzyme)
        sqlpart = sqlpart + tmp
    sqlpart = sqlpart[:-4]

    sql = "INSERT INTO %s..%s SELECT id FROM %s..%s WHERE %s " % (db,
no_cut_ids, db, base, sqlpart)
    dbc.ExecSQL(sql ,constants.adCmdText, timeout = 3600)

A few hundred lines of that can make one somewhat dizzy.

What I'd want to have instead is a somekind of oo wrapper for sql, probably
something working on top of the python db api.
Something that would let my say:

oldtable1 = dboo.table.existing(connection,database,tablename1)
oldtable2 = dboo.table.existing(connection,database,tablename2)
newtable =  dboo.table.join(newtablename, oldtable1, oldtable2,
some_condition)

This would take a couple of old tables in the my database join them whith
some
condition and insert the result as a new table in the database.

Of course there should be tons of other possible operations. I want to be
able to do pretty much everything
I can do with sql, without having to maintain that horrible sql code.

Have you seen anything like this for python, or maybe in for some other
language?

oivvio polite