Database interfacing

[Harry George]

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)

... 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)")

... which would return me an array of dictionary items like:

[ {'ID': 143, 'FirstName': 'Michael' ... }, {'ID': 242, ... }, ... ]

Just curious, thanks (I've written a piece of code to generate the
array of dictionary results myself actually, but I'm sure someone
else's is better).

You can write tools like this yourself or use existing tools. For
starters take a look at: http://skunkweb.sourceforge.net/PyDO/

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.)

Python-first opens the door to aspectual programming. But you may
need both approaches when you need to work with existing code or
databases.

 

[Michael T. Babcock]

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)

.... 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)")

.... which would return me an array of dictionary items like:

[ {'ID': 143, 'FirstName': 'Michael' ... }, {'ID': 242, ... }, ... ]

Just curious, thanks (I've written a piece of code to generate the array
of dictionary results myself actually, but I'm sure someone else's is
better).

 

[Mike C. Fletcher]

userSchema = schema.lookupName( 'users' )
data = userSchema.itemClass(
FirstName = 'Michael',
LastName= 'Babcock'
Age= 99,
)
data.insertQuery( APPLICATION.getDBConnection() )
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/

 

[George Young]

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)

... 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)")

... which would return me an array of dictionary items like:

[ {'ID': 143, 'FirstName': 'Michael' ... }, {'ID': 242, ... }, ... ]

Just curious, thanks (I've written a piece of code to generate the array
of dictionary results myself actually, but I'm sure someone else's is
better).

You might find dbrow helpful:
http://opensource.theopalgroup.com/
http://opensource.theopalgroup.com/files/db_row-0.8.tgz
http://opensource.theopalgroup.com/files/db_row.py