topic Re: SQL Error arcpy.FeatureClassToFeatureClass in Python Questions

SQL Error arcpy.FeatureClassToFeatureClass

LloydBronn — Fri, 10 Dec 2021 22:21:06 GMT

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

Re: SQL Error arcpy.FeatureClassToFeatureClass

IanMurray — Thu, 04 May 2017 20:39:22 GMT

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

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

Re: SQL Error arcpy.FeatureClassToFeatureClass

LloydBronn — Fri, 10 Dec 2021 22:21:09 GMT

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

Re: SQL Error arcpy.FeatureClassToFeatureClass

IanMurray — Thu, 04 May 2017 21:24:10 GMT

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

Re: SQL Error arcpy.FeatureClassToFeatureClass

LloydBronn — Thu, 04 May 2017 21:27:07 GMT

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)

Re: SQL Error arcpy.FeatureClassToFeatureClass

IanMurray — Thu, 04 May 2017 21:51:26 GMT

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.

Re: SQL Error arcpy.FeatureClassToFeatureClass

LloydBronn — Thu, 04 May 2017 21:56:01 GMT

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

Re: SQL Error arcpy.FeatureClassToFeatureClass

IanMurray — Thu, 04 May 2017 21:59:53 GMT

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

Re: SQL Error arcpy.FeatureClassToFeatureClass

IanMurray — Thu, 04 May 2017 22:06:41 GMT

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.

Re: SQL Error arcpy.FeatureClassToFeatureClass

DanPatterson_Retired — Fri, 10 Dec 2021 22:21:12 GMT

if the tuple is singular what about slicing it

field = 'a'
top_cities = (10,)
query = "\"{0}\" == {1}".format(field,top_cities)
query
'"a" == (10,)'  # wrong

query = "\"{0}\" == {1}".format(field,top_cities[0])
query
'"a" == 10'  # right??‍‍‍‍‍‍‍‍‍

Re: SQL Error arcpy.FeatureClassToFeatureClass

XanderBakker — Fri, 10 Dec 2021 22:21:15 GMT

To avoid using escape characters, I normally use the arcpy.AddFieldDelimiters to take care of the field delimiters based on the type of data source:

datasource = r'C:\GeoNet\GW1\rectangles_v01.shp'
fld_name = 'MyField'
fld_where = arcpy.AddFieldDelimiters(datasource, fld_name)
print fld_where
# "MyField"‍‍‍‍‍

When you create the top cities this is a list:

lst_values = [10, 14, 7, 11, 12, 9]
top_cities = sorted(lst_values)[-4:]
print type(top_cities), top_cities
# <type 'list'> [10, 11, 12, 14]‍‍‍‍

To separate the list of values with commas you could use join (but this requires the values to be string to I used a list comprehension for that:

values = ', '.join([str(a) for a in top_cities])
print values
# 10, 11, 12, 14‍‍‍

To create the final where clause you can simply use this:

query = '{0} IN ({1})'.format(fld_where, values)
print query
# "MyField" IN (10, 11, 12, 14)‍‍‍

In case your values would be strings (just as an example) this could be done like this:

values = "', '".join(top_cities)
print values
# a', 'b', 'c', 'd
‍‍‍

And the query would be formed like this:

query = "{0} IN ('{1}')".format(fld_where, values)
print query
# "MyField" IN ('a', 'b', 'c', 'd')‍‍‍

Re: SQL Error arcpy.FeatureClassToFeatureClass

LloydBronn — Mon, 08 May 2017 14:43:37 GMT

This returns "Pop_MAX" IN ([328585]) as above and I still get the SQL error.

Re: SQL Error arcpy.FeatureClassToFeatureClass

LloydBronn — Mon, 08 May 2017 14:53:22 GMT

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.