topic Re: simple insert cursor issues in Python Questions

simple insert cursor issues

ThomasCaruso — Sun, 12 Dec 2021 00:22:27 GMT

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.

Re: simple insert cursor issues

RichardFairhurst — Sun, 12 Dec 2021 00:22:30 GMT

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.

Re: simple insert cursor issues

ThomasCaruso — Sun, 12 Dec 2021 00:22:32 GMT

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?

Re: simple insert cursor issues

RichardFairhurst — Wed, 11 Feb 2015 16:24:56 GMT

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.

Re: simple insert cursor issues

ThomasCaruso — Sun, 12 Dec 2021 00:22:35 GMT

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.

Re: simple insert cursor issues

RichardFairhurst — Wed, 11 Feb 2015 17:30:50 GMT

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?

Re: simple insert cursor issues

ThomasCaruso — Wed, 11 Feb 2015 17:41:04 GMT

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.

Re: simple insert cursor issues

RichardFairhurst — Wed, 11 Feb 2015 17:51:08 GMT

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.

Re: simple insert cursor issues

ThomasCaruso — Wed, 11 Feb 2015 17:56:43 GMT

"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.

Re: simple insert cursor issues

RichardFairhurst — Wed, 11 Feb 2015 17:57:52 GMT

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.

Re: simple insert cursor issues

ThomasCaruso — Wed, 11 Feb 2015 18:04:09 GMT

Well, that was my mistake it seems. It worked perfectly with another test name.

Thank you for your patience, it's really very appreciated.

Re: simple insert cursor issues

XanderBakker — Wed, 11 Feb 2015 18:21:10 GMT

May I suggest that you carefully check the results of your script? You only check on part of the name and when that part of the name already exists in the dictionary the value will be overwritten.

Re: simple insert cursor issues

XanderBakker — Wed, 11 Feb 2015 18:30:32 GMT

... and BTW, you could find matching owner names using sets:

lst_matched_owners = list(set(dct_well_owners.values()) & set(dct_orps_owners.value()))

More tips can be found here: Some Python Snippets

Re: simple insert cursor issues

RichardFairhurst — Wed, 11 Feb 2015 19:13:29 GMT

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.

Re: simple insert cursor issues

XanderBakker — Sun, 12 Dec 2021 00:22:38 GMT

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:

## define all user inputs
fc_wells = arcpy.GetParameterAsText(0)
fc_orps = arcpy.GetParameterAsText(1)
tbl_out = arcpy.GetParameterAsText(2)
fld_well_owner = arcpy.GetParameterAsText(3)
fld_orps_owner = arcpy.GetParameterAsText(4)
ws = arcpy.GetParameterAsText(5)
fld_oid = "OID@"
fld_DecWell = "DECWell_"
fld_match = "match"

Re: simple insert cursor issues

BlakeTerhune — Wed, 11 Feb 2015 22:17:54 GMT

Great point Richard. I did a little research and I found the reserved keywords in Python 2.7‌

Although TABLE is not a Python reserved keyword, it is in SQL and so it should be avoided.