topic Re: OBJECTID in an UpdateCursor in Python Questions

OBJECTID in an UpdateCursor

KathyJohnson1 — Sat, 11 Dec 2021 14:47:51 GMT

I am writing a python script that reads a csv file that contain the OBJECTID and a CompKey. I am using a csv dictionary to store the values in. Once they are read in, I want to use an updatecursor to update the compkey based on a matching OBJECTID, but I can't get the update cursor to find any matching values. My update cursor looks like this:

with arcpy.da.UpdateCursor(fc, fc_fields) as cursor:
     for row in cursor:
         OBJECTID = (row[0])
         print OBJECTID
 # this loop is to assign each field in the dictionary to the cooresponding field in the fc_fields list
         if csvdict.has_key(OBJECTID):
             print('found OBJECTID for {}'.format(OBJECTID))
             row[1] = csvdict[OBJECTID][0]
             cursor.updateRow(row)
             print cursor
         else:
             print ('no matching OBJECTID')

When I run the entire script all I get is the no matching objectid. The above script worked when comparing 
to a field other than OBJECTID. When I change OBJECTID to UNITID, it works, the problem is UNITID is not a
unique value, so I am getting duplicates. Any help would be greatly appreciated.

Re: OBJECTID in an UpdateCursor

JoshuaBixby — Mon, 19 Oct 2020 18:53:19 GMT

How are you defining fc_fields? Is it just ["OID@", "CompKey"]?

Re: OBJECTID in an UpdateCursor

KathyJohnson1 — Sat, 11 Dec 2021 14:47:54 GMT

Sorry I thought about that late last night as I hadn't given all the information. In my script this is what I have that runs with no errors, but it doesn't do anything.

fc_fields = ['OBJECTID','COMPKEY']

When I tried to use OID@, if I replaced it everywhere I had OBJECTID, I ended up with errors first with this bolded line below:
with arcpy.da.UpdateCursor(fc, fc_fields) as cursor:
     for row in cursor:
         OID@ = (row[0])
         print OBJECTID
 # this loop is to assign each field in the dictionary to the cooresponding field in the fc_fields list
         if csvdict.has_key(OID@):
             print('found OBJECTID for {}'.format(OID@))
             row[1] = csvdict[OID@][0]
             cursor.updateRow(row)
             print cursor
         else:
             print ('no matching OBJECTID')

Re: OBJECTID in an UpdateCursor

JoeBorgione — Tue, 20 Oct 2020 17:35:15 GMT

I wonder if using OID@ as a variable name is causing you the problem. Change that to something like myOID....

Re: OBJECTID in an UpdateCursor

KathyJohnson1 — Sat, 11 Dec 2021 14:47:56 GMT

This is my new code:

#I have a CSV that contains the information required to populate the COMPKEY based on myOID.
#Need to pick which layer you are adding COMPKEY's
import csv, arcpy

# set the database to connect to and the csv file to read
workspace = r"Database Connections\Connection to hwdsql02.sde"
CompKey= r"C:\GIS\Python\CompKey.csv"

# Create a Describe object for an SDE database; to make sure connecting to proper version
desc = arcpy.Describe(workspace)

# Print Connection properties
cp = desc.connectionProperties
print("\nDatabase Connection Properties:")
print("%-12s %s" % ("  Server:", cp.server))
print("%-12s %s" % ("  Instance:", cp.instance))
print("%-12s %s" % ("  Database:", cp.database))
print("%-12s %s" % ("  Version:", cp.version))

arcpy.env.workspace = workspace

#start an edit session
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): startEditing ({with_undo}, {multiuser_mode})
edit.startEditing(True, True)
print ("start an edit session")

# Start an edit operation
edit.startOperation()

# set up some variables; fc=feature class; csvdict=CSV Dictionary
fc = r"sdedpwutilities.DBO.WaterDistribution\ sdedpwutilities.DBO.wMain"

myOID = ['OID@']
fc_fields = ['OID@','COMPKEY']
csvdict = {}
header = True

#Read the CSV File and store in csvdict dictionary
with open(CompKey, 'r') as f:
    reader = csv.reader(f)
#skip header
    _ = next(f)
    for row in reader:
       key = [int(row[0])]
#       print (key)
       csvdict[row[0]] = [row[1]]
       print (csvdict)
print "That was the dictionary"

