Thanks for the reply James. I will definitely give cx_Oracle a try.Here is what I am doing with ArcSDESQLExecute:
import arcpy, os
from arcpy import env
import sys
import time
try:
t0 = time.clock()
env.workspace = r"C:\ags\python"
sdeConn = arcpy.ArcSDESQLExecute(r"C:\ags\gp\gisselect_cdodb_t.sde")
sql = "SELECT lon, lat, snow FROM gis.snow_sum_partitioned WHERE date_time = to_date('19991231','yyyymmdd') AND snow IS NOT NULL"
try:
# execute SQL statement
sdeReturn = sdeConn.execute(sql)
except Exception, ErrorDesc:
print ErrorDesc
sdeReturn = False
# if return value is a list (a list of lists), display each list as a row from the table being queried.
if isinstance(sdeReturn, list):
# set spatial reference
prjFile = "geo_wgs84.prj"
spatialRef = arcpy.SpatialReference(prjFile)
tmpWorkspace = "in_memory"
outXYfc = "XY_FeatureClass"
# create in_memory empty feature class
arcpy.CreateFeatureclass_management(tmpWorkspace, outXYfc , "POINT", "", "", "", spatialRef)
# build tmp FC path and add new field for values to interpolate
tmpFC = os.path.join(tmpWorkspace, outXYfc)
arcpy.AddField_management(tmpFC, "Snow", "DOUBLE")
# create insert cursor
inCur = arcpy.InsertCursor(tmpFC)
for rec in sdeReturn:
pnt = arcpy.Point(rec[0], rec[1])
row = inCur.newRow()
row.Shape = pnt
row.Snow = rec[2]
inCur.insertRow(row)
del inCur, row
# Set layer symbology
symbology_layer = "snow_symbology.lyr"
arcpy.ApplySymbologyFromLayer_management(outXYfc, symbology_layer)
else:
# If the return value was not a list, the statement was most likely a DDL statment. Check its status.
if sdeReturn == True:
print "SQL statement: " + sql + " ran sucessfully."
else:
print "SQL statement: " + sql + " FAILED."
except Exception, ErrorDesc:
print Exception, ErrorDesc
except:
print "Problem executing SQL."
