Skip to content

Instantly share code, notes, and snippets.

@TheBryanMac
Created January 4, 2018 01:54
Show Gist options
  • Save TheBryanMac/98b15a98a309ac66dc91734a9cba235a to your computer and use it in GitHub Desktop.
Save TheBryanMac/98b15a98a309ac66dc91734a9cba235a to your computer and use it in GitHub Desktop.
Multiple Field Key to Single Field Key for Relates using Python and ArcPy
#Name: Multiple Field Key to Single Field Key for Relates using Python and ArcPy
#Author: Bryan McIntosh
#Description: For updating a single LINK_ID field in both the parent and child table
# based on a natural 4 column composite index relationship.
import arcpy
## VARIABLES ##
edit_ws = r'C:\SpatialTimes.gdb' #Edit workspace
xs_fc = r'C:\SpatialTimes.gdb\CrossSection' #parent FC
xs_tbl = r'C:\SpatialTimes.gdb\CrossSection_RatingCurves' #related table
xs_att = ['Link_ID','F1','F2','F3','F4','OID@']
## FUNCTIONS ##
def fnUpdateXSTBL(qry, linkID):
with arcpy.da.UpdateCursor(xs_tbl,xs_att, qry) as cursor2:
for row in cursor2:
row[0] = linkID
cursor2.updateRow(row)
## MAIN CODE ##
edit = arcpy.da.Editor(edit_ws) #Set the edit workspace
edit.startEditing(False, False) #Note: change second param to true if using SDE WS
with arcpy.da.UpdateCursor(xs_fc, xs_att) as cursor:
for row in cursor:
row[0] = row[5] #Makes the LinkID equal to the current ObjectID of parent
cursor.updateRow(row) #Update the parent row
#Set the query for the multi-field natural key (fields are F1, F2, F3, F4)
xs_tbl_qry = xs_att[1] + " = '{0}' AND ".format(row[1]) + xs_att[2] + " = '{0}' AND ".format(row[2]) + xs_att[3] + " = '{0}' AND ".format(row[3]) + xs_att[4] + " = {0}".format(row[4])
fnUpdateXSTBL(xs_tbl_qry, row[5]) # Send the query and parent ObjectID to update the related table
edit.stopEditing(True)
@elbreidenbach
Copy link

My table does not have everything that my feature class does (I am really trying to find which entries are in the table and which entries are not). Is there a way to check if cursor2 is empty before trying to update the row? Right now it returns an error about an Invalid SQL Statement...

Thanks!

@TheBryanMac
Copy link
Author

Hey @elbreidenbach, I haven't looked at this in a few years, but at first glance you could check if there are related records before using cursor2. Many approaches - select statement and count, query layer, or a searchCursor (easy since uses the same syntax).

I haven't test this but try adding a new first line inside the fnUpdateXSTBL function. Use the python any() function to evaluate if there is a selection using the same logic as the updateCursor (would return true if results). If so, cursor2 to the races.

if any(arcpy.da.SearchCursor(xs_tbl,xs_att, qry)):

@elbreidenbach
Copy link

Hi @TheBryanMac,

Thanks for your help! The any with a SearchCursor works for me. I appreciate your super quick response. On to the next challenge.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment