Using python delete old tables with date and time in table's name

SheikhHafiz1 · ‎08-07-2019

I have been using ArcGIS 10.3.1.

I have a list of tables in an enterprise geodatabase with date and time in the table name. Every day one table is created, as an outcome of some geoprocessing tasks with the date and time in the table’s name when the table is created.

Naming format of the tables are FHRT_YYYYmmdd_HHMMSS

Like

FHRT_20190808_071015 (this table is created on 08 August 2019 at 07:10:15)
FHRT_20190807_071218 (this table is created on 07 August 2019 at 07:12:18)
FHRT_20190806_071119
FHRT_20190805_071913
FHRT_20190804_071418
FHRT_20190803_071014
FHRT_20190802_070815

etc.

I want to create a python script that will keep the latest 3 tables (in this example FHRT_20190808_071015, FHRT_20190807_071218 and FHRT_20190806_071119) and delete the rest with similar name format. The geodatabase may have other tables with other name format (cadastre, property etc.), I do not want to touch those.

Being new in Python I do not know how I would create the script that will specify the dates.

I mean how will I instruct the script to keep the 3 tables that have the latest dates and delete the older tables?

What module do i need to import and what would be the statement?

Thank you in advance.

Regards,

Hafiz

JoshuaBixby · ‎08-08-2019

It seems you are very new to Python/ArcPy. If so, you will need to learn to walk before you try running (not that your question is all that complicated).

I recommend starting with learning how to list feature classes in a workspace. Once you that process, then you can start manipulating the list to find the 3 most recent tables. I personally prefer using Walk—Help | ArcGIS Desktop since it mimics native Python file system functionality.

In terms of finding the most recent 3 tables based on datetimes in table names, the structure of your naming convention should allow you to not bother with actually converting the date string to Python datetime.

>>> import datetime
>>>
>>> fhrt_tables = [  # List of tables starting with FHRT
...     "FHRT_20190808_071015",
...     "FHRT_20190807_071218",
...     "FHRT_20190806_071119",
...     "FHRT_20190805_071913",
...     "FHRT_20190804_071418",
...     "FHRT_20190803_071014",
...     "FHRT_20190802_070815"
... ]
>>>
>>> recent_three = sorted(fhrt_tables, reverse=True)[:3]
>>> recent_three
['FHRT_20190808_071015', 'FHRT_20190807_071218', 'FHRT_20190806_071119']
>>> ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

SheikhHafiz1 · ‎08-12-2019

Hi Joshua,

Thank you for your quick help.

I tried to delete old three tables and I created the script like the following

import arcpy

arcpy.env.workspace = "\\\\main\\gis_data\\FHRITest.gdb"
Tables = arcpy.ListTables("FHRT_20*")
print(Tables)
old_three = sorted(Tables, reverse=False)[:3]

print ("\n" + "Following are the tables:" + "\n")

print (old_three)
arcpy.Delete_management(old_three, "table")

and the script is throwing the following error

[u'FHRT_20190805_161001', u'FHRT_20190805_161142', u'FHRT_20190805_162929', u'FHRT_20190805_163204', u'FHRT_20190805_163527']

Following are the tables:

[u'FHRT_20190805_161001', u'FHRT_20190805_161142', u'FHRT_20190805_162929']

Traceback (most recent call last):
File "N:\Scripts\DeleteOldTables.py", line 11, in <module>
arcpy.Delete_management(old_three, "table")
File "C:\Program Files (x86)\ArcGIS\Desktop10.3\ArcPy\arcpy\management.py", line 4092, in Delete
raise e
RuntimeError: Object: Error in executing tool

It must be very easy for you to resolve the issue.

DanPatterson_Retired · ‎08-13-2019

You will have to delete them one at a time since you can only delete multiple data elements since ArcGIS Pro 2.4. You will have to use a for loop in your code

Delete—Data Management toolbox | ArcGIS Desktop

Starting in ArcGIS Pro 2.4, the Delete tool can be used to delete multiple items.

MichaelVolz · ‎08-13-2019

Dan:

If the code sample was formatted to run with python 3.x that is bundled with Pro 2.4, a list of items would be able to be deleted all at once instead of in a loop? I would guess this type of capability would never be ported to python 2.x that is bundled with ArcMap as python 2.x and ArcMap, although not retired, are in the process of being retired over the next 2-5 years?

DanPatterson_Retired · ‎08-13-2019

Michael, the last line in the post is copied from the help suggesting that the ability to delete multiple elements began with Pro 2.4. Esri doesn't backport enhancements to previous versions, and the ArcMap help for 10.7 would have to be consulted to see if the capability also exists there. Generally arcmap and pro keep in step, but I don't use arcmap.

SheikhHafiz2 · ‎08-13-2019

Thank you Dan.

I created the following script to delete one table from an SDE database connection. When I change the workspace path to a local file geodatabase it works fine but the sde database connection doesn't work.

import arcpy

Delete_succeeded = "true"

arcpy.env.workspace = "C:\\Users\\app_arcgis\\AppData\\Roaming\\ESRI\\Desktop10.3\\ArcCatalog\\VMAPPDBUAT_GISDEL_GIS.sde"
Tables = arcpy.ListTables("FHRT_20*")
print(Tables)
old_one = sorted(Tables, reverse=False)[:1]
print ("\n")
for one in old_one:
print(one)
arcpy.Delete_management(one)

and I am getting the following error

[]

Traceback (most recent call last):
File "C:\Scripts\DeleteOldTable.py", line 13, in <module>
arcpy.Delete_management(one)
NameError: name 'one' is not defined

Could you please help me to resolve?

DanPatterson_Retired · ‎08-13-2019

/blogs/dan_patterson/2016/08/14/script-formatting

would help with line numbers, since it is failing on line 13, and I can only see 9.

SheikhHafiz2 · ‎08-13-2019

import arcpy

Delete_succeeded = "true"
arcpy.env.workspace = "C:\\Users\\app_arcgis\\AppData\\Roaming\\ESRI\\Desktop10.3\\ArcCatalog\\VMAPPDBUAT_GISDEL_GIS.sde"

Tables = arcpy.ListTables("FHRT_20*")
print(Tables)
old_one = sorted(Tables, reverse=False)[:1]
print ("\n")
for one in old_one:
    print(one)
    
arcpy.Delete_management(one)‍‍‍‍‍‍‍‍‍‍‍‍‍

Above is the code and the error is as follows

>>> 
[]



Traceback (most recent call last):
  File "C:\FishHealth\Scripts\DeleteOldTable.py", line 13, in <module>
    arcpy.Delete_management(one)
NameError: name 'one' is not defined
>>> ‍‍‍‍‍‍‍‍‍‍

Thank you for your help Dan.

DanPatterson_Retired · ‎08-13-2019

print(Tables)  

# change to....

print("{} tables found\n{}".format(len(Tables), Tables))‍‍‍‍‍

just to make sure there is something. And if there is and if you want to delete, move it up to line 12 and indent by 4 spaces