SQL Error arcpy.FeatureClassToFeatureClass

866
12
Jump to solution
05-04-2017 01:02 PM
LloydBronn
Occasional Contributor II

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‍‍‍‍‍‍‍‍‍‍‍‍‍
0 Kudos
1 Solution

Accepted Solutions
LloydBronn
Occasional Contributor II

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. 

View solution in original post

0 Kudos
12 Replies
IanMurray
Frequent Contributor

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
Occasional Contributor II

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)‍‍‍‍‍‍‍‍‍‍‍‍‍
0 Kudos
IanMurray
Frequent Contributor

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

0 Kudos
LloydBronn
Occasional Contributor II

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)
0 Kudos
IanMurray
Frequent Contributor

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
Occasional Contributor II

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

0 Kudos
IanMurray
Frequent Contributor

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

0 Kudos
IanMurray
Frequent Contributor

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.

0 Kudos
LloydBronn
Occasional Contributor II

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. 

View solution in original post

0 Kudos