Data-aware Grid

I'm working on a project that requires quite a bit of data to be
displayed in an editable grid. For this reason, I have begun work on a
data-aware grid. The approach I'm taking is to create a subclass of
wxPyGridTableBase which handles the interraction between the database
and the grid. My question for the list is what is the most useful way
to implement the data connection? I'm currently considering the
following options:

1) DB-API only: use Python's db-api defined classes and accept (may) a
cursor and a column list in the table constructor. This has the
advantage that no additional modules are needed (except the specific
db-api module, of course) and it supports a wide variety of databases.
The disadvantage is that it will probably be the most "bare bones"
implementation: using the resulting class will require intimate
knowledge of the database, etc.

2) SQLObject (http://colorstudy.com/software/SQLObject/):
3) ORM (http://www.tux4web.de/orm/):
   Both are ways to abstractly represent a table or dataset. Both have
subtle things that preventing me from fully loving them, but both have a
pretty good feature set. I guess I tend to prefer SQLObject because it
more fully masks the database stuff, but I could be swayed.

Are there other connection models I'm not considering? Are there other
ideas I should take into account? Any feedback will be appreciated.

Thanks,

Nathan R. Yergler

Hi Nathan,

I'd prefer to use ORM as it is simple and has the features that meet my needs. I've also used it extensively in other areas of my app. If you decide to go with ORM, I will volunteer as much of my time as I can spare. I could sure use a data aware grid!

Best,

Eric.

Nathan R. Yergler wrote:

···

I'm working on a project that requires quite a bit of data to be
displayed in an editable grid. For this reason, I have begun work on a
data-aware grid. The approach I'm taking is to create a subclass of
wxPyGridTableBase which handles the interraction between the database
and the grid. My question for the list is what is the most useful way
to implement the data connection? I'm currently considering the
following options:

1) DB-API only: use Python's db-api defined classes and accept (may) a
cursor and a column list in the table constructor. This has the
advantage that no additional modules are needed (except the specific
db-api module, of course) and it supports a wide variety of databases. The disadvantage is that it will probably be the most "bare bones"
implementation: using the resulting class will require intimate
knowledge of the database, etc.

2) SQLObject (http://colorstudy.com/software/SQLObject/): 3) ORM (Wiki):
   Both are ways to abstractly represent a table or dataset. Both have
subtle things that preventing me from fully loving them, but both have a
pretty good feature set. I guess I tend to prefer SQLObject because it
more fully masks the database stuff, but I could be swayed.

Are there other connection models I'm not considering? Are there other
ideas I should take into account? Any feedback will be appreciated.

Thanks,

Nathan R. Yergler

So I've begun work on implementing the data-aware grid. More
accurately, I'm implementing a data-aware Table, for use with the
standard wxGrid. My current implementation (attached) uses the Python
db-api cursor and connection objects to retrieve data. It's fairly
limited right now: it's read only (well, the SetCellValue method is
implemented, but probably just broken), but it's just proof of concept.

I've also begun some work with a ORM version (Eric), but because there's
a learning curve there I decided to do one with the db-api, then the ORM
version. My assumption is that the ORM version will be more powerful in
the long run, but also take some more effort to develop. Personally I
can see a use for both a "quick and dirty" db-api version and an ORM
version as well.

Any feedback, suggestions, etc are welcome.

Nathan R. Yergler

wxPyDBAPITable.py (2.46 KB)

···

----------------

