Hi there. I'm having an issue using a list in an SQL expression for FeatureClasstoFeatureClass export. Here is what I have so far. I'm working with a value list from user input.
cab_list = arcpy.GetParameterAsText(0)
olt_list = cab_list.replace(";", ",")
olt_list = olt_list.split(',')
str_list = '"{}"'.format('", "'.join(olt_list))
arcpy.FeatureClassToFeatureClass_conversion(outcabs, scratch, 'Cabs_Boundary', f"cab_id IN ({str_list})")
The expression evaluates to
cab_id IN ("ESC-C02", "ESC-C04", "SDY-HUT")
I'm getting this error:
ERROR 160144: An expected Field was not found or could not be retrieved properly.
I know that cab_id is the field name (not alias) so I'm guessing it has to be an issue with how I'm formatting the SQL clause. Any input is appreciated.
Solved! Go to Solution.
That did it. I just had to change that line to the inverse as follows -
str_list = "'{}'".format("','".join(olt_list))
Thank you and everyone else for the tips!
Typically SQL clauses in python look something like this:
""""roadclass" = 2"""
but in terms of a where in clause, try the expression in either map or pro and copy it into the script to see if it works.
If a SQL expression isn't working, then you can try the SearchCursor option to find the values that you need.
Check your SQL documentation for proper query syntax. You might try replacing the double quote with a single one, so you get:
cab_id IN ('ESC-C02', 'ESC-C04', 'SDY-HUT')
Thanks for the input. I think this might be the case but I'm having a hard time formatting this line to use single quotes.
str_list = '"{}"'.format('", "'.join(olt_list))
That did it. I just had to change that line to the inverse as follows -
str_list = "'{}'".format("','".join(olt_list))
Thank you and everyone else for the tips!
How about:
origString = 'a;b;c'
newList = origString.split(';')
#newList
#Out[16]: ['a', 'b', 'c']
That'll get you a list rather than the tuple which might be easier for the SQL statement.