SQL where caluse syntax Please Help

[ScottMacDonald]

Not sure if I have to post again but older things just seem to get ignored.

Please can someone tell me how to write the where clause for

"CRIME_INDE" <= 0.02 AND "UNIVERSITY" = 1

when everthing is a variable.

I have posted my code below.  Please ignore the other lines, they all work.  The workspaces, paths etc. are user input and are not shown, dont worry - they work.  You'll notice several lines of code for the whereclause preceded by # thats because I've tried them and they dont' work. The ONLY part that does not work is the where clauses for SelectLayerByAttribute.

Any suggestions are greatly appreciated so please help.

Scottaidh

# import system modules
import arcpy, os, arcpy
from arcpy import env
arcpy.env.overwriteOutput = True

# get user supplied path, layers and fields
path = arcpy.GetParameterAsText(0) # path is H:\working\Findsites.gdb
cities = arcpy.GetParameterAsText(1) # cities Layer is cities Feature Layer cities.shp
citiesL = "citiesL"
counties = arcpy.GetParameterAsText(2) # counties Layer is counties Feature Layer counties.shp
countiesL ="countiesL"
interstates = arcpy.GetParameterAsText(3) # interstates Layer is Feature Layer interstates.shp
crimeField = arcpy.GetParameterAsText(4) # crimeField is fieldname 'CRIME_INDE' SQL expression
crimefieldindex = arcpy.GetParameterAsText(5) # crime index is CRIME_INDE and is a string 0.02
#whereClause = '"' + crimeField + '" <= " + crimefieldindex'
#whereClause = '"' + crimeField + '" <= ' + "'" + crimefieldindex + "'"
whereClause = "\"%s\" = '%s'" % (crimeField, crimefieldindex)
universityField = arcpy.GetParameterAsText(6) # universityField is fieldname 'UNIVERSITY' SQL expression
universityfieldindex = arcpy.GetParameterAsText(7) # universityfieldindex is the UNIVERSITY field and is string integer 1
#whereClause2 = "'"' + universityField + '" <=' + universityindex"
#whereClause2 = '"' + universityField + '" <= ' + "'" + universityfieldindex + "'"
whereClause2 = "\"%s\" = '%s'" % (universityField, universityfieldindex)
workforceField = arcpy.GetParameterAsText(8) # workforceField is fieldname 'AGE_18_64' SQL expression
workforceindex = arcpy.GetParameterAsText(9) # workforce index is attribute of AGE_18_64 field and is a Double and is 25000
#whereClause3 = "'"' + workforceField + '" >=' + workforceindex"
#whereClause3 = '"' + workforceField + '" >= ' + "'" + workforceindex + "'"
whereClause3 = "\"%s\" = '%s'" % (workforceField, workforceindex)
farmField = arcpy.GetParameterAsText(10) # farmField is fieldname 'NO_FARMS87' SQL expression
farmfieldindex = arcpy.GetParameterAsText(11) # farmfieldindex is the NO_FARMS87 field and is Double integer is 500
#whereClause4 = "'"' +farmField + '" >=' + farmfieldindex"
#whereClause4 = '"' + farmField + '" >= ' + "'" + farmfieldindex + "'"
whereClause = "\"%s\" = '%s'" % (farmField, farmfieldindex)
maxKmInterstate = arcpy.GetParameterAsText(12) # interstate WITHIN_A_DISTANCE linear unit
nearestInterstate = arcpy.GetParameterAsText(13) # Near Table Analysis table generated
targetFeatures = "cityListL" # cities spatial join layer
joinFeatures = "countiesL" # counties spatial join layer
cityListFC = arcpy.GetParameterAsText(14) # narrowed down cities list temporary
cityListL = "CityListL" # the feature layer of the generated cityList feature class
outputLayer = arcpy.GetParameterAsText(15) # ootput

#altwhereclause1 = "CRIME_INDE <= 0.02"
#altwhereclause2 = "UNIVERSITY = 1"
#altwhereclause3 = "AGE_18_64 >= 25000"
#altwhereclause4 = "NO_FARMS87 = 500"


# make a layer from the cities feature class
arcpy.MakeFeatureLayer_management(cities, citiesL)

# select layer by location to interstates
arcpy.SelectLayerByLocation_management(citiesL, "WITHIN_A_DISTANCE", interstates, maxKmInterstate, "NEW_SELECTION")

# Here is the ESRi suggested code for select layer by attribute where clause - doesn't work though
# whereClause = '"' + crimeField + '" <=' + "'" + crimefieldindex + "'"
# arcpy.SelectLayerByAttribute_management(citiesL, "NEW_SELECTION", whereClause + "AND" + whereClause2)
# stack exchange suggestion - whereClause = '"' + PipelineIDField + '" = ' + "'" + fullRouteName + "'"

# from selection above select layer by attribute select "CRIME_IND" <= 0.02 AND "UNIVERSITY" = 1
arcpy.SelectLayerByAttribute_management(citiesL, "NEW_SELECTION", whereClause + "AND" + whereClause2)

# make counties feature layer
arcpy.MakeFeatureLayer_management(counties,countiesL)

# select workforce and number of farms
# new selection on counties layer countiesL  "AGE_18_64" >= 25000 AND "NO_FARMS87" >= 500")
arcpy.SelectLayerByAttribute_management(countiesL, "NEW_SELECTION", whereClause3 + "AND" + whereClause4)

# from selection above select cities intersecting counties
arcpy.SelectLayerByLocation_management(citiesL, "INTERSECT", countiesL, "", "SUBSET_SELECTION")
 
