An invalid SQL statement was used

448
11
Jump to solution
2 weeks ago
hamidmechouet
New Contributor II

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)

 

0 Kudos
1 Solution

Accepted Solutions
JohannesLindner
Occasional Contributor III

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.

JohannesLindner_0-1623405362345.png

 

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

 


Have a great day!
Johannes

View solution in original post

0 Kudos
11 Replies
DanPatterson
MVP Honored Contributor

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:

 


... sort of retired...
hamidmechouet
New Contributor II

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

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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
New Contributor II

Sql_statements.PNG

0 Kudos
hamidmechouet
New Contributor II

code.PNG

0 Kudos
JohannesLindner
Occasional Contributor III

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!

Have a great day!
Johannes
0 Kudos
hamidmechouet
New Contributor II

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

0 Kudos
JohannesLindner
Occasional Contributor III

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.

JohannesLindner_0-1623405362345.png

 

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

 


Have a great day!
Johannes

View solution in original post

0 Kudos
hamidmechouet
New Contributor II

as you can see the field names and aliases have the same valuesFields.PNG

0 Kudos