Select Layer by Attribute using an expression failing

807
5
08-15-2017 11:12 AM
DonnaRiebe
New Contributor III

Greetings, I am writing some code that puts in an expression into a SelectLayerByAttribute statement and am running into a problem. I am generating a running sequence of numbers (closNum) that are made into text. (using an SDE geodatabase FYI)

closNum = "'" + '{:05d}'.format(i) + "'"

When I print that out it looks like closNum = '00000'

I generate an expression statement expression = '"' + ' LABEL = ' + "'" + closNum + "'"+ '"' which prints out as " LABEL = '00000'"

What I would like to do is put that into my layer selection query arcpy.SelectLayerByAttribute_management(closureLyr, "NEW_SELECTION", expression)

So when I run that, I get an invalid expression error, but if I run arcpy.SelectLayerByAttribute_management(closureLyr, "NEW_SELECTION", " LABEL = '00000'"), it works just fine.

Somehow I am missing something but I can't figure out what.

Any help would be awesome

Actual code below

import string, arcpy
from arcpy import env
arcpy.env.overwriteOutput = True

inFC = 'Database Connections/GISTLMD on DNRHLN6341 as TLMD_FMB_MGR - FMB_RoadInventory.sde/GISTLMD.TLMD_DO.FMB_RoadClosures'
closureLyr = 'closureLyr'
count = 1
arcpy.MakeFeatureLayer_management(inFC,closureLyr)


for i in range(0,10,1):
#  print '{:05d}'.format(i)
 closNum = "'" +  '{:05d}'.format(i) + "'"
 expression = '"LABEL = '  +  closNum +  '"'
 
 arcpy.AddMessage(expression)
 arcpy.SelectLayerByAttribute_management(closureLyr, "NEW_SELECTION", expression)
 # arcpy.SelectLayerByAttribute_management(closureLyr, "NEW_SELECTION", "LABEL = '00000'")
 result = arcpy.GetCount_management(closureLyr)
 cnt = int(result.getOutput(0))
 if cnt == 0:
  arcpy.AddMessage(expression + ' is missing')
  count = count + 1
 #count = count + 1
 if count == 5 :
  break

Donna

0 Kudos
5 Replies
MitchHolley1
MVP Regular Contributor

Is the overall goal to simply count the instances of a certain value in a field?  If so, I would just use a search cursor to count the values. 

0 Kudos
DonnaRiebe
New Contributor III

I am just trying to flag gaps in the sequence so when I get new records I can fill in the gaps.

d.

0 Kudos
MitchHolley1
MVP Regular Contributor

So, if a closNum is not 00000, 11111, 22222, etc... than it needs to be updated?

0 Kudos
DonnaRiebe
New Contributor III

Yes, I then equipped with a list of labels that are aren't currently being used, as field folk add records, I can add a unique label to the record. 

0 Kudos
DonnaRiebe
New Contributor III

I finally figured out what the problem was, the expression statement wasn't formatted correctly.

original - expression = '"LABEL = '  +  closNum +  '"' since the orginal closNum variable had the single quotes already in it.

revised (after I removed the single quotes from the closNum variable) - expression ="Label = \' " + closNum + "\'"

Thanks

Donna Riebe

Montana DNRC OIT/GIS