simple insert cursor issues

Basically I have a dictionary, where each key corresponds to a list of orps. I want the inset cursor to iterate over each list in each dictionary key and insert a row with the corresponding values. Seems like it should be simple, but for some reason I keep getting an SQL error. I've used search and update cursors extensively, but haven't had a need for insert cursors before now, so any advice would be very greatly appreciated.

It appears you are dealing with a one-to-many relationship of wells to owners and that your match dictionary contains a list of Long values, not a single long value. You must iterate not only over the objects in the match dictionary, but the listed items in the values of that dictionary to insert just the long value, not a list of long values. Additionally, you are not getting the one-to-many relationship preserved in the match dictionary, because you are resetting the list each time you find the same owner name. You should have the logic be:

Ahhh, dammit, that's why I was only getting one match for each well, I figured it was an issue with my lists. Many thanks for your input!! However, when I run the script with the logic you suggested, it gave the error that the "INT object not iterable". I think that this part:

for s in match[key]:
for item in s:
rows.insertRow((wellnum[key], item))

it's already understanding "match[key]" as a list object, and "s" as an integer in that list. However, when I took that out and let it run like this:

I agree I was mistaken about the long value vs. list value in your last loop.

Your DECwell field length should be explicitly set when you create that field to be at least as long as the length of the longest owner name in your list. You just accepted the default text length, which I believe is 50. The moment you try to insert a record with an owner name with 51 characters or more you will get an error. You should add logic to get the longest string length of wellnum[key] in your first loop before creating the field and making sure the new field is long enough (add a little more unless you know the max. field length and it will not grow).

Also, the code you posted never defines the wellnum dictionary, so I cannot be sure that dictionary does not have an issue. If wellnum is an undefined variable then the insertrow line is the first to use it and will throw all sorts of weird errors. If wellnum was defined in code you did not post, are you sure wellnum even has the same key list as the match dictionary? If it might not you need logic to deal with that. i.e., insert a row only when - if key in wellnum:

I would say this post is not about simple insert cursor issues, it is about complex dictionary interactions and value evaluation issues.

The DECWell field stores a unique identifier, this identifier is always two letters followed by four numbers, like so:

AL1905, RS3000, CH0560

The table being created will only hold that identifier and the objectID of an orps point, so that the well data can be related to matching orps through this table.

Attached is my full code. Note that the feature layers were created because I was interested in selecting orps within a certain distance of the wells and only checking those, as opposed to the entire set of orps.

import arcpy
from arcpy import env
arcpy.env.overwriteOutput = 1
## define all user inputs for creation of arcmap toolbox
a = arcpy.GetParameterAsText(0) ## wells
b = arcpy.GetParameterAsText(1) ## orps
g = arcpy.GetParameterAsText(2) ## output table
c = arcpy.GetParameterAsText(3) ## well owner name field
d = arcpy.GetParameterAsText(4) ## orps owner name field
h = arcpy.GetParameterAsText(5) ## workspace
e = "OBJECTID" ## objectid of both well and orps data
f = "DECWell_" ## dec well identifier
env.workspace = h
##################### BEGINNING OF MATCH #########################################
## Make feature layers
arcpy.MakeFeatureLayer_management(a, "wells")
arcpy.MakeFeatureLayer_management(b, "orps")
wellsFL = "wells"
orpsFL = "orps"
## Create dictionary of well owners
well_owners = {}
wellnum = {}
## cursor to look through well data, getting DEC well number, owner name and object ID of well.
rows = arcpy.da.SearchCursor(wellsFL, [e, c, f])
for row in rows:
## split the owner's name in FIRST LAST format at a space, creating a list
owner = row[1].split(" ")
## take the last entry in the list and make that the entry for well_owners[objectID]
well_owners[row[0]] = "{}".format(owner.pop().upper())
## take the well identifier and make that the entry for wellnum[objectid]
wellnum[row[0]] = str(row[2])
del rows
## create dictionary of orps owners
orps_owners = {}
## cursor to look through orps data and return name of owner and objectID
rows = arcpy.da.SearchCursor(orpsFL, [e, d])
for row in rows:
## split owner's name in LAST FIRST format at the space, creating a list
owner = row[1].split(" ")
## take the first item in this list and make that the entry for orps_owners[objectid]
orps_owners[row[0]] = "{}".format(owner.pop(0).upper())
del rows
## create dictionary of matches
match = {}
## search through owner's names to find matching names
for o in well_owners:
for p in orps_owners:
if orps_owners[p] == well_owners[o]:
if not o in match:
match[o] = []
match[o].append(p)
## create an empty table to fill with matches
arcpy.CreateTable_management(h, "TABLE")
## add relevant fields to the new table
arcpy.AddField_management("TABLE", "DECWell_", "TEXT")
arcpy.AddField_management("TABLE", "match", "LONG")
## create insert cursor to insert a new row in the newly created table for each match found
rows = arcpy.da.InsertCursor("TABLE", ["DECWell_", "match"])
## for each entry in the dictionary of matches
for key in match:
## assign a variable for the unique ID of the well in the first for loop
ID = wellnum[key]
## iterate through each value in the list of matches
for s in match[key]:
## assign a tuple of the values the cursor will insert into the fields of each row
insert = (ID, s)
## insert new row
rows.insertRow(insert)
del rows

I have used an old variation of this script to produce a similar result in the past, and have observed no errors with regards to matching the correct well to the correct orps and getting the right well identifier out of the dictionary.

In the ArcGIS help for insert cursors, it says that you can select all fields in the table using a " * " in the "field_name" parameter of the cursor, and I thought that perhaps it was messing up because I was using all fields by name instead of using the " * ". So I had the script add a third field and try to use only those two, and also attempted to use the " * " with only two fields, and both still returned the same error that I mentioned above, of incorrect SQL syntax.

So just to be clear, the orp field and DECWell contain this 6 character string. In other words, the fields identified by input d (orpNames) and input f (DECwell) hold the same set of values. (again this code is not commented with a lot of meaningless variable names and is complex to follow).

At this point I would add print outputs to verify what the actual ID and s values are within the loop that inserts records immediately prior to inserting each record. Or else put that loop in a try except block and print those values in the except block if you are getting some records inserted and a specific records is failing. Do you know if the failure is always occurring with the first insert record operation, or is it only for a specific record?

Sorry, I'm really quite new to programming and am entirely self taught. I'll add some explanations to the reply above, however the DEC field holds that identifier, and the "match" field will hold an object ID of an orps point, which will be no higher than 5-6 digits.

I would also not ever create a table named TABLE. That is too likely to be a key word somewhere in ArcGIS and you need to avoid naming objects like Tables and fields with key word names. They can bite you in random ways, depending on how the object name is parsed and passed around in the internal code of the tools you are using. Some parts of the code may recognize the value passed as a string for an object name, while other parts may think they are being passed a key word used by SQL in table creation operations.

"TABLE" was simply my test name so I did not spend all of my time sorting out the parameters of my toolbox instead of trying to debug the script. when it's complete, the name of the table will be assigned by the user when the tool is run. Also, please note that I have updated my full code above with comments.

I agree with Xander that the full code you posted populates dictionaries with the same flaw as I pointed out in the case of the match dictionary. All values in all dictionaries need to be lists, and the list should only be initialized when the key is not in your dictionaries. Then append values to the list each time the dictionary key reappears. Use my suggestion for how to build the match dictionary as an example for the other dictionaries.

While we're at it... although you commented your code, it would be good to use variable names that mean something. Using a till h for the input parameters and settings will make it more difficult to understand the code. It is more understandable to do something like: