[DB-SIG] How to *you* access your database objects?

[Eivind Tagseth]

I'm new to this list, so I apologize if this subject has been discussed
before.

I'm not asking how to use the DB-API, that's pretty straight forward and well
documentet.

But what do you actually do with the data you retrieve.  And how do you
retrieve it?

Personally, I think it makes sense to create a python class for each
database table I have, and make the entity attributes available as a
dictionary.  I think this is cleaner than directly accessing attributes
and more convenient than adding getters and setters as one would do in
e.g. Java.

For example, a class for a Tool-entity may look like this:

----------------8<----------8<-------------8<---------------8<------------

from UserDict import UserDict

class Tool (UserDict):
        datatypes = { "id":"number",
                           "Name":"varchar",
                           "URL":"varchar",
                           "Description":"varchar" }

        def __init__(self):
                self.data = { "id":None, "Name":None, "URL":None, "Description":None }

        def __setitem__(self, name, value):
                if name not in self.keys():
                        raise KeyError("This object does not support the key %s" % name)
                UserDict.__setitem__(self, name, value)

----------------8<----------8<-------------8<---------------8<------------

It seems to work pretty well, but I haven't had a chance to put a lot of
thought into it yet.  What do other people do?

I'm not pleased with the Tool-class yet though.  There is no mechanism for
fetching rows from the database and returning these as Tool-objects, and there
is no way to update or insert these back into the database.

Do people have generalized patterns on how to do this?

J2EE has special objects called Entity objects which solves this problem.
Creating a new EntityObject() actually inserts that object into the database,
and deleting the EntityObject removes it.

Would it make sense to expand my Tool class to have methods for
inserting, updating, deleting and finding the object in the database
with SQL-commands?

I thought about creating a method like:

---------------8<-----------8<----------8<-------------8<-------------8<-----
        def findById(self, id):
                self._dbi.execute("SELECT %s FROM %s WHERE id = %s" % (
                        ", ".join(self.keys()), "Tool", id))

                data = self._dbi.fetchone()

                if not data:
                        return None

                obj = self
                i = 0
                for k in self.keys():
                        obj[k] = data[i]
                        i = i + 1

                return obj

-------------8<--------------8<---------------8<-------------8<--------------

It shouldn't be very hard to generalize this further, and be able to define
an python class for accessing a database table just be registering information
about attributes, datatypes and primary keys.


I'm sure I'm not the first one to think about this.  What are other people
doing?  What's braindead about my way?  Comments?




Eivind


-- 
Eivind Tagseth,
E-mail jobb: eivind.tagseth@consultit.no, E-mail priv: eivindt@multinet.no
Mobil: +47 922 43742