topic Re: Referencing Multiple Data Sources with Python in Python Questions

Referencing Multiple Data Sources with Python

Anonymous User — Sat, 11 Dec 2021 09:38:38 GMT

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

Re: Referencing Multiple Data Sources with Python

VinceAngelo — Thu, 31 Aug 2017 15:40:43 GMT

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

Re: Referencing Multiple Data Sources with Python

DanPatterson_Retired — Thu, 31 Aug 2017 15:41:43 GMT

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

Re: Referencing Multiple Data Sources with Python

RachelAlbritton — Sat, 11 Dec 2021 09:38:41 GMT

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

Re: Referencing Multiple Data Sources with Python

Anonymous User — Sat, 11 Dec 2021 09:38:44 GMT

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.

Re: Referencing Multiple Data Sources with Python

JoshuaBixby — Sat, 11 Dec 2021 09:38:47 GMT

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

Re: Referencing Multiple Data Sources with Python

Anonymous User — Thu, 31 Aug 2017 16:50:50 GMT

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!

Re: Referencing Multiple Data Sources with Python

Anonymous User — Thu, 31 Aug 2017 16:54:01 GMT

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.

Re: Referencing Multiple Data Sources with Python

Anonymous User — Sat, 11 Dec 2021 09:38:50 GMT

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!

Re: Referencing Multiple Data Sources with Python

Anonymous User — Thu, 31 Aug 2017 21:22:18 GMT

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.

Re: Referencing Multiple Data Sources with Python

JoshuaBixby — Sat, 11 Dec 2021 09:38:53 GMT

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

Re: Referencing Multiple Data Sources with Python

Anonymous User — Fri, 01 Sep 2017 15:30:49 GMT

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

Re: Referencing Multiple Data Sources with Python

JoshuaBixby — Fri, 01 Sep 2017 19:43:45 GMT

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?

Re: Referencing Multiple Data Sources with Python

Anonymous User — Sat, 11 Dec 2021 09:38:55 GMT

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.

Re: Referencing Multiple Data Sources with Python

DanPatterson_Retired — Sat, 11 Dec 2021 09:38:58 GMT

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

Re: Referencing Multiple Data Sources with Python

Anonymous User — Sat, 11 Dec 2021 09:39:01 GMT

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.

Re: Referencing Multiple Data Sources with Python

DanPatterson_Retired — Fri, 01 Sep 2017 23:50:48 GMT

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?

Re: Referencing Multiple Data Sources with Python

JoshuaBixby — Sat, 02 Sep 2017 23:45:26 GMT

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.

Re: Referencing Multiple Data Sources with Python

Anonymous User — Sun, 03 Sep 2017 06:43:48 GMT

I believe that error was referencing the insert row operation. In trying to find a solution I added the variables fo each row item thinking that it might alleviate the error, but it did not.

Take away the 3 row variable lines and line 10 lands you at the insert row line, which is the same as row 14 in the very next example of my code.

Sorry for the confusion, I just had some remnants of trial and error that took place after the error code was generated.

Re: Referencing Multiple Data Sources with Python

Anonymous User — Thu, 14 Sep 2017 21:46:52 GMT

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.