Is there a way to execute SQL queries on file geodatabase from Python? I'm looking for the same functionality provided by arcpy.ArcSDESQLExecute for SDE databases.
2 Answers 2
Most Python queries are limited to a Where clause, however, you can execute a subquery in the Select_By_attribute tool. Please see the ArcGIS SQL Reference for more details. Also, the arcpy.da UpdateCursor and SearchCursor let you issue groupby, distinct, top, order by in a sql_clause along with the where_clause.
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.
It is possible to use arbitrary SQL (though likely with some limitations) with the file geodatabase API driver of GDAL/OGR. See my answer this related question: ogr2ogr / GDAL remove features in FileGDB while in use by ArcGIS Server map service
Also, if you are looking to do this with Python and on Windows take a look at this question: Gdal Python Bindings and File Geodatabase API
SELECTto the file geodatabase, or a more complex query?