#cursor to update the fc
with arcpy.da.UpdateCursor(fc, fc_fields) as cursor:
     for row in cursor:
         myOID = (row[0])
         print myOID
 # this loop is to assign each field in the dictionary to the cooresponding field in the fc_fields list
         if csvdict.has_key(myOID):
             print('found myOID for {}'.format(myOID))
             row[1] = csvdict[myOID][0]
             cursor.updateRow(row)
             print cursor
         else:
             print ('no matching myOID')

# Stop the edit session and save the changes: stopEditing (save_changes) default value is true
edit.stopEditing(True)
print ("stop an edit session")

del cursor

This is a partial of the output in PyCharm:

{'321972': ['463250'], '321973': ['463676'], '321970': ['463032'], '321971': ['463248'], '321974': ['463680'], '454363': ['463885'], '454344': ['463692'], '454342': ['463687'], '454380': ['464931'], '454381': ['464932'], '454382': ['464950'], '454383': ['464965'], '454384': ['469329'], '454385': ['469330'], '454386': ['469341'], '321969': ['463031'], '454371': ['464118'], '454370': ['463928'], '454373': ['464837'], '454372': ['464564'], '454374': ['464838'], '454377': ['464887'], '454376': ['464839'], '454379': ['464921'], '454378': ['464920']}
That was the dictionary
1
no matching myOID
2
no matching myOID
3
no matching myOID

Still not working.

Re: OBJECTID in an UpdateCursor

JoeBorgione — Sat, 11 Dec 2021 14:47:59 GMT

I took the liberty of using the Syntax Highlighter to format you code. I think it's right. Look at lines 63 through 75, in particular line 69; I think it needs to be indented so the if statement is within the 'for' loop at line 64. ('If' should line up with line 66). Also, can you verify your csv is being read into the dictionary properly? For scripts like this with several moving parts, I like to run them in a console piece by piece to make sure everything is functioning the way I want them to. I use Spyder, but I think Pycharm can do the same thing...

#I have a CSV that contains the information required to populate the COMPKEY based on myOID.
#Need to pick which layer you are adding COMPKEY's


import csv, arcpy

# set the database to connect to and the csv file to read
workspace = r"Database Connections\Connection to hwdsql02.sde"

CompKey= r"C:\GIS\Python\CompKey.csv"

# Create a Describe object for an SDE database; to make sure connecting to proper version


desc = arcpy.Describe(workspace)

# Print Connection properties
cp = desc.connectionProperties
print("\nDatabase Connection Properties:")
print("%-12s %s" % ("  Server:", cp.server))
print("%-12s %s" % ("  Instance:", cp.instance))
print("%-12s %s" % ("  Database:", cp.database))
print("%-12s %s" % ("  Version:", cp.version))

arcpy.env.workspace = workspace

#start an edit session
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): startEditing ({with_undo}, {multiuser_mode})

edit.startEditing(True, True)
print ("start an edit session")

# Start an edit operation

edit.startOperation()

# set up some variables; fc=feature class; csvdict=CSV Dictionary
fc = r"sdedpwutilities.DBO.WaterDistribution\ sdedpwutilities.DBO.wMain"

myOID = ['OID@']
fc_fields = ['OID@','COMPKEY']
csvdict = {}
header = True

#Read the CSV File and store in csvdict dictionary

with open(CompKey, 'r') as f:
    reader = csv.reader(f)
#skip header
    _ = next(f)
    for row in reader:
       key = [int(row[0])]
#       print (key)
       csvdict[row[0]] = [row[1]]
       print (csvdict)
print "That was the dictionary"

#cursor to update the fc

with arcpy.da.UpdateCursor(fc, fc_fields) as cursor:
     for row in cursor:
         myOID = (row[0])
         print myOID
 # this loop is to assign each field in the dictionary to the cooresponding field in the fc_fields list
        
 if csvdict.has_key(myOID):
             print('found myOID for {}'.format(myOID))
             row[1] = csvdict[myOID][0]
             cursor.updateRow(row)
             print cursor
         else:
             print ('no matching myOID')

# Stop the edit session and save the changes: stopEditing (save_changes) default value is true


edit.stopEditing(True)
print ("stop an edit session")

del cursor‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: OBJECTID in an UpdateCursor

KathyJohnson1 — Sat, 11 Dec 2021 14:48:02 GMT

I copied your script and tried to run again. It did do new things. It is now executing this part of the code:

for row in cursor:
         myOID = (row[0])
         print myOID

As it printed all the OID, the it said no matching myOID statement and ended the edit. I am wondering if the OID is coming in from the CSV as text and not an integer. I know how to force it to be a float variable, but was not sure how to make it an integer, which is believe is what it needs to be.

