HOW TO
The syntax of a SQL statement depends on the workspace type that is being used. When making scripting tools, this can be a problem if the WHERE statement is hard-coded into the script for only one type of workspace. For example, the code may have the following WHERE statement:
WhereClause = '"ObjectID" = ' + value
This SQL statement only works for file geodatabases.
For greater flexibility in the code, workspace describe properties can be used to determine the workspace type of the feature class to allow for the correct syntax. This is handy for anything that uses a WHERE clause such as selections or cursors.
Note:
'WhereClause' is being used as a Python variable and not as an actual SQL WHERE clause.
The first sample looks at the layers in a map document with a hard-coded field value:
Code:
import arcpy
#makes map document object
mxd = arcpy.mapping.MapDocument(r"C:\Data\Maps\Sample.mxd")
#Predetermined FID that is needed.
value = "5"
layername = "states"
layers = arcpy.mapping.ListLayers(mxd, layername)
for layer in layers:
#finds the workspace path for the layer/Feature Class
WP = layer.workspacePath
print WP
#Creates a describe object for the workspace path
desWP = arcpy.Describe(WP)
#Pulls the workspace product type
WPtype = desWP.workspaceFactoryProgID
print WPtype
#Identifies File Geodatabase
if WPtype == "esriDataSourcesGDB.FileGDBWorkspaceFactory.1":
#sample where clause for fgdb data
WhereClause = '"ObjectID" = ' + value
print WhereClause
#Identifies Personal Geodatabase
if WPtype == "esriDataSourcesGDB.AccessWorkspaceFactory.1":
#sample where clause for mdb data
WhereClause = "[ObjectID] = " + value
print WhereClause
#Identifies SDE database
if WPtype == "esriDataSourcesGDB.SdeWorkspaceFactory.1":
#sample where clause for SDE data
WhereClause = "ObjectID = " + value
print WhereClause
#Other (Shapefile, coverage, CAD, VPF, and so on)
if WPtype == "":
#sample where clause for shapefile data
WhereClause = '"ObjectID"= ' + value
print WhereClause
del WhereClause
The second sample allows for a variable to pass the field name into the WHERE clause.
Code:
import arcpy
#makes map document object
mxd = arcpy.mapping.MapDocument(r"C:\Data\Maps\Sample.mxd")
#field name variable that is needed.
fieldname = "STATE"
#Predetermined value
value = "'NC'"
layername = "states"
layers = arcpy.mapping.ListLayers(mxd, layername)
for layer in layers:
#finds the workspace path for the layer/Feature Class
WP = layer.workspacePath
print WP
#Creates a describe object for the workspace path
desWP = arcpy.Describe(WP)
#Pulls the workspace product type
WPtype = desWP.workspaceFactoryProgID
print WPtype
#Identifies File Geodatabase
if WPtype == "esriDataSourcesGDB.FileGDBWorkspaceFactory.1":
#sample where clause for fgdb data
WhereClause = '"' + fieldname + '"= ' + value
print WhereClause
#Identifies Personal Geodatabase
if WPtype == "esriDataSourcesGDB.AccessWorkspaceFactory.1":
#sample where clause for mdb data
WhereClause = "[" + fieldname + "] = " + value
print WhereClause
#Identifies SDE database
if WPtype == "esriDataSourcesGDB.SdeWorkspaceFactory.1":
#sample where clause for SDE data
WhereClause = fieldname + "= " + value
print WhereClause
#Other (Shapefile, coverage, CAD, VPF, and so on)
if WPtype == "":
#sample where clause for shapefile data
WhereClause = '"' + fieldname + '"= ' + value
print WhereClause
del WhereClause
Get help from ArcGIS experts
Download the Esri Support App