Data-Aware Grid: Sql API

wxPython Users
1

Hi Nathan,

I looked over your samples and see what you're getting at. I have been
working on something that might help with the dbAPI piece. The attached
class will manage a connection to a table: you can execute a query and it
will load the result into a list; then you can navigate through the list
pulling each record's values into the 'current' recordset.

The part I think might be helpful is that, since I was using this in a
similar way (in a UI), the edit values generally must be strings (for
example, a datetime value has to be edited as a string), and I needed a way
to easily convert to/from native values and strings for editing. This class
handles that, so that you can display the str value in the grid for editing,
and save the str value back, to have it automatically converted to native
value for the update.

This conversion is handled by a type class that contains the logic for
converting most of the mysql types anyway. The rest of it is standard dbAPI
as far as I know so plugging in another database should be pretty easy. I
didn't do anything fancy with the SQL anyway.

The other tricky part came with char type fields: if they contain quotes or
other illegal characters you have to build in more logic to handle them so
they don't messup your SQL expressions. To handle this, I am converting
string types (especially longtext) to hex; come to think of it, hex is valid
for MySQL expressions, but I wonder about postgre.

The class auto-detects primary keys and doesn't write over them (assuming
that pk's are read only).

Basically, you update the values in the class using
.set_value(fieldname,value), then when you're ready you call .Save_Row() or
.Insert_Row().

The code's pretty clean and should be easy to read. If not let me know.
Also, I would be happy to accept assignments for additional functionality to
build into this class to support your work. I could probably turn it around
pretty quickly.

HTH,

Jeff Childers
jchilders_98@yahoo.com

jsql.py (14.3 KB)

···

-----Original Message-----
From: Nathan R. Yergler [mailto:nathan@yergler.net]
Sent: Monday, February 17, 2003 11:05 AM
To: wxpython-users@lists.wxwindows.org
Subject: [wxPython-users] Data-Aware Grid: wxPyOO v 0.1.0

Attached is my first implementation of data-aware grid support for
wxPython. Support is implemented with two different classes. One is a
table "adapter" which is subclassed from wxPyGridTableBase. This class,
wxPyOOTable, handles interaction with the actual grid object. It only
works in conjunction with a table "provider". The provider knows
nothing of the grid, only of the storage mechanism. A brain-dead
provider is implemented as wxPyTableProvider. Real providers should be
sub-classed from wxPyTableProvider. An example, dbapiProv, which users
SQL and db-api calls, is provided. dbapiProv (and wxPyOOTable) can be
used as in the following example:

        self.dbconn = MySQLdb.connect(host="wednesday",
                                      db="garbo",
                                      user="nyergler")
        
        # initialize the grid (the actual test)
        self.tableProv = wxPyOO.wxPyDbApiTable.dbapiProv(self.dbconn,
                                   "select * from courses", "courses",
                                                    "row_id", "Null")
        self.tableHandler =
wxPyOO.wxPyOOTable.wxPyOOTable(self.FindWindowById(ID_GRID),
                                                    self.tableProv)
        
        self.FindWindowById(ID_GRID).SetTable(self.tableHandler)

Note that the handler (self.tableHandler) is initialized with a
reference to both the grid and the provider.

I've been able to use dbapiProv in some very simple tests, but other
than that it's received very little testing. I have to use it in a
project this week, so I'm sure I'll find some bugs. I'll also try to
put together some simple documentation and use cases. As always,
comments, suggestions, questions and feedback are appreciated.

Nathan R. Yegrler

2

I will look at adding a bridge for the wxprop/wxoo editors to the data tables you have in the jtype class. However, the mechanism for determining the table structure isn't portable to PostgreSQL (which doesn't have a SHOW columns or a SHOW tables), so I'm still stuck with needing to write an introspection-abstraction API for the various databases. Not sure about SQLite, but I'd doubt it provides SHOW.

Note:
    The DB API 2.0 allows you to define values in your queries using one of five or six different variable substitution types. PyPgSQL and SQLite both support the "python-friendly" dictionary form, I'd guess MySQL's adapter does as well, and all adapters should support one of the forms:

    cursor.execute ( """
        update tableName set propertyName=%(value)s
            where keyName=%(keyValue)s
        """, dictionary_of_values
    )

