topic Re: Creating an ArcToolbox from a SQL sp with python? in Python Questions

Creating an ArcToolbox from a SQL sp with python?

JeremyBeasley — Thu, 31 Jul 2014 16:02:04 GMT

Hello everybody, let me start by saying my experience with python is limited, so excuse my ignorance if I ask something simple. My question is this...Is it possible to call on a SQL sp from a SQL server through python on my local machine and then create a toolbox that will return the desired results from the SQL sp just like it was executed on the SQL server? I'm trying to streamline my process and allow other users who are not familiar with SQL sp's to use these capabilities...TIA!

Cheers,

Jeremy

Re: Creating an ArcToolbox from a SQL sp with python?

BenSmith — Thu, 31 Jul 2014 16:23:21 GMT

Try this using the arcpy.ArcSDESQLExecute function. Not a spatial SQL function but it work the same way and return your results.

workspace = "Database Connections\\GGE01000_ggadmin.sde"

conn = arcpy.ArcSDESQLExecute(workspace)

sql = "SELECT GROUPID FROM ggadmin.SCH_PAGE_DATA WHERE GROUPID IS NOT NULL AND ENABLE = 1 GROUP BY GROUPID;"

GroupIDvalues = conn.execute(sql)

Re: Creating an ArcToolbox from a SQL sp with python?

JeremyBeasley — Thu, 31 Jul 2014 20:04:28 GMT

Hi Ben, thanks for your response. I'm making some progress, but have one question...do I need to change what is in quotations after sql= in your statement? I'm guessing that is a generic example, but what info needs to be written there? The stored procedure itself? Thanks!

Re: Creating an ArcToolbox from a SQL sp with python?

BenSmith — Thu, 31 Jul 2014 20:13:02 GMT

Yes, after the SQL = should be your spatial query. That was a generic flat query from DB2 and was there just as an example. If you are calling a stored procedure then I would think it would be whatever commands you would use in the SQL tool you used to create and query when you created the SP.

The query in the example I sent is formatted for a basic select against a DB2 database.

Re: Creating an ArcToolbox from a SQL sp with python?

JeremyBeasley — Fri, 01 Aug 2014 14:05:37 GMT

Cool, thanks. Basically what I'm doing with this SP is examining a feature and it's related features for a given condition and if that condition is met, it is shown in a table and used for reporting purposes. I've copied out the SP script that created the SP in SQL Sever Management Studio and placed it in the "" after SQL=. When I run the execute script for my SP in SSMS, I'm required to provide a unique 'date' and a unique 'name'. If possible I'm trying to create a toolbox that would allow others users who are not familiar with SQL the ability to execute this script and select the two requirements as drop down lists parameters. Is this possible?

Re: Creating an ArcToolbox from a SQL sp with python?

BenSmith — Fri, 01 Aug 2014 15:58:04 GMT

I know there are some esri examples in the help that describe creating a toolbox function that will take user input values. To pass variable such as user input to the SQL function I would do something like this with the SQL string.

userDATE = (input from toolbox)

userNAME = (input from toolbox)

SQL = "SELECT WHERE DATE = '%s' AND NAME = '%s';" %(userDATE, userNAME)

The ‘%S’ takes the variables userDATE and userNAME in order and substitutes them into the string.