SQL Error arcpy.FeatureClassToFeatureClass

[LloydBronn]

I have a Python GP tool on our Arc GIS Server that generates a report based on a polygon drawn on a web map. I have a query that sorts up to the top 15 cities in the region by population and creates a temporary shapefile of these cities. Everything works fine until I have a small enough region that only has one populated city. I get this error from the server logs:

An invalid SQL statement was used. An invalid SQL statement was used. The Project method cannot do a datum transformation. Failed to execute (FeatureClassToFeatureClass). 

I do not get this error if there are two or more populated cites in the region. Here is the Python snippet with the query. It's set to skip the temporary shapefile creation if there are no cities in the region. 

cities = temp_path + region_name + "\\" + "cities.shp"

values = [row[0] for row in arcpy.da.SearchCursor(cities, field)]
        top_cities = sorted(values)[-15:] #adjust max rows count    
        query = "\"{0}\" in {1}".format(field, tuple(top_cities))
        
        check_list = [cities,]

        for shapefile in check_list:
            if arcpy.GetCount_management(shapefile)[0] != 0:
                arcpy.FeatureClassToFeatureClass_conversion(cities,temp_path + region_name,"pop.shp",query)
            else:
                continue‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[IanMurray]

It has to do with how tuples function.  If you put only a single string inside your tuple(), it will return a tuple of the string broken up into its individual characters.

https://docs.python.org/2/library/functions.html#tuple

To have a tuple with a single string in it, you would need to have a comma in the parathenses after your string.

http://stackoverflow.com/questions/12876177/why-do-tuples-with-only-one-element-get-converted-to-str...

I would find a way to make sure your list of values ends in a comma, regardless of the number of values in it, that way even with a single value it would end up a true tuple

https://wiki.python.org/moin/TupleSyntax

[LloydBronn]

I figured as much. But the output of this short test script is this: (328585,) which is a true tuple, no?

import os
import arcpy

cities = "R:\\temp\\reno\\cities.shp"

field = "POP_MAX"

values = [row[0] for row in arcpy.da.SearchCursor(cities, field)]
top_cities = sorted(values)[-15:] #adjust max rows count    
query = "\"{0}\" in {1}".format(field, tuple(top_cities))
    
    
print tuple(top_cities)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[IanMurray]

In that case I think SQL isn't liking the extraneous comma in the expression and is causing the expression to fail.

[LloydBronn]

I tried taking out the tuple function and just using this query and it returns a single value list. I still get the SQL error. Would it be possible to put in a second value of zero in the tuple?

query = "\"{0}\" in {1}".format(field,top_cities)

[IanMurray]

I printed that query out and it doesn't keep the parentheses for the list, so the IN function doesn't work.

Try this maybe:

query = "\"{}\" in {}".format("Pop_MAX", "({})".format(toplist))

returned this for a single value

"Pop_MAX" in (328585)

and returned this for multiple

"Pop_MAX" in ((328585, 568494))

The trick of it is to keep the toplist in parathenses regardless of if it is a list or not in the SQL expression.  If you end up with the extra set of parentheses it shouldn't be an issue, but better than not having a single set.

[LloydBronn]

It returns this for me, "Pop_MAX" in ([328585]) and I still get the SQL error

[IanMurray]

Yea I just realized I made a tuple not a list when testing multiple values......

[IanMurray]

You could just turn the list into a string, then remove the brackets and then use that string in the expression.....

print str(toplist).strip("[]")

returns:

328585, 568494

Either way this is a silly amount of work for what should be something very straightforward.

[LloydBronn · Accepted solution]

You were right about the parenthesis. This works:

query =  "\"{0}\" in ({1})".format(field, top_cities[0])‍

Now I just need to add an if statement to check if there is only one city, use this query. Otherwise use the tuple query.