Seeking Python->mySQL OR module

[aum]

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.

What I'm looking for in an object-relational wrapper layer is:

1) Pure python
2) Talks to standard MySQLdb python module
3) No need for third-party modules (apart from standard python modules)
3) Can access *existing* tables on *existing* databases, without me
needing to
create python classes which define the columns
4) Can query existing databases to get table names, and open those tables
5) On opening the tables, can get a list of column names
6) Makes tables available as python objects
7) Elegant pythonic interface to these objects
8) No requirement to use SQL statements, but the ability to do so if
desired

Any suggestions?

Thanks in advance
a

[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/

[aum]

On Mon, 02 Feb 2004 22:15:52 -0500, Mike C. Fletcher <mc******@rogers.com>
wrote:

Give in to the idea that there is no perfect object-relational mapper :)
After several hours of looking, this is dawning on me.
. There is a high impedance mismatch between the two domains
I'm really seeing what you mean. And how!

Back in my bad old PHP days, the paradigm difference wasn't as severe.
But since I've disinfected myself of PHP and settled into Python, the
two worlds are universes apart.

The general feeling I get is that DB-wrapping is probably a personal
thing, extremely subject to the needs and tastes of the programmer.

In my case, I think I'll go down the track of having a single module for
all
things SQL, and writing specific funcs/classes for specific queries.
http://pytable.sourceforge.net/
Thx for the recommendation. I've downloaded it, and am studying it along
with a few others.

Good luck in your search,
Mike

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

--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/