topic Re: Update existing rows/add new rows in master table from records in second table in Python Questions

Update existing rows/add new rows in master table from records in second table

MarkHuffman — Mon, 13 Feb 2012 17:54:38 GMT

I have a table that is a subset of a master table representing the parcel database. This subset is created every week and contains only updated and new records, and my task is to compare that table to the master table and, if existing records have different values, update those fields, and if there are new records, add those records to the master table.

This process has up to now been done in SDE using "sdeimport -o update_else_insert." I want to avoid interacting with SDE, but will if necessary.

My plan is to use SearchCursor to read the subset and the master table, and UpdateCursor and InsertCursor to edit the master table, using the APN as the key field. First, is this the right approach? Second, can someone please provide some starting code or psuedocode to help me? I am new at Python but not at programming. Thank you.

Re: Update existing rows/add new rows in master table from records in second table

StacyRendall1 — Sun, 12 Dec 2021 04:43:35 GMT

Hi Mark,

there are a huge number of ways you could do this, I would suggest taking advantage of Python dictionaries if you can... Maybe something like so (should be almost ready to run, except for any errors/typo's):

import arcpy

# paths to tables
masterTable = '//masterTable'
subsetTable = '//subsetTable'

# define fields:
keyField = 'APN'
testFeild1 = 'a'
testField2 = 'b'
#... and so on
## If you know all your fields and will never need to change them I would recommend using the 'dynamic' noation row.APN inseatd of row.getValue('APN') as I have used below

# dictionary of the contents of the subset table
# APN is key, fields to test are stroed in an ordered list
subsetDict = {}
sC = arcpy.SearchCursor(subsetTable)
for row in sC:
 subsetDict[row.getValue(keyField)] = [row.getValue(testField1), row.getValue(testField2)]
del sC, row

# get list of values that need to be updated
updateDict = {}
sC = arcpy.SearchCursor(masterTable)
for row in sC:
 key = row.getValue(keyField)
 testList = [row.getValue(testField1), row.getValue(testField2)]

 # if records have changed add to updateDuct and delete from subsetDict
 if subsetDict[key] != testList:
  updateDict[key] = subsetDict[key]
  del subsetDict[key]
 # records are the same, delete from subsetDict
 else:
  del subsetDict[key]
del sC, row

# anything left in subsetDict is new, can use insertCursor
iC = arcpy.InsertCursor(masterTable)
for key in subsetDict:
 row = iC.newRow()
 row.setValue(keyField, key)
 row.setValue(testField1, subsetDict[key][0])
 row.setValue(testField2, subsetDict[key][1])
 iC.insertRow(row)
 del row
del iC, subsetDict

# anything in updateDuct needs to be updated, use updateCursor
uC = arcpy.UpdateCursor(masterTable)
for row in uC:
 if row.getValue(keyField) in updateDict:
  row.setValue(testField1, updateDict[key][0])
  row.setValue(testField2, updateDict[key][1])
del uC, row, updateDict

The code takes a key field and two test fields (fields tested to see if they have been updated), but would be pretty expandable to any number of test fields...

Cheers,
Stacy

Re: Update existing rows/add new rows in master table from records in second table

MarkHuffman — Mon, 13 Feb 2012 19:46:57 GMT

H Stacy,

I'm near speechless with gratitude. I will study this code, adapt it, and give it a try. Thanks so much.

Mark

Re: Update existing rows/add new rows in master table from records in second table

MarkHuffman — Sun, 12 Dec 2021 04:43:38 GMT

Hi Stacy,

I am running the code with my test tables and fields, and I am encountering "Runtime error <type 'exceptions.KeyError'>: u'018021107'". I have narrowed it down to occurring in or after this block:

if subsetDict[key] != testList:
  updateDict[key] = subsetDict[key]
  del subsetDict[key]
 # records are the same, delete from subsetDict
 else:
  del subsetDict[key]
del sC, row

And here is my code:

import arcpy
from arcpy import env

# Define workspace
env.workspace = "C:/PythonParcelProcesses"

# paths to tables
masterTable = 'UpdateInsertTo.dbf'
subsetTable = 'UpdateInsertFrom.dbf'

# define fields:
keyField = 'APN'
testField1 = 'APN_SUFFIX'
testField2 = 'TRA'
testField3 = 'NAME_1'
testField4 = 'NAME_2'
testField5 = 'MAIL_ADDR'
testField6 = 'CTY_STA'

#... and so on
## If you know all your fields and will never need to change them I would recommend using the 'dynamic' notation row.APN instead of row.getValue('APN') as I have used below

# dictionary of the contents of the subset table
# APN is key, fields to test are stored in an ordered list
subsetDict = {}
sC = arcpy.SearchCursor(subsetTable)
for row in sC:
 subsetDict[row.getValue(keyField)] = [row.getValue(testField1), row.getValue(testField2), row.getValue(testField3), row.getValue(testField4), row.getValue(testField5), row.getValue(testField6)]
del sC, row

# get list of values that need to be updated
updateDict = {}
sC = arcpy.SearchCursor(masterTable)
for row in sC:
 key = row.getValue(keyField)
 testList = [row.getValue(testField1), row.getValue(testField2), row.getValue(testField3), row.getValue(testField4), row.getValue(testField5), row.getValue(testField6)]

 # if records have changed add to updateDict and delete from subsetDict
 if subsetDict[key] != testList:
  updateDict[key] = subsetDict[key]
  del subsetDict[key]
 # records are the same, delete from subsetDict
 else:
  del subsetDict[key]
del sC, row

# anything left in subsetDict is new, can use insertCursor
iC = arcpy.InsertCursor(masterTable)
for key in subsetDict:
 row = iC.newRow()
 row.setValue(keyField, key)
 row.setValue(testField1, subsetDict[key][0])
 row.setValue(testField2, subsetDict[key][1])
 row.setValue(testField3, subsetDict[key][2])
 row.setValue(testField4, subsetDict[key][3])
 row.setValue(testField5, subsetDict[key][4])
 row.setValue(testField6, subsetDict[key][5])
 iC.insertRow(row)
 del row
del iC, subsetDict

# anything in updateDict needs to be updated, use updateCursor
uC = arcpy.UpdateCursor(masterTable)
for row in uC:
 if row.getValue(keyField) in updateDict:
  row.setValue(testField1, updateDict[key][0])
  row.setValue(testField2, updateDict[key][1])
  row.setValue(testField3, updateDict[key][2])
  row.setValue(testField4, updateDict[key][3])
  row.setValue(testField5, updateDict[key][4])
  row.setValue(testField6, updateDict[key][5])  
del uC, row, updateDict

Can you please take another look? Thank you.

Mark

Re: Update existing rows/add new rows in master table from records in second table

StacyRendall1 — Sun, 12 Dec 2021 04:43:41 GMT

Hi Mark,

Sorry - I wrote it but wasn't able to test it, so I didn't pick this up. It is occurring where there are records in the master table that don't exist in the subset table (that is fine, and from your description, expected), this causes a KeyError from the dictionary (accessing a key that doesn't exist). So I have added a try...except statement that tells it to pass (do nothing) if a key error occurs for a particular APN...

