Problems with Cursor and Editor in ArcMap vs ArcGIS Pro

990
10
12-13-2018 03:04 AM
StevenSchreuder
New Contributor II

Dear all,

While working on a script to migrate some relationship classes, we've come up with some strange behaviour in both ArcMAP 10.6.1 (python27) and ArcGIS Pro. The script seems to work okay for a few relationships, but after migrating a couple the script sometimes crashes or just stops at the end of an edit session.

Even more strange (for us) is that the same code does find matches in ArcMap, but doesn't always find the same matches in an ArcGIS Pro environment, which led us towards thinking that the cursors aren't completed right, so we put in a del cursor. This worked, well actually now it finds more matches in ArcGIS Pro, but we get the same behaviour with sometimes just stopping at the end of an edit session.

By the way: we are working with versioned SDE (Oracle).

Perhaps some of you got any ideas, based on a code snippet?

# Import arcpy module
import arcpy
##import arceditor
from datetime import datetime as dt
import os
import time

start = dt.now()
print("Migratie globalID is gestart op: {}".format(dt.now()))


database = r"C:\Users\pschuurman\Documents\ArcGIS\Projects\DAMO\Damo_W@dmoTST6 (2).sde"


arcpy.env.workspace = database
arcpy.env.overwriteOutput = True
print("Gebruikte database connectie: {}".format(database))

fcs = []
del_lst = []
rcsnm_nw = [] #relationship many to many
rcsnm = set()
rcs1n = set()
rcFail = []
glbCount = 0

#print("Maak een lijst met alle FeatureClasses en Tables in de database.")
# Vul de lijsten met featureClassens en tables.
for item in arcpy.ListFeatureClasses("*"):    fcs.append(item)
for item in arcpy.ListTables("*"):    fcs.append(item)
#print(fcs)

# Een dictionary om de input van CreateRelationshipClass de juiste waarde mee te geven.
# Welke benaming gebruikt de database; OneToOne of esriRelCardinalityOneToOne?
mapping_cardinality = {'OneToOne': 'ONE_TO_ONE',
                       'OneToMany': 'ONE_TO_MANY',
                       'ManyToMany': 'MANY_TO_MANY'}
rename_nopm_rc = {'BergingsgebiedNormGP': 'Bergingsgebied_NormGP',
                   'HydroObjectNormGP': 'HydroObject_NormGP',
                   'MeetnetMeetlocatie': 'Meetnet_Meetlocatie',
                   'RefstelselRefpunt': 'Refstelsel_Refpunt',
                   'WaterkeringNormGP': 'Waterkering_NormGP',
                   'WaterkeringReferentiestelsel': 'Waterkering_Refstelsel',
                   'WaterkeringWKStelselAgg': 'Waterkering_WKStelselAgg',}

rename_1opn_rc= {'DuikerSifonHelvel_Bedieningsplicht':'DuikerSifonHevel_Bedieningsplicht'}

rename_dfk_rc = {'NORMGEPARAMPROFIEL_OBJECTID': 'normGeparamProfielID',
                    'DAMOTABEL_OBJECTID': 'DAMOTabelID',
                    'NORMGEPARAMPROFIEL_OBJECTID': 'normGeparamProfielID',
                    'MEETNET_OBJECTID': 'MeetnetID',
                    'REFERENTIEPUNT_OBJECTID' : 'referentiepuntID',
                    'NORMGEPARAMPROFIEL_OBJECTID' : 'normGeparamProfielID',
                    'REFERENTIESTELSEL_OBJECTID' : 'referentiestelselID',
                    'WATERKERINGSTELSELAGG_OBJECTID': 'waterkeringStelselAggregatieID' }

# Haal de namen van de relationshipClasses uit de lijst met featureClassens en tables.
for fc in fcs:
    desc = arcpy.Describe(fc)
    rcs = desc.relationshipClassNames

    # Maak een onderscheid tussen N op M en 1 op N/1 relaties
    for rc in rcs:
        relcDesc = arcpy.Describe(rc)
        cardinality = relcDesc.cardinality

        # Maak lijst met de N op M relationshipClasses
        if cardinality == "ManyToMany":
            rcsnm.add(rc)

        # Maak lijst met de 1 op N/1 relationshipClasses
        else:
##            print "Maak een lijst met relationshipclasses aan die cardinaliteit 1 op 1/N hebben."
            destinationclasskeys = relcDesc.destinationclasskeys
            rcs1n.add(rc)

# Verwerkt de afzonderlijke N op M relationshipclasses in een aantal stappen die
# bij de stap zelf verder beschreven staat.

#print("Many to Many relationship list")
#print(rcsnm)


