Re: [web2py] SQLFORM.grid exports the whole table
On 12 November 2012 11:13, Niphlod wrote: With the jids= notation the current grid won't have any functional > pagination,ordering, export, etc. That's because vars are not propagated. I > sent a patch to Massimo for that a few days ago. > > Thanks. I have used session variables to do that in the past. It will be an improvement if it can be handled properly by the grid. > The question still stands: without using jids= and using only keywords= > "styled urls" when you click on the export button you get back the full > data or only the records that you see on the grid at the moment you click > the export link ? > > Apologies. I have missed this one. Yes when using a query created by the grid's search function (keywords=...) the export is limited to the same content. So it works as expected in this case. If the export cannot be done in the same way when using a predetermined query (not using keywords=), then it must be documented that in such a case the developer will have to write his/her own export function. It will be very nice if both options can be handled by the same export mechanism though. Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3) --
Re: [web2py] SQLFORM.grid exports the whole table
With the jids= notation the current grid won't have any functional pagination,ordering, export, etc. That's because vars are not propagated. I sent a patch to Massimo for that a few days ago. The question still stands: without using jids= and using only keywords= "styled urls" when you click on the export button you get back the full data or only the records that you see on the grid at the moment you click the export link ? Il giorno lunedì 12 novembre 2012 08:23:01 UTC+1, Johann Spies ha scritto: > > On 8 November 2012 23:00, Niphlod > wrote: > >> ok, give me some hints at least (trying to reverse engineer here :P). >> > > Sorry for the delay in answering. I was away from office for a few days. > > The problem is that your query can come from more than one source: > > 1. The original request: > > e.g. > http://localhost:8000/init/journal/journals?jids=1057&jids=12302&jids=878&_signature=b1038a2b2ffd913064ffc64b48e6c133ac0f3158 > > 2. Searches by the user using the grid's search mechanisms: > > > http://localhost:8000/init/journal/journals?keywords=akb_journal.id+%3D+%221057%22+or+akb_journal.id+%3D+%2212302%22+or+akb_journal.id+%3D+%22878%22 > > > Both the above url's have the same result on the screen. > > The export functions should be able to determine the query from the url if > possible. > > Other queries might look like this: > > > http://localhost:8000/init/journal/journals?keywords=akb_journal.title+starts+with+%22public%22 > > > Which I think you were expecting with your first patch. > > I hope this helps. > > Regards > Johann > > > -- > Because experiencing your loyal love is better than life itself, > my lips will praise you. (Psalm 63:3) > > --
Re: [web2py] SQLFORM.grid exports the whole table
ok, give me some hints at least (trying to reverse engineer here :P). when you go to http://localhost:8000/init/journal/journals how many records are returned ? I assume 1. in other words: are you using request.vars.jids to create the query you pass on the tables ? If yes, I remember someone else asked for this. I should have prepared a patch then at that time was discarded but I think it's useful nonetheless: we have an "args" parameter to pass to the grid to let the grid discard some arguments, but we haven't a 'vars' parameter to pass along. This "issue" shows up because the link the grid generates as an export button doesn't retain your original jids parameters --> when the set is built by your controller, no jids are set in that request --> then you get back the full table. PS: Can you verify that using the default search widget to filter out some data, the number of records shown on the table (beware, no jids=number should be in your address) is equal to the number of the exported records ? --
Re: [web2py] SQLFORM.grid exports the whole table
On 8 November 2012 15:32, Niphlod wrote: > ehm missing something here. jids is not a standard var for the grid, > are you using that var to filter the query passed to the grid ? > > One thing is taking care of the default filtering (keywords variable), > another is to accomplish the same thing with a customization like that. > > The grid was build with the following syntax SQLFORM(query, ..other arguments). jids was part of the query. It was not Smartgrid. Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3) --
Re: [web2py] SQLFORM.grid exports the whole table
ehm missing something here. jids is not a standard var for the grid, are you using that var to filter the query passed to the grid ? One thing is taking care of the default filtering (keywords variable), another is to accomplish the same thing with a customization like that. On Thursday, November 8, 2012 1:06:36 PM UTC+1, Johann Spies wrote: > > On 8 November 2012 12:19, Niphlod > wrote: > >> what is the url you exported from ? (i.e. hover on the export link and >> paste here (the relevant part is from the controller onwards)) >> > > The grid was produced with > http://localhost:8000/init/journal/journals?jids=331&jids=6972 > > And the csv-file with > > > http://localhost:8000/init/journal/journals?_export_type=csv&keywords=&order=&_signature=1a47676d3f77539c3a3f3ef122ba53cdbbc5e416 > > > Regards > Johann > > > -- > Because experiencing your loyal love is better than life itself, > my lips will praise you. (Psalm 63:3) > > --
Re: [web2py] SQLFORM.grid exports the whole table
what is the url you exported from ? (i.e. hover on the export link and paste here (the relevant part is from the controller onwards)) On Thursday, November 8, 2012 11:08:11 AM UTC+1, Johann Spies wrote: > > On 7 November 2012 17:38, Niphlod > wrote: > >> patch to apply to trunk. Please test it extensively with all the possible >> combinations. >> > > I applied the patch but did not see any different behaviour. > > A view with two records in the grid exported the whole table of more than > 1 records. > > And yes, I have restarted web2py after applying the patch. > > Regards > Johann > -- > Because experiencing your loyal love is better than life itself, > my lips will praise you. (Psalm 63:3) > > --
Re: [web2py] SQLFORM.grid exports the whole table
On 7 November 2012 17:38, Niphlod wrote: > patch to apply to trunk. Please test it extensively with all the possible > combinations. > I applied the patch but did not see any different behaviour. A view with two records in the grid exported the whole table of more than 1 records. And yes, I have restarted web2py after applying the patch. Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3) --
Re: [web2py] SQLFORM.grid exports the whole table
patch to apply to trunk. Please test it extensively with all the possible
combinations.
On Wednesday, November 7, 2012 2:23:35 PM UTC+1, Niphlod wrote:
>
> at that point you miss formatting fields and references. In addition,
> db.executesql() doesn't return an iterator either. You should code your own
> exporter using db._adapter.execute() and a yielding fetchone()s.
>
> On Wednesday, November 7, 2012 2:01:45 PM UTC+1, Johann Spies wrote:
>>
>> On 7 November 2012 14:05, Niphlod wrote:
>>
>>> if your set doesn't fit into memory, saving it to a temp file won't get
>>> you out of troubles. One of DAL problems is that a Rows object does not
>>> return an iterator from the cursor, it's fetched all into memory first.
>>>
>>>
>> In that case I would be inclined to use bypass DAL and db.executesql and
>> use the backend to export to a file. That should be more efficient.
>>
>> Regards
>> Johann
>> --
>> Because experiencing your loyal love is better than life itself,
>> my lips will praise you. (Psalm 63:3)
>>
>>
--
@@ -1934,25 +1934,23 @@ class SQLFORM(FORM):
orderby = db[tablename][fieldname]
if sign == '~':
orderby = ~orderby
-
-table_fields = [f for f in fields if f._tablename in tablenames]
-if (export_type in ('csv_with_hidden_cols', 'tsv_with_hidden_cols')
-and export_type in exportManager):
+expcolumns = columns
+if export_type.endswith('with_hidden_cols'):
+expcolumns = [f for f in fields if f._tablename in tablenames]
+if export_type in exportManager:
if request.vars.keywords:
try:
dbset = dbset(SQLFORM.build_query(
fields, request.vars.get('keywords', '')))
-rows = dbset.select(cacheable=True)
+rows = dbset.select(cacheable=True, *expcolumns)
except Exception, e:
response.flash = T('Internal Error')
rows = []
else:
-rows = dbset.select(cacheable=True)
-else:
-rows = dbset.select(left=left, orderby=orderby,
-cacheable=True, *columns)
+rows = dbset.select(left=left, orderby=orderby,
+cacheable=True, *expcolumns)
-if export_type in exportManager:
+#begin building up exported file
value = exportManager[export_type]
clazz = value[0] if hasattr(value, '__getitem__') else value
oExp = clazz(rows)
Re: [web2py] SQLFORM.grid exports the whole table
at that point you miss formatting fields and references. In addition, db.executesql() doesn't return an iterator either. You should code your own exporter using db._adapter.execute() and a yielding fetchone()s. On Wednesday, November 7, 2012 2:01:45 PM UTC+1, Johann Spies wrote: > > On 7 November 2012 14:05, Niphlod > wrote: > >> if your set doesn't fit into memory, saving it to a temp file won't get >> you out of troubles. One of DAL problems is that a Rows object does not >> return an iterator from the cursor, it's fetched all into memory first. >> >> > In that case I would be inclined to use bypass DAL and db.executesql and > use the backend to export to a file. That should be more efficient. > > Regards > Johann > -- > Because experiencing your loyal love is better than life itself, > my lips will praise you. (Psalm 63:3) > > --
Re: [web2py] SQLFORM.grid exports the whole table
On 7 November 2012 15:01, Johann Spies wrote: > > In that case I would be inclined to use bypass DAL and db.executesql and > use the backend to export to a file. That should be more efficient. > Sorry. That should read ... inclined to bypass DAL and use db.executesql ... -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3) --
Re: [web2py] SQLFORM.grid exports the whole table
On 7 November 2012 14:05, Niphlod wrote: > if your set doesn't fit into memory, saving it to a temp file won't get > you out of troubles. One of DAL problems is that a Rows object does not > return an iterator from the cursor, it's fetched all into memory first. > > In that case I would be inclined to use bypass DAL and db.executesql and use the backend to export to a file. That should be more efficient. Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3) --
Re: [web2py] SQLFORM.grid exports the whole table
if your set doesn't fit into memory, saving it to a temp file won't get you out of troubles. One of DAL problems is that a Rows object does not return an iterator from the cursor, it's fetched all into memory first. On Wednesday, November 7, 2012 12:45:46 PM UTC+1, Johann Spies wrote: > > On 7 November 2012 13:32, Niphlod > wrote: > >> far too magic to check for something that basically isn't trustable: even >> if you code something like that and you check that there's x mb available, >> another user can ask for another huge set of data and your "previoulsy" >> free RAM is not free anymore. >> >> > True. What about writing it to a temporary file and stream it from > there. What would the effect on resources be? > > Regards > Johann > -- > Because experiencing your loyal love is better than life itself, > my lips will praise you. (Psalm 63:3) > > --
Re: [web2py] SQLFORM.grid exports the whole table
far too magic to check for something that basically isn't trustable: even if you code something like that and you check that there's x mb available, another user can ask for another huge set of data and your "previoulsy" free RAM is not free anymore. On Wednesday, November 7, 2012 11:59:18 AM UTC+1, Johann Spies wrote: > > On 7 November 2012 12:33, Niphlod > wrote: > >> woking on 1. >> > > Thanks. > > >> for 2, how do you "foresee" how much time and RAM the present query will >> take to be serialized ? >> > > I don't know. I suspect a possible method would be: > > * Determine the average size of a record calculated on the result of the > present query handled by the grid. > * Determine the available ram on the server > * Calculate the size of the total result. If it is more than say 80% of > the available ram, download in multiple files and warn the user about > it. > * I don't know about the time-out issue. > > An alternative (and easier option) would be to warn the user that the > result is too large to be downloaded in one file and advise him/her to > adapt the query to get the result in smaller portions. > > Regards > Johann > -- > Because experiencing your loyal love is better than life itself, > my lips will praise you. (Psalm 63:3) > > --
Re: [web2py] SQLFORM.grid exports the whole table
woking on 1. for 2, how do you "foresee" how much time and RAM the present query will take to be serialized ? On Wednesday, November 7, 2012 11:25:29 AM UTC+1, Johann Spies wrote: > > On 7 November 2012 10:56, Niphlod > wrote: > >> uhm, good point on "if you want the entire table, just remove the >> filters". >> how to handle something that is impossible to handle (export a table with >> so many rows that you can't export without timeouts or consuming >> memory). just timeout ? >> > > I had a situation on Webfaction where a customer downloaded a csv file > (before the time of smartgrid) which put too much strain on the available > RAM and the result was incorrect and inconsistent. In the end I had to use > the backend (postgresql) through ssh to get the correct data. > > I suspect that one can put something like 'limitby' into the csv-query and > download several files if it exceeds a certain ceiling. > > I am working with sets of data of which some tables can contain millions > of records. > > So there are two issues in this thread: > > 1. The csv-download buttons in smartgrid/grid should download the result > of the present query > 2. Some safeguards should be build to prevent large datasets to consume > too much memory or time. > > Regards > Johann > -- > Because experiencing your loyal love is better than life itself, > my lips will praise you. (Psalm 63:3) > > --
Re: [web2py] SQLFORM.grid exports the whole table
uhm, good point on "if you want the entire table, just remove the filters". how to handle something that is impossible to handle (export a table with so many rows that you can't export without timeouts or consuming memory). just timeout ? On Wednesday, November 7, 2012 2:17:02 AM UTC+1, howesc wrote: > > i agree that i expected the export buttons to export what is shown above. > if you want to export the whole table, remove all filters so that the whole > table is shown above and then click export. > > (note that i do expect export to export all the rows that match the query > even if the table above is paginated). what i don't know is how we should > handle tables that are so large that the export function will timeout or > consume too much memory to complete. > > christian > > On Tuesday, November 6, 2012 8:34:58 AM UTC-8, Bill Thayer wrote: >> >> FWIW, My users will be filtering test station settings to upload the csv >> to thier test bench software. My users will need only thier own filtered >> data in the CSVso big Me Too! here > > --
Re: [web2py] SQLFORM.grid exports the whole table
i agree that i expected the export buttons to export what is shown above. if you want to export the whole table, remove all filters so that the whole table is shown above and then click export. (note that i do expect export to export all the rows that match the query even if the table above is paginated). what i don't know is how we should handle tables that are so large that the export function will timeout or consume too much memory to complete. christian On Tuesday, November 6, 2012 8:34:58 AM UTC-8, Bill Thayer wrote: > > FWIW, My users will be filtering test station settings to upload the csv > to thier test bench software. My users will need only thier own filtered > data in the CSVso big Me Too! here --
Re: [web2py] SQLFORM.grid exports the whole table
FWIW, My users will be filtering test station settings to upload the csv to thier test bench software. My users will need only thier own filtered data in the CSVso big Me Too! here --
Re: [web2py] SQLFORM.grid exports the whole table
I think it depends on how many records are in the table. If you get a couple million rows, that's quite a load for a spread sheet to import. On Tuesday, November 6, 2012 9:24:26 AM UTC-5, Niphlod wrote: > > to me instead it's useful to let them download the whole table are we > saying we should support 6*2 = 12 formats (current 6, "with filters" and > "without filters") ? How would you name them ? > > --
Re: [web2py] SQLFORM.grid exports the whole table
to me instead it's useful to let them download the whole table are we saying we should support 6*2 = 12 formats (current 6, "with filters" and "without filters") ? How would you name them ? --
Re: [web2py] SQLFORM.grid exports the whole table
I would personally expect the export to give the representation only of the fields I've made a query for. On Tue, Nov 6, 2012 at 4:10 PM, Johann Spies wrote: > On 6 November 2012 15:57, Niphlod wrote: > >> one sec. >> export is meant to give the user the full resultset he can access (let's >> say, offline consultation). >> Currently, only the "with hidden cols" exports honour the current query. >> Before calling it a bug we should agree on what functionalities expose on >> the grid . >> >> > If that is so, I think it should be the other way round. Export with > hidden columns should export all data, while the normal csv-download should > export what the user can see using the grid at that stage. > > Anyhow it should be clear to the user what he/she can expect and at the > moment it is not. > > > Regards > Johann > -- > Because experiencing your loyal love is better than life itself, > my lips will praise you. (Psalm 63:3) > > -- > > > > --
Re: [web2py] SQLFORM.grid exports the whole table
On 6 November 2012 15:57, Niphlod wrote: > one sec. > export is meant to give the user the full resultset he can access (let's > say, offline consultation). > Currently, only the "with hidden cols" exports honour the current query. > Before calling it a bug we should agree on what functionalities expose on > the grid . > > If that is so, I think it should be the other way round. Export with hidden columns should export all data, while the normal csv-download should export what the user can see using the grid at that stage. Anyhow it should be clear to the user what he/she can expect and at the moment it is not. Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3) --
Re: [web2py] SQLFORM.grid exports the whole table
one sec. export is meant to give the user the full resultset he can access (let's say, offline consultation). Currently, only the "with hidden cols" exports honour the current query. Before calling it a bug we should agree on what functionalities expose on the grid . Il giorno martedì 6 novembre 2012 14:47:40 UTC+1, Aurelijus Useckas ha scritto: > > OK > > On Tuesday, November 6, 2012 3:35:10 PM UTC+2, Johann Spies wrote: >> >> On 6 November 2012 12:50, Aurelijus Useckas wrote: >> >>> I want to export a certain query in SQLFORM.grid to a CSV, but once I >>> press on the (any) export option bellow the SQLFORM.grid, it exports the >>> whole table and not just the ones meeting the certain criteria. >> >> >> >> This must be viewed as a bug in my opinion. To me it is important that >> the csv-buttons at the bottom of the grid must work with the same query >> that determines what is shown in the grid. If the user refines the >> original query using the search options available, that must be reflected >> in the csv-export. >> >> I think it is worth opening an issue about this. Will you do it please? >> >> As a workaround you can add your own export button which redirects to a >> function which handles the query correctly. >> >> Regards >> Johann >> >> -- >> Because experiencing your loyal love is better than life itself, >> my lips will praise you. (Psalm 63:3) >> >> --
Re: [web2py] SQLFORM.grid exports the whole table
OK On Tuesday, November 6, 2012 3:35:10 PM UTC+2, Johann Spies wrote: > > On 6 November 2012 12:50, Aurelijus Useckas > > > wrote: > >> I want to export a certain query in SQLFORM.grid to a CSV, but once I >> press on the (any) export option bellow the SQLFORM.grid, it exports the >> whole table and not just the ones meeting the certain criteria. > > > > This must be viewed as a bug in my opinion. To me it is important that > the csv-buttons at the bottom of the grid must work with the same query > that determines what is shown in the grid. If the user refines the > original query using the search options available, that must be reflected > in the csv-export. > > I think it is worth opening an issue about this. Will you do it please? > > As a workaround you can add your own export button which redirects to a > function which handles the query correctly. > > Regards > Johann > > -- > Because experiencing your loyal love is better than life itself, > my lips will praise you. (Psalm 63:3) > > --
Re: [web2py] SQLFORM.grid exports the whole table
On 6 November 2012 12:50, Aurelijus Useckas wrote: > I want to export a certain query in SQLFORM.grid to a CSV, but once I > press on the (any) export option bellow the SQLFORM.grid, it exports the > whole table and not just the ones meeting the certain criteria. This must be viewed as a bug in my opinion. To me it is important that the csv-buttons at the bottom of the grid must work with the same query that determines what is shown in the grid. If the user refines the original query using the search options available, that must be reflected in the csv-export. I think it is worth opening an issue about this. Will you do it please? As a workaround you can add your own export button which redirects to a function which handles the query correctly. Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3) --
[web2py] SQLFORM.grid exports the whole table
I want to export a certain query in SQLFORM.grid to a CSV, but once I press on the (any) export option bellow the SQLFORM.grid, it exports the whole table and not just the ones meeting the certain criteria. Any ideas? --
