Your approach still mystifies me and I still don't understand your business rules. Your rules may make sense to you and may be correct for your business needs, but on the surface they at least partially conflict with my experience in synchronizing data and matching tables. The picture in your head of how everything should work is not transferring into mine yet.
I highly recommend that you reconsider the rule that says:
- Table 2 - Features missing [EasyID] need to be assigned the next number for that [SiteID] in either Table 1 or 2. If current EasyID's for a site are '1', '3', '4A', '6-7','S', and '55', new features would be '2', '4', '5', '6', '7', '8'....etc.
The EasyID is anything but easy to understand or program as you have described it. Filling in these blanks this way seems arbitrary to me especially given that the natural sort of the strings is actually '1', '3', '4A', '55', '6-7', 'S'. Why fill in blanks at all? Over time that means any deleted records will have their SiteID + EasyID combination reused for an entirely unrelated record, and therefore that key is only unique within the snapshot in time before the script reuses it. In other words, you will never be able to use the SiteID + EasyID key if you ever have to compare two different data snapshots that were taken before and after the script ran. This rule may make sense to you, but in my experience this is a bad database practice. Unique keys (single or multi-field) are only valuable in my experience if they are unique to one record over all time or support actual data relationships and become a problem if they are ever reused for completely unrelated records. I personally don't want to help implement this rule, since its seems excessively complicated to me, and I believe from experience that a day will come when you will want to use that key to recover from a data corruption event and the code that implements this rule will make that recovery nearly impossible. You also will greatly increase the likelihood of creating data corruption if you accidentally link together two snapshots that reassigned the same keys to different records.
I have several other questions about this EasyID field. How many characters are allowed in this field? Why does it contain letters and what is the significance of those letters? Why are there dashes to combine two numbers? Since this field is a string field, how do your users handle the fact that it will never sort numerically in any table, since you don't include leading spaces or strings to right-justify them? What type of business are you working for where this business process was developed to track any of this data in either table?
So what little I do understand (or think I understand) I will try to present some code that should fit your needs. This code is more or less what I would start with. Key fields always should come first in the field list and value fields always should follow. I would incorporate the OID field into the code processes and dictionaries as a fail safe unique key for linking back to the original table where ever the user defined keys turn out to be duplicated and not unique.
The code below handles both a 1:1 and 1:M relationship possibility, so even if the key value is not unique you will be able to trap that and fix it.
import arcpy
import sys
T1 = r"C:\Python\Scratch.gdb\Table1"
T2 = r"C:\Python\Scratch.gdb\Table2"
fields = ["SiteID", "EasyID", "FeatureID", "OID@"]
# Intialize T1 as a dictionary
T1Dict = {}
# Initialize a list to hold any concatenated key duplicates found
T1KeyDups = []
# Open a search cursor and iterate rows
with arcpy.da.SearchCursor(T1, fields) as searchRows:
for searchRow in searchRows:
# Build a composite key value from 2 fields
keyValue = '{};{}'.format(searchRow[0], searchRow[1])
if not keyValue in T1Dict:
# Key not in dictionary. Add Key pointing to a list of a list of field values
T1Dict[keyValue] = [list(searchRow[2:])
]
else:
# Key in dictionary is not unique.
T1KeyDups.append(keyValue)
# Append a list of field values to the list the Key points to
T1Dict[keyValue].append(list(searchRow[2:])
)
del searchRows, searchRow
# Sample of how to access the keys, record count, and record values of the dictionary
for keyValue in T1Dict.keys():
for i in range(0, len(T1Dict[keyValue])):
print "The SiteID;EasyID key is {} with {} record(s). Record {} has FeatureID {} and ObjectID {}.".format(keyValue, len(T1Dict[keyValue]), i+1, T1Dict[keyValue][0], T1Dict[keyValue][1])
if len(T1KeyDups) > 0:
# Duplicate keys exist in T1
# Give a warning and either exit the script or else do a fix of T1 before proceeding
print("Duplicate keys found! They are:")
for keyValue in T1KeyDups:
for i in range(0, len(T1Dict[keyValue])):
print "The SiteID;EasyID key is {} with {} record(s). Record {} has FeatureID {} and ObjectID {}.".format(keyValue, len(T1Dict[keyValue]), i+1, T1Dict[keyValue][0], T1Dict[keyValue][1])
# Either exit or fix T1 here
sys.exit(-1)