topic Re: Using a list in SQL expression for FeatureClasstoFeatureClass in Python Questions

Using a list in SQL expression for FeatureClasstoFeatureClass

ChrisGAEG — Tue, 10 Aug 2021 16:47:22 GMT

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.

Re: Using a list in SQL expression for FeatureClasstoFeatureClass

RPGIS — Tue, 10 Aug 2021 17:13:35 GMT

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.

Re: Using a list in SQL expression for FeatureClasstoFeatureClass

RandyBurton — Tue, 10 Aug 2021 17:16:54 GMT

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

Re: Using a list in SQL expression for FeatureClasstoFeatureClass

JoeBorgione — Tue, 10 Aug 2021 17:17:37 GMT

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.

Re: Using a list in SQL expression for FeatureClasstoFeatureClass

ChrisGAEG — Tue, 10 Aug 2021 18:35:39 GMT

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

Re: Using a list in SQL expression for FeatureClasstoFeatureClass

ChrisGAEG — Tue, 10 Aug 2021 18:42:46 GMT

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!