Heatmap script gives ERROR 000358 from SQL expression using ArcPy with ArcMap

Question

I have a heatmap script that will eventually print 10 PDF maps. At the moment it is failing at the SQL expression in the Make Feature Layer right before Kernel Density Raster creation because the column datatype is string. I'm having trouble changing it to an integer in the loop.

Traceback (most recent call last): File "\coacd.org\dfs\SWS\Code Enforcement\ACD Admin Operations\GIS\Operations\Council District Analysis Reporting\Auto_HeatMap_WIP\test.py", line 58, in int(stringA) ValueError: invalid literal for int() with base 10: '%d'

Here is the part of the script that is failing:

arcpy.CheckOutExtension('Spatial')
#use 'CURRENT' if running from arcmap, when published use MXD on disk
mxd = arcpy.mapping.MapDocument(workspace+"\\working_environment.mxd")
df = arcpy.mapping.ListDataFrames(mxd)[0] # address location data frame and turn on select layers
df.zoomToSelectedFeatures()
arcpy.env.extent = df.extent #set extent to current one
arcpy.env.overwriteOutput = True #overwrite existing files
my_point_features = autoLayerGDB +"\\coded_xy_points_FIPS4203"
scratch_output = workspace+"scratch"
 
#Select each district via script
 
 for i in range(1, 11):
 stringA = '%d'
 int(stringA)
 my_clause = 'COUNCILDISTRICT =' + stringA % i
 my_filename = "kden_d%d" % i
 arcpy.MakeFeatureLayer_management(my_point_features, 'templyr', where_clause=my_clause,)
 outKDens = KernelDensity("templyr", "None", cell_size=100)
 outKDens.save(autoLayerGDB+"\\"+my_filename)
 print("\tRaster created for District %d" % i)
 

I also tried this:

for i in range(1, 11):
 my_clause = 'COUNCILDISTRICT = "%d"' % i
 my_filename = "kden_d%d" % i
 arcpy.MakeFeatureLayer_management(my_point_features, 'templyr', where_clause=my_clause,)
 outKDens = KernelDensity("templyr", "None", cell_size=100)
 outKDens.save(autoLayerGDB+"\\"+my_filename)
 print("\tRaster created for District %d" % i)

But received this error message:

raceback (most recent call last): File "\coacd.org\dfs\SWS\Code Enforcement\ACD Admin Operations\GIS\Operations\Council District Analysis Reporting\Auto_HeatMap_WIP\test.py", line 59, in arcpy.MakeFeatureLayer_management(my_point_features, 'templyr', where_clause=my_clause,) File "C:\Program Files (x86)\ArcGIS\Desktop10.5\ArcPy\arcpy\management.py", line 6997, in MakeFeatureLayer raise e arcgisscripting.ExecuteError: ERROR 000358: Invalid expression COUNCILDISTRICT = "1" Failed to execute (MakeFeatureLayer).

I found gis.stackexchange.com/questions/137517/... and docs.microsoft.com/en-us/sql/odbc/microsoft/.... Based on this information I changed the schema file to interpret the COUNCILDISTRICT Collumn as "short" instead of "text" and it worked

• Bera: selection_subset is not a layer, it is a result object. So if KernelDensity wants a layer it will fail. Look at the syntax for MakeFeatureLayer. Second parameter is output layer and you are trying to store this in a geodatabase which is not possible. Use some string, for example 'templayer' and then pass the same string to Kernel..
• Anthony Stokes: I may not fully understand but I made an edit based on your suggestion. The error has been updated.
• Anthony Stokes: Still getting an error regarding the MakeFeatureLayer before KernelDensity. The error in the body has been updated.
• Bera: You have added to much code. It should be the shortest possible snippet to reproduce the error. You are still trying to save the layer in a database which is not possible. A layer is only temporary and should be stored in memory. Try something like: arcpy.MakeFeatureLayer_management(my_point_features, 'templyr', "{0} IS NOT NULL".format(arcpy.AddFieldDelimiters(my_point_features, 'COUNCILDISTRICT')). Then use the layer like: KernelDensity('templyr'...
• Anthony Stokes: I tried that and it gave me a syntax error for OutKDens in line that says outKDens = KernelDensity("templayer", "None", cell_size=100). Also I edited the code so that only the relevant part is displayed.

Answer 1

I found the links, Cannot convert string to int using ArcPy Calculate Field? and https://docs.microsoft.com/en-us/sql/odbc/microsoft/schema-ini-file-text-file-driver?view=sql-server-2017. Based on this information I changed the schema file to interpret the COUNCILDISTRICT Collumn as "short" instead of "text" and it worked

1. Find the schema.ini file that is next to the .csv file.

2. Open the file and check what it says:

   [SOURCE_DATA.csv] Col14=COUNCILDISTRICT text

3. Change the "text" to "short" so that it reads:

   [SOURCE_DATA.csv] Col14=COUNCILDISTRICT Short

4. Save the file and run the script.

And now it works.