Just replace the second Search Cursor block with this:

# get list of values that need to be updated
updateDict = {}
sC = arcpy.SearchCursor(masterTable)
for row in sC:
 key = row.getValue(keyField)
 testList = [row.getValue(testField1), row.getValue(testField2), row.getValue(testField3), row.getValue(testField4), row.getValue(testField5), row.getValue(testField6)]

 # try to get record in subsetDict to see if it has changed
 # if the subset table didn't contain a record for this entry, will trow KeyError (that's fine - do nothing)
 try:
  # if records have changed add to updateDict and delete from subsetDict
  if subsetDict[key] != testList:
   updateDict[key] = subsetDict[key]
   del subsetDict[key]
  # records are the same, delete from subsetDict
  else:
   del subsetDict[key]
 except KeyError:
  # this APN only exists in the master table, do nothing...
  pass
del sC, row

Hopefully that should work fine now!

Cheers,
Stacy

p.s. How does it compare speed-wise to the method you were using previously? Well, you would probably only notice a difference if the tables are pretty large, but there are a few things you could do to speed it up a little...

Re: Update existing rows/add new rows in master table from records in second table

MarkHuffman — Mon, 13 Feb 2012 23:37:33 GMT

Stacy,

Great! Thanks. Making progress. The master table is now being updated with new rows from the subset table. Strangely the first new row is being placed last in the master table, and a KeyError is being called on its key field value and, although a print command placed after the last line confirms that the script is completing, the insertCursor block does not seem to be working.

Any ideas?

Thanks again!

Mark

Re: Update existing rows/add new rows in master table from records in second table

StacyRendall1 — Tue, 14 Feb 2012 00:48:44 GMT

Hi Mark,

The master table is now being updated with new rows from the subset table

Good.

Strangely the first new row is being placed last in the master table

Do you mean: 1. 'the new rows are being added at the end', or 2. ' the top-most new row is bottom-most in the updated table'? Either way:
1. The InsertCursor adds a new row to the bottom of the table, so this is expected.
2. Dictionary keys are not actually ordered; so the order in which the rows are added to the table is (effectively) random.

and a KeyError is being called on its key field value

I need more detail on this.

A KeyError means that it is asking the dictionary for a key that doesn't exist. Which block is this occurring in?

and, although a print command placed after the last line confirms that the script is completing, the insertCursor block does not seem to be working.

