searchcursor on layer using text field and query

[DamianMilne]

Hi, I'm trying to use a SearchCursor to loop through specific records in a layer in an ArcMap project.  I would like loop through specific records based on a text field in the layer but I'm having trouble figuring out the syntax. Can anyone help please? Code below.

# Set the variables mxd = arcpy.mapping.MapDocument(r"\\working\BioFire.mxd") df = arcpy.mapping.ListDataFrames(mxd,"Layers")[0] lyr = arcpy.mapping.ListLayers(mxd,"Fire",df)[0]  # Loop through specified records in layer  ### I CAN'T FIGURE OUT THE CODE FOR THIS NEXT BIT cur = arcpy.SearchCursor(lyr, '"FIRECODE" in ("ARC", "CEA", "DAB", "MGD")')    ### IF I USE THE LINE BELOW INSTEAD IT WORKS FINE BUT LOOPS THROUGH ALL RECORDS WHICH I DON'T WANT #cur = arcpy.SearchCursor(lyr)  . . . . 

Cheers, Damian

[KimOllivier · Accepted solution]

Your SQL query has the quotes mixed up. SQL uses single quotes only for strings, and there is a general kludge to surround invalid field names with double quotes. If the field name has no spaces or punctuation, speclal characters, does not start with a number or an other indecencies in a field name then you can forget the double quotes. This is not the official advice of course, and if you do not have control of field names you may have to add double quotes.

SQLexpression = "FIRECODE in ('ARC', 'CEA', 'DAB', 'MGD')" cur = arcpy.SearchCursor(lyr,sQLexpression)

Similarly if you really want to have double quotes in the SQL query (why?) instead of escaping them with clumsy escape characters (\) just surround the whole expression with triple quotes of either type. Spaces added for clarity on the triples.

SQLexpression = ''' "FIRECODE" in ('ARC', 'CEA', 'DAB', 'MGD') ''' cur = arcpy.SearchCursor(lyr,sQLexpression)

[XanderBakker]

Hi Damian,

Kim already suggested a valid syntax for your where clause, but I would like to add something for you to consider. Since the syntax of the where clause depends on the dataset, it might be better to write code that is independent of the type of data you are querying. For this purpose arcpy provides the AddFieldDelimiters functionality. Have a look at the code below:

fldname = "FIRECODE"
lst_values = ["ARC", "CEA", "DAB", "MGD"]
where = "{0} in ('{1}')".format(arcpy.AddFieldDelimiters(lyr, fldname), "','".join(lst_values))

cur = arcpy.SearchCursor(lyr, where)

In this case I have the field name defined as a variable "fldname" and I also have a list of values to match it to ("lst_values"). I create a variable "where" containing the where clause and pass that variable in the search cursor (BTW if you have access to 10.1 you should try the arcpy.da.SearchCursor which is much faster, but uses a different syntax).

Let's take the where clause construction apart to explain what happens:

The AddFieldDelimiter adds the quotes, square brackets or nothing depending on the data source:

print arcpy.AddFieldDelimiters(lyr, fldname)
# >> "FIRECODE"

If you use the join method on a string a trow in a list, it will return the list of items where the string is placed between each item:

print "','".join(lst_values)
# >> ARC','CEA','DAB','MGD

If you use the format on a string you can replace the {0}, {1} by values you pass in, allowing better readability. In this case the list is enclosed by curved brackets and single quotes:

print "('{0}')".format("','".join(lst_values))
# >> ('ARC','CEA','DAB','MGD')

Putting it all together gives:

where = "{0} in ('{1}')".format(arcpy.AddFieldDelimiters(lyr, fldname), "','".join(lst_values))
print where
# >> "FIRECODE" in ('ARC','CEA','DAB','MGD')

Kind regards,

Xander

[DamianMilne]

Dear Kim and Xander, thank you very much for your responses, both worked a treat. I wish the ESRI online help was that clear and succinct.

Xander, I'm afraid you've started something. I do have 10.1 (10.2 actually), and I tried using arcpy.da.SearchCursor as you suggested. I followed the esri online help and examples as best I could and ended up with this:

