topic Re: Using python delete old tables with date and time in table's name in Python Questions

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

SheikhHafiz1 — Thu, 08 Aug 2019 06:00:51 GMT

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

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

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

JoshuaBixby — Fri, 10 Dec 2021 22:17:37 GMT

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']
>>> ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

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

SheikhHafiz1 — Tue, 13 Aug 2019 03:32:30 GMT

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.

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

DanPatterson_Retired — Tue, 13 Aug 2019 07:54:05 GMT

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.

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

MichaelVolz — Tue, 13 Aug 2019 10:35:13 GMT

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?

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

DanPatterson_Retired — Tue, 13 Aug 2019 10:53:06 GMT

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.

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

Sehafiz — Tue, 13 Aug 2019 23:37:15 GMT

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?

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

DanPatterson_Retired — Wed, 14 Aug 2019 00:50:04 GMT

/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.

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

Sehafiz — Fri, 10 Dec 2021 22:17:39 GMT

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.

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

DanPatterson_Retired — Fri, 10 Dec 2021 22:17:42 GMT

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

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

Sehafiz — Fri, 10 Dec 2021 22:17:45 GMT

Still the same error.

The code now looks like

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*")

old_one = sorted(Tables, reverse=False)[:1]
print ("\n")
for one in old_one:
    print(one)
    print("{} tables found\n{}".format(len(Tables), Tables))
arcpy.Delete_management(one)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

and the error is

>>> 



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
>>> ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Although your code works for a file geodatabase saved locally.

Thank you for your help.

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

JoshuaBixby — Wed, 14 Aug 2019 03:40:28 GMT

Looking at Line #02 of your results:

[]

That line shows that Tables is an empty list, i.e., there are no tables being returned from arcpy.ListTables. Since Tables is empty, sorted will also return an empty list, and your line "for one in old_one" will never be execute and create the variable "one".

Your issue is with listing tables, not deleting them.

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

SheikhHafiz1 — Fri, 10 Dec 2021 22:17:47 GMT

It is happening only when the workspace is an sde database connection (C:\\Users\\app_arcgis\\AppData\\Roaming\\ESRI\\Desktop10.3\\ArcCatalog\\VMAPPDBUAT_GISDEL_GIS.sde)

The code lists tables when the workspace is a file geodatabase (\\\\scamain\\gis_data\\FHRITest\\FHRITest.gdb)

and deletes the table listed in line 6 of the following result, which is the oldest in the tables' list.

>>> 


10 tables found
[u'FHRT_20190810_070048', u'FHRT_20190814_070034', u'FHRT_20190813_153517', u'FHRT_20190813_153907', u'FHRT_20190813_154351', u'FHRT_20190813_154438', u'FHRT_20190813_154801', u'FHRT_20190813_154945', u'FHRT_20190813_155547', u'FHRT_20190813_155829']
FHRT_20190810_070048
>>> ‍‍‍‍‍‍‍‍‍‍‍‍‍‍

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

DanPatterson_Retired — Wed, 14 Aug 2019 04:41:24 GMT

If you can't even get a list of tables from *.sde, then they either aren't there or there is something inherently wrong with the workspace and/or the connection.

Also, I noticed "Desktop 10.3' in the path. It might be prudent to upgrade to rule out searching for an old 'bug' that is now fixed

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

SheikhHafiz1 — Wed, 14 Aug 2019 05:45:14 GMT

Managed to resolve.

instead of

Tables = arcpy.ListTables("FHRT_20*")‍‍

I should have used

Tables = arcpy.ListTables("GISTEST.TEST.FHRT_20*")‍‍‍‍

as the wild card. I missed the database and the user name.

Now it works.

Thank you Dan and Joshua for your kind effort to help me.