Error: Exporting CSV file in SQLFORM.grid

[Prasad Muley]

Hello All,

       I've used a SQLFORM.grid for displaying table content. I've provided export to CSV option in grid.

My app settings are as below:

models/db.py

db.define_table('company',

                Field('name', 'string', length=128, notnull=True, unique=True),

                Field('address', 'string'), format='%(name)s')

db.define_table('document',

                          Field('name', 'string', length=128, notnull=True),

                          Field('type', 'string', notnull=True),

                          Field('company', db.company, format='%(name)s')               

Here, 

        document table has company as a reference key,

        Exported CSV should contain the company names not ids

So I've defined a class CSVExporter according to anthony  solution for web2pygrid-csv-exports-shows-ids-not-values-for-reference-fields

modules/doc_utils.py

from cStringIO import StringIO

class CSVExporter(object):

    """This class is used when grid's table contains reference key id.

       Exported CSV should contain reference key name of reference 

       key not ids"""

    file_ext = "csv"

    content_type = "text/csv"

    def __init__(self, rows):

        self.rows = rows

    def export(self):

        if self.rows:

            s = StringIO()

            self.rows.export_to_csv_file(s, represent=True)

            return s.getvalue()

        else:

            return ''

#Grid uses Custom search for string

def search_query(fields, keywords):

    """" Custom search for doc grid"""

    if isinstance(keywords, (tuple, list)):

        keywords = keywords[0]

        request.vars.keywords = keywords

    key = keywords.strip()

    if key and not '"' in key and not "'" in key and key:

        SEARCHABLE_TYPES = ('string', 'text', 'list:string')

        words = key.split(' ') if key else []

        filters = []

        for field in fields:

            #apply search on company_name also

            if field.name == "portfolio_company":

                #get db from current module

                db = current.db

                #get company name from record

                company_ids = [company.id for company in db(

                    db.company.name.contains(words)).select(db.company.id)]

                if company_ids:

                    filters.append(field.belongs(company_ids))

                continue

            if field.type in SEARCHABLE_TYPES:

                all_words_filters = []

                for word in words:

                    all_words_filters.append(field.contains(word))

                filters.append(reduce(lambda a, b: (a & b), all_words_filters))

        parts = filters

    else:

        parts = None

    if parts:

        return reduce(lambda a, b: a | b, parts)

    else:

        return None

controllers/documents.py

from applications.asdf.doc_utils import CSVExporter, search_query

def docs():

        export_csv = False

        export_classes = None

        query = valid_db_query_here

        if auth.has_membership('manager'): 

                 export_csv = True

                 export_classes = dict(csv=(CSVExporter, 'CSV'), xml=False, html=False,

                                                    json=False, csv_with_hidden_cols=False,

                                                    tsv=False, tsv_with_hidden_cols=False)

        grid = SQLFORM.grid(query, orderby=~db.document.created_on,

                                             showbuttontext=False, csv=export_csv, deletable=False,

                                             searchable=search_query, exportclasses=export_classes)

        

       return (grid=grid)

If I search single keyword like Google or MicroSoft in then export works as expected.

if I search multiple keywords like Google India Private Limited or Redhat India Pvt Ltd then it shows expected rows in grid

But If I click on export button then it gives me Following error

Ticket ID

127.0.0.1.2015-01-14.21-56-57.34fb2b60-2857-4c1a-9626-a854630fc9c7

<type 'exceptions.AttributeError'> 'list' object has no attribute 'colnames'

Version

web2py™ Version 2.9.5-stable+timestamp.2014.03.16.02.35.39

Traceback

1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

11.

12.

13.

14.

15.

16.

Traceback (most recent call last):

  File "/home/prasad/Rootpy/web2py_2.9/gluon/restricted.py", line 220, in restricted

    exec ccode in environment

  File "/home/prasad/Rootpy/web2py_2.9/applications/asdf/controllers/documents.py", line 137, in <module>

  File "/home/prasad/Rootpy/web2py_2.9/gluon/globals.py", line 385, in <lambda>

    self._caller = lambda f: f()

  File "/home/prasad/Rootpy/web2py_2.9/gluon/tools.py", line 3287, in f

    return action(*a, **b)

  File "/home/prasad/Rootpy/web2py_2.9/gluon/tools.py", line 3287, in f

    return action(*a, **b)

  File "/home/prasad/Rootpy/web2py_2.9/applications/asdf/controllers/documents.py", line 112, in upload

    exportclasses=export_classes)

  File "/home/prasad/Rootpy/web2py_2.9/gluon/sqlhtml.py", line 2221, in grid

    rows.colnames = expcolumns # expcolumns is all cols to be exported including virtual fields

AttributeError: 'list' object has no attribute 'colnames'

I've started debugging and got following the point.

For single keyword search, the rows object has <class 'gluon.dal.Rows'> type and it contains exact rows which query gives 

But the rows object has list type for multiple search keywords

Any clues/hints/suggestions for debugging this error?

[Tim Richardson]

A quick question: if you try the standard Tab Separated Value export, does it work? (you can refer back to your stack overflow link to see why I ask).

(I don't think it will)

[Prasad Muley]

Hi Tim,

      Export format should be CSV file. I am getting that error because of the custom search in grid.

I got some clues from Exporting from a SQLFORM.grid with customized search queries

I'll try TSV once I fix this issue.

On Thu, Jan 15, 2015 at 1:32 PM, Tim Richardson <t...@tim-richardson.net> wrote:

A quick question: if you try the standard Tab Separated Value export, does it work? (you can refer back to your stack overflow link to see why I ask).

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/P2avEkVVeZg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Thanks and Regards
Prasad M. Muley

[gi...@storedot.us]

Could you share your solution?

[sandeep patel]

I have solved this problem. This problem is occurring because of filter object you have to convert into the list.

You have to need some changes in the sqlhtml file.

go to gluon/sqlhtml.py change line no : 2688 

From - sfields = reduce(lambda a, b: a + b, [filter(is_searchable, t) for t in tables])

To - sfields = reduce(lambda a, b: a + b, [list(filter(is_searchable, t)) for t in tables])

Note: Web2py version 2.17.2 and Python 3

Thanks

Sandeep

--

Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---

You received this message because you are subscribed to the Google Groups "web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.

[Massimo Di Pierro]

Thanks!

To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscribe@googlegroups.com.