topic Re: Cannot edit versioned SDE data with Python update or insert cursor functions in Geoprocessing Questions

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

AnthonyGangemi — Tue, 12 Oct 2010 04:06:17 GMT

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

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

MikeLong — Tue, 30 Nov 2010 21:26:50 GMT

You might try passing into the Field Calculator tool. It won't be as fast as the UpdateCursor but it gets the job done. We frequently use this approach.

gp.CalculateField_management("featureclass", "field", <Parcel Variable Here>, "VB")

-Mike

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

ArcGISUser — Thu, 21 Apr 2011 17:05:04 GMT

Is this still true for 10.0? We are in the same situation and are looking to use cursors on SDE versioned data. Any feedback would be highly appreciated.

Thank you.

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

RuthEmerick — Mon, 09 Jan 2012 14:18:21 GMT

I've run into the same issue with cursors on a table. My script works fine on a file geodatabase, but does not update or insert records in a versioned SDE. Does anyone know if there is a workaround for this?

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

RussellBrennan — Tue, 10 Jan 2012 15:24:11 GMT

This was a bug that we fixed at 10.0 SP3. If you are attempting to use insert or update cursors against versioned data and they are not working, please try installing SP3.

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

SeanRedar — Thu, 12 Jan 2012 19:07:01 GMT

I am attempting to use the insert cursor on an SDE connection and v10 sp3 doesn't seem to help, no row is added. Perhaps I am doing something else incorrectly?

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

RussellBrennan — Sat, 11 Dec 2021 14:56:57 GMT

I am attempting to use the insert cursor on an SDE connection and v10 sp3 doesn't seem to help, no row is added. Perhaps I am doing something else incorrectly?

Can you send a simple example of what you are attempting to do?

Here is a quick script that I wrote that is working on my 10.1 beta build and should also work on 10.0 SP3:

import arcpygdb = 'Database Connections/gdb.sde' #change this to your GDB
table = arcpy.CreateTable_management(gdb, 'testCursor')
arcpy.AddField_management(table, 'testField', "TEXT")
cur = arcpy.InsertCursor(table)
row = cur.newRow()
row.testField = 'insertedTextFromCursor'
cur.insertRow(row)
del cur
del row

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

SeanRedar — Fri, 13 Jan 2012 15:48:56 GMT

I am creating a tool that will allow a user to upload a csv file into our Oracle/SDE database. Not sure what changed from yesterday to today but now this script is working, it successfully inserted the record. Here is the code,

import arcpy, csv
from arcpy import env

#### parameters
inCSV = "C:\\Temp\\test.csv"

#### connect to sde database
env.workspace = "\\\\egis\\GeoTools\\System\\cw_em_load.sde"
copfc = "EM.COPIncident_P"

#### parse and convert csv to list, create point the prepare attributes
opencsv = open(inCSV, 'rt')
csvlist = csv.reader(opencsv)
for row in csvlist:
    pnt = arcpy.Point()
    pnt.Y = float(row[7])
    pnt.X = float(row[8])
    inctype = row[0]

#### create insert cursor and insert feature
cur = arcpy.InsertCursor(copfc,"\\\\egis\\GeoTools\\System\\WGS 1984.prj")
sdrow = cur.newRow()
sdrow.SHAPE = pnt
sdrow.INCIDENTTYPE = inctype
cur.insertRow(sdrow)

#### cleanup
opencsv.close()
del sdrow, cur, pnt

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

RuthEmerick — Wed, 18 Jan 2012 15:03:33 GMT

This was a bug that we fixed at 10.0 SP3. If you are attempting to use insert or update cursors against versioned data and they are not working, please try installing SP3.

I've actually been using SP3 for a while. I've decided to just to my edits in a file geodatabase and then just replace all the rows in the SDE table on a weekly basis with Delete and Append.

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

RussellBrennan — Thu, 19 Jan 2012 15:24:10 GMT

I've actually been using SP3 for a while. I've decided to just to my edits in a file geodatabase and then just replace all the rows in the SDE table on a weekly basis with Delete and Append.

Were you able to run the sample code that I posted above?

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

PhilipSmith1 — Fri, 23 Mar 2012 22:04:22 GMT

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

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

RussellBrennan — Fri, 30 Mar 2012 21:21:12 GMT

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

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

JakeBrown1 — Thu, 03 May 2012 23:13:13 GMT

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

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

dmacq — Mon, 09 Jul 2012 18:20:44 GMT

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.

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

Anonymous User — Thu, 12 Jul 2012 16:38:49 GMT

@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.

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

cedricvenzo — Wed, 05 Dec 2012 10:51:54 GMT

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

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

markdenil — Wed, 05 Dec 2012 12:21:03 GMT

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....

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

KenHartling — Wed, 23 Jan 2013 21:52:01 GMT

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

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

BrandonFlessner — Sat, 11 Dec 2021 14:57:00 GMT

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.)

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

BrandonFlessner — Mon, 19 Dec 2016 14:10:29 GMT

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.