I have the following code below and my question is this:
How do I take the returning value of a function that I've defined and use it in an UpdateCursor (updateRow)? The issue is that I'm (obviously) getting an error with regards to the syntax type not being compatible.
Essentially, what I'm trying to do is take the "raw" number of total_seconds() and update a column in the rows to make them easier to read - i.e. instead of it saying 3454352 seconds, it will be in dd:hh:mm:ss. The function works fine, it is the "cursor.updateRow(row)" that draws the syntax error. I understand that it is because it will not accept a function as the input, so how do I take just the value of the function and use that as the criteria for cursor.updateRow(row) ? Thanks in advance for any help!
error:
\Toolbox_Python_SQL_Scripts\HISTORY_ARCHIVE_query.py", line 89, in <module> cursor.updateRow(row) RuntimeError: The value type is incompatible with the field type. [Total_Time] Failed to execute (HistoryArchiveScriptTool). |
# import arcpy module
import arcpy
# Ask user to select the Geodatabase workspace to use for data output
userWorkspace = arcpy.GetParameterAsText(0)
# Set workspace environment based upon user's choice
arcpy.env.workspace = userWorkspace
# Ask user to select an Oracle dB Connection
oracleDB = arcpy.GetParameterAsText(1)
# Ask user to name the Query Layer
queryLayer = arcpy.GetParameterAsText(2)
# Ask user for an SQL Query Expression to be run against the selected Oracle dB
sqlQuery = arcpy.GetParameterAsText(3)
# Create spatial reference variable to assign to queryLayer
spatialRef = arcpy.SpatialReference("Vector\Coordinate Systems\WGS 1984.prj")
# Process: 'Make Query Layer' - Creates a Query Layer using the user's Oracle dB and SQL query expression
arcpy.MakeQueryLayer_management(oracleDB, "Temp_Layer", sqlQuery, "UNIQUE_ID", "POLYLINE", "1050010", spatialRef)
# Process: 'Copy Features' - Copies the temporary Query Layer and stores it as a permanent feature class
arcpy.CopyFeatures_management("Temp_Layer", queryLayer)
# Process: 'Define Projection' - Defines the projection of queryLayer feature class output
arcpy.DefineProjection_management(queryLayer, spatialRef)
# Process: 'Add Field' - Adds new column fields to queryLayer
arcpy.AddField_management(queryLayer, "First_Time", "DATE") # The first LOGDT ping
arcpy.AddField_management(queryLayer, "Last_Time", "DATE") # The last LOGDT ping
arcpy.AddField_management(queryLayer, "Total_Time", "FLOAT") # Summation of the first to last ping in time
arcpy.AddField_management(queryLayer, "Total_Pings", "INTEGER") # Total number of pings (rows)
arcpy.AddField_management(queryLayer, "Possible_Pings", "INTEGER") # Total number of pings possible in given timeframe
arcpy.AddField_management(queryLayer, "Time_to_Process", "DATE") # How long it took for each ping to process
# Calculates the total number of rows for newly added column Total_Pings
numRows = int(arcpy.GetCount_management(queryLayer).getOutput(0))
# UpdateCursor that will write the value of numRows to the Total_Pings column
cursor = arcpy.da.UpdateCursor(queryLayer, "Total_Pings")
for row in cursor:
row[0] = numRows
cursor.updateRow(row)
# SearchCursor that will read the values of LOGDT and return the first value (earliest)
firstLogdt = [row[0] for row in arcpy.da.SearchCursor(queryLayer, "LOGDT")][0]
# UpdateCursor that will write the first (earliest) LOGDT value to the field First_Time
cursor = arcpy.da.UpdateCursor(queryLayer, "First_Time")
for row in cursor:
row[0] = firstLogdt
cursor.updateRow(row)
# SearchCursor that will read the values of LOGDT and return the last value (latest)
lastLogdt = [row[0] for row in arcpy.da.SearchCursor(queryLayer, "LOGDT")][-1]
# UpdateCursor that will write the last (latest) LOGDT value to the field Last_Time
cursor = arcpy.da.UpdateCursor(queryLayer, "Last_Time")
for row in cursor:
row[0] = lastLogdt
cursor.updateRow(row)
# Calculates the difference between firstLogdt and lastLogdt
timeDiff = lastLogdt - firstLogdt # Produces a timedelta object, not datetime
# Calculates the total number of seconds from timeDiff
timeSecs = timeDiff.total_seconds()
# Creates a function that will convert timeDiff.total_seconds() to a readable format (dd:hh:mm:ss)
def readTime(seconds):
minutes, seconds = divmod(seconds, 60)
hours, minutes = divmod(minutes, 60)
days, hours = divmod(hours, 24)
return '%02d:%02d:%02d:%02d' % (days, hours, minutes, seconds)
# UpdateCursor that will write the time difference calculation to the new Total_Time field
cursor = arcpy.da.UpdateCursor(queryLayer, "Total_Time")
for row in cursor:
row[0] = readTime(timeSecs)
cursor.updateRow(row)
# Calculates the total number of pings that would occur if no pings were dropped
possiblePings = timeSecs / 5 # 1 ping every 5 seconds (with good reception)
# UpdateCursor that will write the total number of pings possible to the new Possible_Pings field
cursor = arcpy.da.UpdateCursor(queryLayer, "Possible_Pings")
for row in cursor:
row[0] = possiblePings
cursor.updateRow(row)
