[DB-SIG] Oracle tables encapsulation

[Tom Bryan]

On Thu, 2 Dec 1999, Olivier BERGER wrote:

> I'd like to have a Python framework with classes encapsulating the
> physical tables and columns of my Oracle database. For instance, I
> would have a class called OracleRow which would allow me to map
> automatically the results of DCOracle fetch results to an instance of
> a subclass of OracleRow.
> 
> This would, for instance, give the following :
> 
> class MyTable(OracleTable) :
>     def __init__(self) :
>         OracleTable("mytable")
> ...
> 
> ... so that I get a MyTableRow class corresponding to rows in the MYTABLE
> oracle table, and then I would be able to do something like
> 
> cursor.execute("select * from mytable")
> for r in cursor.fetchall :
>     instance = MyTableRow(r)
>     do_something(instance)
> 
> and if MYTABLE contained the ID, NAME, ADDRESS columns, then I would be able
> to do a :
> 
> def do_something(mytablerow) :
>     print mytablerow.id, mytablerow.name, mytablerow.address

I haven't seen anything like this on the list, but it wouldn't be
difficult to do with most databases.  OracleTable simply needs to query
the "data dictionary" for the appropriate information, assuming that the
user has the permissions to query the appropriate table of the data
dictionary.  

Something like this might work.  I'm sure that it could be made much
better with some thought.  Note that I don't have my Oracle reference or
an Oracle database here so that the column/table names probably need to be
changed.  Untested code follows:

class OracleTable:
    def __init__(self,tableName,dbConnection):
        """tableName is the name of a table in the database
        for which there is an open connection referenced by dbConnection"""
        cur = dbConnection.cursor()
        # not sure about COLUMN_NAME, USER_COLUMNS, or TABLE_NAME
        # check your Oracle reference materials
        cur.execute(
          "select COLUMN_NAME from USER_COLUMNS where TABLE_NAME='%s'" % 
          tableName)
        columnNames = cur.fetchall()
        for el in columnNames:
            setattr(self,el,None)
       cur.close()
class MyTable(OracleTable):
    def __init__(self,dbConnection,**kw):
        OracleTable.__init__(self,'mytable',dbConnection)
        for el in kw.keys():
            """how do we make sure we assign to the attributes in the
            correct order?"""
            setattr(self,el,kw[el])

The problem, of course, is that with the above code, you'd have to do
something like 
row = MyTable(connection,id=10,name='db-sig',address='db-sig@python.org')
It's more generally useful, but it can't be used like you wanted to use it
above.  I'm not sure how to ensure that the values of a "select *" would
be assigned to the correct attributes without playing with it a lot more.
I don't remember how to obtain the order of the columns in a table from
the data dictionary.

> Such a framework allows automatical encapsulation of low-level tables into
> obect instances, thus facilitating the algorithms writing.

Nice idea. :)

just-enough-to-get-you-started-ly yours

---Tom