I would like to create a subset of a table based on values in a Python list that I created from a search cursor. I have not been able to get the where_clause correct and keep getting errors. Here is my code:

pu = 'PlanningUnits'
hab = 'lu_Habitat'

with arcpy.da.SearchCursor(pu, "unique_id") as cursor:
    selected_pu = sorted({row[0] for row in cursor})

where_clause = """{} IN selected_pu""".format(arcpy.AddFieldDelimiters(hab, "unique_id"))
selected_hab = arcpy.TableToTable_conversion(hab, r'in_memory', "selected_hab", where_clause)

I have also tried the following as a where_clause:

where_clause = """{} IN """.format(arcpy.AddFieldDelimiters(hab, "unique_id")) + str(tuple(selected_pu))

In both cases, I am getting the following error:

ExecuteError: ERROR 999999: Error executing function. An invalid SQL statement was used. An invalid SQL statement was used.

I believe the issue is in your where clause:


where_clause = """{} IN selected_pu""".format(arcpy.AddFieldDelimiters(hab, "unique_id"))

should be:

where_clause = """{} IN {}""".format(arcpy.AddFieldDelimiters(hab, "unique_id"), selected_pu)

That should work assuming your unique_id field has string values, as you are casting them all to string to add single quotes around them.

| improve this answer | |

You need to come up with something like this...

for a string field: unique_id IN ('a1', 'a2', 'b4')

for a numeric field: unique_id IN (7, 9, 56)

So first create your list of values:

with arcpy.da.SearchCursor(pu, "unique_id") as cursor:
    selected_pu = sorted([row[0] for row in cursor])

Then, for string:

selectedStr = "', '".join (selected_pu)
where_clause = "{} IN ('{}')".format(arcpy.AddFieldDelimiters(hab, "unique_id"), selectedStr)

or for numeric:

selectedStr = ", ".join (map (str, selected_pu))
where_clause = "{} IN ({})".format(arcpy.AddFieldDelimiters(hab, "unique_id"), selectedStr)


>>> li = [1,2,3]
>>> stng = ", ".join (map (str, li))
>>> stng
'1, 2, 3'
>>> sql = "{} IN ({})".format ("unique_id", stng)
>>> sql
'unique_id IN (1, 2, 3)'
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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