topic Re: Where Clause SQL syntax problem - Urgent Please Help in Python Questions

Where Clause SQL syntax problem - Urgent Please Help

ScottMacDonald — Thu, 11 Apr 2013 02:00:33 GMT

Hi,

I am running a select layer by attribute and location query on a feature layer of points made from a shapefile.

I have been stuck for hours on the wording of it and would be grateful if some one would tell me how to do it because I have looked and tried many variations to no avail and there are so many suggestions but none seem to fit.

I want the code to be

"CRIME_INDE" <= 0.02 AND "UNVERSITY" = 1

but I can't get the right syntax in this case beacuse all the components are variables. Please could anyone offer any suggestions, it's really urgent and preventing me from progressing and I'll be happy to post up the resuts that work.

Thanks
scottaidh

Re: Where Clause SQL syntax problem - Urgent Please Help

JakeSkinner — Sat, 11 Dec 2021 23:48:04 GMT

Try the following for your where clauses:

whereClause = '"' + crimeField + '" <=' + "'" + crimefieldindex + "'"
whereClause2 = '"' + universityField + '" =' + "'" + universityfieldindex + "'"

And for your select by attribute, try the following:

arcpy.SelectLayerByAttribute_management(citiesL, "NEW_SELECTION", whereClause + "AND" + whereClause2)

Re: Where Clause SQL syntax problem - Urgent Please Help

ScottMacDonald — Thu, 11 Apr 2013 13:03:38 GMT

Thanks JSkinn for your reply I really appreciate it.

Unfortunately the code still doesn't work and I get the following error message-

<class 'arcgisscripting.ExecuteError'>: ERROR 000358: Invalid expression
Failed to execute (SelectLayerByAttribute).

Failed to execute (tryWC2).
Failed at Thu Apr 11 14:55:11 2013 (Elapsed Time: 2.00 seconds)

Please, do you have any more suggestions to what seems to be a typical problem? Unfortunately I can't find appropriate code in other messages.

I've posted my code below with your suggestion in it, please ignore all the add, delete and field mappings etc. as I will get round to them once the select layer by attribute works. Everything works apart from select layer by attributes, well in fact, that does work when nothings selected so it is just the SQL code that I can't get right.

Any help would be appreciated and thanks again for your suggestion, I thought that was it!

Thanks

Scott

