How to Delete in_memory table

5867
9
12-07-2017 12:28 PM
Highlighted
MVP Esteemed Contributor

I create a table in memory like this:

temptable = arcpy.CreateTable_management("in_memory","tableinmemory",template)
### house dir is null or street dir is null; item1 & item2 qaqc summary
select = "HouseDir IS NULL OR StreetDir IS NULL"
arcpy.SelectLayerByAttribute_management("view","ADD_TO_SELECTION",select )
if arcpy.Describe("view").FIDSet =='':
    pass
elif int(arcpy.Describe("view").FIDSet[0]) >= 1:
    arcpy.Append_management("view",temptable,"NO_TEST")
    expression = "HouseDir or StreetDir is null"
    arcpy.CalculateField_management(temptable,"ErrorType","'{}'".format(expression),"PYTHON")
    outtable = r"C:\Replicas\Replicas.gdb\Errors"
    arcpy.Append_management(temptable,outtable,"TEST")
    arcpy.DeleteRows_management("tableinmemory")
    arcpy.SelectLayerByAttribute_management("view","CLEAR_SELECTION")
‍‍‍‍‍‍‍‍‍‍‍‍‍‍

"view" in line 10 is a table view of a feature class created earlier; basically I have on table (view) that is of one schema and I need to copy selected records from it to my 'temptable' which has a different schema. Then I'm able to append records from temptable to an actual table in a real live gdb of the same schema.  This whole piece of code works marvelously, and I repeat several times collecting various data entry errors if they exist. But...

I need to get rid of the tableinmemory, and can't seem to figure out a way to do so. From the python window in ArcMap I run this code:

>>> if arcpy.Exists("in_memory\\tableinmemory"):
...     print "trying to delete"
...     arcpy.Delete_management("tableinmemory")
...     arcpy.Exists("in_memory\\tableinmemory")
...    
trying to delete
>>> ‍‍‍‍‍‍‍

But notice after my printed 'trying to delete' there is no indication of TRUE as to whether or not line 4 successfully runs.  However, in the geoprocessing/results I see this warning:

         !WARNING 00010:tableinmemory does not exist

Okay, that's cool.  I guess.  But when I then try to run the code in the top window, I get:

>>> 
Traceback (most recent call last):
  File "C:\Users\JBorgione\AppData\Local\ESRI\Desktop10.5\AssemblyCache\{7A2884A1-0732-4438-A865-0C69929F369F}\QAQC_addin.py", line 23, in onClick
    temptable = arcpy.CreateTable_management("in_memory","tableinmemory",template)
  File "C:\Program Files (x86)\ArcGIS\Desktop10.5\ArcPy\arcpy\management.py", line 17411, in CreateTable
    raise e
ExecuteError: ERROR 000258: Output in_memory\tableinmemory already exists
Failed to execute (CreateTable).
‍‍‍‍‍‍‍‍

I have another module in which I create a simple polygon feature in memory, and I get rid of it like this:

for lyr in arcpy.mapping.ListLayers(mxd, "", df):
  if lyr.name == "ExtentPoly":
    arcpy.mapping.RemoveLayer(df,lyr)
arcpy.Delete_management("in_memory")‍‍‍‍

 I've tried this approach as well for tableinmemory but to no avail.  In fact, in a python window if I try to delete it, it still shows up as an option, even though its gone from the TOC, and I've deleted workspace in_memory:

So how does one really, really, really, delete a table in memory without closing down arcmap?

Having read Joshua Bixby‌ 's  timely series beginning with:  /blogs/tilting/2014/08/01/when-inmemory-doesnt-mean-in-memory I realize I'm not the first, and probably not the last to find him/herself in this conundrum. (However, I did create a new tag that is a catch all....)

Reply
0 Kudos
9 Replies
Highlighted
MVP Esteemed Contributor

you can Delete ... in_memory stuff, but I notice that you don't use

arcpy.overwriteOutput = True

they are vague on setting environments when in_memory is used

Reply
0 Kudos
Highlighted
Occasional Contributor III

Unless you have set your worksapce to "in_memory", 

arcpy.Delete_management("tableinmemory") 

is not the same as:
arcpy.Delete_management("in_memory\\tableinmemory")

I think your just trying to delete a table called "
tableinmemory" within your current workspace, which likely doesn't exist.

Highlighted
MVP Esteemed Contributor

Tools like Delete will remove layers and views (not the underlying data) first when passed a name that matches multiple objects, regardless of what the workspace is set to:

