AnsweredAssumed Answered

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

Question asked by jplay on Jan 12, 2016
Latest reply on Jan 12, 2016 by jplay

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.

Outcomes