Using Date Fields in Compute Field Causes Error on CSV Upload

[Kyle Flanagan]

I'm encountering an error when uploading a CSV file when dates are involved in a computed field. Using sqlite, web2py version: 2.8.2-stable+timestamp.2013.11.28.13.54.07 (also tested on 2.9.5-stable+timestamp.2014.03.16.02.35.39).

E.g., consider the following table definition in a model:

db.define_table('POC2',

                Field('poc_date', 'date', required=True),

                Field('ten_days_before_poc_date', 'date', compute = lambda r: r['poc_date'] - datetime.timedelta(10))

                )

Now, use appadmin to manage the POC2 table and insert a new record. This works as expected. 

Now, try uploading the following CSV file and importing it into the table:

POC2.id,POC2.poc_date

1,2014-05-30

The following error appears:

unable to parse csv file

unsupported operand type(s) for -: 'str' and 'datetime.timedelta'

It appears that r['poc_date'] is passed to the lambda as a string when uploading via CSV, but as a date when going through appadmin. Is this the designed behavior, and is it documented anywhere? 

I can always test the datatype of r['poc_date'] and convert it to a datetime explicitly, but I don't want to make my code any messier than needed if I'm missing something regarding computed fields and CSV imports.

[Kyle Flanagan]

I've found the issue. The function import_from_csv_file in dal.py calls a fix() function which reads the columns of the file and parses the data and gives each value a datatype according to the database table's field data type. Unfortunately, for any 'date' type, it just passes through this fix() function and remains a simple string. This works fine if the value isn't used in a computed field that expects a datetime object to be returned by the Row object.

I fixed this by changing the fix() function as follows:

        def fix(field, value, id_map, id_offset):

            list_reference_s='list:reference'

            if value == null:

                value = None

            elif field.type=='blob':

                value = base64.b64decode(value)

            elif field.type=='double' or field.type=='float':

                if not value.strip():

                    value = None

                else:

                    value = float(value)

            elif field.type in ('integer','bigint'):

                if not value.strip():

                    value = None

                else:

                    value = long(value)

            elif field.type.startswith('list:string'):

                value = bar_decode_string(value)

            ##### begin additional code ########################################
            elif field.type == 'date':

                # assume an ISO date

                if not value.strip():

                    value = None

                else:

                    value = datetime.datetime.strptime(value, "%Y-%m-%d")
           
            ###### end additional code #########################################

            elif field.type.startswith(list_reference_s):

                ref_table = field.type[len(list_reference_s):].strip()

                if id_map is not None:

                    value = [id_map[ref_table][long(v)] \

                             for v in bar_decode_string(value)]

                else:

                    value = [v for v in bar_decode_string(value)]

            elif field.type.startswith('list:'):

                value = bar_decode_integer(value)

            elif id_map and field.type.startswith('reference'):

                try:

                    value = id_map[field.type[9:].strip()][long(value)]

                except KeyError:

                    pass

            elif id_offset and field.type.startswith('reference'):

                try:

                    value = id_offset[field.type[9:].strip()]+long(value)

                except KeyError:

                    pass

            return (field.name, value)