# 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 + "'")
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 + '" =' + "'" + 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 + "'")
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
whereClause3 = ('"' + 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

# 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")

# 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, "#", "#", "#")

# arcpy.FeatureClassToShapefile_conversion(citiesL, path)

#arcpy.CopyFeatures_management(cityListL, ouputLayer) # cityListFC)
#fieldmappings = arcpy.FieldMappings()
#fieldmappings.addTable(targetFeatures)
#fieldmappings.addTable(joinFeatures)
#NameFieldIndex = fieldmappings.findFieldMapIndex("NAME")
#fieldmap = fieldmappings.getFieldMap(NameFieldIndex)

# spatially join the narrowed down city/road and counties layers remove unwanted fields and rename others
# arcpy.SpatialJoin_analysis("cityListL", "countiesL", "H:/working/Findsites.gdb/FoundCities", "#", "#", "")
# create new fieldMappings

# Get the output field's properties as a field object
#field = fieldmap.outputField

# Rename the field and pass the updated field object back into the field map
#field.name = "COUNTYNM"
#field.aliasName = "COUNTYNM"
#fieldmap.outputField = field
# replace old field map in mappings object with new one
#fieldmappings.replaceFieldMap(NameFieldIndex, fieldmap)

# delete fields no longer applicable
#v = fieldmappings.findFieldMapIndex("AREA")
#fieldmappings.removeFieldMap(v)
#w = fieldmappings.findFieldMapIndex("POP1990")
#fieldmappings.removeFieldMap(w)
#x = fieldmappings.findFieldMapIndex("POP_SQMILE")
#fieldmappings.removeFieldMap(x)
#y = fieldmappings.findFieldMapIndex("SQ_MILES")
#fieldmappings.removeFieldMap(y)
#z = fieldmappings.findFieldMapIndex("X")
#fieldmappings.removeFieldMap(z)
#a = fieldmappings.findFieldMapIndex("Y")
#fieldmappings.removeFieldMap(a)
#b = fieldmappings.findFieldMapIndex("X_1")
#fieldmappings.removeFieldMap(b)
#c = fieldmappings.findFieldMapIndex("Y_1")
#fieldmappings.removeFieldMap(c)
#d = fieldmappings.findFieldMapIndex("PERIMETER")
#fieldmappings.removeFieldMap(d)
#e = fieldmappings.findFieldMapIndex("GAVPRIMARY")
#fieldmappings.removeFieldMap(e)
#f = fieldmappings.findFieldMapIndex("NearRoad_OBJECTID")
#fieldmappings.removeFieldMap(f)
#g = fieldmappings.findFieldMapIndex("NearRoad_IN_FID")
#fieldmappings.removeFieldMap(g)
#h = fieldmappings.findFieldMapIndex("NearRoad_NEAR_FID")
#fieldmappings.removeFieldMap(h)

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

# order and rename final output fields where appropriate
#arcpy.AddField_management(outfc, "INTERSTATEkm", "SHORT", "3", "1", "")
#arcpy.AddField_management(outfc, "COUNTY", "TEXT", "", "", "14")
#arcpy.AddField_management(outfc, "WORKFORCE", "LONG", "", "", "")
#arcpy.AddField_management(outfc, "FARMS", "LONG", "", "", "")
#arcpy.CalculateField_management(outfc, "INTERSTATEkm", "!NearRoad_NEAR_DIST!", "PYTHON")
#arcpy.CalculateField_management(outfc, "COUNTY", "!COUNTYNM!", "PYTHON")
#arcpy.CalculateField_management(outfc, "WORKFORCE", "!AGE_18_64!", "PYTHON")
#arcpy.CalculateField_management(outfc, "FARMS", "!NO_FARMS87!", "PYTHON")
#arcpy.DeleteField_management(outfc,["NearRoad_NEAR_DIST", "LABEL", "CLASS"])
#arcpy.DeleteField_management(outfc,["COUNTYNM", "LABEL", "CLASS"])
#arcpy.DeleteField_management(outfc,["AGE_18_64", "LABEL", "CLASS"])
#arcpy.DeleteField_management(outfc,["NO_FARMS87", "LABEL", "CLASS"])
#arcpy.DeleteField_management(outfc,["OBJECTID_1", "LABEL", "CLASS"])
#arcpy.DeleteField_management(outfc,["Shape_Leng", "LABEL", "CLASS"])
#arcpy.DeleteField_management(outfc,["Join_Count", "LABEL", "CLASS"])
#arcpy.DeleteField_management(outfc,["TARGET_FID", "LABEL", "CLASS"])


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]

Re: Where Clause SQL syntax problem - Urgent Please Help

MichaelVolz — Thu, 11 Apr 2013 13:33:11 GMT

Scott:

Can you put in a print statement to determine what citiesL is defined as?

I do not see you setting the workspace in your script (e.g. file geodatabase, SDE database, shapefile), so I do not know how the script knows how to define citiesL for the SelectLayerByLocation_management(citiesL, "WITHIN_A_DISTANCE", interstates, maxKmInterstate, "NEW_SELECTION").

Re: Where Clause SQL syntax problem - Urgent Please Help

ScottMacDonald — Thu, 11 Apr 2013 14:03:49 GMT

Thanks mvolz47.

citiesL = "citiesL"

This script is for a tool in toolbox so all the workspaces, files etc. are put in by the user in GetParameterAsText - hence no workspace shown etc.

Also, SelectlayerByLocation is not the problem - that works fine. The problem is the SQL syntax in the where clause for the SelectLayerByAttribute code.

