Variable in where clause arcpy.Select

Question

I'm trying to set up a loop that includes using items in the array along with string text to populate the where clause. I'm getting the invalid sql expression for the clause. Here's what I have so far.

# Flight Levels array
 flightLevelsArray = ["FL290", "FL300", "FL310", "FL320", "FL330", "FL340", "FL350", "FL360", "FL370", "FL380", "FL390", "FL400", "FL410", "FL420"]
queryFL290 = "FL IN ('FL285', 'FL286', 'FL287', 'FL288', 'FL289', 'FL290', 'FL291', 'FL292', 'FL293', 'FL294')"
queryFL300 = "FL IN ('FL295', 'FL296', 'FL297', 'FL298', 'FL299', 'FL300', 'FL301', 'FL302', 'FL303', 'FL304')"
for flightLevel in flightLevelsArray:
flightLevelField = "!"+flightLevel+"!"
rangeToQuery = "query"+flightLevel
arcpy.SelectLayerByAttribute_management(out_layer_file, "ADD_TO_SELECTION", rangeToQuery)

• Jotter: BTW this was working before the loop when I directly referenced the where clause, so the syntax is good there.
• Hornbydd: Your whereclause rangeToQuery is meaningless. On the first iteration it would be "query!FL290!".. Have a look at the help file for this tool and look at the code samples.
• jbchurchill: It will also be useful for you, as you debug this, to print the output to see what you are getting in the rangeToQuery variable. As @Hornbydd indicates, you can see you are not passing in a valid query.
• Jotter: @Hornbydd rangeToQuery actually returns queryFL290 on the first iteration, which is good. But this is a string instead of the variable I've assigned. How would I make this a valid input?
• klewis: try eval(queryFL290)

Answer 1

You appear to be trying to call a different variable based on each value in the flightLevelsArray. For this, you should use a dictionary--not try to dynamically call variable names.

However, it doesn't appear that's actually what you need anyway. I would assume that you're trying to construct a series of queries, like this:

FL IN ('FL285', 'FL286', 'FL287', 'FL288', 'FL289', 'FL290', 'FL291', 'FL292', 'FL293', 'FL294')
FL IN ('FL295', 'FL296', 'FL297', 'FL298', 'FL299', 'FL300', 'FL301', 'FL302', 'FL303', 'FL304')
FL IN ('FL305', 'FL306', 'FL307', 'FL308', 'FL309', 'FL310', 'FL311', 'FL312', 'FL313', 'FL314')
FL IN ('FL315', 'FL316', 'FL317', 'FL318', 'FL319', 'FL320', 'FL321', 'FL322', 'FL323', 'FL324') ...

That would mean that you have a field named FL and within it are values like FL285 and FL307. If so, you would want to iterate over the ranges of values, rather than typing out each range.

Try:

# Iterate over values between 290 and 420, incrementing by 10
for fl in range(290, 430, 10):
 # Compile a list of 'FLxxx' for each number from 5 below to 4 above the
 # flight level
 values = ["'FL{}'".format(v) for v in range(fl - 5, fl + 5)]
 # Construct the full query
 query = 'FL IN ({})'.format(', '.join(values))
 # Continue on with whatever processing you do with that query
 arcpy.SelectLay...

I'm not sure what you're doing with your flightLevelField variable, since you never seem to use it. Based on the ambiguity of the code, I would suspect that your field isn't actually named FL and/or that your field values don't have the "FL" prefix; if it is and they are, then you may want to reconsider the data design.

• Jotter: Yes, the field name is "FL" and the values contain the "FL" prefix. But I am selecting the values in this field by groups, i.e. FL285-FL294 to compare a related field named "FL290".with yet another field containing data. The flightLevelField variable is used in a separate sql expression, so I didn't need to include that. I will try this solution.

Answer 2

Here's what I ended up doing: I created a dictionary called flightLevelsArray referencing the flight levels string to search and the associated query for the where clause statement. I used UpdateCursor instead of Calculate Field:

flightLevelsArray = {"FL290":"FL IN ('FL285', 'FL286', 'FL287', 'FL288', 'FL289', 'FL290', 'FL291', 'FL292', 'FL293','FL294')", 
etc for each flight level range
}
for flightLevel in flightLevelsArray:
 calcFields = ['FL', flightLevel]
 queryFL = flightLevelsArray[flightLevel]
 cursor = arcpy.da.UpdateCursor(out_layer_file, calcFields, queryFL)
 for row in cursor:
# Calculate values for each FL
 #do calculations
 cursor.updateRow(row)
 cursor.reset(row)