How do I transfer a SQL variable back into an Arcpy script?

Question

I am trying to teach myself to access sql tables in an SDE database from an Arcpy script as part of a larger QC process. I am fairly certain I have the sql statement correct (it works in SQL Server, albeit formatted a little differently -  the python window didn't like the line breaks), but I have no clue how to return my declared sql variable back as a python variable so that I may run other stuff on it. Ultimately, I would like to be able to run stored procedures via the script, but baby steps first.

I am trying to check a sql table in a SDE Database to see if the value of a single column is smaller than 1000 but larger than 10. If there are values outside of this range, I need to run other processes.

This is what I've got so far:

SDE = r'Database Connections\NC_RISK - test - 106.sde'
    SQLLink = arcpy.ArcSDESQLExecute(SDE)
    L_HAZARDPROBABILITY = r'\NC_RISK.sql.L_HAZARDPROBABILITY'
    
    sql = """LARE @MyBadIndicator int; SET @MyBadIndicator = 0; SET @MyBadIndicator = SELECT 1 WHERE EXISTS (SELECT L_HAZARDPROBABILITY.F_EVENT
        FROM L_HAZARDPROBABILITY WHERE L_HAZARDPROBABILITY.F_EVENT<10 Or L_HAZARDPROBABILITY.F_EVENT >1000)"""

SQL has always been a little intimidating to me (primarily b/c I've just never known where to start). So, I just going to jump in head first. Any help would be greatly appreciated. If there are any recommended resources I should check out to learn this stuff, that would be appreciated as well.

I have been playing around with this a little more trying to get something to happen, anything really, and I keep getting the same errors.

In the above script, I never execute the sql query. When I do, I receive the following error:

AttributeError: ArcSDESQLExecute: StreamPrepareSQL ArcSDE Error -37 \ue61c

I tried changing the query to

sql = "SELECT F_EVENT FROM L_HAZARDPROBABILITY WHERE F_EVENT<10"

and I get the same error.

When I change the code to

try:
    sql = "SELECT F_EVENT FROM L_HAZARDPROBABILITY WHERE F_EVENT<10"
    result = SQLLink.execute(sql)
except Exception as err:
    print (err)
    result = False

I get

UnicodeEncodeError: 'ascii' codec can't encode character u'\ue61c' in position 52: ordinal not in range(128)

Message was edited by: John Lay to include further experimentation.

JohnLay · Answer

Thanks James,

Of course I was trying to make things way more difficult than they needed to be. I had originally gone down this route because I was unable to access the sql.L_HAZARDPROBABILITY table after performing a os.path.join on the database and the table name. I left out the very important "sql."

I will look into this other method when I start down the SQL path again--shouldn't be too long.

JamesCrandall · Answer

If they are SDE tables then why not just use arcpy.ListTables() to access and work with cursors?

If you truly need to access outside of the ESRI stack, I'd start with just issuing the desired T-SQL against the database using pyodbc.  This doesn't give you an solution for Stored Procedures but might start you off down that path.

Adapted from existing implementation but untested:

Update (referencing Sproc with parameters seems pretty straight forward):

sqlcursor.execute("{call dbo.SProName(?,?)}", (param1), (param2))

Sample pyodbc:

import pyodbc

conn = pyodbc.connect('DRIVER={SQL Server Native Client 11.0};' +
                             'SERVER=' + servername +
                             ';DATABASE=' + databasename +
                             ';schema=' + schemaname +
                             ';UID=' + username +
                             ';PWD=' + password)

#I'm not totally sold on this being the best choice of sql
sql = """SELECT 1 WHERE EXISTS (SELECT L_HAZARDPROBABILITY.F_EVENT 
      FROM L_HAZARDPROBABILITY WHERE L_HAZARDPROBABILITY.F_EVENT<10 Or L_HAZARDPROBABILITY.F_EVENT >1000)"""

sqlcursor = conn.cnxn.cursor()
sqlcursor.execute(sql)
sqlrows = sqlcursor.fetchall()

datArray = []
for sqlrow in sqlrows:
     datArray.append(sqlrow)

if len(datArray) > 0:
     #we have at least 1 row in the array filled by cursor
     'do stuff

sqlcursor.close()