If the Insert Cursor is not working, new rows would not be being added from the subset table (your first comment) - do you mean the Update Cursor?

If there are still issues, can you attach your test files and I will try to sort them out...

Re: Update existing rows/add new rows in master table from records in second table

MarkHuffman — Tue, 14 Feb 2012 15:00:30 GMT

Hi Stacy,

I wrote my last post in a rush while doing something else. I'll clarify.

Do you mean: 1. 'the new rows are being added at the end', or 2. ' the top-most new row is bottom-most in the updated table'? Either way:
1. The InsertCursor adds a new row to the bottom of the table, so this is expected.
2. Dictionary keys are not actually ordered; so the order in which the rows are added to the table is (effectively) random.

I meant #2 - but that's okay since they are added in random order, as you say.

I need more detail on this.

A KeyError means that it is asking the dictionary for a key that doesn't exist. Which block is this occurring in?

The KeyError calls out the APN value of the last new record in the master table: Runtime error <type 'exceptions.KeyError'>: u'203006146' -- That is the last record in the newly updated master table.

If the Insert Cursor is not working, new rows would not be being added from the subset table (your first comment) - do you mean the Update Cursor?

Yes, I meant the Update Cursor. The error is occurring in the Update Cursor block. The changes to the existing records in the subset table are not updated to the master table.

Thanks again for all your time and help.

Mark

Re: Update existing rows/add new rows in master table from records in second table

StacyRendall1 — Tue, 14 Feb 2012 18:02:18 GMT

Mark,

can you send me the files you are testing with (you can attach files to posts)? If you have to, zip them together.

I just can't figure what the problem might be without being able to test stuff and see it for myself.

Stacy

Re: Update existing rows/add new rows in master table from records in second table

MarkHuffman — Tue, 14 Feb 2012 20:46:09 GMT

Mark,

can you send me the files you are testing with (you can attach files to posts)? If you have to, zip them together.

I just can't figure what the problem might be without being able to test stuff and see it for myself.

Stacy

Stacy,

I have sent you a private message to arrange to send the data by more secure means. Thanks.

Mark

Re: Update existing rows/add new rows in master table from records in second table

StacyRendall1 — Tue, 14 Feb 2012 23:30:53 GMT

Stacy,

I have sent you a private message to arrange to send the data by more secure means. Thanks.

Mark

