nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

simple insert cursor issues

ThomasCaruso

Hi folks,

I'm having some weird issues with an insert cursor.

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.

Here is the offending code:

match = {}

for o in well_owners:
    for p in orps_owners:
        if orps_owners
 == well_owners:
            match = []
            match.append(p)

arcpy.CreateTable_management(h, "TABLE")
arcpy.AddField_management("TABLE", "DECWell_", "TEXT")
arcpy.AddField_management("TABLE", "match", "LONG")

for key in match:
    rows = arcpy.da.InsertCursor("TABLE", ["DECWell_", "match"])
    for s in match[key]:
        rows.insertRow((wellnum[key], s))
    del rows

where well_owners and orps_owners are dictionaries of last names of owners and wellnum is a dictionary of unique identifiers to be written to the table.

Thanks in advance.

Find more posts tagged with

Accepted answers

RichardFairhurst

TABLE is a terrible test name. Test any other name, just be sure it is not a key word. Test values used by any code should always avoid using keywords when naming objects or fields.

All comments

ThomasCaruso

Richard,

I just want to thank you again for taking the time to help me out.

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
 == well_owners:  
            if not o in match:    
                match = []    
            match.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.

RichardFairhurst

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.

ThomasCaruso

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:

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

it's giving me an SQL error of...

"RuntimeError: An invalid SQL statement was used. [SELECT * FROM TABLE]"

in line 71, which, in my script, is this line:

rows.insertRow((wellnum[key], s))

Any idea what might cause this?

RichardFairhurst

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:

for o in well_owners:  
    for p in orps_owners:  
        if orps_owners
 == well_owners:
            if not o in match:  
                match = []  
            match.append(p)

arcpy.CreateTable_management(h, "TABLE")  arcpy.AddField_management("TABLE", "DECWell_", "TEXT")  arcpy.AddField_management("TABLE", "match", "LONG")    for key in match:      rows = arcpy.da.InsertCursor("TABLE", ["DECWell_", "match"])  
    for s in match[key]:
        for item in wellnum[key]:  
            rows.insertRow((wellnum[key], item))  
    del rows

You could add a check to see if s == [] and still create the wellnum[key] entry with a null item value, assuming you want all matched and unmatched well owners in the final output.

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.