>>> arcpy.env.workspace
u'\\\\....\\My Documents\\ArcGIS\\Default.gdb'
>>>
>>> arcpy.CreateTable_management("in_memory","tableinmemory")
<Result 'in_memory\\tableinmemory'
>>> arcpy.Exists("tableinmemory")
True
>>> desc = arcpy.Describe("tableinmemory")
>>> desc.datatype
u'TableView'
>>> desc = arcpy.Describe("in_memory/tableinmemory")
>>> desc.datatype
u'Table'
>>> del desc
>>>
>>> arcpy.env.workspace = "in_memory"
>>> arcpy.Delete_management("tableinmemory")
<Result 'true'>
>>> arcpy.Exists("tableinmemory")
True
>>> desc = arcpy.Describe("tableinmemory")
>>> desc.datatype
u'Table'
>>> del desc
>>> arcpy.Delete_management("tableinmemory")
<Result 'true'>
>>> arcpy.Exists("tableinmemory")
False
>>>

There is a hierarchy to referencing objects by name.  I can't say I know the exact order because I can't recall ever seeing a published list, but I do know that layers and views are higher than data sources.

Highlighted
MVP Esteemed Contributor

I'm finding the same thing Joshua shows:

>>> import arcpy
>>> arcpy.env.workspace
u'C:\\Users\\JBorgione\\Documents\\ArcGIS\\Default.gdb'
>>> arcpy.env.workspace = "in_memory"
>>> arcpy.Delete_management("tableinmemory")
<Result 'true'>
>>>
Traceback (most recent call last):
  File "C:\Users\JBorgione\AppData\Local\ESRI\Desktop10.5\AssemblyCache\{7A2884A1-0732-4438-A865-0C69929F369F}\QAQC_addin.py", line 23, in onClick
    temptable = arcpy.CreateTable_management("in_memory","tableinmemory",template)
  File "C:\Program Files (x86)\ArcGIS\Desktop10.5\ArcPy\arcpy\management.py", line 17411, in CreateTable
    raise e
ExecuteError: ERROR 000258: Output in_memory\tableinmemory already exists
Failed to execute (CreateTable).

>>> arcpy.Exists("tableinmemory")
True
>>> ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Above is a view of the Python window in ArcMap.  Prior to line #1, I'm running my arcpy script that creates the table in memory as mentioned in the op:  That script now ends with:

arcpy.Delete_management("points")
arcpy.Delete_management("quads")
arcpy.Delete_management("view")
arcp.env.Workspace = "in_memory"
arcpy.Delete_management("tableinmemory")
‍‍‍‍‍‍‍‍‍‍‍

Where I delete two feature layers (points & quads), a table view (view) and then attempt to get rid of tableinmemory.  From the script, the first three are removed as hoped for, but tableinmemory persists in the TOC.  That's where the python window commands come in: At line 5,  I delete it, after setting the workspace, and line 6 "confirms it". Then I try to run my script again from a button in arcmap and get Lines 8 through 14.  Line 16 is a manual command.

Lately here at work we've been talking about the "What 3 Words" thing.  Can you buy your own three words for your front yard?  If so I want "Weirdness In ArcPy"....

Highlighted
MVP Esteemed Contributor

Now I'm somehwat confused:  I've tried to copy Joshua Bixby‌ 's commands provided above, but I'm getting different results:  

>>> import arcpy
>>> arcpy.env.workspace
u'C:\\Users\\JBorgione\\Documents\\ArcGIS\\Default.gdb'
>>> arcpy.CreateTable_management("in_memory","tableinmemory")
<Result 'C:\\Users\\JBORGI~1\\AppData\\Local\\Temp\\arcAE20\\j1a1dcbba788c4257ad067108e736e0ea.gdb\\tableinmemory'>
>>> arcpy.Exists("tableinmemory")
True
>>> desc = arcpy.Describe("tableinmemory")
>>> desc.datatype
u'TableView'
>>> desc = arcpy.Describe("in_memory/tableinmemory")
Runtime error
Traceback (most recent call last):
  File "<string>", line 1, in <module>
  File "C:\Program Files (x86)\ArcGIS\Desktop10.5\ArcPy\arcpy\__init__.py", line 1253, in Describe
    return gp.describe(value)
  File "C:\Program Files (x86)\ArcGIS\Desktop10.5\ArcPy\arcpy\geoprocessing\_base.py", line 376, in describe
    self._gp.Describe(*gp_fixargs(args, True)))
