Database interfacing

[Mike C. Fletcher]

Harry George wrote:

>"Michael T. Babcock" <mbabcock at fibrespeed.net> writes:
>
>  
>
>>I'm working with databases (MySQL primarily) more and more with
>>Python, and having used PERL in the past for similar work, I'm
>>wondering if there are good tools for doing 'intelligent'
>>selects/inserts to/from dictionaries, etc.  For example:
>>
>>data = {'FirstName': 'Michael', 'LastName': 'Babcock', 'Age': 99}
>>lastid = db.insert('Users', data)
>>    
>>
userSchema = schema.lookupName( 'users' )
data = userSchema.itemClass(
    FirstName = 'Michael',
    LastName= 'Babcock'
    Age= 99,
)
data.insertQuery( APPLICATION.getDBConnection() )

>>... which would execute "INSERT INTO Users (FirstName, LastName, Age)
>>VALUES ('Michael', 'Babcock', 99)"
>>
>>And also helpful would be:
>>
>>data = db.query("dbname", "SELECT * FROM Users WHERE Age >= 99)")
>>    
>>
users = userSchema.query( """SELECT * FROM Users WHERE Age >= 99)""", 
APPLICATION.getDBConnection())

>>... which would return me an array of dictionary items like:
>>
>>[ {'ID': 143, 'FirstName': 'Michael' ... }, {'ID': 242, ... }, ... ]
>>    
>>
In this case, a row of wrappers around dictionaries (objects).  You can 
change what class those objects are by declaring it in the userSchema 
object.  The default ones are fairly heavy wrappers with properties for 
each field in the table, coercian of field-values to specified 
classes/types, insert/refresh/update/delete query methods, etceteras.

>You can write tools like this yourself or use existing tools.  For
>starters take a look at: http://skunkweb.sourceforge.net/PyDO/
>  
>
Or, for the code above, PyTable RDBMS Manager. 
http://pytable.sourceforge.net/ I mostly use it with PostgreSQL, but it 
does have MySQL-compatible plumbing as well.

>If you roll your own, here are some considerations:
>
>You need to synchronize the table definitions in the DBMS with the
>class definitions in python.  Some approaches start with the DBMS and
>extract the table definitions (attributes, keys, etc) for use in
>generating the needed python code.  Some start with python and define
>a list of attributes and their meta data as a class-level var for a
>class.  (I've tried both ways.)
>  
>
PyTable normally goes Python-definition -> database, but does have some 
support for reading the definition out of the database.

There are, as Harry points out, a number of such wrapper mechanisms 
available.  The biggest problem with all of them is that it's almost as 
much effort to learn and adapt them as it is to write your own.  The 
impedence mismatch between OO programming (Python) and RDBMS just 
doesn't seem to allow for a "perfect" solution to the problem, so 
instead you see a number of different approaches with different 
strengths and weaknesses.

Good luck,
Mike

_______________________________________
  Mike C. Fletcher
  Designer, VR Plumber, Coder
  http://members.rogers.com/mcfletch/