0

I am Curious about the behaviour of the SearchCursor on a server Database feature class. the data for some of the the fields I want to read is set as TEXT, with some of those fields values set as <Null>

I am using an insert cursor to write those fields to another database of which the equivalent field is set as DOUBLE.

The Source field names and target field names are variable and could be one field, could be 10+ fields (but always 1 to 1 mapping from source to target fields) depending on the DS/FC combo

the basis of the code that is doing the read from source and then write to target is this:

        TargetCursor = arcpy.da.InsertCursor(TargetFC,TargetFields) 
        
        #grab the row information from the source feature class and the equivalent mapped source fields (that should line up to the target fields)
        with arcpy.da.SearchCursor(SourceFC,SourceFields) as cursor:
            for row in cursor:
                print row
                TargetCursor.insertRow(row)
        del TargetCursor

The TargetFC is iterated from all feature classes within all Datasets. And the SourceFields and TargetFields are built for each specific target Featureclass so that they map when the insert cursor happens.

Mostly the source field types (however many there might be) are the same as the target field types, but there are a few instances where one of the source fields is a type TEXT and the Target is a type DOUBLE (Allows Null) But when the script runs the search cursor interprets Nulls in the source data as ' ' so when the insert cursor runs, it produces an incompatible field type error.

TargetCursor.insertRow(row) RuntimeError: The value type is incompatible with the field type. [CONFDATE]

Example:

SourceFields = [u'Shape@', u'DATA_SOURC', u'DESCRIPTIO', u'DATE_SOURC',
       u'DATE_DATA_', u'AMENDMENT_']

TargetFields = [u'Shape@', u'DATASRCE', u'DESCRIPT', u'SRCEDATE', u'CONFDATE',
       u'AMENDATE'

(note these Source and Target Field maps are specific for this loop of the overall DS/FC loop)

The last line that prints from the SearchCursor is this:

(<Polygon object at 0x46a9f10[0x104fd8c0]>, u'SP', u'SP172165ROADWAY', u'20170329', u' ', u'20170403')

So I can see that the second to last field in the source data (DATE_DATA_) is ' '

but if I look in the database, it is Nullenter image description here

Is there a way to read that Null from the source field and insert it as Null also into the target for where that scenario happens? Or alternatively insert the 0 into the target for that situation where a TEXT is going to a DOUBLE and the Source field is Null (or interpreted as ' ')?

4
  • 1
    I dont know if this is a good idea, but what if you replace the empty string with None like this: row = (val if val !='' else None for val in row), then insert this
    – BERA
    Sep 22, 2021 at 8:10
  • An empty string is not a NULL. Only None is a NULL. If you want an empty string to map to a numeric NULL, you need to code it.
    – Vince
    Sep 22, 2021 at 11:56
  • Hi @Vince, I think that is why i am a little perplexed as the source data shows as <Null> for the String (the DATE_DATA_ field). If it was an empty string instead of NULL then wouldn't those values in DATE_DATA_ just report as a blank value? I assumed behaviour of the SearchCursor would have interpreted <Null> in the database as 'None' in Python, and an empty field in the database as simply '' (no spaces). But I am getting ' ' (with a space) for the <Null> entries (or is that what SearchCursor does for the <Null> entries to not represent them as 'None'?) Sep 23, 2021 at 0:55
  • Hi @BERA I tried it with the print straight afterwards but it looks a little dangerous as you mention, I am guessing i need to then iterate through that particular row, determine if the field type is a TEXT or INTEGER and see if that aligns to the Target Field Type and then if not the same, then set that specific value in the row to be 'None' or 0? Sep 23, 2021 at 0:58

1 Answer 1

0

I don't know if this is helpful or not, I tried to take on what @BERA and @Vince mentioned and highlighted but I didn't really know how to iterate within the fields of a row based on the field types at each section in the row.

I know my mapping spreadsheet is one to one. So I utilised that bit of knowledge and used the zip feature to compare the Source Field Mapping name, Target field mapping name and the specific field within the row.

I could determine the field type for the source and target fields because i knew the field names. So iterated through each of those FeatureClasses to get their respective field types

Then after that, could work out if a particular source field that was of type 'Text' with a entry when looking at the database (reported as a ' ' in the search cursor) was going to be inserted into the new database as target of 'Double'

I could then change that one particular field to a zero (0) and learnt along the way that I couldn't directly modify the row so had to create a list of the row, modify the particular field I was looking at (through way of a counter within the row) and then recreate the row again ready for the InsertCursor.

The Code looks like it is working but the loops have drastically slowed down the process from something that took 15 minutes to something that now takes many hours. But...it is doing things :)

#iterate through the target featureclass in the fields recognised from the mapping xlsspreadsheet
CursorTarget = arcpy.da.InsertCursor(TargetFC,TargetFieldsFromMap) # note arcmap 10.6 doesn't allow trspatial coordinate referencing in this step, arcgis pro does seem to allow it though

#grab the row information from the source feature class and the equivalent mapped source field (that should line up to the target fields)
with arcpy.da.SearchCursor(SourceFC,SourceFieldsFromMap) as rows:
    for row in rows:
       Counter = 0 #counter for iterating through the specific fields in a particular row so that i know what field index to modify if required
       for (a,b,c) in zip (row,SourceFieldsFromMap,TargetFieldsFromMap): #comparison of the row, and the two lists from the mapping sheet iterate through each part of the row
          SourceFCFields = arcpy.ListFields(SourceFC,b) #theListfields should only have the one field object in there based on the source field from the mapping table
          TargetFCFields = arcpy.ListFields(TargetFC,c) #theListfields should only have the one field object in there based on the target field from the mapping table
          SourceFCFieldType =''
          TargetFCFieldType =''
          for SourceFCField in SourceFCFields:
              SourceFCFieldType = SourceFCField.type #should only loop through onece to record the field type of the specific sourcefield recorded in regards to the mapping sheet)
          for TargetFCField in TargetFCFields:
              TargetFCFieldType = TargetFCField.type #should only loop through onece to record the field type of the specific targetfield recorded in regards to the mapping sheet)
          #print (a,b,SourceFCFieldType,c,TargetFCFieldType)
          if SourceFCFieldType == 'String' and TargetFCFieldType == 'Double' : #only come in here if there is a potential mismatch of Field Types
             if a ==' ' or a == '' or a is None:
                #change the value of the Field that is a string entry from an incompatible target vallue to one that the system will accept
                CopyOfTheRow = list(row)
                CopyOfTheRow[Counter] = 0
                row  = tuple(CopyOfTheRow)
                    
          Counter = Counter + 1 #iterate the counter within the specific row to determine which field index is being interrogated 
                 
                 
            
       print row
       CursorTarget.insertRow(row)
del CursorTarget

Don't know if this is useful for anyone or not or if there is a drastically more efficient way but it is something for someone potentially.

1
  • strange....i now seem to get an error in what feels like some inconsistent spots? { CursorTarget.insertRow(row) RuntimeError: A row with this OID already exists.} i can focus on the specific feature class and it won't error, but if the feature class is part of an iterated dataset, then it seems to randomly pop up....but not necessarily part of the above code??? And i don't even parse the OID from the search cursor or the insert cursor. Strange. Sep 29, 2021 at 8:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.