IOError: "in_memory/tableinmemory" does not exist
>>> del desc
>>> arcpy.env.workspace = "in_memory"
>>> arcpy.Delete_management("tableinmemory")
<Result 'true'>
>>> arcpy.Exists("tableinmemory")
False
>>> desc = arcpy.Describe("tableinmemory")
Runtime error
Traceback (most recent call last):
  File "<string>", line 1, in <module>
  File "C:\Program Files (x86)\ArcGIS\Desktop10.5\ArcPy\arcpy\__init__.py", line 1253, in Describe
    return gp.describe(value)
  File "C:\Program Files (x86)\ArcGIS\Desktop10.5\ArcPy\arcpy\geoprocessing\_base.py", line 376, in describe
    self._gp.Describe(*gp_fixargs(args, True)))
IOError: "tableinmemory" does not exist
>>>

Notice the path to my tableinmemory on line 5 compared his.  At line 11, trying to describe a table that doesn't exist tosses an error. But line 5 is actually encouraging at the same time.  Here is what I've added to my button called python script:

arcpy.Delete_management("points")
arcpy.Delete_management("quads")
arcpy.Delete_management("view")
desc =arcpy.Describe("tableinmemory")
deletethis = desc.catalogPath
arcpy.Delete_management(deletethis)

Lines 4 through 6 seem to be the right combination.  This allows me to recreate tableinmemory without exiting ArcMap.  There still is some lingering weirdness: tableinmemory never leaves the TOC.  I suppose I could try a refresh() but for now I like what I'm getting...

Thanks guys!

Joshua Bixby

Dan Patterson‌ 

Bill Daigle

Highlighted
MVP Esteemed Contributor

Did you run the command above in the interactive Python window?  If so, do you have Background Processing enabled?  I am guessing yes given the result on line #5, which indicates the table actually isn't being created in-memory but in a temporary geodatabase on disk.

Highlighted
MVP Esteemed Contributor

Yes; back ground processing is enabled.  I initially ran the code lines(shown as lines 1-6) in a python window to see if it worked.  When it did, I put them into the python script that is within my QAQC_addin.py script, and is called by a python addin button while in an ArcMap session.

Joshua Bixby

Reply
0 Kudos
Highlighted
MVP Esteemed Contributor

I will have to take a look at the code itself when I can grab some time.  Background processing complicates in-memory workspaces because "in_memory" doesn't actually mean in memory.  When using the "in_memory" flag with background processing enabled, you have to keep track of the results from running tools with "in_memory" so that you can reference the data again.  Since Line #4 results in Line #5, Line #11 returns a runtime error because there is no table in the GPInMemoryWorkspace that comprises "in_memory."

Given the back and forth and changes to the code, can you re-post the code as it stands now and describe what is currently still causing issues?  Thanks.

Reply
0 Kudos
Highlighted
MVP Esteemed Contributor

As a follow up to Joshua Bixby 's comment on background processing, I ran a little experiment just to clarify things in my own head. Copied from a python window:

>>> import arcpy
>>> temptable = arcpy.CreateTable_management("in_memory","background_on")
>>> desc =arcpy.Describe("background_on")
>>> desc.catalogPath
u'C:\\Users\\JBORGI~1\\AppData\\Local\\Temp\\arcCEC8\\jed5ec4309eed46ab8fa56fd231940d25.gdb\\background_on'
>>> temptable2 = arcpy.CreateTable_management("in_memory","background_off")
>>> desc2 = arcpy.Describe("background_off")
>>> desc2.catalogPath
u'in_memory\\background_off'
>>> deletethis = desc.catalogPath
>>> deletethis2 = desc2.catalogPath
>>> arcpy.Delete_management(deletethis)
<Result 'true'>
>>> arcpy.Delete_management(deletethis2)
<Result 'true'>
>>> arcpy.Exists(temptable)
False
>>> arcpy.Exists(temptable2)
False

Where tables background_on and background_off are self explanatory.

I also took a look at this:  Can background processing be enabled/disabled in arcpy?  which I came upon with a google search of just about the same title.  In my situation, I have no control of how the end user has his/her background processing set.  I have to think it is set to enabled by default as I've never had any inclination to change it; I polled two of the end users of my addin and they both responded that theirs are set to enabled.  For me, capturing the catalogPath and deleting it accordingly ensures the deletion of an in_memory table (I'm using the term in_memory with some creative license...)

With respect to my earlier comment from above "There still is some lingering weirdness: tableinmemory never leaves the TOC."  the remedy I found for that is to use :

>>> arcpy.Delete_management("background_off")
<Result 'true'>
>>> arcpy.Delete_management("background_on")
<Result 'true'>

This has been a very enlightening discussion!  Thanks!