Selecting table records by attribute if in Python list

Question

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.

Answer 1

I believe the issue is in your where clause:

This:

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.

Answer 2

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)

Example:

>>> 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)'
>>>