nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Find more posts tagged with

Accepted answers

All comments

DanielAbera

Thanks guys !!!

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.

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)

FabianBlau

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

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

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

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

achapkowski

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

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.