How to execute a SQL Server stored procedure with ArcPy

807
4
Jump to solution
01-02-2019 08:11 AM
JoseSanchez
Occasional Contributor III

Hello everyone,

I need to execute a Ms SQL Server stored procedure using Arcpy.  Any source code samples that will hep me.

Thanks

0 Kudos
1 Solution

Accepted Solutions
JoshuaBixby
MVP Esteemed Contributor

ArcSDESQLExecute—ArcPy classes | ArcGIS Desktop may work, it depends on a couple of factors.

Why do you have to execute the stored procedure in "ArcPy?"  Why not just connect to it using a Python wrapper for SQL Server?

View solution in original post

4 Replies
JoshuaBixby
MVP Esteemed Contributor

ArcSDESQLExecute—ArcPy classes | ArcGIS Desktop may work, it depends on a couple of factors.

Why do you have to execute the stored procedure in "ArcPy?"  Why not just connect to it using a Python wrapper for SQL Server?

JoseSanchez
Occasional Contributor III

I was looking for an option to run a Ms SQL Update statement because updating records using  arcpy.da.UpdateCursor  is too slow.

The first option was to add the SQL code in a stored procedure and run the stored procedure.

The second option, I just copy/pasted the code sample (http://pro.arcgis.com/en/pro-app/arcpy/classes/arcsdesqlexecute.htm) , added an Update SQL Statement to variable "sql_statement" , ran the code and it worked.

sql_statement = " UPDATE [owner].[Property] " \

"SET [FOLIO] = APP.TCIS_GIS.FOLIO " \

 ……..

sql_statement_list = sql_statement.split(";")

egdb_return = egdb_conn.execute(sql)

Thanks

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

Glad you could get something working.  Just a note of caution, ArcSDESQLExecute doesn't recognize versions, so you will be making updates to base tables unless your SQL is using versioned views.

0 Kudos
JoseSanchez
Occasional Contributor III

In this case layers are not versioned, so ArcSDESQLExecute runs fine.

0 Kudos