Subquery a list in select layer by attribute management using python

Question

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")
 ComtrsList.append(comtrsValue)
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")

• klewis: Subqueries only work in a geodatabase.

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.