[Web-SIG] SQLAlchemy Queries & HTML Data Grid

[Randy Syring]

Aaron,

Sorry, I must not really have explained clearly.  This isn't an 
abstraction layer, but more like a UI component or widgit that 
facilities basic reporting.  Look at these pages:

http://www.redmine.org/issues
http://trac.edgewall.org/query

Both pages have a similar structure:

    * UI Controls
          o filtering
          o grouping
          o column selection
          o sorting
    * Paged/Sortable Recordset Display

The library would do the heavy lifting and allow any application using 
SQLAlchemy to easily create such query/recordset interfaces to 
underlying data.

You would need to:

    * Instantiate the DataGrid class
    * Create a base SQLAlchemy query to be used for the data
    * Define the filter types associated with the columns (i.e.
      TextFieldFilter, OptionsFilter('low','medium', 'high'),
      DateSpanFilter, etc.)
    * Limit sorting, grouping to appropriate columns
    * choose which columns of the dataset to show by defaults

The library would then:

    * parse GET/POST for filter/column/sort/page settings &/or use defaults
    * compose SQLALchemy query to satisfy the request
    * execute the query and get the database results
    * put results into an HTML table
    * return the HTML form needed for the controls and the recordset
      table including necessary CSS and JS

Obviously, the library should be easily customizable and the rendering 
of HTML should be flexible.  It should also be flexible enough to work 
with the different WSGI libraries out there.

I hope that makes better sense.  If you or anyone else is interested, I 
can give you some code I have working from the pysapp project which does 
some basic filtering, paging, and sorting.  The API is awful, but I 
think it might give you a better idea of what I am talking about.

--------------------------------------
Randy Syring
Intelicom
502-644-4776

"Whether, then, you eat or drink or 
whatever you do, do all to the glory
of God." 1 Cor 10:31



Aaron Watters wrote:
>
> Thanks Randy, very interesting.
>
> My initial reaction is that you are building a stack on top of a stack.
> It's not clear to me what problem you want to solve
> and your requirements are.  It's possible that
> you could find it easier to abstract directly on top of SQL or 
> alternatively
> you could consider using another sort of data model like mongodb.
> Building an abstraction on top of SQLAlchemy which hasn't even reached
> 1.0 strikes me as dubious.
>
> Thanks again,  -- Aaron Watters
>
> --- On *Tue, 4/6/10, Randy Syring /<rsyring at inteli-com.com>/* wrote:
>
>
>     From: Randy Syring <rsyring at inteli-com.com>
>     Subject: [Web-SIG] SQLAlchemy Queries & HTML Data Grid
>     To: web-sig at python.org
>     Date: Tuesday, April 6, 2010, 4:37 PM
>
>     I am planning on building a library that will facilitate creation
>     of custom queries and html display of resulting datasets from
>     SQLAlechemy queries.  I have some basic work done here:
>
>     https://svn.rcslocal.com:8443/svn/pysmvt/pysapp/branches/0.1/pysapp/modules/datagrid/
>
>     But I don't like the API and I don't want the library to be
>     dependent on pysapp.  Furthermore, I would like to have a more
>     verbose querying ability akin to Redmine:
>
>     http://www.redmine.org/projects/redmine/issues
>
>     Including:
>
>         * Filters
>         * Column Selection
>         * Grouping (multiple levels)
>         * Sorting (multiple columns)
>         * some kind of query saving/loading mechanism with a flexible
>           backend
>
>     I have done some basic table generation work here:
>
>     https://svn.rcslocal.com:8443/svn/pysmvt/pysdatagrid/trunk/
>
>     with the tests being the best place to get an idea of how it works:
>
>     https://svn.rcslocal.com:8443/svn/pysmvt/pysdatagrid/trunk/pysdatagrid/tests/test_render.py
>
>     Looking for comments, pointers to other projects, and/or possibly
>     interest in helping with a project like this.  I am currently
>     working in SVN but will most likely move to hg/git if there are
>     others who are interested.
>
>     -- 
>     --------------------------------------
>     Randy Syring
>     Intelicom
>     502-644-4776
>
>     "Whether, then, you eat or drink or 
>     whatever you do, do all to the glory
>     of God." 1 Cor 10:31
>
>
>     -----Inline Attachment Follows-----
>
>     _______________________________________________
>     Web-SIG mailing list
>     Web-SIG at python.org </mc/compose?to=Web-SIG at python.org>
>     Web SIG: http://www.python.org/sigs/web-sig
>     Unsubscribe:
>     http://mail.python.org/mailman/options/web-sig/arw1961%40yahoo.com
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/web-sig/attachments/20100407/5215250b/attachment-0001.html>