Referencing Multiple Data Sources with Python

[Anonymous User]

*EDIT*

I marked Joshua Bixby's response as the correct answer as it solved the original issue I had, but I also want to give a special thanks to Dan Patterson for helping me figure out the tuple issue.  Thanks for everyone else for chiming in as well.  Much appreciated!  Please reference the final working script at the bottom of this request. 

Hello, I have working scripts that use UpdateCursor and InsertCursor, but all of my previous work has been in the fashion of inserting or updating records in the feature class or table that I am referencing. I'm struggling to figure out how to reference data in one feature class, and then insert entries using that information into a separate database table. I'm pretty confident that the problem I am having is very basic, but I am no expert in Python and I'm obviously missing something. Basically I want to take every entry in a feature class, and then depending on a user input, enter X number of entries into a related table for each feature in the feature class.

So say the number of features in the feature class is 10, and the user states that we need 2 records in the related table for each site, then I need the script to create 20 entries. In those 20 entries, though, I need for the script to take a number of fields from the feature class, and port them over into the appropriate fields in the related table entries.

with arcpy.da.UpdateCursor(inTable, inField) as cursor: 
     for row in cursor: 
          row[0] = name 
          cursor.updateRow(row)‍‍‍‍ ‍‍‍‍‍‍‍‍‍‍‍‍

The above code is something I'm familiar with, but again, it's only referencing one feature class, so the inField variable represents fields that are contained within the inTable variable, obviously. How do I grab fields from a feature class, create entries in a table, and then populate some of the fields in those entries based on the feature class?

Working Script:

import arcpy
numIDs = user_input  # user input variable
targetTBL = target_table  # table that rows will be inserted into
targetFields = ('SimpleID', 'ExtendedID', 'InternalID', 'ExternalID', 'Termination', 'ProjectRegion', 'ProjectState', 'ProjectName', 'ProjectCustomer', 'NodeGUID', 'CircuitID')
sourceFC = source_table  # table that information comes from
sourceFields = ('SimpleID', 'ExtendedID', 'InternalID', 'ExternalID', 'Termination', 'ProjectRegion', 'ProjectState', 'ProjectName', 'ProjectCustomer', 'GlobalID')

# Execute Insert Cursor and Search Cursor
with arcpy.da.InsertCursor(targetTBL, targetFields) as targetCursor:
    with arcpy.da.SearchCursor(sourceFC, sourceFields) as sourceCursor:
        for row in sourceCursor:
            for i in range(1, numIDs+1):
                if i < 10:
                    IDnumber = "0" + str(i)
                else:
                    IDnumber = str(i)
                InternalID = row[2]
                Termination = row[4]
                Customer = row[8]
                circuitID = "({})-({})-({})-({})".format(InternalID, Termination, Customer, IDnumber)
                targetCursor.insertRow(row + (circuitID,))‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[VinceAngelo]

There seems to be some pieces missing from your code sample.  arcpy.da.InsertCursor creates rows, and arcpy.da.UpdateCursor changes specific rows. You seem to be trying to use UpdateCursor to insert rows. If you really want to update rows, then you should be applying a where_clause to limit the interaction to the affected rows.

Please remember to specify the exact version of software in use and what the data source(s) is/are (file geodatabase has different SQL options than a real database).

- V

[DanPatterson_Retired]

why not use Select by Attributes or Select by Location to get the features you need.. then follow that by a Copy Features to a new file... Delete Fields if there are some fields you don't want... or maybe an AddField if you want one with a specific name, then a Calculate Field to get values from the old table, whose field name you don't like, into the new field.

If you emulate that workflow, does it give you what you want?  if it does, you can simplify your script writing by using the existing tools.  Once you get that working, you can strip the functionality down to searchcursors, insertcursors and update cursors if you really need to

[RachelAlbritton]

If I'm understanding what you need - try using a search cursor to read the values from the source table and then use an insert cursor to insert those values into the second table. Something like this should work:

sc = arcpy.SearchCursor(featureClassName)
    for row in sc:
 
    #Get Field Values
    fieldValue = row.fieldValue
  
    #Use insert cursor to write values to the destination table or feature class
    intcur = arcpy.InsertCursor(destinationTable)
    icur = intcur.newRow()
    icur.setValue("Field Name", fieldValue)
    intcur.insertRow(icur)
    del intcur
 
 del row
 del sc‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[Anonymous User]

Thank you for this sample.  I believe that the combination of the this logic and the logic listed below in Joshua's sample, I will be able to work something up to get started. 

[Anonymous User]

Vince/Dan,

Thanks for your responses.  I will clarify the parameters a bit more. 

I am working in Desktop 10.4.1, and the data references are housed in a 10.2.2 SDE geodatabase. 

The code above was just a sample to illustrate how I've used the functions in the past, and mainly to highlight that I've only ever referenced one layer and its associated set of fields. 

Here's a sample that I have used with InsertCursor:

import arcpy
table = r'FiberStrands'
i = arcpy.GetParameterAsText(0)

cursor = arcpy.da.InsertCursor(table, ['ProjectID', 'SegmentID', 'StrandNumber'])
     for i in range(1, 73):
          cursor.insertRow(["NAME HERE", "Segment ID 1", i])‍‍‍‍‍‍‍

