Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

Is there a way to execute a SQL Server stored procedure with arcpy or Python and retrieve the results returned by the stored procedure?

There is a very similar question posted on ArcGIS forum with no answer so far.

share|improve this question
    
Executing stored procedures via ArcSDESQLExecute is not supported: import arcpy sde_conn = arcpy.ArcSDESQLExecute(r"D:\ConnFiles\RDSQLDEV@KnownPoint@osa.sde") sde_return = sde_conn.execute('EXEC usp_GIS_NewlyReleasedWellData') produces: return convertArcObjectToPythonObject(self._arc_object.Execute(*gp_fixargs(args))) AttributeError –  Matej Feb 18 at 15:59
add comment

1 Answer 1

up vote 7 down vote accepted

pyodbc library is capable of executing stored procedures. Check out the getting started guide here.

It is a Python 2.x and 3.x module that allows you to use ODBC to connect to almost any database from Windows, Linux, OS/X, and more. It implements the Python Database API Specification v2.0, but additional features have been added to simplify database programming even more.

Pyodbc is licensed using an MIT license, so it is free for commercial and personal use. You can even use the source code in your own projects. Installers for Windows are available here and most Linux distributions are starting to provide pre-compiled packages. Full source code is also available.

If you will ever need to execute SQL commands for an ArcSDE geodatabase, consider using the ArcSDESQLExecute class which provides a means of executing SQL statements via an ArcSDE connection. I am not sure though if you can shoot SQL commands for stored procedures, but it might be worth trying.

share|improve this answer
2  
Thanks, I just tested pymssql library that works well with stored procedures. What about arcpy? I didn't find anywhere in the docs that it does not support stored procedures. –  Matej Feb 17 at 17:22
1  
You can mix regular python with arcpy stuff. Arcpy is just a library for regular python. So do your normal Arcpy stuff, and then follow the pyodbc documentation to run those functions. –  alexgleith Feb 18 at 0:36
    
That is correct, you can use both ArcPy and Python code in the same file/module. If you will ever need to run SQL code with ArcPy and you don't have access to pyodbc library, I usually use ArcSDESQLExecute class which works however only with geodatabases (as far as I tested). I have updated my answer too. –  Alex Tereshenkov Feb 18 at 8:08
add comment

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.