Thanks for your interest in this problem. I suspect that there is an issue with python memory management. Here is the code with some comments:
import string, os, sys, locale, arcpy, pyodbc, random
try:
arcgis_con = pyodbc.connect('Driver={SQL Server Native Client 10.0};Server=xxx,xxx;Database=xxx;Trusted_Connection=yes;')
arcgis_cur = arcgis_con.cursor()
Area_of_Interest = arcpy.GetParameterAsText(0) #user defined polygon
outputFC = arcpy.GetParameterAsText(1) #output
year = arcpy.GetParameterAsText(2) #year
inDesc = arcpy.Describe(Area_of_Interest)
inRows = arcpy.SearchCursor(Area_of_Interest)
inRow = inRows.next()
outLine = ""
while inRow: #creates a string defining coordinates of polygon
partnum=0
feat = inRow.getValue(inDesc.ShapeFieldName) ##when run the second time, degugging reveals script fails here trying to reference inDesc.ShapeFieldName
while partnum < 1:
part = feat.getPart(partnum)
part.reset()
pnt = part.next()
pnt_count = 0
while pnt:
if pnt_count==0:
outLine = str(pnt.X) + " " + str(pnt.Y)
else:
outLine = str(pnt.X) + " " + str(pnt.Y) + " , " + outLine
pnt = part.next()
pnt_count += 1
if not pnt:
pnt = part.next()
partnum += 1
inRow = inRows.next()
randomTab = "dbo." + ''.join(random.choice(string.ascii_uppercase) for x in range(10)) #table with random name to be inserted into DBMS for query
querystring = "CREATE TABLE " + randomTab + " (id int IDENTITY (1,1), GeogCol1 geography);" #creates a sql server table with a geography column based on polygon coordinates
arcgis_cur.execute(querystring)
querystring = "INSERT INTO " + randomTab + " (GeogCol1) VALUES (geography::STGeomFromText('POLYGON((" + outLine + "))', 4283));"
arcgis_cur.execute(querystring)
querystring = ##sql query which is complex##
arcgis_cur.execute(querystring)
arcpy.CopyFeatures_management(Area_of_Interest, outputFC)
total = 0
while 1:
row = arcgis_cur.fetchone()
if not row:
break
age_var = row.age
age_var = age_var.replace("-","_")
age_var = age_var.replace(" ","")
age_var = age_var.replace("+","plus")
var = row.Sex[0] + "_" + age_var
arcpy.AddField_management(outputFC, var, "DOUBLE",10,3,"","","NULLABLE")
arcpy.CalculateField_management(outputFC, var, row.population, "PYTHON_9.3")
total = total + row.population
arcpy.AddField_management(outputFC, "Total", "DOUBLE",10,1,"","","NULLABLE")
arcpy.CalculateField_management(outputFC, "Total", total, "PYTHON_9.3")
querystring = "DROP TABLE " + randomTab + ";"
arcgis_cur.execute(querystring)
arcgis_con.commit()
arcgis_con.close()
except Exception, ErrorDesc:
The script uses a polygon defined by the user, extracts the coordinates, and inserts in SQL Server so as to carry a spatial join with spatially enabled data there. It then creates a table and polygon for the user. The script runs fine multiple times in desktop. I thought I narrowed down the issue to the line 'feat = inRow.getValue(inDesc.ShapeFieldName)', however, hardcoding a value here just makes the script run successfully one extra time, before failing again somewhere else in the program (haven't search where this time) when run again with the same memory access issue.
Good luck and appreciate any help.
Nick