An invalid SQL statement was used

[hamidmechouet]

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)

[DanPatterson]

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:

[hamidmechouet]

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

[JoshuaBixby]

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.

[hamidmechouet]

[hamidmechouet]

[JohannesLindner]

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!

[hamidmechouet]

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

[JohannesLindner · Accepted solution]

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

[hamidmechouet]

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