empy string from csv for integer for integer field

[goome]

Hello

i have a table with integer field.

i populated thhe db from a csv (created from other then me).

it sometimes has empty value for the integers fields.

So when i try to get a list of the records of the table, i got:

type 'exceptions.ValueError'> invalid literal for long() with base 10: ''

from db.py  #table prodotti

Field("lunghezza","integer"),

Field("larghezza","integer"),

from default.py

prodotti = legacy_db().select(legacy_db.prodotti.ALL,orderby='prodotti.id DESC', limitby=limitby)

The error ticket:

File "/home/marcello/scripts/web2py/2/web2py/applications/PROVE/controllers/default.py", line 171, in lista_prodotti

    prodotti = legacy_db().select(legacy_db.prodotti.ALL,orderby='prodotti.id DESC', limitby=limitby)

  File "/home/marcello/scripts/web2py/2/web2py/gluon/packages/dal/pydal/objects.py", line 2026, in select

    return adapter.select(self.query,fields,attributes)

  File "/home/marcello/scripts/web2py/2/web2py/gluon/packages/dal/pydal/adapters/sqlite.py", line 125, in select

    return super(SQLiteAdapter, self).select(query, fields, attributes)

  File "/home/marcello/scripts/web2py/2/web2py/gluon/packages/dal/pydal/adapters/base.py", line 1239, in select

    return self._select_aux(sql,fields,attributes)

  File "/home/marcello/scripts/web2py/2/web2py/gluon/packages/dal/pydal/adapters/base.py", line 1220, in _select_aux

    return processor(rows,fields,self._colnames,cacheable=cacheable)

  File "/home/marcello/scripts/web2py/2/web2py/gluon/packages/dal/pydal/adapters/base.py", line 1596, in parse

    value = self.parse_value(value,ft,blob_decode)

  File "/home/marcello/scripts/web2py/2/web2py/gluon/packages/dal/pydal/adapters/base.py", line 1450, in parse_value

    return self.parsemap[key](value,field_type)

  File "/home/marcello/scripts/web2py/2/web2py/gluon/packages/dal/pydal/adapters/base.py", line 1534, in parse_integer

    return long(value)

ValueError: invalid literal for long() with base 10: ''

how to face with empty values?

[Niphlod]

an integer can't be ''. set them to null of to 0 at import time :D

[Dave S]

On Wednesday, June 24, 2015 at 10:28:21 AM UTC-7, goome wrote:

Hello

i have a table with integer field.

i populated thhe db from a csv (created from other then me).

it sometimes has empty value for the integers fields.

So when i try to get a list of the records of the table, i got:

type 'exceptions.ValueError'> invalid literal for long() with base 10: ''

[...]

how to face with empty values?

I think the simplest choices are probably
*  preprocessing (for example, runnng the CSV file through sed or awk to replace ',,' with ' ,-9999999,' (or other value you can recognize as "illegal")
*  customizing the CSV import code to allow an empty field.

/dps

[Dave S]

On Wednesday, June 24, 2015 at 11:43:46 AM UTC-7, Dave S wrote:

But hey, Niphlod beat me to it.

 

This is similar to Niphlod's advice.  But lacking his deeper knowledge, I'd be running a "cleanup job" (post-processing) on the imported data to convert the "illegal" values to NULL.  Probably through the scheduler.
 

*  customizing the CSV import code to allow an empty field.

Or changing the field definition, perhaps to a string type, and then converting non-empty strings at a later time.

Or changing the validator to provide the NULL.
 
/dps

[goome]

> Or changing the validator to provide the NULL.

how could it be done? in the model?

Thanks

[黄祥]

they suggest to change your csv that have '' empty or null value for the field that have integer type. another way around is to set default = 0 in your table definition in models for that table.

best regards,
stifan

[Dave S]

On Wednesday, June 24, 2015 at 2:34:40 PM UTC-7, goome wrote:

> Or changing the validator to provide the NULL.

how could it be done? in the model?

I believe so, but I've only played around a little with validators.  The other suggestions may be simpler for you.
Or you can search for custom validators among the old posts, and check the web2py book
<URL:http://www.web2py.com/books/default/chapter/29/07/forms-and-validators#Custom-validators>

In my db.py, I have (at the end) this terribly unsophisticated custom validator.

from gluon.validators import Validator

class IS_HEXSTR(Validator):
    def __init__(self, format='%c%c%c%c', error_message='must be a 4-digit hex string like A09F!'):
        self.format = format
        self.error_message = error_message
    def __call__(self, value):
        try:
            if len(value) != 4:
                raise ValueError
            intvalue = int(value, 16)
            print "should be valid " + str(intvalue)
            return (value, None)
        except ValueError:
            print "invalid " + value
            return (value, self.error_message)
        except Exception as e:
            print "invalid " + value
            raise e
    def formatter(self, value):
        return value.upper()