Mixing
ArcPy cursor: RuntimeError: An invalid SQL statement was used
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
1
down vote
favorite
I'm trying to iterate through polygons in a feature class to perform zonal statistics on a raster mosaic.
I found inspiration from Iterate through features to use selection as input for ExtractByMask for my code but I get a "RuntimeError: An invalid SQL statement was used. [SELECT OBJECTID, Shape, Group FROM GROUP_RINGS_prj]" message.
Here is the code I use:
import arcpy, datetime
from arcpy import env
from arcpy.sa import *
arcpy.env.overwriteOutput = True
arcpy.CheckOutExtension("Spatial")
env.workspace = "D:/PROJECT.gdb"
aoi_prj = "GROUP_RINGS_prj"
forest_prj = "FOREST_prj"
## Stats by group
with arcpy.da.SearchCursor(aoi_prj, ["OID@", "SHAPE@", "Group"]) as cursor:
for row in cursor:
print(datetime.datetime.now().strftime("%Y-%m-%d %H:%M") + ": Processing: " + str(row[2]) + "...")
zs = ZonalStatisticsAsTable(row[1], "Group", forest_prj, "D:/Projects/Results/ZS_FOREST_" + str(row[0]) + ".dbf", "NODATA", "MEAN")
row = cursor.next()
It results with:
Traceback (most recent call last):
File "D:ProjectsScripts.py", line 87, in <module>
for row in cursor:
RuntimeError: An invalid SQL statement was used. [SELECT OBJECTID, Shape, Group FROM GROUP_RINGS_prj]
Do I miss something obvious in the code?
arcpy
asked 3 hours ago
Marc
337
 |Â
show 1 more comment
up vote
1
down vote
favorite
I'm trying to iterate through polygons in a feature class to perform zonal statistics on a raster mosaic.
I found inspiration from Iterate through features to use selection as input for ExtractByMask for my code but I get a "RuntimeError: An invalid SQL statement was used. [SELECT OBJECTID, Shape, Group FROM GROUP_RINGS_prj]" message.
Here is the code I use:
import arcpy, datetime
from arcpy import env
from arcpy.sa import *
arcpy.env.overwriteOutput = True
arcpy.CheckOutExtension("Spatial")
env.workspace = "D:/PROJECT.gdb"
aoi_prj = "GROUP_RINGS_prj"
forest_prj = "FOREST_prj"
## Stats by group
with arcpy.da.SearchCursor(aoi_prj, ["OID@", "SHAPE@", "Group"]) as cursor:
for row in cursor:
print(datetime.datetime.now().strftime("%Y-%m-%d %H:%M") + ": Processing: " + str(row[2]) + "...")
zs = ZonalStatisticsAsTable(row[1], "Group", forest_prj, "D:/Projects/Results/ZS_FOREST_" + str(row[0]) + ".dbf", "NODATA", "MEAN")
row = cursor.next()
It results with:
Traceback (most recent call last):
File "D:ProjectsScripts.py", line 87, in <module>
for row in cursor:
RuntimeError: An invalid SQL statement was used. [SELECT OBJECTID, Shape, Group FROM GROUP_RINGS_prj]
Do I miss something obvious in the code?
arcpy
asked 3 hours ago
Marc
337
Are you trying to select all rows with the same Group attribute and perform one zs for each Group? Or do you want one zs for each row in the feature class?
– BERA
3 hours ago
Each group attribute is unique so I get one zs for each row in the fc..
– Marc
3 hours ago
yes, forest_prj is a raster in the PROJECT.gdb
– Marc
3 hours ago
2
Hm, group is a reserved keyword in some types of database, maybe that's your problem. You could try to rename that field. When I try to create a field named group in a File Geodatabase, it gives me an error: The following field names are invalid: group
– Berend
3 hours ago
That was it! I removed Group and it works now... Thanks!
– Marc
3 hours ago
 |Â
