Debugging code to export .shp that gives invalid SQL statement was used?

Question

I have a shapefile composed of a number of polygons. The shapefile has table column entitled as name (field: NAME).

How can I export with ArcPy each polygon so I get a shapefile for each one and it's name (ex: name.shp) corresponds with the ones in the table column?

I've attempted to use the following code:

import arcpy
shp = "c:/temp/folder/shapefile.shp"
TargetFolder = "c:/temp/folder"
query = """"NAME" > 2"""
with arcpy.da.SearchCursor(shp, ["NAME","SHAPE@"], query) as cursor:
    for row in cursor:
        arcpy.FeatureClassToFeatureClass_conversion(row[0], TargetFolder, row[1])

I get the error code: An invalid SQL statement was used.

I used code from Export to SHP with ArcPy with query as the basis for mine.

NAME > 2 is a numeric operator which you're trying to implement on a string field, this is why it's complaining. What is it that you think this query is supposed to do? — Michael Stimson, Oct 26 '18 at 02:17

score 1 · Accepted Answer · answered Oct 26 '18 at 02:14

This is some working code that I have modified (simplified) to suit your purpose, the original is more generic but may be confusing:

import os, sys, arcpy

InShp     = "c:/temp/folder/shapefile.shp" # from your values
OutFolder = "c:/temp/folder"
Field     = "NAME"

UValues = [] # an empty list, then recurse and add each unique value
with arcpy.da.SearchCursor(InShp,Field,Field + " Is not null") as sCur:
    for sRow in sCur:
        if sRow[0] not in UValues:
            UValues.append(sRow[0])

for ThisValue in UValues: # loop through the values
    ShpName = "{}\\{}.shp".format(OutFolder,ThisValue) # generate the shapefile name from the 'name' field value

    arcpy.AddMessage("Exporting {} to {}".format(ThisValue,ShpName)) # not necessary but nice to have
    arcpy.Select_analysis(InShp,ShpName,"{} = '{}'".format(Field,ThisValue))

First, using a search cursor get all the unique values for 'Name' field (note: case sensitive, which can cause problems later), then iterate the unique values and use select to export each using a where clause to a shapefile - named after the value in the name so make sure there are valid shapefile names (don't use non alphanumeric characters in your names).

score -1 · Answer 2 · edited Oct 26 '18 at 04:04

-1

Assuming that:

query = '"NAME"> ' + str(2)

does not work then, like @Michael Stimson said, it's probably an issue of NAME being a text field. Maybe try basing it on OID:

with arcpy.da.SearchCursor(shp, ['OID@']) as cursor:
    for row in cursor:
        feature_number = row[0]
        if feature_number > 2:
            shp_out = 'OutName_' + str(feature_number) + '.shp'
            arcpy.SplitByAttributes_analysis(shp_out, TargetFolder, ['NAME'])

edited Oct 26 '18 at 04:04

PolyGeo

65,136
29
109
338

answered Oct 26 '18 at 01:53

a11

940
10
22

Unfortunately, it didn't work. I still receive the same error code: "An invalid SQL statement was used." – complexsets Oct 26 '18 at 02:09
2

Note that the SplitByAttributes_analysis tool is new at 10.5, users of 10.4 and previous versions don't have access to this tool. I thought I'd mention it here before someone tries to utilize this code and gets object has no property errors that are hard to diagnose. – Michael Stimson Oct 26 '18 at 03:27

Debugging code to export .shp that gives invalid SQL statement was used?

2 Answers2