SQLAlchemy Queries & HTML Data Grid

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

SQLAlchemy Queries & HTML Data Grid

Randy Syring-3
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

_______________________________________________
Web-SIG mailing list
[hidden email]
Web SIG: http://www.python.org/sigs/web-sig
Unsubscribe: http://mail.python.org/mailman/options/web-sig/lists%40nabble.com
Reply | Threaded
Open this post in threaded view
|

Re: SQLAlchemy Queries & HTML Data Grid

Aaron Watters-2

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 <[hidden email]> wrote:

From: Randy Syring <[hidden email]>
Subject: [Web-SIG] SQLAlchemy Queries & HTML Data Grid
To: [hidden email]
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@...
Web SIG: http://www.python.org/sigs/web-sig
Unsubscribe: http://mail.python.org/mailman/options/web-sig/arw1961%40yahoo.com

_______________________________________________
Web-SIG mailing list
[hidden email]
Web SIG: http://www.python.org/sigs/web-sig
Unsubscribe: http://mail.python.org/mailman/options/web-sig/lists%40nabble.com
Reply | Threaded
Open this post in threaded view
|

Re: SQLAlchemy Queries & HTML Data Grid

Randy Syring-3
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
    • filtering
    • grouping
    • column selection
    • 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 [hidden email] wrote:

From: Randy Syring [hidden email]
Subject: [Web-SIG] SQLAlchemy Queries & HTML Data Grid
To: [hidden email]
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@...
Web SIG: http://www.python.org/sigs/web-sig
Unsubscribe: http://mail.python.org/mailman/options/web-sig/arw1961%40yahoo.com

_______________________________________________
Web-SIG mailing list
[hidden email]
Web SIG: http://www.python.org/sigs/web-sig
Unsubscribe: http://mail.python.org/mailman/options/web-sig/lists%40nabble.com
Reply | Threaded
Open this post in threaded view
|

Re: SQLAlchemy Queries & HTML Data Grid

Aaron Watters-2

Randy:

It seems you want a sortable HTML table that talks to a back end
query engine.  I don't see why this needs to be specific to SQLAlchemy.

Here is a WHIFF middleware which does some of what you are talking
about (the demo formatting is basic/ugly for simplicity purposes).

Demo

  http://whiffdoc.appspot.com/tests/misc/testSortTable

Demo source

   http://whiffdoc.appspot.com/tests/showText?path=./misc/testSortTable

The documentation

 http://whiffdoc.appspot.com/docs/W1200_1400.stdMiddleware#Header83

is not extensive, but here is the source for the
core middleware widget.

  http://aaron.oirt.rutgers.edu/cgi-bin/whiffRepo.cgi/file/8c031c68a5a0/whiff/middleware/sortTable.py

As written it requires the whole table as a list of dictionaries and then does
paging from the full list.  It certainly needs generalization but maybe it's
a start.  Let me know if you have questions or comments.

   -- Aaron Watters

--- On Wed, 4/7/10, Randy Syring <[hidden email]> wrote:

From: Randy Syring <[hidden email]>
Subject: Re: [Web-SIG] SQLAlchemy Queries & HTML Data Grid
To: "Aaron Watters" <[hidden email]>
Cc: [hidden email]
Date: Wednesday, April 7, 2010, 1:37 PM

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

_______________________________________________
Web-SIG mailing list
[hidden email]
Web SIG: http://www.python.org/sigs/web-sig
Unsubscribe: http://mail.python.org/mailman/options/web-sig/lists%40nabble.com
Reply | Threaded
Open this post in threaded view
|

Re: SQLAlchemy Queries & HTML Data Grid

Randy Syring-3
> It seems you want a sortable HTML table that talks to a back end
> query engine.  I don't see why this needs to be specific to SQLAlchemy.
Well...not just sorting though.  Sorting, filtering, grouping, column
selection, and paging.  You are right that the backend does not need to
be SQLAlchemy specific, but since that is what I use, that is what I was
going to start with.  Ideally, the library would be both sql framework
and wsgi framework agnostic.

The main point of the library would be to save the time/hassle in
creating the HTML/CSS/JS for the query controls.

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


_______________________________________________
Web-SIG mailing list
[hidden email]
Web SIG: http://www.python.org/sigs/web-sig
Unsubscribe: http://mail.python.org/mailman/options/web-sig/lists%40nabble.com