show 1 more comment
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I'm trying to iterate through polygons in a feature class to perform zonal statistics on a raster mosaic.
I found inspiration from Iterate through features to use selection as input for ExtractByMask for my code but I get a "RuntimeError: An invalid SQL statement was used. [SELECT OBJECTID, Shape, Group FROM GROUP_RINGS_prj]" message.
Here is the code I use:
import arcpy, datetime
from arcpy import env
from arcpy.sa import *
arcpy.env.overwriteOutput = True
arcpy.CheckOutExtension("Spatial")
env.workspace = "D:/PROJECT.gdb"
aoi_prj = "GROUP_RINGS_prj"
forest_prj = "FOREST_prj"
## Stats by group
with arcpy.da.SearchCursor(aoi_prj, ["OID@", "SHAPE@", "Group"]) as cursor:
for row in cursor:
print(datetime.datetime.now().strftime("%Y-%m-%d %H:%M") + ": Processing: " + str(row[2]) + "...")
zs = ZonalStatisticsAsTable(row[1], "Group", forest_prj, "D:/Projects/Results/ZS_FOREST_" + str(row[0]) + ".dbf", "NODATA", "MEAN")
row = cursor.next()
It results with:
Traceback (most recent call last):
File "D:ProjectsScripts.py", line 87, in <module>
for row in cursor:
RuntimeError: An invalid SQL statement was used. [SELECT OBJECTID, Shape, Group FROM GROUP_RINGS_prj]
Do I miss something obvious in the code?
arcpy
asked 3 hours ago
Marc
337
I'm trying to iterate through polygons in a feature class to perform zonal statistics on a raster mosaic.
I found inspiration from Iterate through features to use selection as input for ExtractByMask for my code but I get a "RuntimeError: An invalid SQL statement was used. [SELECT OBJECTID, Shape, Group FROM GROUP_RINGS_prj]" message.
Here is the code I use:
import arcpy, datetime
from arcpy import env
from arcpy.sa import *
arcpy.env.overwriteOutput = True
arcpy.CheckOutExtension("Spatial")
env.workspace = "D:/PROJECT.gdb"
aoi_prj = "GROUP_RINGS_prj"
forest_prj = "FOREST_prj"
## Stats by group
with arcpy.da.SearchCursor(aoi_prj, ["OID@", "SHAPE@", "Group"]) as cursor:
for row in cursor:
print(datetime.datetime.now().strftime("%Y-%m-%d %H:%M") + ": Processing: " + str(row[2]) + "...")
zs = ZonalStatisticsAsTable(row[1], "Group", forest_prj, "D:/Projects/Results/ZS_FOREST_" + str(row[0]) + ".dbf", "NODATA", "MEAN")
row = cursor.next()
It results with:
Traceback (most recent call last):
File "D:ProjectsScripts.py", line 87, in <module>
for row in cursor:
RuntimeError: An invalid SQL statement was used. [SELECT OBJECTID, Shape, Group FROM GROUP_RINGS_prj]
Do I miss something obvious in the code?
arcpy
arcpy
asked 3 hours ago
Marc
337
asked 3 hours ago
Marc
337
edited 3 hours ago
asked 3 hours ago
Marc
337
asked 3 hours ago
Marc
337
asked 3 hours ago
Marc
337
337
Are you trying to select all rows with the same Group attribute and perform one zs for each Group? Or do you want one zs for each row in the feature class?
– BERA
3 hours ago
Each group attribute is unique so I get one zs for each row in the fc..
– Marc
3 hours ago
yes, forest_prj is a raster in the PROJECT.gdb
– Marc
3 hours ago
2
Hm, group is a reserved keyword in some types of database, maybe that's your problem. You could try to rename that field. When I try to create a field named group in a File Geodatabase, it gives me an error: The following field names are invalid: group
– Berend
3 hours ago
That was it! I removed Group and it works now... Thanks!
– Marc
3 hours ago
 |Â
