whereClause syntax error

[JacobDrvar]

All,

The script is almost complete.  I am attempting to have the user input the six values as strings, then the script selects and zooms to feature. I am running into a syntax error within the whereClause.  Any suggestions as to why?

#Import import arcpy  #Variables  TOWNSHIP = arcpy.GetParameterAsText(0) RANGE = arcpy.GetParameterAsText(1) SEC = arcpy.GetParameterAsText(2) SUBCODE = arcpy.GetParameterAsText(3) BLOCK = arcpy.GetParameterAsText(4) LOT = arcpy.GetParameterAsText(5)  mxd = arcpy.mapping.MapDocument("CURRENT") df = arcpy.mapping.ListDataFrames(mxd, "Layers")[0] lyr = arcpy.mapping.ListLayers(mxd, "Parcels", df)[0]  #Logic try:  whereClause = ""TOWNSHIP" AND "RANGE" AND "SEC" AND "SUBCODE" AND "BLOCK" AND "LOT" = '"+Parcels+"'"  arcpy.SelectLayerByAttribute_management(lyr, "NEW_SELECTION", whereClause)  df.extent = lyr.getSelectedExtent()  df.scale = df.scale*1.1  except:  print arcpy.GetMessages()

[DanPatterson_Retired]

ands need to an individual equivalency check, you are scrimping on your coding.  Did you try one first?  then add a second?  equivalence is tested by == not = which is an assignment statement

[Anonymous User · Accepted solution]

You said that the user is supposed to enter 6 values for the query?  Your query is not constructed correctly.  If you are wanting to a value for each variable you need to use something like "TOWNSHIP" = '81' AND "RANGE" = '04' etc.  Also, what is the value at the end (the 'Parcels') ?

You could do something like this and have the where clause get printed out in the results window for verification:

#Import import arcpy, sys, traceback  try:     mxd = arcpy.mapping.MapDocument("CURRENT")     df = arcpy.mapping.ListDataFrames(mxd, "Layers")[0]     lyr = arcpy.mapping.ListLayers(mxd, "Parcels", df)[0]      fields = ["TOWNSHIP", "RANGE", "SEC", "SUBCODE", "BLOCK","LOT"]     sql_dict = {}     for field in fields:         sql_dict[arcpy.AddFieldDelimiters(lyr, field)] = arcpy.GetParameterAsText(fields.index(field))      # construct where clause     whereClause = ' AND '.join("{0} = '{1}'".format(k,v) for k,v in sql_dict.iteritems())     arcpy.AddMessage('"  %s  "' %whereClause)     arcpy.SelectLayerByAttribute_management(lyr, "NEW_SELECTION", whereClause)     df.extent = lyr.getSelectedExtent()     df.scale = df.scale*1.1  except:     # Get the traceback object     tb     = sys.exc_info()[2]     tbinfo = traceback.format_tb(tb)[0]     pymsg = "PYTHON ERRORS:\nTraceback info:\n%s\nError Info:\n%s\n" % (tbinfo, sys.exc_info()[1])     msgs  = "ArcPy ERRORS:\n%s\n" % arcpy.GetMessages(2)     arcpy.AddError(pymsg)     arcpy.AddError(msgs)

I just did a quick test with some dummy variables, the where clause was:

"  "SEC" = '36' AND "RANGE" = '04' AND "BLOCK" = '54' AND "SUBCODE" = 'NW NW' AND "TOWNSHIP" = '81' AND "LOT" = 'C'  "

Is this the type of where clause you are trying to form?  Otherwise, I am confused as to what kind of expression you are trying to build.

[JacobDrvar]

Caleb,

This is the type of where clause I was attempting to create. It was just more advanced than I anticipated.  Can you explain the sql_dict, your whereClause statement, and the traceback?

Thanks.

[JacobDrvar]

Caleb,

This is the type of whereClause that I was attempting to build.  But, was much more involved than I anticipated.  Will you please explain the sql_dict, your whereClause statement, and how your traceback works?

Thanks.

[Anonymous User]

