Using a variable (in SQL) to select by attribute with Python

[MichelleConvey]

Hi there,

I am trying to select out any polygons from a feature class that are greater than the standard deviation of the entire feature class.  I firstly calculate the standard deviation, store that in a variable and then am getting stuck on how I need to incorporate that into my sql query. 

It keeps giving me error 000358 when it gets to the SelectByAttribute part and no matter what I change it doesn't appear to make a difference.  I am pretty new to this so sorry if it's massively inefficient.

I have posted the code below and any help would be greatly appreciated!


import sys, arcgisscripting
gp=arcgisscripting.create()

gp.OverWriteOutput = True

# Variables
table = "C:\\temp_Statistics"
field="STD_AREA"

# Process
rows=gp.searchcursor(table,"","",field)
row=rows.next()

while row:
    queryValue = row.STD_AREA 
    print queryValue # check that the number is stored in this variable
    row=rows.next()
   
# Make feature layer

in_features = "C:\\tester.shp"
out_layer = "temp_layer"

gp.MakeFeatureLayer_management (in_features, out_layer, "#", "#", "#")
print "made feature layer"

# Select by attribute based upon the value obatained from standard deviation calc.

where_clause = "area" >= queryValue
out_feature_class = "C:\\test_Copy.shp"

gp.SelectLayerByAttribute_management(out_layer, "NEW_SELECTION", where_clause)
print "selected features"

# Copy the selected features to a new layer
gp.CopyFeatures ("temp_layer", out_feature_class, "#", "#", "#")
print "features copied"

Thanks!!

[AndrewChapkowski]

Try changing this:
where_clause = "area" >= queryValue
to
where_clause = "\"area\" >= " + queryValue

[MichelleConvey]

Thanks Andrew!

That did the trick - all I had to do after that was to change my variable to a string:

where_clause = "\"area\" >= " + str(queryValue)

Cheers,
Michelle

[RoryHall]

Hi Michelle,
Just an additional thought. I have had similar problems in getting the syntax right, so have in some circumstances I have created what I need to to in a basic model using modelbuilder, then exporting that as a python script. Opening the script generally gives you the syntax you are looking for.

cheers ~ Rory

[DanielAbera]

Hi how about passing morethan one variable. Iam getting an error message

part of the code :-

MER  = sys.argv[1]
TWP  = sys.argv[2]
RNG  = sys.argv[3]
SEC  = sys.argv[4]

Expression ="MER= "+MER "AND" "TWP= "+TWP "AND" "RNG= "+RNG "AND" "SEC= "+SEC


# Process: Select Layer By Attribute
arcpy.SelectLayerByAttribute_management(lyr, "NEW_SELECTION", Expression)


any idea,

Thanks

[FabianBlau]

Expression = '"MER"= ' + MER + ' AND "TWP" = ' + TWP + ' AND "RNG" = ' + RNG + ' AND "SEC"= ' + SEC

[BruceNielsen]

If any of your variables are strings instead of integers, they need to be surrounded by single quotes in the expression, which makes things a bit more complicated. I like to use string substitution:

Expression = "\"MER\"= '%s' AND \"TWP\" = '%s' AND \"RNG\" = '%s' AND \"SEC\"= '%s'" % (MER, TWP, RNG, SEC)

[MathewCoyle]

Or even better. Through this method you don't have to worry about what format the variables go through as. Add field/attribute delimiters as needed.

Expression = "MER = {0} AND TWP = {1} AND RNG = {2} AND SEC = {3}".format(MER,TWP,RNG,SEC)

I should also add this was only implemented in Python 2.6, so ArcGIS 10 and above.

[DanielAbera]

Thanks guys !!!