show 1 more comment
Are you trying to select all rows with the same Group attribute and perform one zs for each Group? Or do you want one zs for each row in the feature class?
– BERA
3 hours ago
Each group attribute is unique so I get one zs for each row in the fc..
– Marc
3 hours ago
yes, forest_prj is a raster in the PROJECT.gdb
– Marc
3 hours ago
2
Hm, group is a reserved keyword in some types of database, maybe that's your problem. You could try to rename that field. When I try to create a field named group in a File Geodatabase, it gives me an error: The following field names are invalid: group
– Berend
3 hours ago
That was it! I removed Group and it works now... Thanks!
– Marc
3 hours ago
Are you trying to select all rows with the same Group attribute and perform one zs for each Group? Or do you want one zs for each row in the feature class?
– BERA
3 hours ago
Are you trying to select all rows with the same Group attribute and perform one zs for each Group? Or do you want one zs for each row in the feature class?
– BERA
3 hours ago
Each group attribute is unique so I get one zs for each row in the fc..
– Marc
3 hours ago
Each group attribute is unique so I get one zs for each row in the fc..
– Marc
3 hours ago
yes, forest_prj is a raster in the PROJECT.gdb
– Marc
3 hours ago
yes, forest_prj is a raster in the PROJECT.gdb
– Marc
3 hours ago
2
2
Hm, group is a reserved keyword in some types of database, maybe that's your problem. You could try to rename that field. When I try to create a field named group in a File Geodatabase, it gives me an error: The following field names are invalid: group
– Berend
3 hours ago
Hm, group is a reserved keyword in some types of database, maybe that's your problem. You could try to rename that field. When I try to create a field named group in a File Geodatabase, it gives me an error: The following field names are invalid: group
– Berend
3 hours ago
That was it! I removed Group and it works now... Thanks!
– Marc
3 hours ago
That was it! I removed Group and it works now... Thanks!
– Marc
3 hours ago
 |Â
