def main(path): """ Read in the Data Working Group sheets """ logger.info(str(('read field information file', path))) properties = ('model_field', 'required', 'default', 'converter') column_definitions = { 'table': 'table', 'field': 'field', 'alias': 'alias', 'queryset': 'queryset', 'show in detail': ('show_in_detail', True, False, util.bool_converter), 'show in list': ('show_in_list', True, False, util.bool_converter), 'show_as_extra_field': ('show_as_extra_field', False, False, util.bool_converter), 'is_lincs_field': ('is_lincs_field', True, False, util.bool_converter), 'is_unrestricted': ('is_unrestricted', False, False, util.bool_converter), 'list_order': ('list_order', True, None, lambda x: util.convertdata(x, int)), 'detail_order': ('detail_order', True, None, lambda x: util.convertdata(x, int)), 'use_for_search_index': ('use_for_search_index', True, False, util.bool_converter), 'Data Working Group version': 'dwg_version', 'Unique ID': ('unique_id', True), 'DWG Field Name': 'dwg_field_name', 'HMS Field Name': 'hms_field_name', 'Related to': 'related_to', 'Description': 'description', 'Importance (1: essential; 2: desirable / recommended; 3: optional)': 'importance', 'Comments': 'comments', 'Ontologies / references considered': 'ontology_reference', 'Link to ontology / reference': 'ontology_reference', 'Additional Notes (for development)': 'additional_notes', } column_definitions = util.fill_in_column_definitions( properties, column_definitions) with open(path) as f: reader = csv.reader(f) labels = reader.next() cols = util.find_columns(column_definitions, labels, all_sheet_columns_required=False) logger.info('delete current table') FieldInformation.objects.all().delete() for j, row in enumerate(reader): logger.debug('row %d: %s', j, row) initializer = {} for i, value in enumerate(row): if i not in cols: logger.info(str(('column out of range', j + 1, i))) continue properties = cols[i] logger.debug(str(('read col: ', i, ', ', properties))) required = properties['required'] default = properties['default'] converter = properties['converter'] model_field = properties['model_field'] # Todo, refactor to a method logger.debug(str(('raw value', value))) if converter: logger.debug(str(('using converter', converter, value))) value = converter(value) logger.debug(str(('converted', value))) # Note: must check the value against None, as False is a valid value if value is None: if default != None: value = default # Note: must check the value against None, as False is a valid value if value is None and required is True: raise Exception('Field is required: %s, record: %d' % (properties['column_label'], j + 1)) logger.debug( str(('model_field: ', model_field, ', value: ', value))) initializer[model_field] = value try: logger.debug(str(('initializer: ', initializer))) if not initializer['field']: logger.warn( str(( 'Note: table entry has no field definition (will be skipped)', initializer, 'current row:', j + 1))) continue lfi = FieldInformation(**initializer) # check if the table/field exists if lfi.table: table = models.get_model(APPNAME, lfi.table) if table: if lfi.field not in map(lambda x: x.name, table._meta.fields): raise Exception(str( ('unknown field: ', lfi.field))) else: raise Exception(str(('unknown table', lfi.table))) lfi.save() logger.info(str(('fieldInformation created:', lfi))) except Exception, e: logger.error( str(("Invalid fieldInformation, initializer so far: ", initializer, 'current row:', j + 1, e))) raise e
def main(path): """ Read in the Data Working Group sheets """ logger.info("start") book = xlrd.open_workbook(path) #open our xls file, there's lots of extra default options in this call, for logging etc. take a look at the docs #sheet = book.sheets()[0] #book.sheets() returns a list of sheet objects... alternatively... #sheet = book.sheet_by_name("qqqq") #we can pull by name worksheet = book.sheet_by_index(0) #or by the index it has in excel's sheet collection properties = ('model_field','required','default','converter') column_definitions = {'table':'table', 'field':'field', 'alias':'alias', 'queryset':'queryset', 'show in detail':('show_in_detail',True,False,util.bool_converter), 'show in list':('show_in_list',True,False,util.bool_converter), 'show_as_extra_field':('show_as_extra_field',False,False,util.bool_converter), 'is_lincs_field':('is_lincs_field',True,False,util.bool_converter), 'is_unrestricted':('is_unrestricted',False,False,util.bool_converter), 'order':('order',True,None,lambda x:util.convertdata(x,int)), 'use_for_search_index':('use_for_search_index',True,False,util.bool_converter), 'Data Working Group version':'dwg_version', 'Unique ID':('unique_id',True), 'DWG Field Name':'dwg_field_name', 'HMS Field Name':'hms_field_name', 'Related to':'related_to', 'Description':'description', 'Importance (1: essential; 2: desirable / recommended; 3: optional)':'importance', 'Comments':'comments', 'Ontologies / references considered':'ontology_reference', 'Link to ontology / reference':'ontology_reference', 'Additional Notes (for development)':'additional_notes', } # convert the labels to fleshed out dict's, with strategies for optional, default and converter column_definitions = util.fill_in_column_definitions(properties,column_definitions) num_rows = worksheet.nrows - 1 num_cells = worksheet.ncols - 1 curr_row = 0 # note zero indexed row = worksheet.row(curr_row) labels = [] i = -1 while i < num_cells: i += 1 # Cell Types: 0=Empty, 1=Text, 2=Number, 3=Date, 4=Boolean, 5=Error, 6=Blank # cell_type = worksheet.cell_type(curr_row, curr_cell) labels.append(str(worksheet.cell_value(curr_row, i))) # create a dict mapping the column ordinal to the proper column definition dict cols = util.find_columns(column_definitions, labels, all_sheet_columns_required=False) logger.info('delete current table'); FieldInformation.objects.all().delete() rows = 0 while curr_row < num_rows: curr_row += 1 actual_row = curr_row + 1 row = worksheet.row(curr_row) if(logger.isEnabledFor(logging.DEBUG)): logger.debug(str(('row', row))) i = -1 initializer = {} while i < num_cells: i += 1 # Cell Types: 0=Empty, 1=Text, 2=Number, 3=Date, 4=Boolean, 5=Error, 6=Blank #cell_type = worksheet.cell_type(curr_row, curr_cell) value = unicode(worksheet.cell_value(curr_row, i)) if i not in cols: continue properties = cols[i] logger.debug(str(('read col: ', i, ', ', properties))) required = properties['required'] default = properties['default'] converter = properties['converter'] model_field = properties['model_field'] # Todo, refactor to a method logger.debug(str(('raw value', value))) if(converter != None): logger.debug(str(('using converter',converter,value))) value = converter(value) logger.debug(str(('converted',value))) if(value == None ): if( default != None ): value = default if(value == None and required == True): raise Exception('Field is required: %s, record: %d' % (properties['column_label'],actual_row)) logger.debug(str(('model_field: ' , model_field, ', value: ', value))) initializer[model_field] = value try: logger.debug(str(('initializer: ', initializer))) #if((initializer['table'] == None and initializer['queryset'] == None ) or if(initializer['field'] == None): logger.warn(str(('Note: table entry has no field definition (will be skipped)', initializer, 'current row:', actual_row))) continue; lfi = FieldInformation(**initializer) # check if the table/field exists if(lfi.table != None): table = models.get_model(APPNAME, lfi.table) if( table != None): if(lfi.field not in map(lambda x: x.name,table._meta.fields) ): raise Exception(str(('unknown field: ', lfi.field))) else: raise Exception(str(('unknown table', lfi.table ))) lfi.save() logger.info(str(('fieldInformation created:', lfi))) rows += 1 except Exception, e: logger.error(str(( "Invalid fieldInformation, initializer so far: ", initializer, 'current row:', actual_row,e))) raise e
def main(path): """ Read in the Data Working Group sheets """ logger.info(str(('read field information file', path))) properties = ('model_field','required','default','converter') column_definitions = { 'table':'table', 'field':'field', 'alias':'alias', 'queryset':'queryset', 'show in detail':('show_in_detail',True,False,util.bool_converter), 'show in list':('show_in_list',True,False,util.bool_converter), 'show_as_extra_field':('show_as_extra_field',False,False,util.bool_converter), 'is_lincs_field':('is_lincs_field',True,False,util.bool_converter), 'is_unrestricted':('is_unrestricted',False,False,util.bool_converter), 'list_order':('list_order',True,None,lambda x:util.convertdata(x,int)), 'detail_order':('detail_order',True,None,lambda x:util.convertdata(x,int)), 'use_for_search_index':('use_for_search_index',True,False,util.bool_converter), 'Data Working Group version':'dwg_version', 'Unique ID':('unique_id',True), 'DWG Field Name':'dwg_field_name', 'HMS Field Name':'hms_field_name', 'Related to':'related_to', 'Description':'description', 'Importance (1: essential; 2: desirable / recommended; 3: optional)':'importance', 'Comments':'comments', 'Ontologies / references considered':'ontology_reference', 'Link to ontology / reference':'ontology_reference', 'Additional Notes (for development)':'additional_notes', } column_definitions = util.fill_in_column_definitions( properties,column_definitions) with open(path) as f: reader = csv.reader(f) labels = reader.next() cols = util.find_columns( column_definitions, labels, all_sheet_columns_required=False) logger.info('delete current table'); FieldInformation.objects.all().delete() for j,row in enumerate(reader): logger.debug('row %d: %s', j, row) initializer = {} for i,value in enumerate(row): if i not in cols: logger.info(str(('column out of range',j+1, i))) continue properties = cols[i] logger.debug(str(('read col: ', i, ', ', properties))) required = properties['required'] default = properties['default'] converter = properties['converter'] model_field = properties['model_field'] # Todo, refactor to a method logger.debug(str(('raw value', value))) if converter: logger.debug(str(('using converter',converter,value))) value = converter(value) logger.debug(str(('converted',value))) # Note: must check the value against None, as False is a valid value if value is None: if default != None: value = default # Note: must check the value against None, as False is a valid value if value is None and required is True: raise Exception('Field is required: %s, record: %d' % (properties['column_label'],j+1)) logger.debug(str(('model_field: ' , model_field, ', value: ', value))) initializer[model_field] = value try: logger.debug(str(('initializer: ', initializer))) if not initializer['field']: logger.warn(str(( 'Note: table entry has no field definition (will be skipped)', initializer, 'current row:', j+1))) continue; lfi = FieldInformation(**initializer) # check if the table/field exists if lfi.table: table = models.get_model(APPNAME, lfi.table) if table: if lfi.field not in map(lambda x: x.name,table._meta.fields): raise Exception(str(('unknown field: ', lfi.field))) else: raise Exception(str(('unknown table', lfi.table ))) lfi.save() logger.info(str(('fieldInformation created:', lfi))) except Exception, e: logger.error(str(( "Invalid fieldInformation, initializer so far: ", initializer, 'current row:', j+1,e))) raise e