datasetname = lyr.dataSource     #this is a shapefile
fldname = "FIRECODE"
lst_values = ["ARC", "CEA", "DAB", "MGD"]
where = "{0} in ('{1}')".format(arcpy.AddFieldDelimiters(lyr1, fldname), "','".join(lst_values))
with arcpy.da.SearchCursor (datasetname,fldname,where) as cur:
  for eachrecord in cur:
    regcode = eachrecord.getValue(fldname)
.
.

At this point I get the error message "AttributeError: 'tuple' object has no attribute 'getValue'"
Any suggestions? Cheers, Damian

[XanderBakker]

Dear Kim and Xander, thank you very much for your responses, both worked a treat. I wish the ESRI online help was that clear and succinct.

Xander, I'm afraid you've started something. I do have 10.1 (10.2 actually), and I tried using arcpy.da.SearchCursor as you suggested. I followed the esri online help and examples as best I could and ended up with this:

datasetname = lyr.dataSource     #this is a shapefile
fldname = "FIRECODE"
lst_values = ["ARC", "CEA", "DAB", "MGD"]
where = "{0} in ('{1}')".format(arcpy.AddFieldDelimiters(lyr1, fldname), "','".join(lst_values))
with arcpy.da.SearchCursor (datasetname,fldname,where) as cur:
  for eachrecord in cur:
    regcode = eachrecord.getValue(fldname)
.
.

At this point I get the error message "AttributeError: 'tuple' object has no attribute 'getValue'"
Any suggestions? Cheers, Damian

Hi Damian,

You are close. The values are retrieved from the row by using an index. If you only provide 1 field as fields in the da.SearchCursor, your value will be at index 0, hence the line: regcode = eachrecord[0]. Just look at the code below where I have introduced more fields to clarify:

datasetname = lyr.dataSource
fldname = "FIRECODE"
lst_values = ["ARC", "CEA", "DAB", "MGD"]
where = "{0} in ('{1}')".format(arcpy.AddFieldDelimiters(lyr1, fldname), "','".join(lst_values))

# define a tuple (or list) of field names to use in the cursor
flds = (fldname, 'someOtherFieldname', 'andYetAnotherFieldName')

with arcpy.da.SearchCursor(datasetname, flds, where) as cur:
    for eachrecord in cur:
        regcode = eachrecord[0] # firecode
        someOtherValue = eachrecord[1]
        andYetAnotherValue = eachrecord[2]

Since you are only using 1 field in a search cursor, I assume you are trying to determine some statistic. If that is the case there maybe faster ways to do that (list comprehensions of using a dictionary). Just let me know what you want to do in the loop and I can give you some pointers.

Another powerful feature of the da cursors are the build-in fields like "SHAPE@" for the geometry, "OID@" for ObjectID, etc.

Kind regards,

xander

[RichardFairhurst]

Hi, I'm trying to use a SearchCursor to loop through specific records in a layer in an ArcMap project.  I would like loop through specific records based on a text field in the layer but I'm having trouble figuring out the syntax. Can anyone help please? Code below.

# Set the variables
mxd = arcpy.mapping.MapDocument(r"\\working\BioFire.mxd")
df = arcpy.mapping.ListDataFrames(mxd,"Layers")[0]
lyr = arcpy.mapping.ListLayers(mxd,"Fire",df)[0]

# Loop through specified records in layer

### I CAN'T FIGURE OUT THE CODE FOR THIS NEXT BIT
cur = arcpy.SearchCursor(lyr, '"FIRECODE" in ("ARC", "CEA", "DAB", "MGD")')  

### IF I USE THE LINE BELOW INSTEAD IT WORKS FINE BUT LOOPS THROUGH ALL RECORDS WHICH I DON'T WANT
#cur = arcpy.SearchCursor(lyr) 
.
.
.
.

Cheers, Damian

The expression has the wrong quotes for the values.  They have to be in single quotes.  Change it to this:

cur = arcpy.SearchCursor(lyr, """FIRECODE"" in ('ARC', 'CEA', 'DAB', 'MGD')")