If the user is inputting the fieldnames and values (they're variables) how do you write the SQL statement for say

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

It's that simple (or not as it may be) but I can't write the SQL for that and neither it seems can the majority. I've checked other posts, manuals, asking on here and no one can give me a working answer!

I appreciate your help but all I need is the whereclause sql code - that's it. Everything works apart from this - the other issues are not the problem.

Can anyone help as I've got a deadline for tomorrow?

Thanks

Scott

Re: Where Clause SQL syntax problem - Urgent Please Help

JakeSkinner — Sat, 11 Dec 2021 23:48:07 GMT

Start small and build from there. I would recommend creating a sample feature class called "Cities" and add the "CRIME_INDE" and "UNIVERSITY" fields with attributes. After you have some sample data, don't use any parameters and try to get the expression syntax correct. The below worked for me for a sample feature class in a File Geodatabase:

import arcpy
from arcpy import env
env.overwriteOutput = 1
env.workspace = r"C:\Temp\Python\Test.gdb"

citiesL = "citiesL"
crimeField = "CRIME_INDE" 
crimefieldindex = "0.02" 
whereClause = '"' + crimeField + '" <=' + "'" + crimefieldindex + "'"

universityField = "UNIVERSITY" 
universityfieldindex = "1" 
whereClause2 = '"' + universityField + '" =' + "'" + universityfieldindex + "'"

arcpy.MakeFeatureLayer_management("Cities", citiesL)
arcpy.SelectLayerByAttribute_management(citiesL, "NEW_SELECTION", whereClause + "AND" + whereClause2)
arcpy.CopyFeatures_management(citiesL, "Cities2")

Once you have that working, sub the values for the variables with the 'arcpy.GetParameterAsText' functions. Then set up the parameters within the toolbox and verify it's still working when you run the tool through ArcMap. Afterwards, you can continue to add to your code.

Re: Where Clause SQL syntax problem - Urgent Please Help

ScottMacDonald — Thu, 11 Apr 2013 17:01:41 GMT

Thanks for the answer Jake.

I done that last week. I have perfect working code that is hard coded. The whole problem is the substitution of hard coded code to generic variable user input code.

I still can't get the right where clause construction at all and have been at it for days!

Thanks though

Scottaidh

Re: Where Clause SQL syntax problem - Urgent Please Help

MichaelVolz — Thu, 11 Apr 2013 17:09:57 GMT

At this point, why don't you just log an incident with an ESRI technical support analyst who can dedicate their time to solving your problem.

Re: Where Clause SQL syntax problem - Urgent Please Help

ScottMacDonald — Thu, 11 Apr 2013 17:32:56 GMT

Thanks for the reply. Can you tell me how to do that? Would they speak to a student before the deadline tomorrow?

I don't think I should get my hopes up but wish I had done it a few days ago.

Would you mind asking any of your colleagues if they know how to do it?

Anyway, I've pasted my cleaned up code in the hope that some one has the answer. Again the ONLY part that doesn't work is the where clause/SelectLayerByAttrribute parts.

Thanks again m8

# import system modules
import arcpy, os, arcpy, traceback
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 = "citiesL" # 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 one of ESRI forums 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)
# GIS stack exchange suggestion 4 above - whereClause = '"' + crimeField + '" = ' + "'" + crimefieldindex + "'"

# 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")

# 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, "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, "#", "#", "#")
[\CODE]

Re: Where Clause SQL syntax problem - Urgent Please Help

JonPedder — Sat, 11 Dec 2021 23:48:10 GMT

I've found the best way to build these queries is using .format()

These used to drive me nuts, this method to me is clean and easy to use.

instead of 
whereClause = ('"' + crimeField + '" <=' + "'" + crimefieldindex + "'")

try 

myfield1 = arcpy.AddFieldDelimiters(lyr,crimeField)

whereClause = '{0} <= '{1}''.format(myfield1,crimefieldindex)

Re: Where Clause SQL syntax problem - Urgent Please Help

ScottMacDonald — Tue, 16 Apr 2013 09:17:50 GMT

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