#print(rcs1n)
print("Start verwerking 1-n relationship classes")
print(dt.now())
for rc1n in rcs1n:
        start_rc1n = dt.now()
        glbCount += 1
        print("Verwerken relationship class {}".format(glbCount))
        #Lees de kenmerken van een RelationshipClass uit.
        relcDesc = arcpy.Describe(rc1n)
        cardinality = relcDesc.cardinality
        print("Verwerk de {}  relationshipclass: {}".format(cardinality,rc1n))
        print("Uitlezen relationshipclass.")
        cardinality_in = mapping_cardinality[cardinality]
        print(cardinality_in)
        otable = relcDesc.originclassnames[0]
        dtable = relcDesc.destinationclassnames[0]
        fplabel = relcDesc.forwardpathlabel
        bplabel = relcDesc.backwardpathlabel
        originclasskeys = relcDesc.originclasskeys
        ofk = list(zip(*originclasskeys))[0][1]
        opk = list(zip(*originclasskeys))[0][0]
        if opk == "GlobalID":
            print("Primary Key is GlobalID. Relationship Class wordt overgeslagen")
            continue
        if rc1n.startswith("DAMO_W.WS_"):
            print("Dit is een Waterschapseigen relatie. Relationship Class wordt overgeslagen")
            continue
        
        opk_nw = "GlobalID"
        # Maak samengestelde variabelen aan voor de tools die de RelationshipClass omzetten.

        ofk_tmp = "WS_Dummy"

##        # Reconnect routine uit de create_damo.py in verband met CalculateField met Archiving aan.
##        arcpy.env.workspace = ""
##        arcpy.ClearWorkspaceCache_management()
##        arcpy.Compress_management(sde_connection)
##        arcpy.env.workspace = database
##        print "Reconnect is uitgevoerd."
##        print("Verwerken relationshipclass")


        # Process: Delete, alleen wanneer er nog geen lock staat op de relatie,
        # Wanneer er wel een lock is toevoegen aan lijst met nog niet behandelde relationships.
        if arcpy.TestSchemaLock(rc1n):
            arcpy.Delete_management(rc1n, "RelationshipClass")
        else:
            print("Unable to acquire the necessary schema lock to delete Relationship Class")
            rcFail.append(rc1n)
            continue

        # Als de naam voorkomt in dictionary bovenaan, verander de naam 
        if rc1n.split(".")[-1] in rename_1opn_rc:
            rc1n = rename_1opn_rc[rc1n.split(".")[-1]]

        #Als er geen records in het foreign key veld gevuld zijn, dan alleen veld verwijderen en nieuw veld aanmaken
        whereClause = "{0} IS NOT NULL".format(ofk)
        cursor = arcpy.da.SearchCursor(dtable, ['OBJECTID', ofk], whereClause)
        print("SearchCursor tabel {}  veld: {}".format(dtable,ofk))
        if len([row for row in cursor]) == 0:
            del cursor
            print("Tabel is leeg")
            arcpy.DeleteField_management(dtable, ofk)
            arcpy.AddField_management(dtable, ofk, "GUID", "", "", "", ofk, "NULLABLE", "NON_REQUIRED", "")
            #arcpy.CreateRelationshipClass_management(otable, dtable, rc1n, "SIMPLE", fplabel, bplabel, "NONE", cardinality_in, "NONE", opk_nw, ofk)
        else:
            del cursor
            # Process: Alter Field
            print("Veldnaam: {} in tabel: {} krijgt nieuwe veldnaam: {}".format(ofk, dtable,ofk_tmp))
            fieldnames = [field.name for field in arcpy.ListFields(dtable)]
            if ofk_tmp in fieldnames:
                print("Veld {} bestaat al en wordt verwijderd".format(ofk_tmp))
                arcpy.DeleteField_management(dtable, ofk_tmp)
 
            arcpy.AlterField_management(dtable, ofk, ofk_tmp, ofk_tmp)

            time.sleep(3)
            # Process: Add Field
            arcpy.AddField_management(dtable, ofk, "GUID", "", "", "", ofk, "NULLABLE", "NON_REQUIRED", "")


            # Aanmaken Dictionary met daarin ObjectID en GlobalID uit de brontabel om daarmee in de
            # doeltabel de BronTabelID een te kunnen vullen met de GlobalID voor de nieuwe relationshipClass
            relclassDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(otable, [opk, opk_nw])}
            #print relclassDict

            # Als de naam begint met DAMO wordt er geen editsession gestart
            #print("resultaat split is : {}".format(dtable.split(".")[-1][:4]))
            if dtable.split(".")[-1][:4] == "DAMO" :
                versioning = False
            else:
                versioning = True

            if versioning:
                edit = arcpy.da.Editor(database)
                print("Start edit session")
                # 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()
            else:
                print("Start verwerking DAMO Tabel")

            # Vullen nieuw veld BrontabelID met de juiste GlobalID uit de doeltabel.
            whereClause = "{0} IS NOT NULL".format(ofk_tmp)
            with arcpy.da.UpdateCursor(dtable, [ofk_tmp, ofk], whereClause) as cursor:
                count = 0
                for row in cursor:
                    joinfld = row[0]
                    #print (joinfld)
                    if joinfld in relclassDict:
                        row[1] = relclassDict[joinfld][0]
                        cursor.updateRow(row)
                        count += 1
                print("Aantal matches: {}".format(count))

            if versioning:
                # Stop the edit operation.
                edit.stopOperation()
                # Stop the edit session and save the changes
                edit.stopEditing(True)
                print("Stop editsession")
            else:
                print("Stop verwerking DAMO Tabel")


        # Process: Create Relationship Class (maak een nieuwe relationshipclass)
        arcpy.CreateRelationshipClass_management(otable, dtable, rc1n, "SIMPLE", fplabel, bplabel, "NONE", cardinality_in, "NONE", opk_nw, ofk)

        # Process: Delete field.
        arcpy.DeleteField_management(dtable, ofk_tmp)

        einde_rc1n = dt.now()
        print ('Doorlooptijd verwerken relatie: {}.'.format(einde_rc1n - start_rc1n))


