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.