Insert Cursor not working, even though exit code 0, on SDE Database

1015
2
Jump to solution
10-18-2017 10:31 AM
Highlighted
Regular Contributor

I have a script that uses a search cursor on a shapefile to retrieve data, add a few things, puts each row in a list, and then copies each item in that list as a new row to a SDE feature class using an insert cursor. The problem is that when I use the insert cursor, it doesn't insert any rows into the SDE feature class, even though I get an exit code of 0 and no error messages. Based on the output window, it appears to have inserted the rows but if I go look at the feature class, nothing is there. I have tested the script by using an insert cursor on a regular file geodatabase feature class and it works just fine, so there must be something special about the SDE environment that I'm missing.

I have the appropriate data types and number of items to match to fields, so it is not a sequence size error or a data type issue. I have attempted to carry out the insert cursor action inside an editing session (edit.startEditing(False, False) and edit.stopEditing(True)) but no luck with that. 

The data is not versioned and never will be. Could that be what is causing the issue? Is there something about database permissions that I don't understand and need to write into the code?

Script:

#Set the workspace to access feature classes in the HbMonitoringTest database
arcpy.env.workspace = r'C:\Users\user\AppData\Roaming\ESRI\Desktop10.5\ArcCatalog\myDbName_HbMonitoringTest.sde'

#Getting feature classes from database
PatchesFC = "Patches"

#Get input shapefile from user
InputFC = r'C:\Users\user\Desktop\GIS_Testing\HbtatTesting\ExpTestFC.shp'

#Set search cursor fields to iterate through the input feature class
scFields = ['SHAPE@','StrMPID', 'StateID', 'Point', 'PatchNum', 'IsDevelope',
            'CropTypeID', 'CropResidu', 'CnpyOver12', 'CnpyDecid',
            'CnpyConif', 'ShrubCover','ShbHiStems', 'GrassCover',
            'ForbCover', 'FrbAsProte', 'ForbSpecie', 'BareGround',
            'HerbHeight', 'IsQuailHab', 'ObsvDate']

#Set insert cursor fields to add new rows to Patches feature class
icFields = ['SHAPE@', 'MonitoringPointID', 'PatchID', 'StateID', 'Point', 'PatchNum',
            'IsDeveloped', 'CropTypeID', 'CropResidue', 'CnpyOver12', 'CnpyDecid',
            'CnpyConif', 'ShrubCover', 'ShbHiStemsDens', 'GrassCover', 'ForbCover',
            'FrbAsProtect', 'ForbSpecies', 'BareGround', 'HerbHeight', 'IsQuailHab',
            'ObsvDate', 'ObserverID']

#Instantiate empty list to add edited rows too
rows2Insert = []

#Create search cursor to pull data from the attribute table of the user-input shapefile
with arcpy.da.SearchCursor(InputFC, scFields) as sCursor:
    for row in sCursor:
        rowList = list(row)
        PatchID = '{' + str(uuid.uuid4()).upper() + '}'
        ObserverID = None
        rowList.insert(2, PatchID)
        rowList.append(ObserverID)
        rows2Insert.append(rowList)


#Create insert cursor to insert new rows into the Patches database
with arcpy.da.InsertCursor(PatchesFC, icFields) as iCursor:
    for item in rows2Insert:
        print item
        iCursor.insertRow(item)
        print "Row inserted."‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

This is the output I get in the output window upon running the script:

Tags (2)
Reply
0 Kudos
1 Solution

Accepted Solutions
Highlighted
Regular Contributor

I have solved this issue, it appears there's a bug (shocker!). The float fields are the culprit, let me explain.

In the shapefile I had set precision and scale of the field to 3 and 2, respectively. Unfortunately, these are reverted to 0 upon clicking "Ok" when adding the field (if I right-click the field and go to properties, I now see precision and scale are both 0 which isn't how I set them).  Shapefiles are supposed to support precision and scale as per documentation here Working with fields in shapefiles by adding a field in ArcCatalog—Help | ArcGIS Desktop . This is the bug.

My target SDE table class for inserting rows also had the matching float fields with precision and scale set to 3 and 2. This did not revert to 0, so now the scale in the shapefile float fields is much larger than the scale in the SDE feature class float fields.

Because it reverted to 0, my float field in the shapefile is now interpreted by arcpy as having a much larger scale than it's supposed to, so when I try to use an insert cursor to take data from the shapefile and insert it into my SDE feature class fields, the float fields won't work, because the scale is too large, thus it won't copy ANYTHING over and it won't give you an error message either. For instance, my shapefile attribute table has a row where the float field value is 23.2. In arcpy, if I print that row, the value is interpreted as "23.2000000762939453" (how it derived such a number I do not know). Because my SDE feature class matching float field has a scale of 2, it will not accept such a long numeric value and will act like it's inserting rows but it isn't.

The only way around this is to set my SDE feature class float fields to a precision and scale of 0 which isn't ideal, but it does allow the process to carry out successfully.

View solution in original post

2 Replies
Highlighted
Regular Contributor

I have solved this issue, it appears there's a bug (shocker!). The float fields are the culprit, let me explain.

In the shapefile I had set precision and scale of the field to 3 and 2, respectively. Unfortunately, these are reverted to 0 upon clicking "Ok" when adding the field (if I right-click the field and go to properties, I now see precision and scale are both 0 which isn't how I set them).  Shapefiles are supposed to support precision and scale as per documentation here Working with fields in shapefiles by adding a field in ArcCatalog—Help | ArcGIS Desktop . This is the bug.

My target SDE table class for inserting rows also had the matching float fields with precision and scale set to 3 and 2. This did not revert to 0, so now the scale in the shapefile float fields is much larger than the scale in the SDE feature class float fields.

Because it reverted to 0, my float field in the shapefile is now interpreted by arcpy as having a much larger scale than it's supposed to, so when I try to use an insert cursor to take data from the shapefile and insert it into my SDE feature class fields, the float fields won't work, because the scale is too large, thus it won't copy ANYTHING over and it won't give you an error message either. For instance, my shapefile attribute table has a row where the float field value is 23.2. In arcpy, if I print that row, the value is interpreted as "23.2000000762939453" (how it derived such a number I do not know). Because my SDE feature class matching float field has a scale of 2, it will not accept such a long numeric value and will act like it's inserting rows but it isn't.

The only way around this is to set my SDE feature class float fields to a precision and scale of 0 which isn't ideal, but it does allow the process to carry out successfully.

View solution in original post

Highlighted
MVP Regular Contributor

BUG-000095512: ArcMap 10.4 and 10.4.1 resets the scale and precisio.. 

Just in case, someone is looking for that Bug...