bill_chuang

the trouble of python connecting the sqlserver with pymssql or pyodbc,help!!

Discussion created by bill_chuang on Jul 27, 2011
Latest reply on Aug 23, 2011 by stacyrendall
InArcGIS 10,I write a GP tool with python to connect the sqlserver2005 in which i can get the points(I don't use the arcsde) and then i want to show the points in the map,but the strange trouble occurs.I succeed executing the script at the first time, but secondly fails and arcmap closed automatically with a dialog about "do you want to send the error" .I tried several times,the errors is same every time.I execute the script in the dos window where i can succeed executing whatever times i try.I am troubling ,this situation is so strange,I need help!!
Below is my code with pymssql to connect sqlserver:
import arcpy,pymssql
from arcpy import env
env.overwriteOutput = True
#env.scratchworkspace = r"G:\SOURCE PROGRAMS\arcgisserver\Isosurface\Scratch"
#arcpy.ImportToolbox(r'G:\SOURCE PROGRAMS\arcgisserver\Isosurface\MyTools.tbx')

dateTime = arcpy.GetParameterAsText(0)
if dateTime == '#' or not dateTime:
    dateTime = '2007081820'

tableName = arcpy.GetParameterAsText(1)
if tableName == '#' or not tableName:
    tableName = 'Temperature'

lookupField = arcpy.GetParameterAsText(2)
if lookupField == '#' or not lookupField:
    lookupField = 'Temperature'
  
#connect sqlserver2005
arcpy.AddMessage('preparing getting FeatureSet from sqlserver')
con_sql = pymssql.connect(host='BILL\SQL2005',user='sa',password = 'sqlserver',database='test')
con_sql = pymssql.connect(host='BILL\SQL2005',user='sa',password = 'sqlserver',database='test')
#get the row cursor
sql_cursor  = con_sql.cursor()
#execute sql clause
sql_cursor.execute('select Longitude,Latitude,'+tableName+'.'+lookupField+' from AutoStation,'+tableName+' where '+\
                    'AutoStation.Station_Num='+tableName+'.Station_Num and '+tableName+'.Data_time = '+dateTime)
#get the all rows from the executed data
arcpy.AddMessage('getting data from sqlserver')
rows = sql_cursor.fetchall()
con_sql.close();
del sql_cursor
del con_sql

#create one FeatureClass
arcpy.AddMessage('calculate the spatialreference')
spatial_ref = arcpy.CreateObject('SpatialReference')
spatial_ref.loadFromString('104000')

#create the empety FeatureClass
arcpy.AddMessage('preparing creating FeatureClass')
pointFC = arcpy.CreateFeatureclass_management('in_memory','points','POINT','',\
                                              'DISABLED','DISABLED',spatial_ref)
arcpy.AddField_management(pointFC,'SPATIALVALUE','DOUBLE')
fc_cursor = arcpy.InsertCursor(pointFC)

#insert geometry data into FeatureClass Object
arcpy.AddMessage('insert geometry data into FeatureClass Object')
pnt = arcpy.Point()
for row in rows:
    print 'Longitude:',row[0],'Latitude:',row[1],'SpatialValue:',row[2]
    pnt.X = float(row[0])
    pnt.Y = float(row[1])
    feat = fc_cursor.newRow()
    feat.shape = pnt
    if row[2] == None:
        feat.setValue('SPATIALVALUE',0.0)
    else:
        feat.setValue('SPATIALVALUE',float(row[2]))
    fc_cursor.insertRow(feat)

#create FeatureSet Object
arcpy.AddMessage('preparing generating the FeatureSet as the input of IsosurfaceAnalyze model')
pntFeatSet = arcpy.FeatureSet()
pntFeatSet.load(pointFC)
arcpy.AddMessage('succeed getting FeatureSet\npreparing generate the isosurface')
arcpy.SetParameter(3,pntFeatSet)

Outcomes