Arcpy query String field

Question

I have numerous shapefiles with two fields, "ID2" (Integer) and "FILENAME" (String). Using arcpy I'm trying to query the attribute table for rows that have a certain value ("SCN_TCV_001_2002") in FILENAME and for those rows find the max value of the ID2 field. I think I'm just missing something in the query syntax. My code is below which returns an error at the last line "AttributeError: 'NoneType' object has no attribute 'getValue'". I'm using ArcGIS 10.2.2 with an Advanced License.

import arcpy, os, sys
from arcpy import env
env.workspace = "C:\\folder1"
fclist = arcpy.ListFeatureClasses()
for fc in fclist:
 field1 = 'FILENAME'
 field2 = 'ID2'
 sWhereClause = '"' + field1 + '"' + ' = ' + "'" + "SCN_TCV_001_2002" + "'"
 maxValue = arcpy.da.SearchCursor(fc, sWhereClause, "", "", field2 + " D").next().getValue(field2)

• NULL.Dude: does it give you an error when it "crashes"
• Andrew15: Yes, the error is: Runtime error Traceback (most recent call last): File "<string>", line 13, in <module> NameError: name 'SCN_TCV_001_2002' is not defined
• Andrew15: Okay just tried that and now I'm getting a new error: RuntimeError: ERROR 999999: Error executing function. An invalid SQL statement was used. An invalid SQL statement was used.
• NULL.Dude: so the error is saying that your value is not assigned to a variable. you need to wrap it quotes to indicate its a string or assign a variable with that value.please update your code to reflect any updates you make.
• artwork21: Without looking too deeply into your logic, remove the "" around the sWhereClause in the arcpy.SearchCursor statement. You need to reference the variable sWhereClasuse, not the string "sWhereClause"

Answer 1

Use the newer Data Access cursor, like the da.SearchCursor. They are alot faster.

Also it's difficult to get SQL queries like this to work:

sWhereClause = '"' + field1 + '"' + ' = ' + "'" + "SCN_TCV_001_2002" + "'"

For example the quotation signs are different depending on type of indata (shape, features in a file geodatabase, etc.).

Try using AddFieldDelimiters and Format String Syntax:

sWhereClause = """{0} = 'SCN_TCV_001_2002'""".format(arcpy.AddFieldDelimiters(fc,field1))

• Andrew15: Thanks @BERA. I have tried this approach yet still get the error: Runtime error Traceback (most recent call last): File "<string>", line 13, in <module> AttributeError: 'NoneType' object has no attribute 'getValue'
• Bera: Try using the da cursor
• Andrew15: Okay, just tried that and now a new error tells me "Line 13, a column specified does not exist. I must have the "ID2" column referenced wrong in the code?
• Andrew15: Now I see the syntax is different for arcpy.da.cursor, which is as follows: SearchCursor (in_table, field_names, {where_clause}, {spatial_reference}, {explode_to_points}, {sql_clause}). I have now set it up like so: maxValue = arcpy.da.SearchCursor(fc, field2, "sWhereClause", "", "", sql_clause=(None, 'ORDER BY ID2')). I am unsure how to extract the max value from this.
• Vince: You're changing the question three comments deep into an answer. Extracting the max just requires an extra test.