Simplifying a feature class to feature class ETL using multiple input feature classes with varied schemas

Question

I'm wondering if anyone can help me brainstorm how to simplify this ETL process? The way I have it written, it just feels very repetitive, but I can't think of a way to avoid the repetition.

I'll eventually be feeding about a dozen feature classes, all with a different schema (different field ordering, field spellings, etc.), into one composite feature class. I've pre-determined the proper index values for each field, and as you can see, the ordering (and as I said, the spelling of field names as well) are wildly different between "fc1" and "fc2," which makes it hard to automate, thus the problem.

I'm afraid that with so many feature classes being added, the code will eventually get unnecessarily long. This example has been abbreviated by quite a lot.

I have modified the code for simplicity and confidentiality:

import arcpy, os
from arcpy import env
arcpy.env.workspace = r"I:\GIS_TOOLS\SDE_connections\gis_sde.sde"
sde = arcpy.env.workspace
masterFC = os.path.join(sde, "sde.SDE.masterFC")
fc1 = os.path.join(sde, "sde.SDE.FC1")
fc2 = os.path.join(sde, "sde.SDE.FC2")
masterFCFieldList = ["Shape", "Id", "Name", "Address", "Zip", "Boro", "Phone"]
# ---- Load feature class 1 ----
insertCursorMasterFC = arcpy.da.InsertCursor(masterFC, masterFCFieldList)
searchCursorFC1 = arcpy.da.SearchCursor(fc1, *)
for row in searchCursorFC1: 
 Shape = row[9] 
 Id = row[5]
 Name = row[1]
 Address = row[2]
 Zip = row[4]
 Boro = row[3]
 Phone = row[6] 
 insertCursorMasterFC.insertRow([Shape, Id, Name, Address, Zip, Boro, Phone]) 
del insertCursorMasterFC
del searchCursorFC1
# ---- Load feature class 2 ----
insertCursorMasterFC = arcpy.da.InsertCursor(masterFC, masterFCFieldList)
searchCursorFC2 = arcpy.da.SearchCursor(fc2, *)
for row in searchCursorFC2: 
 Shape = row[8] 
 Id = row[1]
 Name = row[5]
 Address = row[3]
 Zip = row[7]
 Boro = row[2]
 Phone = row[4] 
 insertCursorMasterFC.insertRow([Shape, Id, Name, Address, Zip, Boro, Phone]) 
del insertCursorMasterFC
del searchCursorFC2
print "Script Complete."

• Mintx: The easy answer is to go get a trial version of FME and do this in about 3 minutes. ;)
• Kristen G.: I'll have to give this a look as well, "FME" (safe.com/how-it-works)

Answer 1

There are a lot of ways to go about organizing something like this. My favorite is to create a dictionary of dictionaries at the top, something like this:

all_fc={
'fc1':{'Shape':9,'Id':5,'Name':1,'etc':'etc'},
'fc2':{'Shape':8,'Id':1,'Name':5,'etc':'etc'}
}
print all_fc['fc1']['Shape']
> 9

You can then loop through the dictionary, which should cut down on some of the redundant code.

• Kristen G.: This was extremely helpful. Thank you. I came up with an answer specifically for my original code that makes use of your suggestion. I'm including it as another answer below. What do you think?
• Mintx: Looks good, glad I could help!

Answer 2

This answer provides a simpler alternative to the original code I posted by using @Mintx's helpful suggestion/answer to iterate through a dictionary of dictionaries:

import arcpy, os
from arcpy import env
arcpy.env.workspace = r"I:\GIS_TOOLS\SDE_connections\gis_sde.sde"
sde = arcpy.env.workspace
masterFC = os.path.join(sde, "sde.SDE.masterFC")
fc1 = os.path.join(sde, "sde.SDE.FC1")
fc2 = os.path.join(sde, "sde.SDE.FC2")
masterFCFieldList = ["Shape", "Id", "Name", "Address", "Zip", "Boro", "Phone"]
allFCs = {
 fc1:{'Shape':9, 'Id':5, 'Name':1, 'Address':2, 'Zip':4, 'Boro':3, 'Phone':6},
 fc2:{'Shape':8, 'Id':1, 'Name':5, 'Address':3, 'Zip':7, 'Boro':2, 'Phone':4}
 }
for fc, fields in allFCs.iteritems():
 insertCursorMasterFC = arcpy.da.InsertCursor(masterFC, masterFCFieldList)
 searchCursorFC = arcpy.da.SearchCursor(fc, "*")
 for row in searchCursorFC:
 Shape = row[allFCs[fc]['Shape']] 
 Id = row[allFCs[fc]['Id']]
 Name = row[allFCs[fc]['Name']]
 Address = row[allFCs[fc]['Address']]
 Zip = row[allFCs[fc]['Zip']]
 Boro = row[allFCs[fc]['Boro']]
 Phone = row[allFCs[fc]['Phone']]
 insertCursorMasterFC.insertRow([Shape, Id, Name, Address, Zip, Boro, Phone])
 del insertCursorMasterFC
 del searchCursorFC
print "Script Complete."

• Mintx: It looks like you will want to move those del commands outside of your for row in searchCursorFC loop, so move them back 1 indent.
• Kristen G.: Oops, sloppy copy/pasting. Thanks for catching!