topic Re: An invalid SQL statement was used in Geoprocessing Questions

An invalid SQL statement was used

hamidmechouet — Thu, 10 Jun 2021 08:48:34 GMT

Hello, I created a function to round the values of the results on my script which queries the atlases by selecting a layout, but when executing it I have the following error: An invalid SQL statement was used.
here is the function:

for field in liste:
with arcpy.da.UpdateCursor(r'in_memory/resultats_table', [field]) as cursor:
for row in cursor:
if row[0] == None:
pass
else:
row[0] = round(row[0], 2)
cursor.updateRow(row)

Re: An invalid SQL statement was used

DanPatterson — Thu, 10 Jun 2021 09:05:21 GMT

Code formatting ... the Community Version - Esri Community

where is "liste" defined? What is "field".

Code formatting will allow one to assess syntax errors.

Finally don't use row[0] == None.

If you want to be explicit use

if row[0] is None:

Re: An invalid SQL statement was used

hamidmechouet — Thu, 10 Jun 2021 09:43:02 GMT

Hello, thanks for your reply, I used row is none but I still have the same error.
here is the code for the full script :

# Import system modules
import arcpy
from arcpy import env
from arcpy.sa import *

# Set local variables
input_layout = arcpy.GetParameter(0)

arcpy.CopyFeatures_management(input_layout, r'in_memory/resultats_table')

raster = [
[r"\speed100m.tif", "speed100m"],

liste = ["speed100m"]

texte = """
<b>AWS</b>
<ul>
<li>100m </li>
</ul>

# Execute ExtractValuesToPoints
arcpy.sa.ExtractMultiValuesToPoints(r'in_memory/resultats_table', raster, "NONE")
# arrondir les colonnes via une liste
for field in liste:
with arcpy.da.UpdateCursor(r'in_memory/resultats_table', [field]) as cursor:
for row in cursor:
if row[0] is None:
pass
else:
row[0] = round(row[0], 2)
cursor.updateRow(row)

# créer les champs 'longitude_wgs84dd' et 'latitude_wgs84dd'
arcpy.management.AddField(r'in_memory/resultats_table', "longitude_wgs84dd", "DOUBLE", 11, 8, None, None, "NULLABLE", "NON_REQUIRED", None)
arcpy.management.AddField(r'in_memory/resultats_table', "latitude_wgs84dd", "DOUBLE", 11, 8, None, None, "NULLABLE", "NON_REQUIRED", None)
# calculer les champs 'longitude_wgs84dd' et 'latitude_wgs84dd'
arcpy.management.CalculateGeometryAttributes(r'in_memory/resultats_table', "longitude_wgs84dd POINT_X", None, None, "GEOGCS['GCS_WGS_1984',DATUM['D_WGS_1984',SPHEROID['WGS_1984',6378137.0,298.257223563]],PRIMEM['Greenwich',0.0],UNIT['Degree',0.0174532925199433]]")
arcpy.management.CalculateGeometryAttributes(r'in_memory/resultats_table', "latitude_wgs84dd POINT_Y", None, None, "GEOGCS['GCS_WGS_1984',DATUM['D_WGS_1984',SPHEROID['WGS_1984',6378137.0,298.257223563]],PRIMEM['Greenwich',0.0],UNIT['Degree',0.0174532925199433]]")

# Afficher les résultats
arcpy.SetParameter(1, texte) # résultat string
arcpy.SetParameter(2, r'in_memory/resultats_table') # résultat table

arcpy.CopyFeatures_management(r'in_memory/resultats_table', r'in_memory/resultats_feature')
arcpy.SetParameter(3, r'in_memory/resultats_feature') # résultat FL

Re: An invalid SQL statement was used

JoshuaBixby — Thu, 10 Jun 2021 12:52:20 GMT

As Dan pointed out earlier, using code highlighting/formatting is helpful. Also, what line is the error happening on because I don't see where you are using an SQL query at all. Also, please provide the traceback of the error message along with the the error.

Re: An invalid SQL statement was used

hamidmechouet — Fri, 11 Jun 2021 07:56:11 GMT

Re: An invalid SQL statement was used

hamidmechouet — Fri, 11 Jun 2021 08:03:12 GMT

Re: An invalid SQL statement was used

JohannesLindner — Fri, 11 Jun 2021 08:20:11 GMT

Please check that the variable field contains the actual field name and not an alias.

# from your code above: liste = ["speed100m"] # each element of liste has to be an actual field name, not an alias!

Re: An invalid SQL statement was used

hamidmechouet — Fri, 11 Jun 2021 08:32:26 GMT

I didn't understand what you mean, can you explain to me please ?.

Re: An invalid SQL statement was used

JohannesLindner — Fri, 11 Jun 2021 10:31:50 GMT

A field of a table can have an alias (alternative name) that is different from its real name. You can use this to make field names more readable in the table view, e.g. using spaces in the name.

The arcpy cursors use SQL in the background. SQL doesn't work with alias names, only with the real ones. Using alias names in the cursor may cause the error you experience.

So if in your example "speed100m" (or another field in liste) is not the real name of the field, that might be the problem.

You can check by doing the ExtractMultiVBaluesToPoints in the python window in ArcGIS or with the toolbox and then looking at the table's fields.

Or, you can print all field names:

arcpy.sa.ExtractMultiValuesToPoint(.....) print([f.name for f in arcpy.ListFields("in_memory/resultats_table")])

Some further tips:

for field in liste: with arcpy.da.UpdateCursor("in_memory/resultats_table", [field]) as cursor: for row in cursor: if row[0] is None: # this is wrong in your code! pass else: # row[0] = round(...) # this won't work. row is a tuple, you can't change a tuple's elements. # do this instead: cursor.updateRow([round(row[0], 2)]) # instead of the check for None, you could do this: for field in liste: with arcpy.da.UpdateCursor("in_memory/resultats_table", [field], "{} IS NOT NULL".format(field)) as cursor: for row in cursor: cursor.updateRow([round(row[0], 2)])

Re: An invalid SQL statement was used

hamidmechouet — Fri, 11 Jun 2021 12:07:46 GMT

as you can see the field names and aliases have the same values

Re: An invalid SQL statement was used

hamidmechouet — Fri, 11 Jun 2021 12:16:18 GMT

thanck you for your help johannes, but i have the same error.

Re: An invalid SQL statement was used

DanPatterson — Fri, 11 Jun 2021 16:10:16 GMT

That is because you left in your old code (lines 55-64) and didn't use the suggested code beginning at line 67.