HOW TO

Use Python to determine the SQL syntax for a WHERE clause depending on the workspace type

Last Published: April 25, 2020

Summary

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.

Procedure

The describe property, workspaceFactoryProgID, allows for the workspace type to be determined. An IF statement can then be used to write the correct WHERE clause based on this workspace type.

This is better than using workspaceType since it allows for more distinction between a file and personal geodatabase.

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

Article ID: 000011590

Software:
  • ArcMap

Receive notifications and find solutions for new or common issues

Get summarized answers and video solutions from our new AI chatbot.

Download the Esri Support App

Related Information

Discover more on this topic

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options