Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I'm trying to put a variable into a string used to do a selection. At the moment it's like this...

expression = "'No_Props' > 5 AND %s < 200 AND 'ST_Ori' >60" % metres_field
arcpy.SelectLayerByAttribute_management(streets_table, "NEW_SELECTION", '"' + expression + '"')

Printing the expression gives this....

'No_Props' > 5 AND 'Shape_Le_2' < 200 AND 'ST_Ori' >60

.. which I thought would work.

I've tried several things. Originally the expression was in a docstring..

"""'No_Props' > 5 AND %s < 200 AND 'ST_Ori' >60""" % metres_field

.. but that didn't work

I considered using the doc attribute but it only worked on a function (not a variable)

Anyone know what I'm doing wrong / how to make it work? The expression looks like it should do the trick when it's printed out..

Anyone have any ideas?

Cheers

share|improve this question
    
According to the documentation, the query string does not need to include surrounding quotations. Note that although you printed expression--which was a good thing to do--you did not print the value you are actually passing to SelectLayerByAttribute_management! –  whuber Jun 21 '13 at 14:18
add comment

5 Answers

This is not an ArcPy problem but a basic problem in Python (and I don't know ArcPy):

  1. From Python 2.6, % is deprecated, use .format() (except in some cases)
  2. So if you want to get a result like arcpy.SelectLayerByAttribute_management("lyr", "SUBSET_SELECTION", ' "population" > 10000 ') as in Select Layer By Attribute (Data Management)

One solution is:

expression  = "{0} > 5 AND {1} < 200 AND {2} >60".format(""" "No_props" """,""" "Shape_Le_2" """,""" "ST_Ori" """) 

and the result is

' "No_props"  > 5 AND  "Shape_Le_2"  < 200 AND  "ST_Ori"  >60'

then

arcpy.SelectLayerByAttribute_management(streets_table, "NEW_SELECTION", expression)

but if you know Python, there are many others...

share|improve this answer
    
Thanks gene, informative answer. But.. I've still got the same issue here that I need to put a variable in the expression. 'Shape_Le_2' could have a different name, thus I need the variable 'metres_field' to be in the expression. I tried (""" metres_field """) but that didn't work –  Mike Jun 26 '13 at 10:13
    
Thanks to you, I've cracked it. expression = "{0} > 5 AND {1} < 200 AND {2} >60".format(""" "No_props" """, metres_field, """ "ST_Ori" """) Variable isn't enclosed in quotes or triple quotes in the format() method. Cheers –  Mike Jun 26 '13 at 10:48
add comment

Without seeing the error being thrown, I would first suggest that you need to enclose your fieldnames with quotation marks (") instead of the apostrophe (') you currently have. See the help bits for building your where_clause on the tool help page.

share|improve this answer
2  
It depends what your source is. It gets confusing as sometimes you need exclamation point, single quotes, double quotes, or square brackets. This goes in depth about building SQL statements resources.arcgis.com/en/help/main/10.1/index.html#//… –  Paul Jun 21 '13 at 14:05
    
It can get confusing, however I believe (and I could definitely be wrong) that when specifying field names using Python, they will always be surrounded by either quotation marks ("field_name") or by brackets ([field_name]), but not apostrophes ('field_name'). Apostrophes are used to surround strings in the SQL query: "field_name" = 'some string' or [field_name] = 'some string' –  Jason Jun 21 '13 at 14:52
    
You are correct! Exlcamation points are used in field calculator for the Python parser and single quotes are used for strings. –  Paul Jun 21 '13 at 15:30
    
OK thanks for that, I originally thought single and double quotations were interchangeable. –  Mike Jun 26 '13 at 10:36
add comment

Not sure, but maybe there should be a space between the > and the 60? Also, usually in Python I see field names enclosed in !'s, not single quotes. For an example, see this. May also need double quotes, based on that example, but I think the main issue is the !'s.

share|improve this answer
    
Odd thing is it works fine when I hard-code the string instead of using the %s value. I've used !'s for the calculate field but not for selection query expressions (and they've worked without probs) but if it's better practice I'll give that a try –  Mike Jun 21 '13 at 13:25
    
Well, you're correct, !'s aren't required in code. You'd think I'd know that since looking at a script I wrote I used double quotes. But single quotes are still probably incorrect. Sorry for the error. My practice is to set field names to variables so I don't usually run into this problem. –  recurvata Jun 21 '13 at 16:19
add comment

Does this work for you?

expression = "\"No_Props\" > 5 AND \"%s\" < 200 AND \"ST_Ori\" > 60" % metres_field
arcpy.SelectLayerByAttribute_management(streets_table, "NEW_SELECTION", expression)
share|improve this answer
    
No, it doesn't =\ –  Mike Jun 26 '13 at 10:16
add comment
up vote 0 down vote accepted

Sorted using the format() method, as below.

metres_field = "field_name"
expression = "{0} > 5 AND {1} < 200 AND {2} >60".format(""" "No_props" """, metres_field, """ "ST_Ori" """)

Also works with the docstring outside of the format method.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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