Using Variables retrieved from a cursor or list in the where clause for SelectLayerByAttribute python 3

BryceBarth — Sat, 11 Dec 2021 14:38:39 GMT

I have a script that takes an excel, imports it as a table then runs a query to get the needed rows and exports them to a new table. After that a search cursor runs through the table and creates a list of values. It then loops through the list of values and uses the values as an input for the Where Clause for select by attribute to select the correct polygon. That permit polygon is then used to select poles from another feature class and then export those poles as a new feature class named by the value used to select the permit polygon. The problem is passing the value from the list of values into the where clause of the SelectLayerByAttribute_management tool.

import arcpy
import datetime

# Variables
time = datetime.date.today()
raw = r"C:\Users\BB0322\Desktop\Projects\MRE_Coversheet\KC\MRE_Table.xlsx"
mre_table = r"C:\Users\BB0322\Desktop\Projects\MRE_Coversheet\KC\KC_MRE.gdb\MRE_Tb"
permits = r"C:\Users\BB0322\Desktop\Projects\MRE_Coversheet\KC\KC_MRE.gdb\Permits"
Poles = r"C:\Users\BB0322\Desktop\Projects\MRE_Coversheet\KC\KC_MRE.gdb\Poles"
work_table = r"C:\Users\BB0322\Desktop\Projects\MRE_Coversheet\KC\KC_MRE.gdb\MRE_workTB"
geoDB = r"C:\Users\BB0322\Desktop\Projects\MRE_Coversheet\KC\KC_MRE.gdb"

arcpy.env.workspace = r"C:\Users\BB0322\Desktop\Projects\MRE_Coversheet\KC\KC_MRE.gdb"
arcpy.env.overwriteOutput = True


try:
    print("Creating Table: " + mre_table)
    arcpy.ExcelToTable_conversion(raw, mre_table, "Sheet1")

    mre_tview = arcpy.MakeTableView_management(mre_table, "MRE_TableView")
    arcpy.SelectLayerByAttribute_management(mre_tview, "NEW_SELECTION", "Ticket_Number IS NOT NULL", None)
    arcpy.SelectLayerByAttribute_management(mre_tview, "REMOVE_FROM_SELECTION", "Permit_Name = ' '", None)
    arcpy.CopyRows_management(mre_tview, work_table)

    permitview = arcpy.MakeFeatureLayer_management(permits, "Permits_Layer")
    poleview = arcpy.MakeFeatureLayer_management(Poles, "Poles_Layer")
#   Creating a list of permit names to pass into the select by attributes for the permit grids
    AllValues = []
    with arcpy.da.SearchCursor(work_table, ("Permit_Name", "Ticket_Number")) as search:
        for row in search:
            if row[0] not in AllValues:
                AllValues.append(row[0])

#   looping through the list and selecting a polygon then using that polygon to select poles located within and exporting those poles into individual feature Classes
    for Value in AllValues:
        print(Value)
        itsa = "permit_name = {}".format(Value)
        print(itsa)
        expression = str(itsa)
        permitSelect = arcpy.SelectLayerByAttribute_management(permitview, "NEW_SELECTION", expression, None)
        permitPoly = arcpy.MakeFeatureLayer_management(permitSelect, "permitPoly_" + Value)
        poleSelect = arcpy.SelectLayerByLocation_management(poleview, "INTERSECT", permitPoly, "30 FEET", "NEW_SELECTION", "")
        arcpy.CopyFeatures_management(poleSelect, geoDB + "\\" + Value)


except Exception as e:
    print("ERROR: " + e.args[0])

The error that I am receiving is:

ERROR: ERROR 000358: Invalid expression
Failed to execute (SelectLayerByAttribute).

Thanks for the help.

Re: Using Variables retrieved from a cursor or list in the where clause for SelectLayerByAttribute python 3

JoshuaBixby — Fri, 02 Aug 2019 21:15:31 GMT

Looking at:

itsa = "permit_name = {}".format(Value)

What data type is Value? If string, you need to put quotes around it in the SQL:

itsa = "permit_name = '{}'".format(Value)‍

topic Using Variables retrieved from a cursor or list in the where clause for SelectLayerByAttribute python 3 in Telecommunications Questions

Using Variables retrieved from a cursor or list in the where clause for SelectLayerByAttribute python 3

Re: Using Variables retrieved from a cursor or list in the where clause for SelectLayerByAttribute python 3