I'm using `ArcGIS for Desktop 10.3.0.4322`.
I have a peculiar occurrence that I don't quite fully understand. I have a Python Script Tool that does a number of different things, to include using the `ArcPy Mapping Module` to add newly acquired data to the `CURRENT` .mxd for viewing. This works perfectly fine, except when running a bit of additional code (bottom of post), which optionally uses (based on user input) the `arcpy.TableToExcel_conversion` tool in order to provide the user with an Excel spreadsheet version of the feature class data they queried. When watching the script run, the new feature class, `queryLayer`, shows up in the .mxd's Table of Contents, only to immediately disappear thereafter. While investigating, the feature class still exists and is fine (as would be expected), as the `arcpy.TableToExcel_conversion` tool does not delete the source file, it merely creates a newly converted one. So it is obvious that something is happening while/after the conversion is being made (as without the conversion it works fine), but I don't understand why it would be removing the newly added feature class from the .mxd's data frame/TOC. Any help or thoughts are greatly appreciated, thank you!
# 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 if they want to overwrite previously named Query Layer
overwriteQ = arcpy.GetParameterAsText(3)
# Ask user for an SQL Query Expression to be run against the selected Oracle dB
sqlQuery = arcpy.GetParameterAsText(4)
# Ask user to name the output of excel file (this is optional, for those who want to do excel analysis)
excelName = arcpy.GetParameterAsText(5)
# Ask user if they want to overwrite previously named Excel File
overwriteE = arcpy.GetParameterAsText(6)
# Ask user to select the folder output of the excel file (optional as well, depending on if user wants an excel file)
excelFolder = arcpy.GetParameterAsText(7)
# Create spatial reference variable to assign to queryLayer
spatialRef = arcpy.SpatialReference("W:\Coordinate Systems\LRS Lambert.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", "POINT", "1050010", spatialRef)
# Set overwrite output for Query Layer to user's choice
arcpy.env.overwriteOutput = overwriteQ
# Process: 'Copy Features' - Copies the temporary Query Layer and stores it as a permanent feature class
arcpy.CopyFeatures_management("Temp_Layer", queryLayer)
# Process: 'Delete Features' - Deletes the temporary file Temp_Layer
# This allows for multiple executions of this tool within the same ArcMap session without error
arcpy.Delete_management("Temp_Layer")
# 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", "STRING") # 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 (pings) for the Total_Pings field
numRows = int(arcpy.GetCount_management(queryLayer).getOutput(0))
# UpdateCursor that will write the value of numRows to the Total_Pings field
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)
try: # try to read in values based on the user's SQL query
firstLogdt = [row[0] for row in arcpy.da.SearchCursor(queryLayer, "LOGDT")][0]
except: # exception error if the user's SQL query produces no return
err = "No Results Found Via SQL Query\nSCRIPT EXITED" # Error message variable
arcpy.AddError(err) # Print error message
sys.exit() # Exits the script, does not attempt to run any further
# UpdateCursor that will write the first (earliest) LOGDT value to the First_Time field
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 Last_Time field
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 timeSecs to a readable format (yy:dd:hh:mm:ss)
def readTime(seconds):
minutes, seconds = divmod(seconds, 60)
hours, minutes = divmod(minutes, 60)
days, hours = divmod(hours, 24)
years, days = divmod(days, 365)
return '%02d:%02d:%02d:%02d:%02d' % (years, days, hours, minutes, seconds)
# UpdateCursor that will write the time difference calculation to the 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 with optimal reception
possiblePings = timeSecs / 5 # 1 ping every 5 seconds
# UpdateCursor that will write the total number of pings possible to the Possible_Pings field
cursor = arcpy.da.UpdateCursor(queryLayer, "Possible_Pings")
for row in cursor:
row[0] = possiblePings
cursor.updateRow(row)
# Using ArcPy Mapping Module to add queryLayer to current .mxd map display and zoom to it
mxd = arcpy.mapping.MapDocument("CURRENT") # Set variable to currently active .mxd
dataFrame = arcpy.mapping.ListDataFrames(mxd)[0] # Set variable equal to the first data frame within mxd
addLayer = arcpy.mapping.Layer(queryLayer) # Set variable for queryLayer
arcpy.mapping.AddLayer(dataFrame, addLayer) # Adds queryLayer to the map
dataFrame.zoomToSelectedFeatures() # Zooms to queryLayer
arcpy.RefreshActiveView() # Refreshes the active data frame's view
# Set overwrite output for Excel File to user's choice
arcpy.env.overwriteOutput = overwriteE
# Change workspace environment to where the user wants the Excel file to be saved
try: # Tries to change the environment workspace to the user's optional excel output folder
arcpy.env.workspace = excelFolder
except:
pass # If user did not specify excel folder input, then let pass and continue on
# Process: 'Table To Excel' - Converts the final queried data to an excel spreadsheet file (optional)
try: # Tries to run the tool (will only work if user specified optional excel inputs)
arcpy.TableToExcel_conversion(queryLayer, excelName+'.xls')
except:
pass # If user did not specify optional excel inputs, then let pass and continue on
# delete cursor, row variables
del cursor, row