show 1 more comment
2 Answers
2
active
oldest
votes
up vote
3
down vote
accepted
Group can't be used as a column name, because it's a reserved keyword.
In fact, when you use ArcCatalog to add a field with that name to a featureclass, it will give an error message: The following field names are invalid: group. I wonder how you managed to create this featureclass?
answered 3 hours ago
Berend
2,295518
I imported it from a .csv file... But now I see "Group" is the alias and ArcGIS renamed the field "Group_" with an underscore...
– Marc
2 hours ago
@Marc That makes sense, thanks for clarifying
– Berend
2 hours ago
add a comment |Â
up vote
2
down vote
You cant use a geometry (SHAPE@) as in_zone_data to zonal statistics, it should be a feature layer or raster layer. Try creating a feature layer using the objectid of each row instead and use feature layer as input:
with arcpy.da.SearchCursor(aoi_prj, ["OID@", "Group"]) as cursor:
for row in cursor:
print(datetime.datetime.now().strftime("%Y-%m-%d %H:%M") + ": Processing: " + str(row[2]) + "...")
sql = """0=1""".format(arcpy.AddFieldDelimiters(arcpy.Describe(aoi_prj).OIDFieldname),row[0])
arcpy.MakeFeatureLayer_management(in_features=aoi_prj, out_layer='templyr', where_clause=sql)
zs = ZonalStatisticsAsTable('templyr', "Group", forest_prj, "D:/Projects/Results/ZS_FOREST_" + str(row[0]) + ".dbf", "NODATA", "MEAN")
This line will probably make you miss every other row. To iterate over each row remove it:
row = cursor.next()
answered 3 hours ago
BERA
11.9k41537
Same error unfortunately... "RuntimeError: An invalid SQL statement was used. [SELECT OBJECTID, Group FROM GROUP_RINGS_prj]
– Marc
3 hours ago
1
Thanks, your answer helped me a lot to understand how to iterate through features.
– Marc
23 mins ago
add a comment |Â
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
3
down vote
accepted
Group can't be used as a column name, because it's a reserved keyword.
In fact, when you use ArcCatalog to add a field with that name to a featureclass, it will give an error message: The following field names are invalid: group. I wonder how you managed to create this featureclass?
answered 3 hours ago
Berend
2,295518
I imported it from a .csv file... But now I see "Group" is the alias and ArcGIS renamed the field "Group_" with an underscore...
– Marc
2 hours ago
@Marc That makes sense, thanks for clarifying
– Berend
2 hours ago
add a comment |Â
up vote
3
down vote
accepted
Group can't be used as a column name, because it's a reserved keyword.
In fact, when you use ArcCatalog to add a field with that name to a featureclass, it will give an error message: The following field names are invalid: group. I wonder how you managed to create this featureclass?
answered 3 hours ago
Berend
2,295518
I imported it from a .csv file... But now I see "Group" is the alias and ArcGIS renamed the field "Group_" with an underscore...
– Marc
2 hours ago
@Marc That makes sense, thanks for clarifying
– Berend
2 hours ago
add a comment |Â
up vote
3
down vote
accepted
up vote
3
down vote
accepted
Group can't be used as a column name, because it's a reserved keyword.
In fact, when you use ArcCatalog to add a field with that name to a featureclass, it will give an error message: The following field names are invalid: group. I wonder how you managed to create this featureclass?
answered 3 hours ago
Berend
2,295518
Group can't be used as a column name, because it's a reserved keyword.
In fact, when you use ArcCatalog to add a field with that name to a featureclass, it will give an error message: The following field names are invalid: group. I wonder how you managed to create this featureclass?
answered 3 hours ago
Berend
2,295518
answered 3 hours ago
Berend
2,295518
answered 3 hours ago
Berend
2,295518
answered 3 hours ago
Berend
2,295518
2,295518
I imported it from a .csv file... But now I see "Group" is the alias and ArcGIS renamed the field "Group_" with an underscore...
– Marc
2 hours ago
@Marc That makes sense, thanks for clarifying
– Berend
2 hours ago
add a comment |Â
I imported it from a .csv file... But now I see "Group" is the alias and ArcGIS renamed the field "Group_" with an underscore...
– Marc
2 hours ago
@Marc That makes sense, thanks for clarifying
– Berend
2 hours ago
I imported it from a .csv file... But now I see "Group" is the alias and ArcGIS renamed the field "Group_" with an underscore...
– Marc
2 hours ago
I imported it from a .csv file... But now I see "Group" is the alias and ArcGIS renamed the field "Group_" with an underscore...
– Marc
2 hours ago
@Marc That makes sense, thanks for clarifying
– Berend
2 hours ago
@Marc That makes sense, thanks for clarifying
– Berend
2 hours ago
add a comment |Â
up vote
2
down vote
You cant use a geometry (SHAPE@) as in_zone_data to zonal statistics, it should be a feature layer or raster layer. Try creating a feature layer using the objectid of each row instead and use feature layer as input:
with arcpy.da.SearchCursor(aoi_prj, ["OID@", "Group"]) as cursor:
for row in cursor:
print(datetime.datetime.now().strftime("%Y-%m-%d %H:%M") + ": Processing: " + str(row[2]) + "...")
sql = """0=1""".format(arcpy.AddFieldDelimiters(arcpy.Describe(aoi_prj).OIDFieldname),row[0])
arcpy.MakeFeatureLayer_management(in_features=aoi_prj, out_layer='templyr', where_clause=sql)
zs = ZonalStatisticsAsTable('templyr', "Group", forest_prj, "D:/Projects/Results/ZS_FOREST_" + str(row[0]) + ".dbf", "NODATA", "MEAN")
This line will probably make you miss every other row. To iterate over each row remove it:
row = cursor.next()
answered 3 hours ago
BERA
11.9k41537
Same error unfortunately... "RuntimeError: An invalid SQL statement was used. [SELECT OBJECTID, Group FROM GROUP_RINGS_prj]
– Marc
3 hours ago
1
Thanks, your answer helped me a lot to understand how to iterate through features.
– Marc
23 mins ago
add a comment |Â
up vote
2
down vote
You cant use a geometry (SHAPE@) as in_zone_data to zonal statistics, it should be a feature layer or raster layer. Try creating a feature layer using the objectid of each row instead and use feature layer as input:
with arcpy.da.SearchCursor(aoi_prj, ["OID@", "Group"]) as cursor:
for row in cursor:
print(datetime.datetime.now().strftime("%Y-%m-%d %H:%M") + ": Processing: " + str(row[2]) + "...")
sql = """0=1""".format(arcpy.AddFieldDelimiters(arcpy.Describe(aoi_prj).OIDFieldname),row[0])
arcpy.MakeFeatureLayer_management(in_features=aoi_prj, out_layer='templyr', where_clause=sql)
zs = ZonalStatisticsAsTable('templyr', "Group", forest_prj, "D:/Projects/Results/ZS_FOREST_" + str(row[0]) + ".dbf", "NODATA", "MEAN")
This line will probably make you miss every other row. To iterate over each row remove it:
row = cursor.next()
answered 3 hours ago
BERA
11.9k41537
Same error unfortunately... "RuntimeError: An invalid SQL statement was used. [SELECT OBJECTID, Group FROM GROUP_RINGS_prj]
– Marc
3 hours ago
1
Thanks, your answer helped me a lot to understand how to iterate through features.
– Marc
23 mins ago
add a comment |Â
up vote
2
down vote
up vote
2
down vote
You cant use a geometry (SHAPE@) as in_zone_data to zonal statistics, it should be a feature layer or raster layer. Try creating a feature layer using the objectid of each row instead and use feature layer as input:
with arcpy.da.SearchCursor(aoi_prj, ["OID@", "Group"]) as cursor:
for row in cursor:
print(datetime.datetime.now().strftime("%Y-%m-%d %H:%M") + ": Processing: " + str(row[2]) + "...")
sql = """0=1""".format(arcpy.AddFieldDelimiters(arcpy.Describe(aoi_prj).OIDFieldname),row[0])
arcpy.MakeFeatureLayer_management(in_features=aoi_prj, out_layer='templyr', where_clause=sql)
zs = ZonalStatisticsAsTable('templyr', "Group", forest_prj, "D:/Projects/Results/ZS_FOREST_" + str(row[0]) + ".dbf", "NODATA", "MEAN")
This line will probably make you miss every other row. To iterate over each row remove it:
row = cursor.next()
answered 3 hours ago
BERA
11.9k41537
You cant use a geometry (SHAPE@) as in_zone_data to zonal statistics, it should be a feature layer or raster layer. Try creating a feature layer using the objectid of each row instead and use feature layer as input:
with arcpy.da.SearchCursor(aoi_prj, ["OID@", "Group"]) as cursor:
for row in cursor:
print(datetime.datetime.now().strftime("%Y-%m-%d %H:%M") + ": Processing: " + str(row[2]) + "...")
sql = """0=1""".format(arcpy.AddFieldDelimiters(arcpy.Describe(aoi_prj).OIDFieldname),row[0])
arcpy.MakeFeatureLayer_management(in_features=aoi_prj, out_layer='templyr', where_clause=sql)
zs = ZonalStatisticsAsTable('templyr', "Group", forest_prj, "D:/Projects/Results/ZS_FOREST_" + str(row[0]) + ".dbf", "NODATA", "MEAN")
This line will probably make you miss every other row. To iterate over each row remove it:
row = cursor.next()
answered 3 hours ago
BERA
11.9k41537
edited 3 hours ago
answered 3 hours ago
BERA
11.9k41537
answered 3 hours ago
BERA
11.9k41537
answered 3 hours ago
BERA
11.9k41537
11.9k41537
Same error unfortunately... "RuntimeError: An invalid SQL statement was used. [SELECT OBJECTID, Group FROM GROUP_RINGS_prj]
– Marc
3 hours ago
1
Thanks, your answer helped me a lot to understand how to iterate through features.
– Marc
23 mins ago
add a comment |Â
Same error unfortunately... "RuntimeError: An invalid SQL statement was used. [SELECT OBJECTID, Group FROM GROUP_RINGS_prj]
– Marc
3 hours ago
1
Thanks, your answer helped me a lot to understand how to iterate through features.
– Marc
23 mins ago
Same error unfortunately... "RuntimeError: An invalid SQL statement was used. [SELECT OBJECTID, Group FROM GROUP_RINGS_prj]
– Marc
3 hours ago
Same error unfortunately... "RuntimeError: An invalid SQL statement was used. [SELECT OBJECTID, Group FROM GROUP_RINGS_prj]
– Marc
3 hours ago
1
1
Thanks, your answer helped me a lot to understand how to iterate through features.
– Marc
23 mins ago
Thanks, your answer helped me a lot to understand how to iterate through features.
– Marc
23 mins ago
add a comment |Â
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f297223%2farcpy-cursor-runtimeerror-an-invalid-sql-statement-was-used%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Are you trying to select all rows with the same Group attribute and perform one zs for each Group? Or do you want one zs for each row in the feature class?
– BERA
3 hours ago
Each group attribute is unique so I get one zs for each row in the fc..
– Marc
3 hours ago
yes, forest_prj is a raster in the PROJECT.gdb
– Marc
3 hours ago
2
Hm, group is a reserved keyword in some types of database, maybe that's your problem. You could try to rename that field. When I try to create a field named group in a File Geodatabase, it gives me an error: The following field names are invalid: group
– Berend
3 hours ago
That was it! I removed Group and it works now... Thanks!
– Marc
3 hours ago