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)
  • BTW this was working before the loop when I directly referenced the where clause, so the syntax is good there. – Jotter Dec 12 '16 at 17:02
  • 2
    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. – Hornbydd Dec 12 '16 at 17:06
  • 2
    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. – jbchurchill Dec 12 '16 at 17:09
  • @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? – Jotter Dec 12 '16 at 17:18
  • try eval(queryFL290) – klewis Dec 12 '16 at 22:28

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.


# 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

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.

| improve this answer | |
  • 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. – Jotter Dec 13 '16 at 18:27

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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.