Retrieving domain value information

Jen_Zumbado-Hannibal — Fri, 30 Jun 2023 01:38:44 GMT

I have perhaps an unusual request. So bear with me.

I am attempting to write a Python 3 script that will list all domain names from my enterprise geodatabase that start with the letter "e", as well as their domain type, code/min, and description/max, and have them saved in a log file (.csv and .xlsx). At the same time, I also want to capture in another log file the history of only those domains that were altered (e.g., deleted, added, changed, etc.) and when it happened.

Here is my three-part question:

1. Does ESRI system tables (SQL Server 2019) keep track of when domain changes happen and what those changes were?

2. Can a Python 3 script retrieve that info from system tables?

3. Is there another way to go about this?

The following code doesn't really give me what I need. Instead, it gives me a list of all the domains that start with "e" yet it adds the date of when I ran the script and not the date when I actually altered the domain. Plus, it doesn't add the header "Date_Modified_2" inside my log file.

See attached log file (.csv).

I want it to write:

DOMAIN_NAME,DATE_MODIFIED,DATE_MODIFIED_2
eGuyType,2023-06-27 13:30:08.858138
eDeviceMounting,2023-06-27 13:30:08.858138,2023-06-29 15:20:00.590812
ePhase,2023-06-27 13:30:08.858138,2023-06-29 15:20:00.590812
eSecConductorType,2023-06-27 13:30:08.858138
eSwitchState,2023-06-27 13:30:08.858138,2023-06-29 15:20:00.590812
eImpedanceUnits,2023-06-27 13:30:08.859136,2023-06-29 15:20:00.590812
eConnectionTypeWyeDelta,2023-06-27 13:30:08.859136,2023-06-29 15:20:00.590812
eSecondaryType,2023-06-27 13:30:08.859136,2023-06-29 15:20:00.590812
ePrimaryType,2023-06-27 13:30:08.859136,2023-06-29 15:20:00.590812
eMountType,2023-06-27 13:30:08.859136

Thanks.

import arcpy import csv from openpyxl import Workbook from openpyxl.styles import Font import datetime gdb = r"E:\SDE\OWNER_PROD.sde" xlsx_filepath = r"E:\ScriptLogs\ExportDomains.xlsx" csv_filepath = r"E:\ScriptLogs\ExportDomains.csv" modification_table_csv = r"E:\ScriptLogs\LPDomainHistory.csv" modification_table_xlsx = r"E:\ScriptLogs\LPDomainHistory.xlsx" domains = arcpy.da.ListDomains(gdb) wb = Workbook() ws = wb.active # Write the header for domain list in Excel file header_xlsx = ["DOMAIN_NAME", "DOMAIN_TYPE", "CODE/MIN", "DESCRIPTION/MAX"] ws.append(header_xlsx) header_font = Font(bold=True) for cell in ws[1]: cell.font = header_font # Write the header for the CSV file header_csv = ["DOMAIN_NAME", "DATE_MODIFIED"] # Read existing modification dates from the CSV file modification_dates = {} if arcpy.Exists(modification_table_csv): with open(modification_table_csv, 'r') as csv_file: reader = csv.reader(csv_file) headers = next(reader) # Get the headers for row in reader: domain_name = row[0] modification_dates[domain_name] = row[1:] # Capture domain modification information for domain in domains: if domain.name.startswith("e"): if domain.domainType == 'CodedValue': coded_values = domain.codedValues if domain.name in modification_dates: if modification_dates[domain.name][0] != str(coded_values): modification_dates[domain.name].append(str(datetime.datetime.now())) else: modification_dates[domain.name] = [str(coded_values), str(datetime.datetime.now())] for val, desc in coded_values.items(): ws.append([domain.name, "Coded", val, desc, ""]) else: range_min = domain.range[0] range_max = domain.range[1] if domain.name in modification_dates: if modification_dates[domain.name][0] != str(domain.range): modification_dates[domain.name].append(str(datetime.datetime.now())) else: modification_dates[domain.name] = [str(domain.range), str(datetime.datetime.now())] ws.append([domain.name, "Range", range_min, range_max, ""]) # Save the workbook including both the domains and modification dates wb.save(xlsx_filepath) # Save the modification history to the CSV file with open(modification_table_csv, 'w', newline='') as csv_file: writer = csv.writer(csv_file) writer.writerow(header_csv) for domain_name, modification_dates_row in modification_dates.items(): row_data = [domain_name] + modification_dates_row writer.writerow(row_data) # Append the new modification dates to the CSV file header_csv_dict = {} with open(modification_table_csv, 'r') as csv_file: reader = csv.reader(csv_file) headers = next(reader) # Get the headers for i, header in enumerate(headers): header_csv_dict[header] = i if len(modification_dates) > 0: for domain_name in modification_dates: if domain_name not in header_csv_dict: header_csv.append(f"DATE_MODIFIED_{len(header_csv) - 1}") with open(modification_table_csv, 'a', newline='') as csv_file: writer = csv.writer(csv_file) for domain_name, modification_dates_row in modification_dates.items(): row_data = [domain_name] + modification_dates_row writer.writerow(row_data) # Convert the CSV file to Excel format wb_csv = Workbook() ws_csv = wb_csv.active with open(modification_table_csv, 'r') as csv_file: reader = csv.reader(csv_file) for row in reader: ws_csv.append(row) wb_csv.save(modification_table_xlsx)

Re: Retrieving domain value information

MobiusSnake — Fri, 30 Jun 2023 12:35:29 GMT

Domains are managed in the GDB_ITEMS system table, with definitions in XML format, very similar (or possibly identical, I can't remember) to what you'd see in an XML Workspace Document. The details of changes are not tracked by this system table, nor is any kind of history, it has no kind of archiving or anything like that on it. You would need to track changes yourself, you could potentially do this with database triggers but I'd be very wary about putting my own triggers on GDB system tables, a scheduled task would probably be a safer approach.

You can query the GDB_ITEMS table with a Python search cursor.

Edit to add - I believe there's some Esri doc around this table (and the GDB_ITEMTYPES table, which you may want to look at as well), but besides that a good way to familiarize yourself with the tables would be by opening them up in SQL Server Management Studio first.

Re: Retrieving domain value information

Jen_Zumbado-Hannibal — Wed, 05 Jul 2023 16:10:58 GMT

@MobiusSnake

I did navigate to the GDB_ITEMTYPES but no specific history information that I could see. However, I appreciate the input.

Thanks.

Re: Retrieving domain value information

MobiusSnake — Thu, 06 Jul 2023 10:52:33 GMT

Ah sorry, I didn't mean to imply that the GDB_ITEMTYPES table would have history information, just that it can be a useful lookup when you're getting to know the GDB_ITEMS table, since it associates IDs used in the GDB_ITEMS table with GDB entities such as domains, feature classes, tables, and so on.

topic Re: Retrieving domain value information in Python Questions

Retrieving domain value information

Re: Retrieving domain value information

Re: Retrieving domain value information

Re: Retrieving domain value information