Cannot edit versioned SDE data with Python update or insert cursor functions

AnthonyGangemi · ‎10-11-2010

ESRI article ID 28084 (http://resources.arcgis.com/content/kbase?fa=articleShow&d=28084) discusses a limitation with the Python UpdateCursor or InsertCursor function when using versioned SDE data.

Now we have an ArcSDE versioned featureclass that portrays property parcels and among the many fields is one called PARCEL_NUMBER. I�??ve developed a Python script within a 9.3.1 SP2 environment that first makes SQL queries against another internal Informix Property database system for all the respective Property Parcels and returns the Parcel Numbers into a Python list variable. The script then takes this list variable, loops through and populates the PARCEL_NUMBER field for the respective property parcel where there is a missing Parcel Number value using the UpdateCursor function. It works well when the featureclass is not versioned. When the featureclass is versioned, the script runs error free but does not populate the respective fields.

A local ESRI support person advised that this would be rectified in ArcGIS 10 but when I tested it with a simple ArcPy script within an ArcGIS 10 Personal ArcSDE Geodatabase (based on a SQL Server Express 2008), it still exhibits the same problem. Given the nature of versioning and its intrinsic use of delta tables, is it really possible to use an UpdateCursor function directly on a versioned featureclass?

Article ID 28084 discusses a possible workaround using disconnected editing via a check-out, run script on featureclass then check-in featureclass approach. I believe this whole workflow could be entirely scriptable but has anyone successfully tried this?

I have an alternative approach but it would involve two separate steps:

Create a stand-alone table with referenced PARCEL_NUMBER values using a Python script.

Start ArcMap and do a spatial join with the table on the property parcel featureclass and use a field calculator script to populate the missing PARCEL_NUMBER fields during an edit session.

To this end, I�??m seeking alternative methods to get around this underlying limitation (or Bug??). I�??d appreciate any good ideas in how I could craft a script that could get all the steps in one all inclusive automotive approach without separating the processes.

Regards

Tony
Bundaberg Regional Council

PhilipSmith1 · ‎03-23-2012

I am having this issue too. I am trying to remove lines from a versioned table and then add new ones. I do not want to delete the table because it is part of a replica.

Russell, I tried your script but I am not sure it would test a versioned table, there is no where that the new table is registered as versioned. I tried a different script running on a table I created and then registered as versioned. In fact I tried this on a non-versioned table and it worked. I then deleted the table and created a new one and registered it as versioned, it did not work. Here is the script I used:

import arcpy
table = 'Database Connections/SDE Dev.sde/CHWKSDE_Dev.DBO.TEMP_FOR_SCRIPT_TEST'
cur = arcpy.InsertCursor(table)
row = cur.newRow()
row.Field1 = 'Test1'
cur.insertRow(row)
del cur
del row

I used a connection that is set to the default version. When I say it did not work, what I mean is that the new row does not show up in the table in ArcMap.

Doing some digging, I do see the row showing up in the table's add table but I can't figure out why it does not appear in ArcMap.

After I found this post, we upgraded ArcMap, server and SDE to SP4.

We have considered changing the table to non-versioned but I have to develop some similar scripts to run against versioned feature classes so I will run into this again.

This is driving us a little crazy. I would appreciate any help.

Thanks
Phil Smith
City of Chilliwack

RussellBrennan · ‎03-30-2012

Phil,

It appears that the issue with using insert cursors against versioned data was only partially fixed. Using insert cursors should work against the Default version, however they still appear to be broken against other edit versions in 10.0 SP4.

A new bug has been submitted for this:
NIM079496 - When editing non-Default versions using insert cursorts the inserts are not being applied in 10.0 SP4.

This works in 10.1 Pre-release and we plan to ensure this gets fixed in an upcoming service pack.

If you have access to a 10.1 Desktop machine you could use this to perform your updates as the fix is on the client side not the geodatabase/server side.

Russell

JakeBrown1 · ‎05-03-2012

Will there be a 10.0 SP5 or will we need to wait for 10.1 ? thanks- Jake Brown

dmacq · ‎07-09-2012

I've noticed that the UpdateCursor will insert rows into the delta tables, but when I compress, the edits disappear. I'm working on a test database, so I've been fooling with the states and state IDs...to no avail. Has anybody come up with a solution to this? I've altered the script to run on a FGDB, but it's not updating the values.

Anonymous User · ‎07-12-2012

@jjbrownBPA
It seems that bug NIM-079496 is not included in Service Pack 5 which came out two days ago
Issues addressed here
http://gisupdates.esri.com/10sp5/ArcGIS/ArcGIS10sp5-issues.htm
Download SP5 here
http://resources.arcgis.com/content/patches-and-service-packs?fa=viewPatch&PID=17&MetaID=1876

I have a colleague who's having a similar issue on ArcGIS 10 SP4. When he runs the script, the Add and Delete tables gets filled but changes don't show up in ArcMap. Compressing the GDB (like what @drewgis does) makes changes disappear from the Add and Delete tables.
I'm setting up a separate test on my machine to figure out the issue.

Any ideas would be welcomed. Thanks.

cedricvenzo · ‎12-05-2012

hi all,
seems that SP5 didn't solve anything about this issue...
I'm still facing the exact same issue on an upgraded-to-SP5 ArcSDE o/Oracle

Cedric

markdenil · ‎12-05-2012

I have found the same, or similar problem in 10.1.
My update cursor in ArcMap makes the changes in the displayed feature class table,
but as soon as one saves, the changes vaporize.

I have found a work-around. I make a dummy CalculateField calculation on one field, where I calculate the field to itself.
That geoprocessing tool calc on one field seems to 'commit' all the changes the cursor made to all the fields.

Like all work-arounds, I hope it continues to help....

KenHartling · ‎01-23-2013

I've been investigating the reported issues here and through reports to our Support team and so far have found only one reason for this to be failing as described in 10.1 (and 10SP4-5). Something has a lock on the data prior to attempting the update or insert.

In some cases this is because something before the call to update or insert has put a lock on the feature class. For example, an UpdateCursor is created on a feature class and the UpdateCursor object is not being deleted prior to trying to insert rows with an InsertCursor on the same versioned feature class.

Please check to make sure that there are no locks on the data that could be preventing the update or insert from occurring. If you are not familiar with Geodatabase locks, please see here.

When moving to 10.1, please consider using the new Data Access arcpy module. You will realize better performance and control when cursors are needed.

In case you haven't seen it yet, the following is from the 10.1 'What's New - Geoprocessing' section of the help:
The data access module, arcpy.da, is a Python module for working with data. It allows control of the edit session, edit operation, improved cursor support (including faster performance), functions for converting tables and feature classes to and from NumPy arrays, and support for versioning, replicas, domains, and subtypes workflows.

Learn more about the data access module

If, after you make sure you aren't inadvertently locking your data, you still have cases where you can't figure out why your updates and inserts are not showing up, please submit a support incident with data and complete instructions on how to reproduce the problem so we can investigate the behavior you are seeing.

Thanks,
Ken Hartling
ESRI
Senior GP Product Engineer

BrandonFlessner · ‎12-15-2016

Still having issues 3 years later at 10.4.1. I'm trying to use an update cursor on an sde versioned database. Here's a test script:

streetCLInput = r'Database Connections\brandon_edits.sde\oki.sde.StreetCL'
workspace = r'Database Connections\brandon_edits.sde'
import arcpy
arcpy.MakeFeatureLayer_management(streetCLInput, 'streets')
arcpy.ChangeVersion_management("streets", "TRANSACTIONAL", "brandon_edits.brandon")
edit = arcpy.da.Editor(workspace)
edit.startEditing(False, True)
edit.startOperation()
with arcpy.da.UpdateCursor('streets', ['objectid','suffix'], 'objectid = 11040') as cursor:
    for row in cursor:
        row[1] = ''
        cursor.updateRow(row)
edit.stopOperation()
edit.stopEditing(True)
print('Script Complete')‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The script crashes at line 10, but its because of line 5. Comment out line 5 and the script works, except the edits are made to the default version.

Here's another script basically copied and pasted from here : http://pro.arcgis.com/en/pro-app/arcpy/data-access/editor.htm with edits to change the version.

import arcpy
fc = r'Database Connections\brandon_edits.sde\oki.sde.StreetCL'
workspace = r'Database Connections\brandon_edits.sde'
arcpy.MakeFeatureLayer_management(fc, 'streets')
#arcpy.ChangeVersion_management("streets", "TRANSACTIONAL", "brandon_edits.brandon")
# Start an edit session. Must provide the workspace.
edit = arcpy.da.Editor(workspace)
# Edit session is started without an undo/redo stack for versioned data
#  (for second argument, use False for unversioned data)
edit.startEditing(True, True)
# Start an edit operation
edit.startOperation()
# Insert a row into the table.
with arcpy.da.InsertCursor("streets", ('name')) as icur:
    icur.insertRow(['New Test'])
# Stop the edit operation.
edit.stopOperation()
# Stop the edit session and save the changes
edit.stopEditing(True)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The script "works" with line 5 commented out except again the edits are made to the default version. With line 5 uncommented, the script fails.

So how do you edit an sde versioned dataset with arcpy and not edit the default version? Any help would be much appreciated. (I'm starting to think the versioning is more hassle than its worth.)

BrandonFlessner · ‎12-19-2016

I found the problem, in Catalog, the connection to the versioned data was using the default version as the transactional version to automatically connect to. To change this, you need to change the geodatabase connection properties to point to the version you wish to edit by default. It's a little confusing and odd that the default behavior is to edit the parent version of the version you are connected to but whatever. Its working now.

I still think the above script should execute without having to edit any geodatabase connection properties.

Also see this: BUG-000101529 : The 'Make Feature Layer' geoprocessing tool fails when 64-bit Background Geoprocessing is utilized with ArcGIS 10.4.1. when using a sde database connection. Potentially fixed at 10.5.