General infrastructure/design question - long

[maxm]

"Bill Witherspoon" <billw at witherspoon-design.com> wrote in message
news:mailman.1006199666.10412.python-list at python.org...

> My fear is that everytime I change the db schema, I have to run around and
update all of the classes to insure that the data maps correctly. Is there
any way to easily have a class that 'knows' how to create its own __init__
so that the db fields always become appropriate properties?

This is a typical object/relational impedance mismatch problem you are
trying to solve. I guess that almost every programmer tries to solve this
problem one way or another at some time.

I have attached a file with a semiworking solution that I am working on. It
currently works on mx.odbc on windows but is rather generic.

The idea is that there is a top class called "Table" that you subclass for
every table in your rdbms.

And then you declare your column names and type, with a default value, and
wether the row should be updated or not.

class Receipees(Table):

    dsn       = 'DSN=odense_marcipan'
    tableName = 'receipees'
    columns = {}            #  R/W      TYPE     DEFAULT
    columns['id']           = 'KEY',   'INT',    None
    columns['title']        = 'WRITE', 'STR',    ''
    columns['summary']      = 'WRITE', 'TXT',    ''
    columns['tips']         = 'WRITE', 'TXT',    ''
    columns['receipee']     = 'WRITE', 'TXT',    ''
    columns['ingredients']  = 'WRITE', 'TXT',    ''
    columns['category']     = 'WRITE', 'INT',    0
    columns['image']        = 'WRITE', 'STR',    ''
    columns['visible']      = 'WRITE', 'INT',    1


This way you can ignore most of the rdbms specific code, and just code as if
your tables are Python objects.

The above table is used like this:

    rcps = Receipees()

    print 'creating new'
    newRcp = rcps.getNew()
    newRcp.title = 'The new architecture'
    newRcp.summary = 'A bad summary'
    rcps.save(newRcp)

    print 'Getting some'
    rcps.getByIds([11,10,12,13])
    for rcp in rcps:
        print rcp.title

    print 'Getting one'
    rcp = rcps.getOneById(10)
    print rcp.title

    print 'Getting all'
    rcps.getAll()
    for rcp in rcps:
        print rcp.title

    print 'The shortcut'
    rcps2 = Receipees([10,11])
    for rcp in rcps2:
        print rcp.title

To make it work with mySql or PostgresSQL only the two methods '_query()'
and 'save()' would need to be altered slightly.

It would even be rather trivial to subclass 'Table' like:

class mysqlTable(Table):
     ...

and then overwrite _query and save.

The idea is also that you should NEVER use a sql query directly in the
objects. If you need a special query, you write a method that does it. Ie.
if you want to get all the receipes by category you would write a method
called:

def getByCategory(self, category):
    query = 'where category=%s' % category
    self.getWhere(query)

If later you find out that you need to put the categories in a many to many
table you would only have to rewrite the "getByCategory()" method. Hiding
the data structure.

Anyhoo I like this style, I hopes that it is of any help to you. The class i
have attached is rather simple and certainly not complete. But it was enough
for my recent project, and I will make it more feature-complete as I need to
solve other problems.

regards Max M