odd troubles with cursors

08-19-2019 08:18 AM
New Contributor II

Hi folks, I'm not new to Python scripting for ArcGIS, but I'm a bit rusty to manipulating Python in GIS specifically, and new to ArcGIS Pro.  That said, what I'm trying to do is fairly simple and I've worked with cursors in GIS before, so I am stumped at what's wrong. 

I have two datasets that I need to, by hand, relate to one another (they are two representations of the same geographic points, but one set is far more spatially accurate than the other, and I need to connect by hand what accurate point in one attribute table belongs to what inaccurate point in the other attribute table, and create a field that links them together as 'the same thing' since no such field currently exists between the two data tables).   Since this is considered a common problem in the municipality I work with (two data sets that need to be linked by hand to each other), I am trying to write a script to aid in this process.  The idea is to, when two points are selected (as being the 'same') in the map, I run a script to pull values out of the accurate table and add them to the other datasets' attribute table.   I found an example on the web a few years' old (predating the arcpy.mp) and thought I'd updated it properly, but I'm still having trouble.  I know my points are selected as expected, but this below script isn't working; I get an SQL error that doesn't make sense to me as an error.   Here is the code (my tab formatting gets butchered when copying from the Python window in Arc; not sure why, but I don't think it's relevant to my error):

import arcpy

aprx = arcpy.mp.ArcGISProject("CURRENT")
m = aprx.listMaps("Map1")[0]
featlyr = m.listLayers("WaterSystemValve")[0]
tblview = m.listLayers("Sheet1_XYTableToPoint")[0]

# field names in Table that you want to copy to water meter layer
tblFields = ['MCID']
# field names in water meter layer that you want populated by table.
lyrFields = ['ValveNumber']

# Get a list of selected records in the table
tblsel = tblview.getSelectionSet()
# Get a list of the selected features in the water meter layer
lyrsel = featlyr.getSelectionSet()

if len(tblsel) == 1 and len(lyrsel) == 1: # If there is a single record/feature selected, then proceed

# Query for Search Cursor to find the selected record
tblExpression = 'ObjectID = {}'.format(tblsel)
print(tblExpression, file=sys.stderr)
# Query for Update Cursor to find the selected feature
lyrExpression = 'ObjectID = {}'.format(lyrsel)

with arcpy.da.SearchCursor(tblview, tblFields, tblExpression) as sCursor:
   for sRow in sCursor:
   with arcpy.da.UpdateCursor(featlyr, lyrFields, lyrExpression) as uCursor:
   for uRow in uCursor:
#Update the water meter feature to the value in the table record
uRow = sRow

# Save the changes
# uRow.updateRow(uRow)
# Bail if there are not the right number of records/features selected
# (e.g. there isn't a single record in the table and feature in the layer)
arcpy.AddError("Not the right number of features/records selected, bailing!")


and the error:

Failed script Script...
ObjectID = {4724}
Traceback (most recent call last):
File "C:\Users\Cynthia\Documents\myGISproj\RIR_watervalves\testme.py", line 29, in <module>
for sRow in sCursor:
RuntimeError: An invalid SQL statement was used. [SELECT MCID, OBJECTID FROM Sheet1_XYTableToPoint WHERE ObjectID = {4724}]
Failed to execute (Script).

I verified that object ID is correct, it should be 4724 and I can't for the life of me see what is invalid about the SQL statement (the fields are properly named).  Any ideas?



Tags (2)
0 Kudos
3 Replies
MVP Esteemed Contributor

Your problem is that layer.getSelectionSet() returns Python sets:

Method Overview

getSelectionSet ()

Returns a layer's selection as a Python set of object IDs.

Since Python represents a set using curly brackets, your SQL expression is ObjectID = {4724} instead of ObjectID = 4724  .

New Contributor II

Thank you!!

0 Kudos
MVP Esteemed Contributor

If someone's response answers your question, please mark it correct to close out the question, or you can reply with follow-up questions.

0 Kudos