0

I created a python script that uses "pyodbc" plugin to connect a SQL Server database and write out data. It was imported in an ArcGIS 10.1 tool and run from there. try:

arcpy.AddMessage('FLAG 0')
cnxn = pyodbc.connect('DSN=' + DSN + ';UID=' + UID + ';PWD=' + PASS)
cursor = cnxn.cursor()

arcpy.AddMessage('FLAG 1')
# Retrieves all the records from the All Obs for KML view
if cnxn is None:
    arcpy.AddMessage('cnxn is None')
else:
    arcpy.AddMessage('cnxn is not None')

if cursor is None:
    arcpy.AddMessage('cursor is None')
else:
    arcpy.AddMessage('cursor is not None')

cursor.execute('SELECT LocationName, Lat from VW_Obs_for_KML_Exp')
for row in cursor:
     arcpy.AddMessage(row.LocationName + ', ' + str(row.Lat))

arcpy.AddMessage('FLAG 2')

except (pyodbc.IntegrityError, pyodbc.ProgrammingError, pyodbc.Error):
    arcpy.AddMessage("PYODBC Error. Details: " + str(sys.exc_info()[:2]))
except TypeError, e:
    arcpy.AddMessage("TypeError. Details: " + str(sys.exc_info()[:2]))
else:
    arcpy.AddMessage("No error occurred.")

The script accesses a Table and outputs 'Lat' as Numeric(38, 8) data type. It worked perfect the first time from ArcCatalog. However, it threw out an error the second time and onwards. Detailed error stack trace is as follow:

FLAG 0
FLAG 1
cnxn is not None
cursor is not None
Runtime error 
Traceback (most recent call last):
  File "<string>", line 1, in <module>
  File "C:\Python27\ArcGIS10.1\Lib\decimal.py", line 543, in __new__
    m = _parser(value.strip())
TypeError: 'NoneType' object is not callable
TypeError. Details: (<type 'exceptions.TypeError'>, TypeError("'NoneType' object is not callable",))
Process is complete.

So I had to restart ArcCatalog to successfully run it again. By the way, it also worked well when run from Python in the command line prompt. So this error only occurred when run from ArcCatalog.

Is there anyone who has worked with "pyodbc" plugin and experienced similar problem before?

  • Are you sure cnxn is not None? One of those variables is returning None. Do you have more of the error output. – Nathan W Aug 19 '13 at 7:06
  • Hi Nathan. I added the full set of codes here. – alextc Aug 19 '13 at 22:58
  • Do you have more of the error output? – Nathan W Aug 19 '13 at 23:04
  • Both "cursor" and "cnxn" were not None. When I took out 'Lat' from the SELECT execution, it worked well. – alextc Aug 19 '13 at 23:14
  • Please see detailed error in the original post. – alextc Aug 19 '13 at 23:17
1

I came across this looking for something else, however I think your issue may be with the connect string and the DSN parameter, try adding Trusted_Connection=yes; after the DSN in the connection string.

I usually run my arcpy scripts with their own database user and permissions without using Microsoft Authentication and the connection string looks like this

'DRIVER={SQL Server};Server=' + machinename + ';DATABASE=' + DBName + ';UID=' + DBUser +';PWD=' + DBPass

It also appears that with a DSN you may not want to be sending a username.

more info here: http://code.google.com/p/pyodbc/wiki/ConnectionStrings

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.