Need to delete tables created in default geodatabase

[VishalShah2]

I am currently working on a custom tool that helps me automate some of my reports. Part of the reports deliverable in a spreadsheet that has footage by each report generated. The reports are KMZs. My script runs as anticipated and created tables as the script says to, but the location in which they are being saved is the default.gdb in my local documents in the ArcGIS folder.  I need to find a way to either be able to delete the tables created in the gdb after I'm able to pull my information using python or being able to save it in a custom location. My current code looks like this:

import arcpy
import arcpy.mapping as map
from arcpy import env
import datetime

date = datetime.date.today()
stats = []

statesFS = {6:'California', 14:'Illinois'}
statesAbbreviationsFS = {6:'CA', 14:'IL'}
statesCDCCode = {6:'California CDC Code', 14:'Illinois CDC Code'}

for state in statesFS:
    valueStatesFS = dict.get(statesFS, state)
    valueStatesAbbreviationsFS = dict.get(statesAbbreviationsFS, state)
    valueCDCCode = dict.get(statesCDCCode, state)
    stateMapName = str(valueStatesAbbreviationsFS) +"-" + str(valueCDCCode) + "-" + str(date)
    for field in arcpy.ListFields('Input Feature Class'):
        if field.name == 'TotalFeet':
            stats.append(['TotalFeet', "SUM"])
            arcpy.Statistics_analysis('Input Feature Class', stateMapName, stats, "")‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Any help is appreciated.

[BlakeTerhune]

Try building the stateMapName as a full path instead of just a table name. If you want it to be a temporary table that is deleted, use in_memory workspace and delete it when finished. If you want to save the output to your custom location, just build the path with the os module.

[VishalShah2]

Blake,

my code still creates in the Default.gdb in my documents on my local server. I was thinking of using the in_memory workspace but not sure how to incorporate that into my code. Do you think you can help me out with that? I'll post my code that currently is working for me:

import arcpy
import arcpy.mapping as map
from arcpy import env
import datetime

mxd = arcpy.mapping.MapDocument("Current")
date = datetime.date.today()
unitedStatesBoundary = arcpy.mapping.ListLayers(mxd, 'United States Boundary')[0]
targetFeature = arcpy.mapping.ListLayers(mxd, 'Target Feature')[0]
mapName = str(mxd.filePath).split('\\')[-1:][0][:-4]

mapFolder = str(mxd.filePath)[:23]
saveLocFS = mapFolder
folderNameFS = mapName[0:7] + " UG " + mapName[8:] + " " + str(date)

df = arcpy.mapping.ListDataFrames(mxd)[0]
stats = []

statesFS = {6:'California'}
statesAbbreviationsFS = {6:'CA'}
statesCDCCode = {6:'California CDC Code'}

for state in statesFS:
    valueStatesFS = dict.get(statesFS, state)
    valueStatesAbbreviationsFS = dict.get(statesAbbreviationsFS, state)
    valueCDCCode = dict.get(statesCDCCode, state)
    unitedStatesBoundaryQuery = "Name = " + "'" + str(valueStatesFS) + "'"
    unitedStatesBoundary.definitionQuery = unitedStatesBoundaryQuery
    arcpy.RefreshActiveView()
    arcpy.SelectLayerByLocation_management(targetFeature,"intersect",unitedStatesBoundary,"","NEW_SELECTION")
    mxd.save()
    stateMapName = str(valueStatesAbbreviationsFS) + "_" + str(valueCDCCode)
    footageFolder = footageReportFolder + "/" + footageReportFolderName + "/"
    footageGDB = "footages.gdb"
    inTable = 'Target Feature'
    exportLoc = footageFolder + footageGDB
    outTable = str(stateMapName)
    outXLS = footageFolder + str(stateMapName) + ".xls"
    for field in arcpy.ListFields('Target Feature'):
        if field.name == 'TotalFeet':
            stats.append(['TotalFeet', "SUM"])
            arcpy.Statistics_analysis('Target Feature', stateMapName, stats, "")
            arcpy.TableToTable_conversion(inTable, exportLoc, outTable, "")
            arcpy.TableToExcel_conversion(inTable, outXLS, '', '')
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

This code is creating it first in my Default.gdb and then saving a copy in my custom gdb called footage (referenced in the code). Then it is creating an excel spreadsheet. I am looking to skip saving in the Default.gdb and preferrable store in temp in_memory workspace and then at the end of my code, clear out the in_memory workspace.

[BlakeTerhune]

Try this. Hopefully I didn't mess up your other logic.

import arcpy
import datetime
import os

mxd = arcpy.mapping.MapDocument("Current")
date = datetime.date.today()
unitedStatesBoundary = arcpy.mapping.ListLayers(mxd, 'United States Boundary')[0]
targetFeature = arcpy.mapping.ListLayers(mxd, 'Target Feature')[0]
mapName = str(mxd.filePath).split('\\')[-1:][0][:-4]

