Searchcursor Returns only First Field in Field_Name List

498
9
Jump to solution
02-27-2019 02:56 PM
BenjaminBarton
New Contributor III

Greetings, I am running into a small problem. SearchCursor documentation states that search cursors can handle multiple field_names yet the below script only returns the first field name explicitly stated in the field_names parameter of the search cursor. If I use an asterisk, it returns the first field name in the list. I need it to return the location_id and lat and long attributes respectively from the view I am querying. Is it possible for a searchcursor to return more than one column / field? 

#Explicitly defined object IDs known to be conflated in conflation data model
objectIDList = (55555,44444) #small test
#gets distinct location_ids
def getUniqueLocIDs():
tableRegistry = os.path.join(conn, "myView")
 fields = ['objectid', 'location_id', 'latitude', 'longitude']
whereExp = "objectid IN {0}".format(objectIDList)
 uniqueLocIDs = list(set("{0}".format(row[0]) for row in arcpy.da.SearchCursor(tableRegistry, ['location_id', 'latitude'], whereExp)))
return list(uniqueLocIDs)
print(getUniqueLocIDs())
0 Kudos
1 Solution

Accepted Solutions
PavanYadav
Occasional Contributor II

See if the following helps:

uniqueLocIDs = list(set("{0}, {1}, {2}".format(row[0], row[1], row[2]) 
    for row in arcpy.da.SearchCursor(tableRegistry, 
        ['location_id', 'latitude'], whereExp)))

View solution in original post

9 Replies
PavanYadav
Occasional Contributor II

See if the following helps:

uniqueLocIDs = list(set("{0}, {1}, {2}".format(row[0], row[1], row[2]) 
    for row in arcpy.da.SearchCursor(tableRegistry, 
        ['location_id', 'latitude'], whereExp)))
JoshuaBixby
MVP Esteemed Contributor

This code will generate an IndexError because the tuple being returned from the search cursor only has 2 items.

BenjaminBarton
New Contributor III

This did the trick. My apologies for only having latitude explicitly specified as as one of the field parameters. it should have been:

(tableRegistry, ['location_id', 'latitude', 'longitude'], whereExp)‍‍‍‍‍‍‍

And here is the final: 

Returns exactly what I wanted it to without having to do a second pass with a separate search cursor to pull in the attributes. For context, there are two object IDs (non-normalized) that match on a conflated location_id I am returning (object IDs below are anonymized) but I only want one location_id (normalized) and the associated set of lat long coords returned:

objectIDList = (55555,44444) #small test
#gets distinct location_ids
def getUniqueLocIDs():
tableRegistry = os.path.join(conn_hg, "plc.plc.conflated_places_lv1")
 fields = ('objectid', 'location_id', 'latitude', 'longitude')
whereExp = "objectid IN {0}".format(objectIDList)
 uniqueLocIDs = list(set("{0}, {1}, {2}" .format(row[0], row[1], row[2]) for row in arcpy.da.SearchCursor(tableRegistry, ['location_id', 'latitude', 'longitude'], whereExp)))
return list(uniqueLocIDs)
#getUniqueLocIDs()
print(getUniqueLocIDs())‍‍‍‍‍‍‍‍‍‍
DanPatterson_Retired
MVP Esteemed Contributor

your where expression is fine

objectIDList = (55555,44444)
whereExp = "objectid IN {0}".format(objectIDList)
whereExp
'objectid IN (55555, 44444)'

but why are you only including 2 fields instead of using your 'fields' list

arcpy.da.SearchCursor(tableRegistry, ['location_id', 'latitude'], whereExp) current

arcpy.da.SearchCursor(tableRegistry, fields, whereExp) maybe?

RandyBurton
MVP Regular Contributor

I like using dictionaries:

sourceFC = "somefeature"

sourceFieldsList = [ 'location_id', 'latitude', 'longitude' ]
whereExp = "objectid IN {0}".format(objectIDList)

uniqueLocIDs = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(sourceFC, sourceFieldsList, where_clause=whereExp)}

# { location_id: ( latitude, longitude), .... )
JoshuaBixby
MVP Esteemed Contributor

Your problem lies in Line #8.  Instead of troubleshooting the line as written, what are you trying to achieve with that line?  You are creating a list from a set, so it appears you are looking for a list of unique items, correct?  If so, what defines uniqueness, location_id, latitude, or both?

BenjaminBarton
New Contributor III

End state is to retrieve location_id, lat and long by passing in a list of objectIDs but there exists a 1:M rel between location_ids and object ids (one location id can match to multiple objectIDs) in this non-normalized view I am querying. My result needs to be normalized so while I am using object id to match on to resolve distinct location_id keys, I don't necessarily need objectID in my result set. This leads me to believe that maybe I need to retrieve the normalized key structure as first pass to establish the normalized relational framework or key structure which the block of code I provided does do when just passing object_id in as the field param, and then query for my attributes using that returned key or keys in a second pass with another search cursor, but before I get to that point, I just wanted to figure out why only one column in fields() returns, regardless of whether I provide an explicit list, use an asterisk (all) or pass in 'fields' as the param. It just returns the first column of whatever is passed in as a param regardless of which method I use. 

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

I only have time at the moment to address:

I just wanted to figure out why only one column in fields() returns, regardless of whether I provide an explicit list, use an asterisk (all) or pass in 'fields' as the param.

Your answer is:

"{0}".format(row[0])

The search cursor is returning everything you are asking, you are ignoring everything but the first column.

DanPatterson_Retired
MVP Esteemed Contributor

format parsing doesn't care what goes in as long as it is one object.  If you have more 'objects', then they would need to be separated by commas

"stuff {}".format([1,2,3,4])
Out[6]: 'stuff [1, 2, 3, 4]'

"stuff {}".format([1,2,3,4][0])
Out[7]: 'stuff 1'

"stuff {}".format([1,2,3,4][1:])
Out[8]: 'stuff [2, 3, 4]'

"stuff {}".format([[1,2,3,4], [5,6,7,8]])
Out[9]: 'stuff [[1, 2, 3, 4], [5, 6, 7, 8]]'