arcpy.Select_management invalid sql expression - VDI

Question

I am using ArcMap 10.2.2 and trying to make separate feature classes from a larger feature class using a field delimiter and SQL statement but am getting an error that the SQL statement is invalid. Here is the snippet of code I'm having a problem with:

# This script looks at a featureclass(FC)/service location(SL)
# and splits it by day for web service data preparation.
# In this manner, end users can toggle off and on days of service.


#import modules
import arcpy
import os

#create input FC/SL -- the mega FC/SL
inputFC = raw_input("Paste Route FC from ArcCatalog: ")

#set default geodatabase
arcpy.env.workspace = raw_input("Paste default file GDB from ArcCatalog: ")

#designate output location as a variable so it can be used in system path
#output filename
outLocation = arcpy.env.workspace

# Designate the type of fleet, service, commodity etc
fleetType = raw_input("Name of output (e.g. Residential_Garbage, NO SPACES!!!): ")

#make a copy of the input FC so nothing gets corrupted
arcpy.MakeFeatureLayer_management(inputFC, "Copy_inputFC")

dayList = []
while True:
    day = raw_input("Type field delimiter and press enter. Type and enter 'Done' when finished: ")
    if day.lower() == 'done':
        break
    dayList.append(day)

#Make multiple FCs based on route number
for day in dayList:
    #Add field delimitters
    delimFld = arcpy.AddFieldDelimiters("Copy_inputFC", "Exist_Partition")
    print "step1"
    if day == '1':
        dayName = 'Monday'
    elif day == '2':
        dayName = 'Tuesday'
    elif day == '3':
        dayName = 'Wednesday'
    elif day == '4':
        dayName = 'Thursday'
    else:
        dayName = 'Friday'

    #Get output name
    outName = "{0}{1}{2}".format(fleetType,"_",dayName)
    outFC = os.path.join(outLocation, outName)
    print "step2", outFC

    #If routes are numbers:
    try:
        sql = "{0}={1}".format(delimFld, day)
        arcpy.Select_analysis("Copy_inputFC", outFC, sql)

    #If routes are strings
    except:
        sql = "{0} = '{1}'".format(delimFld, day)
        arcpy.Select_analysis("Copy_inputFC", outFC, sql)

    print outName


print "Finished"

This is what the error is saying:

ExecuteError: ERROR 000358: Invalid expression "Exist_Partition" = '1'
Failed to execute (Select).

However, if I do the SQL statement in ArcMap it appears to be fine:

Answer 1

My guess is that Exist_Partition doesn't actually exist in your layer.

From your screenshot it appears that your layer datasource is a Shapefile (I'm basing this on the presence of a FID field here). A Shapefile has a maximum field length of 10 characters, however Exist_Partition has 15 characters. When I tried to create that field in my Shapefile I was told it would be shortened to Exist_Part - 10 characters long. The arcpy.AddFieldDelimiters() is also putting double quotes " " around the field name which it usually does on Shapefiles.

With my shapefile and Exist_Part field, I recreated your error message by using a trimmed down version of your code:

>>> delimFld = arcpy.AddFieldDelimiters("testpoints", "Exist_Partition")
>>> print delimFld
"Exist_Partition"
>>> day = "1"
>>> sql = "{0} = '{1}'".format(delimFld, day)
>>> print sql
"Exist_Partition" = '1'
>>> arcpy.env.workspace = 'in_memory'
>>> outLocation = arcpy.env.workspace
>>> outName = "{0}{1}{2}".format("tester","_","Monday")
>>> outFC = os.path.join(outLocation, outName)
>>> arcpy.Select_analysis("testpoints", outFC, sql)

Start Time: Mon Mar 27 18:10:15 2017
ERROR 000358: Invalid expression "Exist_Partition" = '1'
Failed to execute (Select).
Failed at Mon Mar 27 18:10:15 2017 (Elapsed Time: 0.01 seconds) 

>>> 

The expression is Invalid because the field Exist_Partition does not and can not exist in a shapefile.

This doesn't explain why it works when you enter it into the Select by Attributes window though - this does seem odd to me. The screenshot doesn't show double-quotes " " around the field names in the Select by Attributes window though, which mine does on my shapefile.

Answer 2

I ran the script again this morning and it worked perfectly. I added arcpy.env.overwriteOutput = Trueafter setting the workspace with the user prompt. I took it out for testing and was receiving the error with it included so I'm confident it's unrelated to the problem. Otherwise it's exactly the same as my original post.

What was causing it was field mapping issues on my citrix VDI. The program either reads the directory where the data is stored as C:\RS_Data\Workspace or N:\Workspace. They are actually the same location depending on if you navigate through ArcMap or Windows Explorer, respectively.

This drive lives on a server with ArcMap and all of it's associated programs installed on it. Everything else I use is on a citrix server which I access through VDI. The latter acts as my main desktop and I access ArcMap desktop through it, reaching a desktop through another desktop. A virtual one at that. In certain transactions Python can't find certain files because the path names get mixed up.

In short, the problem was solved by terminating my session and logging back on which remapped the drives and allowed the program to find the feature layer.

I apologize for the long schpeil but I hope this will help others that encounter issues using ArcMap on VDI. I also added a virtual machine tag to the post. Thanks to everyone who helped me troubleshoot this.