mapFolder = str(mxd.filePath)[:23]
saveLocFS = mapFolder
folderNameFS = "{} UG {} {}".format(mapName[0:7], mapName[8:], date)
df = arcpy.mapping.ListDataFrames(mxd)[0]
stats = []

statesFS = {6:'California'}
statesAbbreviationsFS = {6:'CA'}
statesCDCCode = {6:'California CDC Code'}

for state in statesFS:
    valueStatesFS = dict.get(statesFS, state)
    valueStatesAbbreviationsFS = dict.get(statesAbbreviationsFS, state)
    valueCDCCode = dict.get(statesCDCCode, state)
    unitedStatesBoundary.definitionQuery = "Name = '{}'".format(valueStatesFS)
    arcpy.RefreshActiveView()
    arcpy.SelectLayerByLocation_management(targetFeature,"intersect",unitedStatesBoundary,"","NEW_SELECTION")
    mxd.save()
    stateMapNameXLS = "{}_{}.xls".format(valueStatesAbbreviationsFS, valueCDCCode)
    outXLS = os.path.join(footageReportFolder, footageReportFolderName, stateMapNameXLS)
    for field in arcpy.ListFields('Target Feature'):
        if field.name == 'TotalFeet':
            stats.append(['TotalFeet', "SUM"])
            stateMap_stats_inmem = os.path.join("in_memory", "stateMap_stats_inmem")
            try:
                arcpy.Statistics_analysis('Target Feature', stateMap_stats_inmem, stats)
                arcpy.TableToExcel_conversion(stateMap_stats_inmem, outXLS)
            finally:
                arcpy.Delete_management("in_memory")‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[VishalShah2]

Blake,

I was able to follow your script most of the way through. I did have a question though. I do want to be able to save a copy of the results in my custom gdb (footage.gdb) hence why in my original script I used TableToTable_conversion previously. Anyway to incorporate that into your potential solution script? Your way made sense for some of the things. I do need to run tests to see if it works for me. 

[BlakeTerhune]

The excel file is just a copy of the Statistics_analysis table. If you want it in a gdb and XLS, just output of Statistics_analysis to your geodatabase instead of in_memory. You can still copy it to XLS from there; no need for the extra table-to-table process.

[VishalShah2]

That does work, but I may leave it as my script below shows it because that way it's a lot easier to follow in terms of what's being done. I may at some point change it to reflect your comment about storing it in my gdb instead of in_memory.

[VishalShah2 · Accepted solution]

Figured it out. Here's my solution which just adds to your code Blake:

Blake, thank you so much for the help!

some of the variables were already defined before so not included in my solution code unless asked for!

import arcpy
import datetime
import os

mxd = arcpy.mapping.MapDocument("Current")
date = datetime.date.today()
unitedStatesBoundary = arcpy.mapping.ListLayers(mxd, 'United States Boundary')[0]
targetFeature = arcpy.mapping.ListLayers(mxd, 'Target Feature')[0]
mapName = str(mxd.filePath).split('\\')[-1:][0][:-4]

mapFolder = str(mxd.filePath)[:23]
saveLocFS = mapFolder
folderNameFS = "{} UG {} {}".format(mapName[0:7], mapName[8:], date)
df = arcpy.mapping.ListDataFrames(mxd)[0]
stats = []

statesFS = {6:'California'}
statesAbbreviationsFS = {6:'CA'}
statesCDCCode = {6:'California CDC Code'}

for state in statesFS:
    valueStatesFS = dict.get(statesFS, state)
    valueStatesAbbreviationsFS = dict.get(statesAbbreviationsFS, state)
    valueCDCCode = dict.get(statesCDCCode, state)
    unitedStatesBoundary.definitionQuery = "Name = '{}'".format(valueStatesFS)
    arcpy.RefreshActiveView()
    arcpy.SelectLayerByLocation_management(targetFeature,"intersect",unitedStatesBoundary,"","NEW_SELECTION")
    mxd.save()
    stateMapNameXLS = "{}_{}.xls".format(valueStatesAbbreviationsFS, valueCDCCode)
    outXLS = os.path.join(footageReportFolder, footageReportFolderName, stateMapNameXLS)
    exportLoc = "{}/{}/{}".format(footageReportFolder, footageReportFolderName, footageReportGDB)
    stateMapName - "{}_{}".format(valueStatesAbbreviationsFS, valueCDCCode)
    for field in arcpy.ListFields('Target Feature'):
        if field.name == 'TotalFeet':
            stats.append(['TotalFeet', "SUM"])
            stateMap_stats_inmem = os.path.join("in_memory", "stateMap_stats_inmem")
            try:
                arcpy.Statistics_analysis('Target Feature', stateMap_stats_inmem, stats)
                arcpy.TableToTable_conversion('Target Feature', exportLoc, stateMapName)
                arcpy.TableToExcel_conversion(stateMap_stats_inmem, outXLS)
            finally:
                arcpy.Delete_management("in_memory")‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