current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 2 down vote favorite

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.

share|improve this question
    
Can you use the arcpy method for Select Layer By Attribute? desktop.arcgis.com/en/arcmap/10.3/tools/data-management-tool‌​box/… – artwork21 18 hours ago
up vote 5 down vote accepted

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.

share|improve this answer
    
That's exactly what I want! Good to note the potential issues when modifying features outside the Arc* environment. – DenaliHardtail 18 hours ago
    
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 16 hours ago
    
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 16 hours ago
    
@jpmc26 you can pass any SQL, including executing stored procs and SQL variables, and include parameters input from python (using python string formatting) – Midavalo♦ 16 hours ago
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 16 hours ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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