Thanks Mark, I received your message but for some reason I cannot reply or send you a new Private Message (seems to send, but nothing appears in the the Sent Items folder, and you obviously haven't received anything).

Regards,
Stacy

Re: Update existing rows/add new rows in master table from records in second table

MathewCoyle — Sun, 12 Dec 2021 04:43:43 GMT

Hate to barge in on a good conversation, but it looks like you are missing an update in your update cursor.

for row in uC:
 if row.getValue(keyField) in updateDict:
  row.setValue(testField1, updateDict[key][0])
  row.setValue(testField2, updateDict[key][1])
  row.setValue(testField3, updateDict[key][2])
  row.setValue(testField4, updateDict[key][3])
  row.setValue(testField5, updateDict[key][4])
  row.setValue(testField6, updateDict[key][5])
  uC.updateRow(row)

Re: Update existing rows/add new rows in master table from records in second table

MarkHuffman — Sun, 12 Dec 2021 04:43:46 GMT

Hate to barge in on a good conversation, but it looks like you are missing an update in your update cursor.

for row in uC:
 if row.getValue(keyField) in updateDict:
  row.setValue(testField1, updateDict[key][0])
  row.setValue(testField2, updateDict[key][1])
  row.setValue(testField3, updateDict[key][2])
  row.setValue(testField4, updateDict[key][3])
  row.setValue(testField5, updateDict[key][4])
  row.setValue(testField6, updateDict[key][5])
  uC.updateRow(row)

Hi Mathew,

Feel free to barge in any time. Thanks for the catch.

I'm still getting the error, which is occurring before uC.UpdateRow.

Mark

Re: Update existing rows/add new rows in master table from records in second table

JakeSkinner — Sun, 12 Dec 2021 04:43:48 GMT

I was executing code similar to this. Have you tried declaring the 'key' in the update cursor. Ex:

for row in uC:
        key = row.getValue(keyField)
 if row.getValue(keyField) in updateDict:
  row.setValue(testField1, updateDict[key][0])
  row.setValue(testField2, updateDict[key][1])
  row.setValue(testField3, updateDict[key][2])
  row.setValue(testField4, updateDict[key][3])
  row.setValue(testField5, updateDict[key][4])
  row.setValue(testField6, updateDict[key][5])
  uC.updateRow(row)

Re: Update existing rows/add new rows in master table from records in second table

MarkHuffman — Sun, 12 Dec 2021 04:43:51 GMT

I was executing code similar to this. Have you tried declaring the 'key' in the update cursor. Ex:

for row in uC:
        key = row.getValue(keyField)
 if row.getValue(keyField) in updateDict:
  row.setValue(testField1, updateDict[key][0])
  row.setValue(testField2, updateDict[key][1])
  row.setValue(testField3, updateDict[key][2])
  row.setValue(testField4, updateDict[key][3])
  row.setValue(testField5, updateDict[key][4])
  row.setValue(testField6, updateDict[key][5])
  uC.updateRow(row)

Hi Jake,

That was the final piece. My script is working now! Actually our script.

Thank you, Jake, Mathew, and especially Stacy.

Mark

Re: Update existing rows/add new rows in master table from records in second table

StacyRendall1 — Thu, 16 Feb 2012 01:51:34 GMT

Hi Mark,

that's great news! Writing is the easy part - it's fixing up all the little mistakes that usually takes the majority of the time, as you have seen!

How does that script compare with your former method; speed, reliability, etc.?

Regards,
Stacy

Re: Update existing rows/add new rows in master table from records in second table

StacyRendall1 — Thu, 16 Feb 2012 01:57:16 GMT

Also, if anyone is interested, here is the code to perform this operation for arbitrary tables (fields only defined once at the start):

import arcpy from arcpy import env  # Define workspace env.workspace = "C:/PythonParcelProcesses"  # paths to tables masterTable = 'UpdateInsertTo.dbf' subsetTable = 'UpdateInsertFrom.dbf'  # define fields: keyField = 'APN' testFields = ['APN_SUFFIX', 'TRA', 'NAME_1', 'NAME_2', 'MAIL_ADDR', 'CTY_STA']  # dictionary of the contents of the subset table # APN is key, fields to test are stored in an ordered list subsetDict = {} sC = arcpy.SearchCursor(subsetTable) for row in sC:  subsetDict[row.getValue(keyField)] = [row.getValue(field) for field in testFields] del sC, row  # get list of values that need to be updated updateDict = {} sC = arcpy.SearchCursor(masterTable) for row in sC:  key = row.getValue(keyField)  testList = [row.getValue(field) for field in testFields]   # try to get record in subsetDict to see if it has changed  # if the subset table didn't contain a record for this entry, will trow KeyError (that's fine - do nothing)  try:   # if records have changed add to updateDict and delete from subsetDict   if subsetDict[key] != testList:    updateDict[key] = subsetDict[key]    del subsetDict[key]   # records are the same, delete from subsetDict   else:    del subsetDict[key]  except KeyError:   # this APN only exists in the master table, do nothing...   pass del sC, row  # anything left in subsetDict is new, can use insertCursor iC = arcpy.InsertCursor(masterTable) for key in subsetDict:  row = iC.newRow()  row.setValue(keyField, key)  for i in range(len(testFields)):   field = testFields   row.setValue(field, subsetDict[key])  iC.insertRow(row)  del row del iC  # anything in updateDict needs to be updated, use updateCursor uC = arcpy.UpdateCursor(masterTable) for row in uC:  key = row.getValue(keyField)  if row.getValue(keyField) in updateDict:   for i in range(len(testFields)):    field = testFields    row.setValue(field, updateDict[key])   uC.updateRow(row) del uC, row, updateDict, subsetDict

Re: Update existing rows/add new rows in master table from records in second table

MarkHuffman — Thu, 16 Feb 2012 21:15:40 GMT

Also, if anyone is interested, here is the code to perform this operation for arbitrary tables (fields only defined once at the start):

Thanks, Stacy. This is what I was looking for - a way to define the fields once at the beginning and be done with it. I've got over a hundred total.:D

How does that script compare with your former method; speed, reliability, etc.?

I expect it will run faster and be more reliable, as well as be easier to edit and update.

Regards,
Mark

Re: Update existing rows/add new rows in master table from records in second table

StacyRendall1 — Thu, 16 Feb 2012 21:31:08 GMT

Hi Mark,

It is not even necessary for you to list all the fields yourself, arcpy.ListFields(table) should do the trick. The purpose of what I had was (started off doing it, then forgot to carry it through) that you could have certain fields that were tested for matching while the other fields were just copied if there was a difference in the test fields, and of course for new entries...

Regards,
Stacy

Re: Update existing rows/add new rows in master table from records in second table

MarkHuffman — Thu, 16 Feb 2012 21:36:37 GMT

Hi Mark,

It is not even necessary for you to list all the fields yourself, arcpy.ListFields(table) should do the trick. The purpose of what I had was (started off doing it, then forgot to carry it through) that you could have certain fields that were tested for matching while the other fields were just copied if there was a difference in the test fields, and of course for new entries...

Regards,
Stacy

I thought there had to be a way to access an existing list: the table itself. Thanks!

I really need to get more proficient at this. Do you recommend any books? I have "Learning Python," by Mark Lutz (O'Reilly).

Mark