Workspace already in transaction mode - two cursors, different workspaces

[MKF62]

**Edit** - I have since closed my python script and reopened it. Now when I run it, I get the error "Cannot open workspace" when edit = arcpy.da.Editor(arcpy.env.workspace) is called. I imagine this is because the SDE database is not versioned, so I have set edit.startEditing('False', 'True') and also tried edit.startEditing('False', 'False') however that does not solve the problem either.

I have a script that takes data from a shapefile (using a search cursor) and copies it to a SDE feature class (using an insert cursor after some data manipulation). Obviously, the shapefile is in a different workspace than the SDE feature class.

I've read that this error occurs because you need to do your nested cursor actions in an edit session, but that does not seem to solve my problem.

I don't think this has anything to do with registering layers with the SDE database as the feature class was created inside the SDE database using ArcMap, therefore it shouldn't need to be registered (right? My knowledge on registering things is limited). It's not versioned and will not be in the future as there is no need.

Here's the main portion of the script:

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

#Getting feature class from database
PatchesFC = "Patches"
#Get input shapefile from different workspace
InputFC = r'C:\Users\user\Desktop\GIS_Testing\HbtatTesting\ExpTestFC.shp'

#create fields variables and a few other things here

#Start editing session on the SDE database
edit = arcpy.da.Editor(arcpy.env.workspace)
edit.startEditing()
edit.startOperation()

with arcpy.da.SearchCursor(InputFC, scFields) as sCursor: #Search cursor acts on shapefile   
     for row in sCursor:
        rowList = list(row)
        PatchID = '{' + str(uuid.uuid4()).upper() + '}'
        ObserverID = None
        rowList.insert(2, PatchID)
        rowList.append(ObserverID)
        rowTuple = tuple(rowList)
        with arcpy.da.InsertCursor(PatchesFC, icFields) as iCursor: #Insert cursor acts on SDE database
            iCursor.insertRow(rowTuple)

#Stop editing session
edit.stopOperation()
edit.stopEditing('True')

[MitchHolley1 · Accepted solution]

I would have two separate cursors.  Nested cursors are funky.  Also, you shouldn't have to loop over an Insert Cursor... just create an object that's pointing to the cursor.

NeedInsert = []
with arcpy.da.SearchCursor(InputFC, scFields) as sCursor: #Search cursor acts on shapefile   
     for row in sCursor:
        rowList = list(row)
        PatchID = '{' + str(uuid.uuid4()).upper() + '}'
        ObserverID = None
        rowList.insert(2, PatchID)
        rowList.append(ObserverID)
        NeedInsert.append[rowList] #append list to master list
del sCursor

iCursor = arcpy.da.InsertCursor(PatchesFC, icFields) #you don't need to loop over insert cursors
for x in NeedInsert:
    iCursor.insertRow(x)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[MKF62]

Thanks, I think that'll work well. I was getting hung up on the looping - I definitely see your point on why you don't have to loop through an insert cursor (duh!). 

I've gotten rid of all the start/stop editing calls, and now it runs "successfully" as in the exit code is 0 and there's no error messages...but when I open my Patches feature class, no rows were inserted at all. Any idea why that could be? I'm inserting the appropriate data types and matching fields for each row as far as I can tell. 

[MitchHolley1]

ObjectIDs will be automatically created when inserting rows into a table.  I'm not sure exactly why the rows aren't inserting properly... I'd have to look at the schema and contents of the list.  Try deleting the 'iCursor' object at the end of the script, and 'Reload Cache' on the SDE table.  I'm also not sure about inserting rows into an SDE environment... are you trying this on a version or DEFAULT?

[MKF62]

I made sure to add the delete statement for the insert cursor, that's been there since I changed the script so I'm all good there. I reloaded the table cache in ArcMap and it didn't do anything, so it must not be adding the rows, or maybe it's doing it temporarily but not saving or something? This is being done on DEFAULT, this data isn't versioned and won't ever be. I might try using the insert cursor in an edit session, if I can get the edit session to work with my non-versioned data, that way I can hardcode the "saving" by committing the edits and stopping the edit session. Perhaps that will shed some light on the issue.

Edit: inserting rows inside an edit session (edit.startEditing(False, False) and edit.stopEditing(True)) didn't work. Hmm...

[MitchHolley1]

Make sure there is actually data in the list you're running the insertCursor on.  Also, make sure that the number of items in each element of the list match the fields listed in the insertCursor.  Maybe try running on a File Geodatabase and see if you get any other results. 

[MKF62]

The number of items and number of fields match, and the list is populated with data. I have tried the script out on a file geodatabase and it works just fine...so I guess the problem is inserting them into the SDE database. Does ESRI require the data be versioned for working with SDE databases and cursors? I see no mention of that being a requirement in any of the help documentation. Guess I will continue my investigation there.

[JamesCrandall]

Make sure 'SHAPE@' is being included in your scFields list.

[MKF62]

Yup, it's there.

I have traced the problem to the "float" fields in my SDE table. When I have a specific precision and scale set, it will not insert any values for any of the rows, even though the data types match (both float) on either attribute table. The float values I am trying to copy over fit within the precision and scale I have set on the SDE table (3 and 2 respectively). If I just leave precision and scale as 0, it works fine and copies the data over.

This problem can actually be traced back to the shapefile not enforcing precision and scale. I can make a float field in my shapefile, set precision and scale, hit ok and then go right click that field to view the properties and the precision and scale have been reverted to 0. Shapefiles are supposed to support precision and scale for float fields as seen here Working with fields in shapefiles by adding a field in ArcCatalog—Help | ArcGIS Desktop  so this seems like a bug.