You can use the current version as follows (assuming MySQLdb and a
wxGrid with an ID of ID_GRID):
        self.dbconn = MySQLdb.connect(host="wednesday",
                                      db="garbo",
                                      user="foo",
                                      passwd="bar")
        
        # initialize the grid (the actual test)
        self.tableHandler = wxPyDBAPITable.wxPyDBTable(self.dbconn,
                                                       sql="SELECT *
FROM courses")
        self.FindWindowById(ID_GRID).SetTable(self.tableHandler)

Nathan R. Yergler wrote:

Are there other connection models I'm not considering? Are there other
ideas I should take into account? Any feedback will be appreciated.

Yes! I've got an idea! :slight_smile:

How about this; in between your grid or grid base and the database you have a true object layer. It'd do what my ObjectListCtrl does. (Check out and run the attached module.) Once you're at a pure object layer like this, we can use _any_ SQL/OO framework.

I described this idea in an article:
http://www.adtmag.com/java/articleold.asp?id=22&mon=12&yr=1999

- Robb

Robb Shecter wrote:

...Check out and run the attached module...

Whoops - here it is.

List-export.py (15.6 KB)

Robb Shecter wrote:

Nathan R. Yergler wrote:

Are there other connection models I'm not considering? Are there other
ideas I should take into account? Any feedback will be appreciated.

Yes! I've got an idea! :slight_smile:

How about this; in between your grid or grid base and the database you have a true object layer.

I think ORM does a nice job of this.

Eric.

Eric Walstad wrote:

I wrote:

How about this; in between your grid or grid base and the database you have a true object layer.

I think ORM does a nice job of this.

Nope, it doesn't. I didn't explain myself very well. Look at the article or the __main__ code in the module I attached.

The idea is: write a grid base that accepts a list of objects as data. It displays the objects one per row. Give it a list of attributes, and it turns these into columns. GetValue / SetValue end up calling the correct accessors on the correct instances. Make the objects Observables and your grid can update itself. I did this in Java, and am going to do it in wxPython.

I've done most of it in this ObjectListCtrl I attached. Here's an example of how I use it in a real app, with a screen shot:

# The object instances I display
self.objectImages = [ObjectImage(1, 'Source'), ObjectImage(1, 'Thumbnail'), ObjectImage(1, 'Large')]

# The attributes I want to see
columns = [ObjectImage.getImageName,
               ObjectImage.getSource,
               ObjectImage.getSize,
               ObjectImage.getStateDescription]

# Specifying special rendering based on object state. Note the
# seperation of model from view.
textAttributes = [
    {'function': ObjectImage.hasNoImage, 'textcolor':(120,120,120), 'bgcolor':(245,245,245)},
    {'function': ObjectImage.isNotToSpec, 'textcolor':(140, 40, 40)}]

# Instantiate the List widget.
aList = ObjectListCtrl(parent,
                       objectList = self.objectImages,
                       columnSpec = columns,
                       imageAccessor = ObjectImage.getIcon,
                       attributeTests = textAttributes,
                       selectedListener = self.editObjectImage)

Robb Shecter wrote:

Eric Walstad wrote:

I wrote:

How about this; in between your grid or grid base and the database you have a true object layer.

I think ORM does a nice job of this.

Nope, it doesn't. I didn't explain myself very well. Look at the article or the __main__ code in the module I attached.

The idea is: write a grid base that accepts a list of objects as data. It displays the objects one per row. Give it a list of attributes, and it turns these into columns. GetValue / SetValue end up calling the correct accessors on the correct instances. Make the objects Observables and your grid can update itself. I did this in Java, and am going to do it in wxPython.

I've done most of it in this ObjectListCtrl I attached. Here's an example of how I use it in a real app, with a screen shot:

# The object instances I display
self.objectImages = [ObjectImage(1, 'Source'), ObjectImage(1, 'Thumbnail'), ObjectImage(1, 'Large')]

# The attributes I want to see
columns = [ObjectImage.getImageName,
              ObjectImage.getSource,
              ObjectImage.getSize,
              ObjectImage.getStateDescription]

# Specifying special rendering based on object state. Note the
# seperation of model from view.
textAttributes = [
   {'function': ObjectImage.hasNoImage, 'textcolor':(120,120,120), 'bgcolor':(245,245,245)},
   {'function': ObjectImage.isNotToSpec, 'textcolor':(140, 40, 40)}]

# Instantiate the List widget.
aList = ObjectListCtrl(parent,
                      objectList = self.objectImages,
                      columnSpec = columns,
                      imageAccessor = ObjectImage.getIcon,
                      attributeTests = textAttributes,
                      selectedListener = self.editObjectImage)

Hi Robb,

I think I understand what you are getting at (I haven't looked at your code and only skimmed your article). I like the "Observable" idea. My thought with ORM was that it would give you the object representation of the data from the database, easing the pain of reading and writing to the data source and of setting up your Observable columns:
contact.first_name = "Donald"
contact.last_name = "Duck"
datasource.commit()

columns = contact.columns.keys()
...etc...
Although ORM supports only PostgreSQL and MySQL right now, the db specific modules are relatively simple and should be relatively easy to add others (famous last words).

ORM isn't "Observable" though. That would have to be created. It would be cool to Observable ORM objects for feeding to the new Grid base object.

I'm looking forward to perusing your code to see how you did it with the list control. I'm on a deadline and can't play around right now, though.

Thanks for the explanation!

Eric.

Very similar functionality is available in wxprop if you feel like swiping some code. There are object-property-collection and object-property views (both based on grids, and providing fairly rich editing-control-sets) that allow you to either have a set of objects or an individual object with associated properties. Properties are observable (though I use lower-level observations in ConflictSolver at the moment), so grids can auto-update. No storage is explicitly required, the approach works nicely with ZODB4 and/or pickle/unpickle.



I've been pushing off working on wxprop for quite a while now. PyOpenGL just has so many more "todo" items under it, (and so many more users) that wxprop just doesn't seem to float to the top very often.

Enjoy yourselves,
Mike

Eric Walstad wrote:

Robb Shecter wrote:

Eric Walstad wrote:

...

Nope, it doesn't. I didn't explain myself very well. Look at the article or the __main__ code in the module I attached.

The idea is: write a grid base that accepts a list of objects as data. It displays the objects one per row. Give it a list of attributes, and it turns these into columns. GetValue / SetValue end up calling the correct accessors on the correct instances. Make the objects Observables and your grid can update itself. I did this in Java, and am going to do it in wxPython.

I've done most of it in this ObjectListCtrl I attached. Here's an example of how I use it in a real app, with a screen shot:

...

···

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

Mike C. Fletcher wrote:

Very similar functionality is available in wxprop if you feel like
swiping some code.

Thanks for the reminder. I've glanced at wxprop, and have it on my list to
really check out.

Eric Walstad wrote:

Hi Robb,

I think I understand what you are getting at (I haven't looked at your
code and only skimmed your article). I like the "Observable" idea. My
thought with ORM was that it would give you the object representation of
the data from the database, easing the pain of reading and writing to
the data source and of setting up your Observable columns:
contact.first_name = "Donald"
contact.last_name = "Duck"

I think we _might_ be talking about the same thing, except I'd say
"attributes", not "columns".

The thing I imagine is that with this OO List and OO grid base, _any_
OO/Relational library can be used - we wouldn't be coding up a grid base
implementation specific to any of them.

Or even better, the widgets could display a mix of ORM objects and normal
objects - it makes no difference as long as they support the same "protocol",
in Smalltalk terms.

I want to change my list to use the new property feature, and then it'll be
pretty easy to use with ORM objects I'd think.

Thanks to everyone for the input on data-aware grid semantics. I do
like the idea of the "object grid", although I worry a little about
over-engineering the problem. But as I think about it and read through
your article and code, Robb, I think that a very generic object-table
class would be useful. In that case, a db-api or orm or <anything>
"provider" could be plugged in, which I think would be cool. I'll take
another stab at some coding and let everyone know what comes of it.

Nathan

OK, I'm going to reply to myself and bounce a couple of ideas off the
list.

What about a sub-class of wxPyGridTableBase, which we'll call
wxPyOOTable for discussion. This overrides the relevant portions of
wxPyGridTableBase, and takes an instance of a 'provider' in it's
constructor.

The provider, wxPyOOTableProvider for discussion, is a simple class
which provides __getitem__, __len__, etc methods for acting like a
sequence of sequences. It would also support some specialized methods
for retrieving "column" names, etc. In it's basic implementation,
wxPyOOTableProvider would be fairly brain dead.

My idea is to require a developer to sub-class wxPyOOTableProvider for
use with wxPyOOTable. In this manner, there would be no need for a
casual developer to know wxGrid semantics: they would simply focus on
representing their particular objects as a sequence, etc. I'm going to
attempt to implement this idea using the db-api and see how far I get
before running into trouble. Again, feedback, suggestions, comments,
questions are all welcome.

Nathan

···

On Fri, 2003-02-14 at 10:45, Nathan R. Yergler wrote:

Thanks to everyone for the input on data-aware grid semantics. I do
like the idea of the "object grid", although I worry a little about
over-engineering the problem. But as I think about it and read through
your article and code, Robb, I think that a very generic object-table
class would be useful. In that case, a db-api or orm or <anything>
"provider" could be plugged in, which I think would be cool. I'll take
another stab at some coding and let everyone know what comes of it.

Nathan

---------------------------------------------------------------------
To unsubscribe, e-mail: wxPython-users-unsubscribe@lists.wxwindows.org
For additional commands, e-mail: wxPython-users-help@lists.wxwindows.org

Nathan R. Yergler wrote:

... I worry a little about
over-engineering the problem. But as I think about it and read through
your article and code, Robb, I think that a very generic object-table
class would be useful. In that case, a db-api or orm or <anything>
"provider" could be plugged in, which I think would be cool.

Exactly! The key idea can be seen in the implementation I wrote for ObjectListCtrl.OnItemGetText():

    def OnGetItemText(self, row, col):
        obj = self.getObjectAt(row)
        method = self.columnSpec[col]
        return method(obj)

I just browsed over your longer post and it sounds good. I hope to have time in the next few days to put more thought into it.

I haven't done the table version in Python, but here's the Java implementation I wrote - This class plays *exactly* the same role as grid base does. Should be good for ideas. I can make the whole package available if anyone's interested.

BTW, although a bit of work does go into a framework like this, I think that the payoff is *huge*: Application code becomes so easy to write and so manageable, because you can focus on writing nice clean business/domain objects and reasonable accessors.

Robb

OOTableModel.java (11.1 KB)

Robb Shecter wrote:

package com.ruleofeight.oow;

import java.beans.*;
import java.lang.reflect.*;
import java.util.*;

import javax.swing.*;
import javax.swing.table.AbstractTableModel;

ACCKK!!! You trying to give me a heart attack??? Java code in the wxPython list??

:wink:

···

--
Robin Dunn
Software Craftsman
http://wxPython.org Java give you jitters? Relax with wxPython!

Robb Shecter wrote:

Nathan R. Yergler wrote:

... I worry a little about
over-engineering the problem. But as I think about it and read through
your article and code, Robb, I think that a very generic object-table
class would be useful. In that case, a db-api or orm or <anything>
"provider" could be plugged in, which I think would be cool.

Exactly! The key idea can be seen in the implementation I wrote for ObjectListCtrl.OnItemGetText():

   def OnGetItemText(self, row, col):
       obj = self.getObjectAt(row)
       method = self.columnSpec[col]
       return method(obj)

I just browsed over your longer post and it sounds good. I hope to have time in the next few days to put more thought into it.

I haven't done the table version in Python,

I have :wink: it's called wxprop.propertytable. You can see it in the wxprop CVS (or any released version of wxpypropdist). There's single-object and multiple-object sub-classes. You'll notice an eerie similarity between the wxPython code and the code you posted. Feel free to rip it off wholesale instead of rewriting it yourself. About the only thing missing is the watching/notification stuff (I handle that at a higher level in ConflictSolver).

but here's the Java implementation I wrote - This class plays *exactly* the same role as grid base does. Should be good for ideas. I can make the whole package available if anyone's interested.

BTW, although a bit of work does go into a framework like this, I think that the payoff is *huge*: Application code becomes so easy to write and so manageable, because you can focus on writing nice clean business/domain objects and reasonable accessors.

Robb

Agreed, it's cool to have applications fall automatically out of the framework + the data-model.

Enjoy,
Mike

···

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

Nathan R. Yergler wrote:

OK, I'm going to reply to myself and bounce a couple of ideas off the
list.

What about a sub-class of wxPyGridTableBase, which we'll call
wxPyOOTable for discussion. This overrides the relevant portions of
wxPyGridTableBase, and takes an instance of a 'provider' in it's
constructor.

Sounds good, I call this object "view" in my code. However, in my code it's object-specific, that is, it only knows about objects and properties. The data-aware grid takes care of figuring out which objects/properties it wants to retrieve. This allows different grid-types to take the same description and produce different views.

In my systems these are fairly generic types ("object view" and "collection view"), and they simply provide a bridge between the table and the property-based objects (with the caveat that the property-based objects don't actually need to be property-based).

The provider, wxPyOOTableProvider for discussion, is a simple class
which provides __getitem__, __len__, etc methods for acting like a
sequence of sequences. It would also support some specialized methods
for retrieving "column" names, etc. In it's basic implementation,
wxPyOOTableProvider would be fairly brain dead.

Yes, that's basically what it does, though I've let the grid get the names from the properties as explained above.

My idea is to require a developer to sub-class wxPyOOTableProvider for
use with wxPyOOTable. In this manner, there would be no need for a
casual developer to know wxGrid semantics: they would simply focus on
representing their particular objects as a sequence, etc. I'm going to
attempt to implement this idea using the db-api and see how far I get
before running into trouble. Again, feedback, suggestions, comments,
questions are all welcome.

Because I use the properties (and pseudo-properties) throughout, there's very few cases where it's actually necessary to sub-class the table class (none so far beyond the "collection" and "object" ones, though I may create one for "mapping" eventually). There's simply the generic "object" and "collection" views that use introspection to determine the properties and object-sets.

I think you'll find that a similar bridge view would work for any object-description mechanism (such as an SQL-hosted one). That's important to me, because it means you don't _need_ to create a bridging sub-class for 90% of your data-classes. That speeds up development (you normally wind up creating bridge classes eventually to make the presentation neater, but you do that during polishing, when you've got a functional application). In wxprop, that would simply be a matter of creating pseudo-properties for your database fields and returning them from a "dbobject" view (and likely a "dbtable" view as well which also returns object-wrappers for your records).

Enjoy,
Mike

···

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

Mike,

I'm interested in the data aware grid. Send me something when there's something to play with and
I'll give you more feedback (right now I'm trying to complete a masked edit control, but by the
time you've got something to look at I'll be finished with it).

Jeff Childers

···

--- "Mike C. Fletcher" <mcfletch@rogers.com> wrote:

Nathan R. Yergler wrote:

>OK, I'm going to reply to myself and bounce a couple of ideas off the
>list.
>
>What about a sub-class of wxPyGridTableBase, which we'll call
>wxPyOOTable for discussion. This overrides the relevant portions of
>wxPyGridTableBase, and takes an instance of a 'provider' in it's
>constructor.
>

Sounds good, I call this object "view" in my code. However, in my code
it's object-specific, that is, it only knows about objects and
properties. The data-aware grid takes care of figuring out which
objects/properties it wants to retrieve. This allows different
grid-types to take the same description and produce different views.

In my systems these are fairly generic types ("object view" and
"collection view"), and they simply provide a bridge between the table
and the property-based objects (with the caveat that the property-based
objects don't actually need to be property-based).

>The provider, wxPyOOTableProvider for discussion, is a simple class
>which provides __getitem__, __len__, etc methods for acting like a
>sequence of sequences. It would also support some specialized methods
>for retrieving "column" names, etc. In it's basic implementation,
>wxPyOOTableProvider would be fairly brain dead.
>
Yes, that's basically what it does, though I've let the grid get the
names from the properties as explained above.

>My idea is to require a developer to sub-class wxPyOOTableProvider for
>use with wxPyOOTable. In this manner, there would be no need for a
>casual developer to know wxGrid semantics: they would simply focus on
>representing their particular objects as a sequence, etc. I'm going to
>attempt to implement this idea using the db-api and see how far I get
>before running into trouble. Again, feedback, suggestions, comments,
>questions are all welcome.
>

Because I use the properties (and pseudo-properties) throughout, there's
very few cases where it's actually necessary to sub-class the table
class (none so far beyond the "collection" and "object" ones, though I
may create one for "mapping" eventually). There's simply the generic
"object" and "collection" views that use introspection to determine the
properties and object-sets.

I think you'll find that a similar bridge view would work for any
object-description mechanism (such as an SQL-hosted one). That's
important to me, because it means you don't _need_ to create a bridging
sub-class for 90% of your data-classes. That speeds up development (you
normally wind up creating bridge classes eventually to make the
presentation neater, but you do that during polishing, when you've got a
functional application). In wxprop, that would simply be a matter of
creating pseudo-properties for your database fields and returning them
from a "dbobject" view (and likely a "dbtable" view as well which also
returns object-wrappers for your records).

Enjoy,
Mike

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

---------------------------------------------------------------------
To unsubscribe, e-mail: wxPython-users-unsubscribe@lists.wxwindows.org
For additional commands, e-mail: wxPython-users-help@lists.wxwindows.org

__________________________________________________
Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day

Well, there's definitely something to play with already:

    http://wxpypropdist.sourceforge.net/

It's been running on Win32 for ~10 months as part of ConflictSolver (in case you want to see how it's used):

    http://conflictsolver.sourceforge.net/

The data-aware-grid gets used daily by the people who asked me to create ConflictSolver for them (they actually use it in their day-to-day office work), and hasn't had any remarkable errors. wxpypropdist doesn't have anything like the final feature-set I'm planning on, so I still consider it early-alpha, but the data-aware grid has been stable for a while (modulo fixes for new versions of wxPython as they come out, and a switch to using Unicode throughout).

Enjoy yourself,
Mike

J. Childers wrote:

Mike,

I'm interested in the data aware grid. Send me something when there's something to play with and
I'll give you more feedback (right now I'm trying to complete a masked edit control, but by the
time you've got something to look at I'll be finished with it).

Jeff Childers

...

···

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

From what I read in the db-sig mailing list, it seems db_row
(see http://opensource.theopalgroup.com/ ) is one of the most
popular object-relational mappers. It's cute with the name ORM,
since it means snake in Swedish, but it seems to be a fairly
new (read immauture) product. SQLObject also seems to have a fan
club, but I don't think I had ever heard of ORM until you mentioned
it now.

···

At 08:00 2003-02-13 -0500, Nathan R. Yergler wrote:

2) SQLObject (http://colorstudy.com/software/SQLObject/):
3) ORM (Wiki):
   Both are ways to abstractly represent a table or dataset. Both have
subtle things that preventing me from fully loving them, but both have a
pretty good feature set. I guess I tend to prefer SQLObject because it
more fully masks the database stuff, but I could be swayed.

Are there other connection models I'm not considering? Are there other
ideas I should take into account? Any feedback will be appreciated.

--
Magnus Lycka, Thinkware AB
Alvans vag 99, SE-907 50 UMEA, SWEDEN
phone: int+46 70 582 80 65, fax: int+46 70 612 80 65
http://www.thinkware.se/ mailto:magnus@thinkware.se