Sure.  What I did with the sql_dict was not necessary, it was just done for efficiency.  I built a python dictionary (you can read about these [url=http://docs.python.org/2/library/stdtypes.html]here[/url])  Dictionaries store pairs known as keys and values.  So all I did was use the fields list and create a dictionary using each field as the key and grabs the parameter from the user using the arcpy.GetParameterAsText() for each user defined value for searching that field.  So for example, the dictionary will actually look like this:

the_dictionary[ key ] = value

So to form a dictionary for your fields and the user defined value I made the key be the field, and the value is a parameter entered by the user.  Also note that I used the AddFieldDelimiters to grab the appropriate delimiter.  Maybe not necessary, but this way you can always be sure the field has the correct delimeter (personal gdb's use [], file gdb's use " " ).

sql_dict[arcpy.AddFieldDelimiters(lyr, field)] = arcpy.GetParameterAsText(fields.index(field))

Once the user has input parameters the actual dictionary will look like this:

{"TOWNSHIP" : '81', "RANGE" : '04', "BLOCK" : '54', "SUBCODE" : 'NW NW', "SEC" : '36'}

So the above values corresponding to each field were values I entered using the tool and they are input into the dictionary because each value for each key is an "arcpy.GetParameterAsText()".  I filled in the correct index for the get params by finding the index of the field in the list (0-5).  I hope this makes sense.


As for the where clause, I just iterated (using .iteritems() ) through the dictionary to grab each key and value pair (i.e. "SEC" = '36' ) using the .format() where the k,v just represents the key and value.  So for each pair you see in the above dictionary, it is just putting an equal sign between them.  Then, I used .join() to join each field and value in the dictionary by the ' AND ' operator to form the expression shown at the bottom of my last post. 

The example I provided gets a little complicated, I suppose I was going for efficiency.  If you are just a beginner in Python you can always form the query in a way that makes more sense to you such as:

TOWNSHIP = arcpy.GetParameterAsText(0)
RANGE = arcpy.GetParameterAsText(1)
SEC = arcpy.GetParameterAsText(2)
SUBCODE = arcpy.GetParameterAsText(3)
BLOCK = arcpy.GetParameterAsText(4)
LOT = arcpy.GetParameterAsText(5)

whereclause = ''' "TOWNSHIP" = '{0}' AND "RANGE" = '{1}' AND "SEC" = '{2}' AND "SUBCODE" = '{3}' AND "BLOCK" = '{4}' AND "LOT" = '{5}' '''.format(TOWNSHIP, RANGE, SEC, SUBCODE, BLOCK, LOT)

This is essentially the exact same thing my dictionary and whereclause did, just with a little more typing.  But it may help you to understand how building the query works.  Using the .format() just inserts the user variables into the expression (from your arcpy.GetParameterAsText() ).

As for the traceback, that is just a standard exception that Esri uses.  I think I copied that into a text file a long time ago from the help pages somewhere.  For some reason, sometimes the arcpy.GetMessages() doesn't work for me so in these cases I use that code to get all the exceptions (regular python errors and arcpy errors).  If your original except block was working, keep using that.  I sometimes use the whole Esri example to be safe.

[JacobDrvar]

Caleb,

Thanks for taking the time to help.  The explanations are extremely useful to a new python scipter, such as myself. 

Thanks.

[RhettZufelt]

    for field in fields:
        sql_dict[arcpy.AddFieldDelimiters(lyr, field)] = arcpy.GetParameterAsText(fields.index(field))

    # construct where clause
    whereClause = ' AND '.join("{0} = '{1}'".format(k,v) for k,v in sql_dict.iteritems())

I had to give you a point for this one.  Great example (both syntax and useage) of using a dict AND join...

R_

[JamesSmith7]

How can the script be altered to change Nulls(<Null>) to Blanks("")?  The script works well until a null is present in the attribute field. 

fieldList = arcpy.ListFields(fc, "", "STRING")
 for field in fieldList:
  updateRows = arcpy.da.UpdateCursor(fc, RMS + " IS NULL", "", RMS)
 for updateRow in updateRows:
  updateRow.setValue(RMS, "")
  updateRows.updateRow(updateRow)
 del updateRow, updateRows