suhanats

runtime error and create and update featureclass

Discussion created by suhanats on Jan 19, 2012
Latest reply on Jan 25, 2012 by suhanats
I have a sql table which has large number of records, more than 1,000,000 with lat lon values along with other attributes.
ID field uniquely identifies each row. The table is updated everyday and will keep growing.
With all the online help, I could write a script where it creates a featureclass and converts to a feature class.
I am experiencing two problems.
1. The script runs fine and creates a featureclass, but after sometime, it stops giving a runtime error saying 'This application has requested the runtime to terminate it in an unusual way' and points to pythonw.exe
Is it because the sql table size is large?
It does create a featureclass and does insert points there until that error.
2. since the sql table gets updated everyday, i want to find the max id from featureclass and update to exising featureclass only those rows from sql where ID greater than featurelcassid
So, the first time it is okay that it creates featureclass, but next time, I just want it to update the existing featureclass with newer records.

import pyodbc
import os, sys
import arcpy
import datetime
from arcpy import env

arcpy.env = r'\\C\test.gdb'

if arcpy.Exists("fcTest"):       
       arcpy.Rename_management(fcTest, 'fcTest + str(datetime.date.today().day))      
              
db = r'\\C\test.gdb'
spatial_reference = r'\\C\test.gdb\fcSR'
fcTest=r'\\C\test.gdb\fcTest'

conn = pyodbc.connect(r"......")

query = 'select * FROM tbl1'
arcpy.CreateFeatureclass_management(db,"fcTest", "POINT", "", ENABLED", "DISABLED", spatial_reference)
arcpy.AddField_management(fcTest, "ID", "LONG", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.AddField_management(fcTest, "NAME", "TEXT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.AddField_management(fcTest, "POSITION", "TEXT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.AddField_management(fcTest, "TITLE", "TEXT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.AddField_management(fcTest, "OTHER", "TEXT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.AddField_management(fcTest, "HIREDATE", "DATE", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.AddField_management(fcTest, "Lat", "FLOAT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")
arcpy.AddField_management(fcTest, "Long", "FLOAT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")

insertCur = arcpy.InsertCursor(fcTest)   
    
for row in conn.execute(query):       
        curRow = insertCur.newRow()      
        curRow.Shape = arcpy.PointGeometry(arcpy.Point(float(row.LONG),float(row.LAT)))       
        curRow.ID = row.ID
        if row.NAME is not None:
                curRow.NAME = row.NAME
        elif row.NAME is None:
                curRow.NAME = None
        if row.POSITION is not None:
                curRow.POSITION = row.POSITION
        elif row.POSITION is None:
                curRow.POSITION = None
        if row.TITLE is not None:
                curRow.TITLE = row.TITLE
        elif row.TITLE is None:
                curRow.TITLE = None
        if row.OTHER is not None:
                curRow.OTHER = row.OTHER
        elif row.OTHER is None:
                curRow.OTHER = None
        if row.HIREDATE is not None:
                curRow.HIREDATE = row.HIREDATE
        elif row.HIREDATE is None:
                curRow.HIREDATE = None           
        if row.LAT is not None:
                curRow.LAT = float(row.LAT)
        elif row.LAT is None:
                curRow.LAT = None
       if row.LONG is not None:
                curRow.LONG = float(row.LONG )
        elif row.LONG is None:
                curRow.LONG = None              
        insertCur.insertRow(curRow)

if insertCur: del insertCur
if curRow: del curRow

Outcomes