Latest Contributions by NasrullahKhan

Hi Folks, need help to sort my first script I have written in python (arcpy, sqlite3). Basically I am trying to update x y fields of a non-spatial sqlite3 database table (contains about 10 million records) with point geometries (SHAP@X and SHAPE@Y) values from one of the point feature layer (about 20000-30000 records), where IDs (Station_Value) match in both point feature layer and target sqlite3 database table. The script I have written will run but take about 40 minutes to update about 20000 records. I can't really figure it out why? hope someone can help to improve it, your help in this regard will be much appreciated. Below is a sample of my script: class customErr(Exception): pass import arcpy, sqlite3, traceback try: # Get parameters inputFC = arcpy.GetParameter(0) dbName = arcpy.GetParameterAsText(1) tblName = arcpy.GetParameterAsText(2) fldStn = arcpy.GetParameterAsText(3) fldX = arcpy.GetParameterAsText(4) fldY = arcpy.GetParameterAsText(5) # Check FC is point type, has ID field. inDesc = arcpy.Describe(inputFC)# Get description of FC if inDesc.shapeType.lower() != "point": raise customErr, "Data must be point type" if not inDesc.hasOID: raise customErr, "Data must have an ID field" # Get reference to active data frame mxd = arcpy.mapping.MapDocument("CURRENT") df = mxd.activeDataFrame arcpy.AddWarning("Updating Database...") cnGP = sqlite3.connect(dbName) fields = ['Station_Value', 'SHAPE@X','SHAPE@Y'] with arcpy.da.SearchCursor(inputFC, fields) as cursor: for row in (cursor): Station_Value = row[0] coordX = row[1] coordY = row[2] strSQL = 'UPDATE {0} SET {3} = {5}, {4} = {6} WHERE {1} in ({2})'.format(tblName, fldStn,Station_Value,fldX,fldY,coordX,coordY) cnGP.execute(strSQL) cnGP.commit() cnGP.close() except customErr, msg: arcpy.AddError(msg) except Exception, ErrorDesc: arcpy.AddError(ErrorDesc) arcpy.AddWarning(traceback.format_exc()) finally: try: del inputFC, DbName, tblName, fldStn , fldX, fldY, mxd, df del planList, inDesc, inputFC del conn, strSQL arcpy.Delete_management("in_memory") except: pass‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍ Note: I am working in ArcGIS/Arcview Basic version 10.6. ... View more