print("Verwijderen tussentijdse tabellen nadat het script goed is doorlopen.")
for item in del_lst:
    # Proces: Delete featureclass
    arcpy.Delete_management(item)

print("Nog niet aangepaste Relationshipclasses")
print(rcFail)

print("De N op M relaties: ")
print(rcsnm)
print("zijn aangepast naar de volgende naamgeving.")
print(rcsnm)

finish = dt.now()
print(finish)
dlt = finish-start
print ("De doorlooptijd was: {}".format(dlt))
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
0 Kudos
10 Replies
DanPatterson_Retired
MVP Emeritus

in lines 141 and 190 you use arcpy.da. cursors, the 2nd one uses the preferred 'with' format since it handles the closure of the cursors better than the old cursors.  perhaps emulate that structure with the cursor on lines 141 on (don't forget the indent).

StevenSchreuder
New Contributor II

Thanks for the quick reply. I was thinking about that as well, but was struggling a bit to put this simple check in a with statement

0 Kudos
DanPatterson_Retired
MVP Emeritus

ahhh apparently you have two cursors and 'with'ing the first cursor would make the second cursor nested (further).

I can only recommend that you use the first cursor to collect what you want and the second cursor to finish the job.  nested cursors rarely go smoothly.  I suspect that is why you have to 'del cursor' in the first part.

This is where a particular bit of functionality could be parcelled into its own 'def' and called when the inputs are collected.  The processing is done within the def and the results returned

StevenSchreuder
New Contributor II

Actually, it´s just a check if there are any values present in the foreign key field.

I was thinking something like this for line 139 - 143:

        #Als er geen records in het foreign key veld gevuld zijn, dan alleen veld verwijderen en nieuw veld aanmaken
        whereClause = "{0} IS NOT NULL".format(ofk)
        with arcpy.da.SearchCursor(dtable, ['OBJECTID',ofk], whereClause) as cursor:
            for row in cursor:
                rc1ndata = True
                break
            else:
                rcn1data = False
        print("SearchCursor tabel {}  veld: {}".format(dtable,ofk))
        if not rc1ndata:‍‍‍‍‍‍‍‍‍‍‍

Does this make sense?

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

Looking only at the suggested code snippet here, and not the overall code and issue, a couple of comments.  When working with ArcPy DA cursors, I use the designated tokens for geodatabase-managed fields instead of their actual name.  I find it makes the code more portable.  For example, instead of['OBJECTID',ofk], I suggest ['OID@',ofk]

It appears the code snippet here is simply checking whether the cursor has any records.  If so, the following code gets the same result while being more Pythonic in structure:

        whereClause = "{0} IS NOT NULL".format(ofk)
        with arcpy.da.SearchCursor(dtable, ['OID@',ofk], whereClause) as cursor:
            rc1ndata = any(row for row in cursor)            

        print("SearchCursor tabel {}  veld: {}".format(dtable,ofk))
        if not rc1ndata:

 

StevenSchreuder
New Contributor II

Thank you for the reply, Joshua. 

You are correct, this part only checks if the foreign key field has records with data in it. If there is no data in the field, we don't need to migrate data and can just alter the field type.

Thanks for the hints on 'OID@' and the suggestion for the with structure for this cursor. I will start testing this.

0 Kudos
StevenSchreuder
New Contributor II

Hi Joshua,

Would you perhaps have a suggestion how to put the following statement in a 'with arcpy.da.SearchCursor' structure:

 relclassDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(otable, [opk, opk_nw])}

Here I am trying to make a dictionary out of the info in two fields, for later use in another cursor

0 Kudos
JoshuaBixby
MVP Esteemed Contributor
with arcpy.da.SearchCursor(otable, [opk, opk_nw]) as cur:
    relclassDict = {r[0]:(r[1:]) for r in cur}
del cur
RandyBurton
MVP Alum

Regarding the line of code you mention:

relclassDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(otable, [opk, opk_nw])}

# produces dictionary entries like:
# {'opk': ('opk_nw',) ....}   value is a tuple or list

#  since there are only two fields, you might try some changes around (r[1:]) like

relclassDict = {r[0]:r[1] for r in arcpy.da.SearchCursor(otable, [opk, opk_nw])}

# produces dictionary entries that might be easier to work with:
# {'opk': 'opk_nw', ....}‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