2

I have a text file of project names used in various workflows, it is structured as the following:

Project_2020_1m_20200212_0900
Project_2020_1m_20200213_0900

I want to use the text file to create an SQL statement, and then use that statement in a SelectLayerbyAttribute_management, and then CalculateField_management. I can create the statement but I don't seem to be able to make the selection (calculate fields is occurring on all the projects).

#create the sql selection string    
datasetslist = datasets.txt

#create empty list
selection_list = []

#append to selection list "Name = Project"- 
for line in datasetslist:
    selection_list.append("Name = "+"'"+ line+"'")

#from selection list create a sql statement/string '"Name = Project or Name =...."
selection_string = ' or '.join([str(elem) for elem in selection_list])

#use sql to select from a layer   
footprint = os.path.join(folder,gdb,"footprint")
footprint_select = arcpy.SelectLayerByAttribute_management(footprint, "NEW_SELECTION", selection_string)

#calculate on selection    
arcpy.CalculateField_management(footprint_select,"TITLE",title,"PYTHON")

I assume I can't use the variable as an argument in the SelectLayerbyAttribute_management, however I'm not sure of any other approach. Options?

2
  • 3
    SelectLayerByAttribute uses a WHERE clause, but not the full SQL statement. Your code sample would not run, so you probably need to work on the Python first. But you really ought to look at using an IN construct (much shorter and more legible).
    – Vince
    Nov 4, 2020 at 1:07
  • 1
    A good way to get started composing python scripts that supply parameters to arcpy routines is to execute the command from the ArcPro toolbox UI manually, then copy the command as python, and reverse-engineer the python variable(s) to be supplied to the command from inspection. What Vince suggested above is also helpful; try using the where clause "LayerName in ('foo','bar','beep','bop')" but first do it manually to get an example python. Nov 4, 2020 at 1:19

1 Answer 1

4

I think you should make use of the 'IN' operator in SQL FIELD IN (value1, value2, value3..) for your where clause in make feature layer. Your text file has no quotes but has a dot in it so it's not a variable, I expect you've obfuscated here.

Give it a try this way:

datasetslist = 'datasets.txt' # you are missing quotes, you probably need the full path

#create empty list
selection_list = []
with open(datasetslist,'r') as DSList:
    for ThisLine in DSList:
        selection_list.append(ThisLine)

# make the query layer using SQL in operator
QF = "NAME IN ('{}')".format("','".join(selection_list))

# you need a layer for Select Layer by Attribute, not a feature class
footprint_select = arcpy.MakeFeatureLayer_management(os.path.join(folder,gdb,"footprint"),'footprint_select_layer',QF)

#calculate on selection    
arcpy.CalculateField_management(footprint_select,"TITLE",title,"PYTHON")

Note that your variable title is undefined in this code block, I hope it's defined somewhere prior to this code block.

The with block opens the file and then automatically closes it when it's done, this method is IMO better than the old model: Open, Read and Close. All the lines coming from this file iterator are strings so you don't need to str(ThisLine), if you're expecting int or float you need to use int(ThisLine) and float(ThisLine) respectively.

1
  • Fantastic, that cleaned it up a lot. Got it working perfectly! thank you.
    – mapface
    Nov 4, 2020 at 3:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.