4

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.

Improve this question
1
6

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.

Improve this answer
5
  • That's exactly what I want! Good to note the potential issues when modifying features outside the Arc* environment. – DenaliHardtail Feb 13 '17 at 18:54
  • Unless I'm mistaken, this doesn't support parameterized queries. Use with caution whenever user input is involved; creating a SQL injection vulnerability would be very easy. – jpmc26 Feb 13 '17 at 20:21
  • owasp.org/index.php/Query_Parameterization_Cheat_Sheet and the famous XKCD are a good start. Any developer worth their salt will tell you to avoid incorporating input directly into a SQL string and use parametrized queries instead, but ESRI's method doesn't appear to support any form of parametrization. If this script is going to accept input and include that in the SQL, then the user can attack the database using SQL injection. I would avoid using this unless I was the only one running the script or the SQL doesn't require input. – jpmc26 Feb 13 '17 at 20:29
  • @jpmc26 you can pass any SQL, including executing stored procs and SQL variables, and include parameters input from python (using python string formatting) – Midavalo♦ Feb 13 '17 at 20:34
  • 1
    @Midavalo It isn't that simple. If the input is ever parsed as SQL, an attacker can inject their own SQL. The parameters have to be passed from code to database using a non-SQL-query mechanism. I'm not 100% clear how database clients do that, but for paramterization to actually be secure, they have to. – jpmc26 Feb 13 '17 at 20:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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