Referencing Multiple Data Sources with Python

Anonymous User · ‎08-31-2017

*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,))‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

JoshuaBixby · ‎08-31-2017

I haven't tested, but what about:

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', 'CircuitID']
sourceFC = nodeFeatures
sourceFields = ['SimpleID', 'ExtendedID', 'InternalID', 'ExternalID', 'Termination', 'ProjectRegion', 'ProjectState', 'ProjectName', 'ProjectCustomer', 'GlobalID']



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 = i
                circuitID = "({})-({})-({})-({})".format(row[2],row[4],row[8],IDnumber)
                targetCursor.insertRow(row + [circuitID])
                ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Anonymous User · ‎09-01-2017

I didn't know you could work it like that. Thanks for the formatting tip also, I like that better than the way I did it before .

This doesn't quite work as intended, though. The script iterates through each feature and enters the appropriate amount of entries, but it does not populate the Circuit ID field. Each time it tries to do so, I get an error:

Here is the error documentation for reference: 000405: No records within table.—Help | ArcGIS Desktop

JoshuaBixby · ‎09-01-2017

The code I provided should populate the CircuitID when the records are inserted/copied into the new table, so that warning and your statement that the records get inserted confuses me. You should no longer be using Calculate Field. Can you post your code?

Anonymous User · ‎09-01-2017

I actually just ran my code in the Python window within ArcMap, right up to the point where I call out the local variables for the loop. Then I copy/pasted your code from above to finish out the loop. Strangely, though, after reopening the map that I was using and attempting it once again, I now get the following error:

And no records got inserted.

I attempted to assign variables for each row, just thinking that that had to do with the list issue:

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

This returned the same error as is shown above, though.

DanPatterson_Retired · ‎09-01-2017

just convert the list to a tuple or the tuple to a list

a = (1, 2, 3)

b = (4, 5, 6)

c = [7, 8, 9]

a + b
Out[8]: (1, 2, 3, 4, 5, 6)

a + c
Traceback (most recent call last):

  File "<ipython-input-9-ca57d551b7f3>", line 1, in <module>
    a + c

TypeError: can only concatenate tuple (not "list") to tuple


a + tuple(c)
Out[10]: (1, 2, 3, 7, 8, 9)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Anonymous User · ‎09-01-2017

So running through your example above, simply using the parentheses instead of the bracket removes the tuple, error. However, simply adding tuple() into the variable does the same.

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 = "(" & InternalID & ")-(" & Termination &")-(" & Customer & ")-(" & IDnumber & ")"
                circuitID = "({})-({})-({})-({})".format(InternalID, Termination, Customer, IDnumber)
                targetCursor.insertRow(row + tuple(circuitID))‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I added the tuple() in the call to circuitID, admittedly via guessing, but now I get a new error:

Was I correct in where I placed tuple()?

In reference to the new error, I looked up Error: TypeError: sequence size must match size of the row, but I can't quite figure out what needs fixed in my code to adhere to this rule.

DanPatterson_Retired · ‎09-01-2017

haven't been following closely... but why are you adding 'row' to the tuple in the first place? are you trying to insert what is in the tuple?

Anonymous User · ‎09-14-2017

Hi Dan,

Apologies for the delay in responding. I attended the Esri GeoConX conference in Chicago last week, and it's taken me a bit to get caught up since I have returned. My understanding of tuples is very limited, and their usage in my code is almost always due to suggestions from others, so I can't really answer the why question that you're asking.

I just need to take every entry from a point feature class, and create a non-standard number of entries into a separate table based on user input. By that I mean that the number may be 2 entries per site on one project, but then the number of entries may be 10 on a different project. The separate table is related to the original feature class, so I need to take information from fields in the original feature class and insert it into fields that are already created in the related table. On top of that, one field in particular is a concatenation of a few fields from the original feature class, and this concatenation is the overarching reason for this tool.

If there are any confusing tuple configurations in my code, it is due to trial and error more than likely. Whenever I got the errors mentioned in various replies, I tried different things to alleviate the issue, but most of them were shots in the dark since I don't fully understand tuples and how they are to be used.

DanPatterson_Retired · ‎09-14-2017

check to see if a final tuple is needed... here is the logic since you can't convert a string directly to a tuple

b = ["{}-{}-{}-{}".format(1, 2, 3, 4)]   # ** I don't think tupling your inputs is needed

b   # --- note I put the string into a list
['1-2-3-4']

tup = ('a', 1, 'b', 2)  # assuming that the row is a tuple

result = list(tup) + b  # convert the tuple to a list, then add the other list

result
['a', 1, 'b', 2, '1-2-3-4']

tuple(result)    # convert back to a tuple if that is what is required
('a', 1, 'b', 2, '1-2-3-4')‍‍‍‍‍‍‍‍‍‍‍‍‍‍

JoshuaBixby · ‎09-02-2017

Unfortunately, line 10 from the error code doesn't correspond to the line 10 in the code snippet you posted back because there is no way that Termination = row[4]would generate that error. Without seeing the entire code block, I can't suggest how to change your code to remove the type error.