Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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

This comment has been minimized.

Copy link

@elbreidenbach elbreidenbach commented Apr 21, 2020

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

This comment has been minimized.

Copy link
Owner Author

@TheBryanMac TheBryanMac commented Apr 22, 2020

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

This comment has been minimized.

Copy link

@elbreidenbach elbreidenbach commented Apr 22, 2020

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