I am new to python and having trouble with my script.

I want only the records from the COMTRS field (of the pur_dbf_view) that match values from the ComtrsList to be selected; however, I am getting an "invalid expression" error message. I think to solve my problem, I need to write the python list (ComtrsList) to a .dbf or a file format that can be later referenced in the Select Layer by Attribute management at the end of the script. Is this right? Is my syntax for the subquery in the Select layer by Attribute statement correct?

import arcpy, sys, os, traceback

# Set workspace
arcpy.env.workspace = "H:\\GIS_AIR\\Python\\PUR_tool"

# Set variables
plss = "Plss.shp"
comm = "Communities_sub.shp" # Input Community feature class
c_layer = "Communities_layer_sub" # Name of the Make Feature Layer output for    communities
p_layer = "Plss_layer" # Name of the Make Feature Layer output
plss_out = "Output_plss.shp" # Name of PLSS (selected) output feature.  Make sure this    has the .shp extension

search_distance = '0 MILES'

pur_dbf = "Glenn_pur.dbf"
pur_dbf_view = "Glenn_pur_view.dbf"

pur_dbf_sum = "Glenn_pur_sum.dbf"

comtrs_list_dbf = "Comtrs_list.dbf"
template = "TemplateA.dbf"

# Make feature layer for PLSS feature
arcpy.MakeFeatureLayer_management(plss, p_layer)

print "Completed make feature layer management PLSS"

# Make feature layer for Communities feature
arcpy.MakeFeatureLayer_management(comm, c_layer)
print "Completed make feature layer management Communities"

# Select features that are within 0 miles of comm fc
arcpy.SelectLayerByLocation_management(p_layer, "WITHIN_A_DISTANCE", c_layer,     search_distance, "NEW_SELECTION")

arcpy.CopyFeatures_management(p_layer, plss_out)

# Create new table with template
arcpy.CreateTable_management("H:\\GIS_AIR\\Python\\PUR_tool", comtrs_list_dbf, template)

ComtrsList = []
ComtrsCursor = arcpy.SearchCursor(plss_out)
for row in ComtrsCursor:
    comtrsValue = row.getValue("CO_MTRS")
print ComtrsList

###Write ComtrsList to comtrs_list_dbf (.dbf)###
arcpy.MakeTableView_management(pur_dbf, pur_dbf_view)
print "Created table view"

# Select layer by attribute on the pur_dbf_view
arcpy.SelectLayerByAttribute_management(pur_dbf_view, "NEW_SELECTION", "\"COMTRS\" IN (SELECT \"COMTRS\" FROM comtrs_list_dbf)")

arcpy.Statistics_analysis(pur_dbf_view, pur_dbf_sum, [["LBS_AI", "SUM"]], "CODE")
  • Subqueries only work in a geodatabase.
    – klewis
    Sep 3, 2013 at 23:28

1 Answer 1


As commented by @klewis ...

From the SQL reference for query expressions used in ArcGIS:

Coverages, shapefiles, and other nongeodatabase file-based data sources do not support subqueries. Subqueries that are performed on versioned ArcSDE feature classes and tables will not return features that are stored in the delta tables. File geodatabases provide the limited support for subqueries explained in this section, while personal and ArcSDE geodatabases provide full support. For information on the full set of subquery capabilities of personal and ArcSDE geodatabases, refer to your DBMS documentation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.