topic the trouble of python connecting the sqlserver with pymssql or pyodbc,help!! in Python Questions

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

billchuang — Thu, 28 Jul 2011 06:25:52 GMT

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)

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

StacyRendall1 — Fri, 29 Jul 2011 01:45:47 GMT

Do you have to run it from Arc? If it works fine from the command (dos) prompt, and you don't get any parameters from Arc, can't you just use it from the command?

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

billchuang — Fri, 29 Jul 2011 03:22:09 GMT

Do you have to run it from Arc? If it works fine from the command (dos) prompt, and you don't get any parameters from Arc, can't you just use it from the command?

Yes,the script runs fine from the command(Python shell and dos) ,but I have to run it from arcmap because i want to use it as GP service in the arcgis server in order that i can submitJob using arcgis js api in the client browser.I publish the GP tool to the server before,as a result,it can run fine once,and secondly it fails unless I close the browser and restart the browser.
now I find solution of connecting the sqlserver in more times,the reason is that i ticked an item which is Script "properties"->"Source" tab->"Run Python script in process",but i unticked the item,new problem occurs,when the script execute to the "pntFeatSet.load(pointFC)",it fails,but the clause works fine in the shell or dos,I can't understand.Besides,the shell always reminder me that my custom toolbox isn't licensed,I'm so depressed?Can you help me?

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

MattFrancis — Tue, 23 Aug 2011 15:38:07 GMT

Any luck on this? I can't get pymsql to import without crashing idle.

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

StacyRendall1 — Fri, 10 Dec 2021 22:29:13 GMT

Matt,

In my experience IDLE sucks. Have you tried the command prompt to see what happens when you import pymsql?

I.e. Start > Run > cmd.exe
then:

C:\> python
>>> import arcpy
>>> import pymsql

If pymsql works here you could try another editor...