[GIS] Using SQL statement with ArcPy

arcgis-10.1arcpyenterprise-geodatabasesql

I've been using insert, update and select cursors found in arcpy.da and so far it's met my needs.

Today, I found another situation where I really just want to throw a SQL statement at the database, from my python/arcpy code, and get the resultset. Specifically, I want to get the Id of a polygon that contains a point; very easy to do with a SQL statement in my SQL tool.

There have been other examples over the last few weeks so this question is not about how do perform the point / polygon intersection. It's about running SQL statements from within my ArcPy code. In other parts of my code, ArcPy uses ArcSDE connection files to access the ArcSDE feature classes and it would be great if I could use the existing data access infrastructure and just send a SQL statement.

Best Answer

If you have SQL access, you can use arcpy.ArcSDESQLExecute() to use SQL from arcpy. I use this regularly to query a number of SQL databases, some of them spatial, others non-spatial. I find it easiest to have a connection to the database in my Database Connections (even for the non-spatial ones), and then reference that .sde file from the tool.

import arcpy

myDB = r"Database Connections\mydatabase.sde"
myQuery = """ SELECT * FROM MYTABLE WHERE ID = 1234 """

sqlConn = arcpy.ArcSDESQLExecute(myDB)
result = sqlConn.execute(myQuery)

You can pass any SQL that you have permissions to run on the database, so be careful. This includes Delete, Drop Table, Execute Stored Procs, etc.

See also Executing SQL using an ArcSDE connection

Note: Modifying GIS data outside of the ArcGIS environment can be dangerous. Using this tool on feature classes bypasses the logic that ArcGIS applies when using one of the in-built editing tools.

Related Question