Using a list in SQL expression for FeatureClasstoFeatureClass

810
5
Jump to solution
08-10-2021 09:47 AM
ChrisGAEG
Occasional Contributor

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. 

 

0 Kudos
1 Solution

Accepted Solutions
ChrisGAEG
Occasional Contributor

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! 

View solution in original post

0 Kudos
5 Replies
RPGIS
by
Occasional Contributor III

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.

0 Kudos
RandyBurton
MVP Alum

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

 

ChrisGAEG
Occasional Contributor

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))
0 Kudos
ChrisGAEG
Occasional Contributor

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! 

0 Kudos
JoeBorgione
MVP Emeritus

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.

That should just about do it....
0 Kudos