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; } } }

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

MarkHuffman

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.

Encuentra más publicaciones etiquetadas con

Accepted answers

StacyRendall1

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

All comments

StacyRendall1

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

MarkHuffman

H Stacy,

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

Mark

MarkHuffman

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

StacyRendall1

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

MarkHuffman

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

StacyRendall1

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

MarkHuffman

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

StacyRendall1

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

MarkHuffman

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

StacyRendall1

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

MathewCoyle

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)

MarkHuffman

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

JakeSkinner

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)

MarkHuffman

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

StacyRendall1

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

StacyRendall1

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

MarkHuffman

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

StacyRendall1

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

MarkHuffman

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

StacyRendall1

Well... Learning Python is one thing; learning Arcpy is something else altogether!

For Python; I think any book is good for the basics, then the rest is time and experience. The web is the resource I use most often, but also books on advanced things (such as GUIs) really help you learn the more complex stuff (even if not directly related). Although, the web help often tends to stupid oversimplification that makes it really hard to actually find what you want. It is something you are constantly learning, and whenever I look at a code from even a few months ago I am always surprised at how much I do different now to what I did then!

Arcpy was incredibly frustrating when I first started to use it. It is basically using Python to glue ArcGIS operations together in sequence, but you can't always take advantage of the cool things that Python lets you do, as you are so constrained by what Arc can (not) do. Your biggest friend, again, will be the Internet; googling 'arcpy thing' will usually turn up something (for example: listing fields in tables)...

The goal of my blog is to make it easier for Geographers to learn Python and easier for Programmers to learn to use Arc/Geographic programming interfaces, but I will be too busy over the next few months to make much progress with that. Keep an eye on it in future though!

Cheers,
Stacy

MarkHuffman

Regarding the use of

 testFields = arcpy.ListFields(masterTable)

instead of

testFields = ['APN_SUFFIX', 'TRA', 'NAME_1', 'NAME_2', 'MAIL_ADDR', 'CTY_STA']

: when I use the former I get the dread ERROR 999999 at the line

subsetDict[row.getValue(keyField)] = [row.getValue(field) for field in testFields]

What am I doing wrong?

Thanks.

Mark

P.S. I ran the script on the entire 262,000 + table for the six fields shown above, and it took 13 minutes in the IDLE GUI. The old method, a korn shell script interacting with SDE, took about 10, for all 100 + fields. (I first tried running it in the ArcCatalog Python window, and it is still running more than 2 hours later. YIKES!)

JakeSkinner

Hi Mark,

You will need to append the field name to the 'testFields' list. Try:

testFields = []

for fields in arcpy.ListFields(masterTable):
    testFields.append(fields.name)

MarkHuffman

Hi Mark,

You will need to append the field name to the 'testFields' list. Try:
testFields = []

for fields in arcpy.ListFields(masterTable):
    testFields.append(fields.name)

Hi Jake,

That did it. Thanks a lot! (And thanks for answering so quickly.)

Mark

MarkHuffman

Well, I'm getting the RuntimeError "Field is not editable" at

 row.setValue(field, subsetDict[key])

I've seen this only since I've changed the code to use the arcpy.ListFields method (see above).

Since the code is now accessing all fields, could it be that the OID (which is indeed not editable) has different values in the two tables that is source of the error? If so, what is the workaround?
Thank you.

Mark

StacyRendall1

Mark,

get it to print out the field names (either with print or arcpy.AddMessage() ) just before the line that errors; so the last one to print out before your error occurs will be the field causing the problem. If it is OID field, you can do something like this to remove it from the analysis:

keyFields = arcpy.ListFields(masterTable)
oidIndex = keyFields.index('OBJECTID') # or whatever the offending field is...
del keyFields[oidIndex]

Let me know what it is - I will update the post with the 'final' code to reflect this.

Cheers,
Stacy

MarkHuffman

Hi Stacy,

I took your advice and it worked (of course).

Here is how I printed the field names, in the InsertCursor block:

for i in range(len(otherFields)):
     field = otherFields
     print fields.name
     row.setValue(field, subsetDict[key])

It printed out only the first field name before it errored out - it was OBJECTID (even though in the table it appears as OID).

So I adapted your sample code to remove that field from analysis and placed it after the # define fields region:

# define fields
keyField = 'APN'
otherFields = []
for fields in arcpy.ListFields(subsetTable):
    otherFields.append(fields.name)
    
oidIndex = otherFields.index('OID')
del otherFields[oidIndex]

Now the script runs to completion. Thank you!

Mark

Únete a la Conversación

Los miembros registrados pueden publicar, seguir actualizaciones y más. ¿Nuevo aquí? Regístrate gratis.

Iniciar Sesión
Registrarse

Enlaces Rápidos

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