# ---------------------------------------------------------------------------
import sys, string, os, arcgisscripting
import pyodbc
gp = arcgisscripting.create()
gp.CheckOutExtension("spatial")
gp.AddToolbox("C:/Program Files (x86)/ArcGIS/ArcToolbox/Toolboxes/Spatial Analyst Tools.tbx")
gp.AddToolbox("C:/Program Files (x86)/ArcGIS/ArcToolbox/Toolboxes/Data Management Tools.tbx")
# What features you want to tabulate
FeaturesToTabulate = 'X:\\TableofFeatures.mdb\\FeatureTable'
# Must be a valid classification table for this raster (all fields in the SQL statements need to be valid)
Classification_Table = 'X:\\ClassificationTables\\SimpleClassTbl.dbf'
Results_Geodatabase = 'U:\\CRUNCH\\TestTabProgram.mdb'
Tabulated_Area_Table = "TblTestSimple"
FieldtoTabulate = 'COUNT' #This the field that will be summarized
TabulationMultiplier = 100 # This is a float(!) multiplier in case of a raster COUNT - * Cell size for example
TabByZoneField = "True"
Zone_field = 'WTRSHD_ID'
WholeZoneName = 'WholeTable'
# GUI entries (shortened for forum posting)
FeatToTab = FeaturesToTabulate
ClassTbl = Classification_Table
TabGeoDB = Results_Geodatabase
TabTbl = Tabulated_Area_Table
FldtoTab = FieldtoTabulate
TabMult = float(TabulationMultiplier)
TabByZone = TabByZoneField
ZoneFld = Zone_field
AltZone = WholeZoneName
# Make new tabulated table
geodbname = TabGeoDB.split("\\")[len(TabGeoDB.split("\\"))-1]
geodbpath = TabGeoDB[:len(TabGeoDB)-len(geodbname)]
if not(os.path.exists(TabGeoDB)):
gp.CreatePersonalGDB_management(geodbpath, geodbname)
try:
gp.CreateTable(TabGeoDB,TabTbl)
except:
gp.AddMessage("Table already exists, please rename the output table or delete the existing table.")
print "Output table already exists."
gp.GetMessages()
TabGeoDBTbl = TabGeoDB + "/" + TabTbl
# Get ClassName and SQL Expression lists
rows = gp.SearchCursor(ClassTbl,"","","","")
row = rows.next()
ClassNmList = [row.GetValue("ClassNm")]
SQLExprList = [row.GetValue("SQLExpr")]
row = rows.next()
while row:
theclass = row.GetValue("ClassNm")
SQLexp = row.GetValue("SQLExpr")
ClassNmList.append(theclass)
SQLExprList.append(SQLexp)
row = rows.next()
del row, rows
# Copy the attribute table or stand-alone table that is being tabulated into the Tabulation Geodatabase.
fullname = gp.ParseTableName(os.path.basename(FeatToTab))
nameList = fullname.split(",")
FeatToTabTbl = nameList[2].split(".")[0].strip()
#print FeatToTabTbl
if FldtoTab.lower() == "count":
FldtoTab = "COUNT_"
gp.OverwriteOutput = 1
#May Need to make a table view for raster feature sets
lt = ["rasterdataset","rasterband"]
try:
dsc = gp.describe(FeatToTab)
if string.lower(dsc.DatasetType) in lt:
TblView = gp.MakeTableView_management(FeatToTab,FeatToTabTbl)
gp.CopyRows_management(TblView, TabGeoDB + '\\' + FeatToTabTbl)
else:
TblView = TabGeoDB + '\\' + FeatToTabTbl
gp.CopyRows_management(FeatToTab, TabGeoDB + '\\' + FeatToTabTbl)
del dsc
except:
gp.GetMessages()
gp.OverwriteOutput = 0
gp.GetMessages()
gp.AddMessage("")
try:
if TabByZone == "True" or TabByZone == "true":
# Get Zone List
zrows = gp.SearchCursor(TblView,"","","",str(ZoneFld) + " A")
zrow = zrows.next()
zonelast = zrow.GetValue(ZoneFld)
ZoneList = [zonelast]
zrow = zrows.next()
maxlen = len(zonelast)
while zrow:
zone = zrow.GetValue(ZoneFld)
if zone == zonelast:
zrow = zrows.next()
else:
ZoneList.append(zone)
zonelast = zone
zrow = zrows.next()
if len(zone)>maxlen:
maxlen = len(zone)
del zrow, zrows
fields = gp.ListFields(TblView)
field = fields.next()
while field:
if field.Name == ZoneFld:
zonefldtype = field.Type
break
field = fields.next()
# Begin the tabulation file with a zone ID and remove the auto-generated "Field1"
try:
if zonefldtype == "String":
gp.AddField_management(TabGeoDBTbl,"ZoneID","Text",maxlen+4)
else:
gp.AddField_management(TabGeoDBTbl,"ZoneID",zonefldtype)
gp.DeleteField_management(TabGeoDBTbl,"Field1")
gp.AddField_management(TabGeoDBTbl,"TOTTABAREA","Long")
except:
gp.GetMessages()
# Add field for Class Name in the Classification Table
for theclass in ClassNmList:
try:
gp.AddField_management(TabGeoDBTbl,theclass,"Long")
except: gp.GetMessages() #Try-Except for debug, if already added
# Open the ODBC Connection to the Personal Geodatabase
try:
DBfile = TabGeoDB #'/data/MSAccess/Music_Library.mdb'
conn = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb)};DBQ='+DBfile)
cursor = conn.cursor()
gp.AddMessage("Connection opened")
except:
print gp.GetMessages()
gp.AddMessage("Problem Occurred with the ODBC connection.")
del gp
exit
i = 0
classedtotarea = 0
# Begin the tabulation table by inserting the ZoneID's
SQL = "SELECT * FROM " + TabTbl
tblcnt = cursor.execute(SQL).rowcount
if tblcnt >0:
print "Tab Table already populated with zones."
else:
SQL = "INSERT INTO " + TabTbl + " ( ZoneID ) " \
"SELECT " + FeatToTabTbl + "." + ZoneFld + " " \
"FROM " + FeatToTabTbl + " " \
"GROUP BY " + FeatToTabTbl + "." + ZoneFld + " " \
"ORDER BY " + FeatToTabTbl + "." + ZoneFld + ";"
cursor.execute(SQL)
conn.commit()
gp.AddMessage("Select Zones into Tab Table Complete.\n")
# Process the features to tabulate, looping through each Class/SQL Expression
for zone in ZoneList:
print "Working on zone: " + str(zone)
gp.AddMessage("Working on zone: " + str(zone))
if zonefldtype == "String":
zone = "'" + zone + "'"
#Get the total area for the zone
whereclause = ZoneFld + ' = ' + zone
totSQL = "SELECT Sum(" + FldtoTab + ") as TOTCOUNT " + \
"FROM " + FeatToTabTbl + " " + \
"WHERE " + whereclause + ";"
print totSQL
gp.AddMessage(str(totSQL))
tottabarea = cursor.execute(totSQL).fetchone()[0]
if tottabarea is None:
tottabarea = 0
else:
tottabarea = tottabarea * TabMult
insertSQL = "UPDATE " + TabTbl + " " + \
"SET TOTTABAREA =" + str(tottabarea) + " " + \
"WHERE ZoneID = " + zone + ";"
cursor.execute(insertSQL)
conn.commit()
i = 0
for theclass in ClassNmList:
SQL = SQLExprList
i = i+1
#Create the summary by this class
whereclause = "(" + SQL.replace('"','') + ") AND " + ZoneFld + ' = ' + zone
totSQL = "SELECT Sum(" + FldtoTab + ") as TOTCOUNT " + \
"FROM " + FeatToTabTbl + " " + \
"WHERE " + whereclause + ";"
gp.AddMessage(str(totSQL))
totarea = cursor.execute(totSQL).fetchone()[0]
gp.AddMessage("Class area is = " + str(totarea))
if totarea is None:
totarea = 0
else:
totarea = totarea * TabMult
#Add the insert SQL to put the results into the Tabulation Table
insertSQL = "UPDATE " + TabTbl + " " + \
"SET " + theclass + "=" + str(totarea) + " " + \
"WHERE ZoneID = " + zone + ";"
cursor.execute(insertSQL)
conn.commit()
gp.AddMessage("Class complete\n")
gp.AddMessage("Completed tabulation table.")
except:
gp.GetMessages()
try:
cursor.close()
conn.close()
del conn, cursor
except: pass
del gp
import arcpy import pyodbc inputString = arcpy.GetParameterAsText(0) arcpy.SetParameterAsText(1, 'echo:'+inputString) try: arcpy.AddMessage("START") conn=pyodbc.connect('DRIVER={SQL Server};SERVER=DGENV-LPD.aris-lux.lan\LPD;DATABASE=lpd_proto;UID=lpd_proto;PWD=lpd_proto') cursor = conn.cursor() cursor.execute("INSERT INTO IMPORT_BATCH (batchDate,lifeProjectNumber,archiveName,stage) VALUES ('2012-03-26 17:16:37.4900000','Minimal test','Grrrrr...','TEMP')") r=cursor.execute("SELECT @@IDENTITY") f=cursor.fetchone() cursor.close() conn.commit() arcpy.AddMessage("DONE") except Exception,e: arcpy.AddMessage("e="+str(e)) finally: conn.close()
Results: out: echo:test Messages: esriJobMessageTypeInformative: Executing (InputScriptLabel): InputScript test esriJobMessageTypeInformative: Start Time: Tue Mar 27 11:27:04 2012 esriJobMessageTypeInformative: Running script InputScript... esriJobMessageTypeInformative: START esriJobMessageTypeInformative: DONE esriJobMessageTypeInformative: Completed script InputScript... esriJobMessageTypeInformative: Succeeded at Tue Mar 27 11:27:04 2012 (Elapsed Time: 0.00 seconds)
Results: out: echo:test Messages: esriJobMessageTypeInformative: Executing (InputScriptLabel): InputScript test esriJobMessageTypeInformative: Start Time: Tue Mar 27 11:23:31 2012 esriJobMessageTypeInformative: Running script InputScript... esriJobMessageTypeInformative: START esriJobMessageTypeInformative: e='NoneType' object is not callable esriJobMessageTypeInformative: Completed script InputScript... esriJobMessageTypeInformative: Succeeded at Tue Mar 27 11:23:31 2012 (Elapsed Time: 0.00 seconds)
if __name__ == '__main__': import os, sys e = sys.executable.lower() if 'arcmap.exe' in e or 'arccatalog.exe' in e: import pyodbc main()
mahunter243
pyodbc and ArcGIS do not play well together. I get the same problem described here, run once ok, then crash on the 2nd run. There are 2 workarounds that I've found. 1st, run the geoprocessing tool out of process. I don't like this option, since it slows things down considerably. 2nd is simply to import pyodbc into the Python window global namespace when you start Arcmap or ArcCatalog. For some reason when that's done, any tool using pyodbc will run as many times as needed. You can do this automatically when arcmap or arccatalog starts (if it starts with the Python window open) by setting the PYTHONSTARTUP environment variable to a startup script. Here's the pertinent part of my startup script:if __name__ == '__main__': import os, sys e = sys.executable.lower() if 'arcmap.exe' in e or 'arccatalog.exe' in e: import pyodbc main()
I�??ve been struggling with the same issue. I have not been able to make my script run properly when deploying them as geoprocessing services using the pyodbc module. To me it seems like installing PyWin32 and using the odbc module is a better approach. Pyodbc and odbc is very alike,
import arcpy, pyodbc conn = pyodbc.connect('DSN=<DSN_name>;PWD=<Password>') conn.autocommit = True cursor = conn.cursor() cursor.execute('SET CHAINED OFF') cursor.execute('{call Sybase_Stored_proc()}') rows = cursor.fetchall() arcpy.AddMessage(len(rows)) cursor.close() conn.close() del rows, cursor, conn