Problem with arcpy select statement

Question

I have a shp layer with an attribute table.

One field has the name Nid and the values are strings (e.g. "0150", "0420", "3462" ...) Now I want to select the rows in the table that match a given Nid value.

This works:

qry = ' "Nid" = \'0150\' '

arcpy.management.SelectLayerByAttribute(layer, "NEW_SELECTION", qry)

But I am really confused by this:

• I know \ is the escape character and used so that 0150 is between two quotes. But why is Nid surrounded by double quotes and not using also \’ ? Note: I tried using \’ Nid\’ and also "0150" but I get errors ... I also found examples using [Nid] = \'0150\'' in the web, but that also did not work.

My problem is that I have the values I want to match in the AreaID variable, so AreaID[1]=’0150’ and I am unable to put this in my query:

qry = ' "Nid" = \'AreaID[1]\' ' - does not select anything

qry = ' "Nid" = AreaID[1]' - gives an error

qry = ' "Nid" = "AreaID[1]" ' - gives an error

qry = ' "Nid" = \"AreaID[1]\" ' - gives an error

How does this work?

• Iced_: If I write AreaID[1] I get: u'0150', so its a Unicode string.

Answer 1

You are touching on a number of issues that are all related:

First, Python uses quotes to create strings. A string can be surrounded by either " or ', and whichever type is not used there can be used inside of the string without a problem. An escape character, as you note, can be used to allow for the outside quotation marks to be used inside the string.

Next, you are asking about field delimiters, i.e. the characters that are require by SQL to demarcate a field name. In your case, you are probably using shapefiles or feature classes. For those datasets, " " is the way to delimit fields. I think the brackets are for .mdb feature classes. If you ever have to write something to accommodate various types of datasets, it's good to use this tool.

Finally, to pass a variable into the query, it's very simple. Use python string formatting (because your "query" here is just a string, afterall):

qry = '"Nid" = \'{0}\''.format(AreaID[1])

EDIT: As @dmahr mentions .addFieldDelimiters() is a good thing to make into a habit. You can use it with above formatting like so

field_name = "Nid" #(or 'Nid', doesn't matter at this point)
delim_name = arcpy.AddFieldDelimiters(shapefile_path,field_name)
qry '{0} = \'{1}\''.format(delim_name,AreaID[1])

• dmahr: +1 for referencing arcpy.AddFieldDelimiters(). This should really be standard practice, in the same way that os.path.join() should be used instead of naive string concatenation.
• GeoJohn: In this specific case you could also use: qry = '"Nid" = \'%s\'' %AreaID[1]
• mr.adam: You could, but .format is much easier to learn and is more flexible, for example you can reuse a given variable by placing it's {0} index in multiple places in the string.
• GeoJohn: Yes, I agree, .format is the more versatile option.