# save selected features
# arcpy.CopyFeatures_management(citiesL, cityListFC)

# arcpy.Select_analysis(citiesL, cityListFC, "")
arcpy.FeatureClassToShapefile_conversion(citiesL, path)

# make temp cities list feature layer so that the output can be spatially joined to counties
# arcpy.MakeFeatureLayer_management(cityListFC,cityListL)

#arcpy.AddField_management(citiesL, "CITYNAME", "TEXT", "", "", "25")
#arcpy.AddField_management(citiesL, "CRIMEINDX", "DOUBLE", "", "", "")
#arcpy.AddField_management(citiesL, "HAS_UNI", "TEXT", "", "", "3")
#arcpy.CalculateField_management(citiesL, "CITYNAME", "!NAME!", "PYTHON")
#arcpy.CalculateField_management(citiesL, "CRIMEINDX", "!CRIME_INDE!", "PYTHON")
#arcpy.CalculateField_management(citiesL, "HAS_UNI", "!UNIVERSITY!", "PYTHON")
#arcpy.DeleteField_management(citiesL,["NAME", "LABEL", "CLASS"])
#arcpy.DeleteField_management(citiesL,["CRIME_INDE", "LABEL", "CLASS"])
#arcpy.DeleteField_management(citiesL,["UNIVERSITY", "LABEL", "CLASS"])
#arcpy.DeleteField_management(citiesL,["ID", "LABEL", "CLASS"])
#arcpy.DeleteField_management(citiesL,["POPULATION", "LABEL", "CLASS"])
#arcpy.DeleteField_management(citiesL,["TOTAL_CRIM", "LABEL", "CLASS"])

# Generate NearTable_analysis to find closest interstate distance
arcpy.GenerateNearTable_analysis(citiesL, interstates, nearestInterstate, maxKmInterstate)

# join new city list layer to generated Near Table
arcpy.AddJoin_management(citiesL, "NEAR_FID", nearestInterstate, "IN_FID")

# Run the Spatial Join tool, using the defaults for the join operation and join type
arcpy.SpatialJoin_analysis(targetFeatures, joinFeatures, outputLayer, "#", "#", "#")


   

 
print arcpy.GetMessages()
print "\n*** LAST GEOPROCESSOR MESSAGE (may not be source of the error)***"; print arcpy.GetMessages()
print "Python Traceback Info: " + traceback.format_tb(sys.exc_info()[2])[0]
print "Python Error Info: " +  str(sys.exc_type)+ ": " + str(sys.exc_value)

# save selected features FINAL OOTPUT here
# arcpy.CopyFeatures_management(citiesL, outputLayer)

print arcpy.GetMessages()
[\CODE]

[MichaelVolz]

I would follow Jake's suggestion, from a previous post of the exact same question, and simplify your script to more easily debug your issue with the SQL clause.  Once that problem has been resolved I would work towards getting your more complex code working.

[ScottMacDonald]

Hi thanks for your reply mvolz.

I wrote perfect working code last week that is hard-coded.

I have already converted 95% of it to generic and it all works apart from the where clause expression.

That's the whole problem!

Any help with the syntax or maybe even how to print out what it's doing might help.  Is there any way to show exactly what its doing?

I can't think of anything else I can do and it's still not right.  I'll keep working on it until the deadline.  All I want is

"CRIME_INDE" <= 0.02 AND "UNIVERSITY" = 1
and
"AGE_18_64" >= 25000 AND "NO_FARMS87" >=500

That's it. Nothing else. Everything works apart from that!

I can even # out the two GetLayerByAttribute lines and it works without the selected features.  It's ONLY the above two statements that are preventing the whole thing from running as perfectly as the hard coded version I wrote last week.

Thanks again for you reply.

Scottaidh

[JakeSkinner]

Scott,

Add the 'arcpy.AddMessage' function to your script to print out your where clauses:

arcpy.AddMessage(whereClause)

Also, what type of data are you working with? (i.e. shapefiles, File Geodatabase feature classes, Personal Geodatabase feature classes)

[AnthonyCalamito]

The key here is to know/remember that in Python the backslash is the escape character.  So you will need to use it to escape the special use of the quote character for defining text syntax.  The following syntax should accomplish what you need:

field1 = "CRIME_INDEX"
field2 = "UNIVERSITY"
field3 = "AGE_18_64"
field4 = "NO_FARMS87"

SQL1 = "\"" + field1 + "\" <= 0.02 AND " + "\"" + field2 + "\" = 1"
SQL2 = "\"" + field3 + "\" >= 25000 AND " + "\"" + field4 + "\" >= 500"

As suggested above, if you use arcpy.AddMessage(SQL1) and arcpy.AddMessage(SQL2) you should see the appropriate where clause which you can pass to the Select By tools as a parameter.

[ScottMacDonald]

Thank you all very much for taking the time to reply.

The line that worked in the end was a Field Delimiter where clause

crimeField = arcpy.GetParameterAsText(4) # crimeField is fieldname 'CRIME_INDE' SQL expression
crimeval = arcpy.GetParameterAsText(5) # crime index is CRIME_INDE and is a Double 0.02
whereClause = "{0} <= {1}".format(arcpy.AddFieldDelimiters(citiesL, crimeField), crimeval)
[\CODE]

Previously, I had two extra ' single abbreviations around the where clause 0 and 1 which made the expression something like ""CRIME_INDE"" = "0.02" so watch out for them! 

Thanks again for the help.

Scottaidh