4

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.

  • 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 '14 at 15:59
9

For working specifically with SQL Server, arguably the best Python package is pymssql which is:

A simple database interface for Python that builds on top of FreeTDS to provide a Python DB-API (PEP-249) interface to Microsoft SQL Server.

I have used it in production for many years making a ton of SQL SP calls and it worked really, really well.

Another package, pyodbc, is also 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.

  • 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 '14 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. – Alex Leith Feb 18 '14 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 '14 at 8:08

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.