Which will automatically perform the appropriate quoting for the database, and also will allow for executemany calls, for batch updating. That doesn't necessarily solve all of the problems, but it means you can store simple text values as text values without doing a lot of extra effort to format them.

    To use that properly will require some significant rewriting of your utility methods (e.g. __get_where), but it will likely be a win overall.

wxoo, wxprop, and basicproperty already have support for editing all of the data-types you've defined in jtypes, though there are a number of types which are not present in that list which are present in postgresql (and which aren't yet supported by wxoo/wxprop/bp). Simply mapping to the wxprop type registry will get the registered viewer and editor classes for the data types, which will allow, for example using wxprop's transient calendar dialog for editing dates.

Okay, onto the next message,
Mike

Jeff Childers wrote:

···

Hi Nathan,

I looked over your samples and see what you're getting at. I have been
working on something that might help with the dbAPI piece. The attached
class will manage a connection to a table: you can execute a query and it
will load the result into a list; then you can navigate through the list
pulling each record's values into the 'current' recordset.

The part I think might be helpful is that, since I was using this in a
similar way (in a UI), the edit values generally must be strings (for
example, a datetime value has to be edited as a string), and I needed a way
to easily convert to/from native values and strings for editing. This class
handles that, so that you can display the str value in the grid for editing,
and save the str value back, to have it automatically converted to native
value for the update.

This conversion is handled by a type class that contains the logic for
converting most of the mysql types anyway. The rest of it is standard dbAPI
as far as I know so plugging in another database should be pretty easy. I
didn't do anything fancy with the SQL anyway.

The other tricky part came with char type fields: if they contain quotes or
other illegal characters you have to build in more logic to handle them so
they don't messup your SQL expressions. To handle this, I am converting
string types (especially longtext) to hex; come to think of it, hex is valid
for MySQL expressions, but I wonder about postgre.

The class auto-detects primary keys and doesn't write over them (assuming
that pk's are read only).

Basically, you update the values in the class using
.set_value(fieldname,value), then when you're ready you call .Save_Row() or
.Insert_Row().

The code's pretty clean and should be easy to read. If not let me know.
Also, I would be happy to accept assignments for additional functionality to
build into this class to support your work. I could probably turn it around
pretty quickly.

HTH,

Jeff Childers
jchilders_98@yahoo.com

-----Original Message-----
From: Nathan R. Yergler [mailto:nathan@yergler.net]
Sent: Monday, February 17, 2003 11:05 AM
To: wxpython-users@lists.wxwindows.org
Subject: [wxPython-users] Data-Aware Grid: wxPyOO v 0.1.0

Attached is my first implementation of data-aware grid support for
wxPython. Support is implemented with two different classes. One is a
table "adapter" which is subclassed from wxPyGridTableBase. This class,
wxPyOOTable, handles interaction with the actual grid object. It only
works in conjunction with a table "provider". The provider knows
nothing of the grid, only of the storage mechanism. A brain-dead
provider is implemented as wxPyTableProvider. Real providers should be
sub-classed from wxPyTableProvider. An example, dbapiProv, which users
SQL and db-api calls, is provided. dbapiProv (and wxPyOOTable) can be
used as in the following example:

       self.dbconn = MySQLdb.connect(host="wednesday",
                                     db="garbo",
                                     user="nyergler")
              # initialize the grid (the actual test)
       self.tableProv = wxPyOO.wxPyDbApiTable.dbapiProv(self.dbconn,
                                  "select * from courses", "courses",
                                                   "row_id", "Null")
       self.tableHandler =
wxPyOO.wxPyOOTable.wxPyOOTable(self.FindWindowById(ID_GRID),
                                                   self.tableProv)
              self.FindWindowById(ID_GRID).SetTable(self.tableHandler)

Note that the handler (self.tableHandler) is initialized with a
reference to both the grid and the provider.

I've been able to use dbapiProv in some very simple tests, but other
than that it's received very little testing. I have to use it in a
project this week, so I'm sure I'll find some bugs. I'll also try to
put together some simple documentation and use cases. As always,
comments, suggestions, questions and feedback are appreciated.

Nathan R. Yegrler

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

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

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