General infrastructure/design question - long

[Steve Holden]

"Bill Witherspoon" <billw at witherspoon-design.com> wrote ...
> (First - this is a great list, I learn alot everyday just reading the
posts. You should all be congratulated for this!)

We try. Your turn will come...

> (Second - I'm not a professional so this question may seem, well, a bit
off...)
>
Seems like quite a reasonable question to me.

> I'm writing an app for small businesses which have to 'track' clients and
schedule visits, (dentist, barber,etc.) I'm using mySQL right now but hope
to keep my programming abstract enough to use Postgres, or another db.
>
Good planning. I've actually been quite impressed with the recent MySQL
release, but each user may have their favorite database.

> I've written a wrapper over the MySQLdb module (thanks Andy) which allows
me to open a database, and execute queries in a very simple fashion. Another
module has all of the classes which I want to load with data (like client
info).  I can load a 'client' object with info from the database with a line
like: client1 = appclasses.Client(queryresults[0]), where queryresults was a
SQL query on the db.
>
> This loads the data into the appropriate object 'properties' using the
client class's __init__ method.
>
> whew... almost there
>
> Is there a better way to do this?
>
> 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?
>
So, by "better" you appear to mean a way which automagically "knows" what
data are stored for each database entity, and sets appropriate attributes
after the row representing a particular instance has been retrieved?

> ie. (passing the db schema, and values for a particular client)
>
> class Client:
> def __init__(self, attributevalues, schema):
> Loop through schema list and create properties
>
> so that I end up with:
>
> client1.Name = 'Bill'
> client1.Address = yadayada...
>
> and if I add a field to the db (like phnumber), the next time I
instantiate a client object it will have a phnumber property.
> (Without changing the __init__ code).
>
> Is this ridiculous? I assume everyone faces this problem so I'm hoping
someone can give me a little guidence.
>
Well, it isn't ridiculous. But it's pretty ambitious. Which is not to say
impossible!

It is possible, with some database modules, to use the cursor.description
attribute after a "SELECT * FROM table" query to discover the names of the
retrieved columns. You can see one way to do this at

    http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/81189

Of course, the cursor.description gives you strings holding the names of the
columns. Ideally you would like to access them as attributes. You should
bear in mind, however, that you aren't going to be able to use code like

    someobject.arbattr = otherobject.arbattr2

because you won't, in the general case, be able to know whether the objects
possess the attributes in question. So the value of named attributes in this
case is debatable. However, it *can* be done. I would recommend taking a
look at Greg Stein's "dtuple" module, which allows you to access your
database tuples by numerical subscript, dictionary key or (if the names are
Python-compatible) attribute name. Again you can get sample code from the
cookbook, this time at

    http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/81252

Hope this helps!

regards
 Steve
--
http://www.holdenweb.com/