Seeking Python->mySQL OR module

[Mike C. Fletcher]

aum wrote:

> Hi,
>
> I'm looking for an object-relational layer, which can wrap a MySQL 
> database into highly pythonic objects.
>
> There seem to be dozens of packages which provide some of this 
> functionality, but all the ones I've looked at so far have major 
> drawbacks.

I'm going to assume you haven't looked at my PyTable RDBMS Wrapper.  I 
know it's shamelessly self-promoting, but hey, what's life with shame, 
really.  Point-by-point below...

> What I'm looking for in an object-relational wrapper layer is:
>
> 1) Pure python

Check.

> 2) Talks to standard MySQLdb python module

Check.  Also PostgreSQL, which is where my day-to-day work is done.

> 3) No need for third-party modules (apart from standard python modules)

Not a check, but the only other package required is my BasicProperty 
pure-python package, so IMO not a huge problem.

> 3) Can access *existing* tables on *existing* databases, without me 
> needing to
>    create python classes which define the columns

Check.

> 4) Can query existing databases to get table names, and open those tables

Check.  Normally you don't "open" tables, you open connections to the 
database(s) and then query the connection to the database for the tables 
(and their contents).

> 5) On opening the tables, can get a list of column names

Check, though this support is more mature on PostgreSQL, and honestly I 
don't use it myself.  On MySQL the query is pretty darn trivial... more 
work for me on PostgreSQL, but really, not a huge deal there once you 
learn about psql's "echo" flag.

> 6) Makes tables available as python objects

Yes, tables are described as Schema objects.  Rows are DBRow objects.

> 7) Elegant pythonic interface to these objects

Check IMO.  Objects support regular x.y access for field values, fields 
can be told to use particular classes for wrapping values via the table 
schema, DBRows have methods for doing insert/update/refresh/delete 
queries that use the tracked changes to the object to determine what to 
actually send to the database.

The system does not, however, do automatic reference lookup or the like 
(it lets you include the information in the schema you provide to let 
you code such things, however).  i.e.  object.location_id doesn't 
automagically get resolved via a background query to a location object, 
you have to do that query yourself.  Of course, if you need that, it's 
pretty trivial to add the functionality, just override the property for 
that field.

As with all things, there are some general principles by which one can 
judge beauty in interface but the differences between any two 
individuals response to the stimuli will outweigh the commonalities that 
can be found.  There is no artifice which can serve all purposes.

> 8) No requirement to use SQL statements, but the ability to do so if 
> desired

No check.  SQL statements are required to get rows from the tables.  i.e.

    schema = findTable( 'person' )
    item = schema.itemClass( login = 'test', password='this' )
    item.insertQuery( APPLICATION.getDBConnection())
    item.fname = 'Bill'
    item.updateQuery( APPLICATION.getDBConnection())
    item.refreshQuery( APPLICATION.getDBConnection())
    item.deleteQuery( APPLICATION.getDBConnection())

can all be done without SQL, but to access rows of the table you need to 
do something akin to:

    for person in schema.query( """SELECT * FROM person WHERE fname LIKE
    'M%';""" ):
        print person.login, person.fname, person.lname

There may be some better way to determining what rows to load from an 
SQL database, but I find the SQL queries work pretty well at this level 
of interface.  Anything higher-level and you start needing to restrict 
the user markedly.

> Any suggestions?

Give in to the idea that there is no perfect object-relational mapper :) 
.  There is a high impedance mismatch between the two domains, so that 
as you make any given aspect of an interface more natural you tend to 
make other aspects less natural and/or more difficult to work around.  
Decide which aspects are most important and go with whichever mapper 
works most closely to that which accomplishes your ends (which appears 
to be what you're doing). 

Your particular requirements seem fairly close, IMO, to my own, so seems 
like PyTable might be for you, but then I have an obvious interest in 
conquering this cold cruel world (I'm in Canada :) ) and bringing to it 
the light of my relational database wrapper ;) .

    http://pytable.sourceforge.net/

Good luck in your search,
Mike

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