Re: OBJECTID in an UpdateCursor

JoeBorgione — Sat, 11 Dec 2021 14:48:05 GMT

OID is coming in from the CSV as text and not an integer....

That's the sort of thing you can check by running each segment of code a priori in the console to make sure it is behaving the way you want it to, and not just how you told it to.

Here is an example of how I use a dictionary with an update cursor:

# use a dictionary to populate a field
import arcpy, time, smtplib, sys, inspect, os


arcpy.env.workspace = r'J:\MayorsMap\AGRC_DownLoads\Parcels_SaltLake.gdb'
ws = arcpy.env.workspace
source = '{}\ParcelInfo'.format(ws)
tableFields = ['parcel_id','OwnerType']
target = '{}\Parcels_CLipped'.format(ws)
updateFields = ['PARCEL_ID', 'OWN_TYPE']

#create a dictionary of the {parcel_id:ownertype} from source...
pidDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(source,tableFields)}


#start an edit session  on non-versioned egdb
edit = arcpy.da.Editor(ws)
edit.startEditing() 
edit.startOperation()

#step through the tables and update
#if the record has a valid site-id and a null site_guid
#update the site_guid Both of these come from sites_1 feature class


try:
    with arcpy.da.UpdateCursor(target,updateFields) as updateRows:
        for updateRow in updateRows:
           keyValue = updateRow[0]
           if keyValue in pidDict and updateRow[1] == None:
               updateRow[1] = pidDict[keyValue][0]
               updateRows.updateRow(updateRow)
           else:
                pass
        edit.stopOperation()
        edit.stopEditing(True)
except Exception as err:
    print(err)
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Don't worry about the try/except blocks but notice how I check to see if the keyValue is in the dictionary in line 30, nested witihin the for loop...

Re: OBJECTID in an UpdateCursor

KathyJohnson1 — Tue, 20 Oct 2020 20:05:59 GMT

Joe,

I am still not sure why it can't match my csv file to the objectid in the update cursor. I have tried you suggestion and it still not doing anything. I am leaving for the day and will start fresh tomorrow. Any other suggestions you have would be greatly appreciated. I did run the code in the python ide in ArcMap and I am getting no errors, it is just not finding my numbers in the objectid field. Tomorrow I may try a searchcursor and see if that finds it.

Thanks,

Kathy

Re: OBJECTID in an UpdateCursor

JoeBorgione — Tue, 20 Oct 2020 20:21:59 GMT

That's a good plan- it's not going anywhere and using a search cursor to trouble shoot you update cursor is good too.

Re: OBJECTID in an UpdateCursor

JoshuaBixby — Tue, 20 Oct 2020 20:27:53 GMT

I am wondering if the OID is coming in from the CSV as text and not an integer.

If OID from CSV is coming as text, than you would not get any matches, so it would be good to verify.

Re: OBJECTID in an UpdateCursor

RandyBurton — Sat, 11 Dec 2021 14:48:07 GMT

Since the OID from the CSV is text, try:

if csvdict.has_key(str(myOID)): # convert myOID to string
    print('found myOID for {}'.format(myOID))
    row[1] = csvdict[str(myOID)][0] # convert myOID to string
    cursor.updateRow(row)
#...
‍‍‍‍‍‍‍‍‍‍‍

This section starts at line 69 in Joe's code, and use the correct indentation for this line. Also, the values in csvdict are also text, so you need to convert them to the correct type.

If your OIDs start at 321972, you may wish to use a where clause so you are not checking unnecessary rows.

Re: OBJECTID in an UpdateCursor

KathyJohnson1 — Sat, 11 Dec 2021 14:48:10 GMT

I finally got it working, as it turned out it was the csv file being read in as text and not an integer. I also had to read my compkey in as an integer also. The code that changed was :

#Read the CSV File and store in csvdict dictionary
with open(CompKey, 'r') as f:
    reader = csv.reader(f)
#skip header
    _ = next(f)
    for row in reader:
       key = [int(row[0])]
#THIS IS THE LINE THAT CHANGED. ########################
       csvdict[int(float(row[0]))] = [int(float(row[1]))]
       print (csvdict)
print "That was the dictionary"

Thanks to all that helped.

Re: OBJECTID in an UpdateCursor

JoeBorgione — Wed, 21 Oct 2020 14:04:54 GMT

What a difference a day makes....

Re: OBJECTID in an UpdateCursor

KathyJohnson1 — Wed, 21 Oct 2020 14:06:48 GMT

Yes, you are so right! Thanks again for you help.