topic Re: Using a variable (in SQL) to select by attribute with Python in Geoprocessing Questions

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

MichelleConvey — Thu, 02 Dec 2010 15:14:42 GMT

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!!

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

AndrewChapkowski — Thu, 02 Dec 2010 15:44:08 GMT

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

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

MichelleConvey — Fri, 03 Dec 2010 13:36:47 GMT

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

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

RoryHall — Thu, 09 Dec 2010 03:58:04 GMT

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

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

DanielAbera — Thu, 31 May 2012 21:20:18 GMT

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

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

FabianBlau — Fri, 01 Jun 2012 10:36:09 GMT

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

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

BruceNielsen — Fri, 01 Jun 2012 11:45:39 GMT

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)

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

MathewCoyle — Mon, 04 Jun 2012 12:31:13 GMT

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.

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

DanielAbera — Fri, 03 Aug 2012 16:49:17 GMT

Thanks guys !!!