That sample iterated through the number in "i" and created as many entries as was given by the user.  *NOTE* I slapped that together, so it may not be 100% correct.  Just know that it worked whenever I originally made it .

The table that the entries are to be placed in is an already configured table which is used to house a set of information pertaining to the features in the original feature class.  This information can range anywhere from a single entry per feature, up to 72 entries per feature.  So the user input tells the tool how many entries per feature are needed, the data that comes from the feature class to the table is primarily for sorting/query purposes, with one of those fields being a related GUID value, and then the tool will populate a number of other fields using field calculator (concatenation mainly). 

Our current method of capturing this attribute information is by allocating 10 fields in the feature class.  However, this either limits us to 10 fields in the cases that require more than 10 ID's created (rare occurrence, but does happen occasionally), or it leaves a number of fields unused in the event that we only need 1 or 2 ID's.  Thus, the initiative to move this ID collection to a related table. 

[JoshuaBixby · Accepted solution]

A general structure for inserting records into a target table from a source table:

tbl_src = # path to source table
tbl_tar = # path to target table
flds =  # list of field names for target table, exluding OID
cnts = # dict or other structure for looking up number of records to copy

with arcpy.da.InsertCursor(tbl_tar, flds) as icur:
    with arcpy.da.SearchCursor(tbl_src, ["OID@"] + flds) as cur:
        for row in cur:
            oid = row[0]
            row = row[1:]
            for i in range(1, cnts[oid] + 1):
                icur.insertRow(row)‍‍‍‍‍‍‍‍‍‍‍‍

[Anonymous User]

Joshua,

Following the logic on this it does appear like this is the right direction for what I need.  I will try to work something up and see where I get.  Thanks for providing this sample!

[Anonymous User]

Your code did end up pushing me in the right direction.  Thank you!  I didn't fully understand your references to OID's, so I left that portion out.  I'm not really sure what impact that will have, though.  I have another question in a reply to the original message as well. 

Since your method was the one that answered my original question, I will likely mark your response as the answer. 

[Anonymous User]

Okay, I have a somewhat working script, but it's not complete just yet. 

import arcpy
import arcpy.mapping as map
from arcpy import env
import datetime


# universal variables
mxd = map.MapDocument("CURRENT")
mapName = str(mxd.filePath).split('\\')[-1:][0][:-4]
nodeFeatures = r'OSP Mapping Layers\Node Locations'
circuitIDTable = r'Circuit ID Table'
strandsPerNode = 6   # arcpy.GetParameterAsText(0)
numIDs = strandsPerNode/2

# Set local variables
targetTBL = circuitIDTable
targetFields = ['SimpleID', 'ExtendedID', 'InternalID', 'ExternalID', 'Termination', 'ProjectRegion', 'ProjectState', 'ProjectName', 'ProjectCustomer', 'NodeGUID']
sourceFC = nodeFeatures
sourceFields = ['SimpleID', 'ExtendedID', 'InternalID', 'ExternalID', 'Termination', 'ProjectRegion', 'ProjectState', 'ProjectName', 'ProjectCustomer', 'GlobalID']

# Execute Insert Cursor and Search Cursor
with arcpy.da.InsertCursor(targetTBL, targetFields) as targetCursor:
    with arcpy.da.SearchCursor(sourceFC, sourceFields) as sourceCursor:
        for row in sourceCursor:
            for i in range(1, numIDs+1):
                targetCursor.insertRow(row)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Everything up to this point works just fine.  The script takes the value that will be entered by the user, derives the correct number of IDs needed, and then generates the appropriate number of entries, as well as porting over the appropriate attributes into their corresponding fields:

In this image here you can see that the tool recognized that each site needed 3 entries per site (reference the GUIDs), and it ported over the appropriate information for Region, State, and Name (others pulled over too, just keeping image small). 

However, at this point the Circuit ID field is blank.  This field is a concatenation of the InternalID, Termination, and ProjectCustomer fields, and then a 2 digit number.  So I added the following code to generate the Circuit ID's (row 1 here is the same as row 22 above):

with arcpy.da.InsertCursor(targetTBL, targetFields) as targetCursor:
    with arcpy.da.SearchCursor(sourceFC, sourceFields) as sourceCursor:
        for row in sourceCursor:
            for i in range(1, numIDs+1):
                targetCursor.insertRow(row)
                calcTable = circuitIDTable
                calcField = "CircuitID"
                if i < 10:
                    IDnumber = "0" + str(i)
                else:
                    IDnumber = i
                expression = "'(' + row[2] + ')-(' + row[4] + ')-(' + row[8] + ')-(' + str(IDnumber) + ')'"
                arcpy.CalculateField_management(calcTable, calcField, expression, "PYTHON_9.3")‍‍‍‍‍‍‍‍‍‍‍‍‍

In this image you can see that it calculated the Circuit ID as desired:

However, the operation stops at this point and I get this error:

Essentially I have everything I need with the code that I have, but I need to tweak it to continue through the iteration and create the rest of the Circuit ID's.  The desired result would look like this:

Thanks!