Calculate Field Tool Does not Complete - Help

[ModernElectric]

I need help really bad. I have been looking through all of the Forum posts and and Help sections to try to figure out what is wrong with no luck. Here is what I am doing:
    I have a File Geodatabase (ArcMap 10.0) with a Feature Class (Service Location) which is the physical location of our electric meters. I have multiple fields that I need to update on a nightly basis. This data comes from our CIS system which holds account, customer and meter attribute information. I have a script to runs automatically every night that pulls an Oracle VW through an ODBC connection and brings it into my File Geodatabase as a Geodatabase Table. What I have to do is to update a specific field (Account Number) in the Feature Class with the same field in the table (ACCOUNTNUM)... Just an example. What I want to be able to do is to also have this script run automatically at night. I have around 9500 records in the feature class with about 15 different fields to do this to. The problem I am having is it takes up to 20 to 25 minutes for each field to update so at times - the script can take 8 to 9 hours to complete. From what I understand - this should take only a few minutes.... So what I am doing is not working.
I decided to go back to the "Drawing Board" to see if there is something I am missing with writing the code..... I decided to work with (1) field right now to see if I can make it work and get the update time down before I rewrite the rest of the code.

This is what I currently have and it does not seem to be working:

## Set the necessary product code import logging  # Import arcpy module import arcpy from arcpy import env   # if run in ArcGIS Desktop, show messages, also print to log def log(method, msg):     print msg     method(msg)  logging.basicConfig(         level=logging.INFO,         format='%(asctime)s %(levelname)-s %(message)s',         datefmt='%a, %d %b %Y %H:%M:%S',         filename='Test_Update.log',         filemode='w'         )  log(logging.info, "Updating Service Locations Feature Class Begins")  # Set environment settings env.workspace = "C:\\MEWCo GIS System\\Electric System\\MEWCo_Electric_Model-LOCAL.gdb"  # Set Local variables: inFeatures = "SERVICE_LOCATION"                         # Service Location Feature Class layerName = "SERVICE_LOCATION_LAYER"                    # Service Location Feature Layer fieldName1 = "SERVICE_ADDRESS" expression1 = "CIS_Account_Data.SERVICE_ADDRESS" joinField = "POLE_NUMBER" joinField2 = "MAPNO" joinTable = "CIS_Account_Data"  # Create a Feature Layer from Service_Location Feature Class log(logging.info, "Create Feature Layer for Service Location Feature Class") arcpy.MakeFeatureLayer_management(inFeatures, layerName)  # Join the feature layer to a table log(logging.info, "Join Table to Feature Layer Service Location") arcpy.AddJoin_management(layerName, joinField, joinTable, joinField2)      # Process: Calculate Field (Member Number) log(logging.info, "Update Member Number Field") arcpy.CalculateField_management(layerName, "SERVICE_LOCATION.MEMBER_NUMBER", '!MEMBERNO!', "PYTHON")  log(logging.info, "Complete")

I am not sure what is slowing this down. Am I looking at this process the wrong way?? Are there different tools that can be used to complete this mission??

Thank you for your help

[ModernElectric]

Michael-

Thank you for the update. I am experimenting with Caleb's script to see if I can get it to work properly. I might do some research on
the arcpy.AddIndex tool and rework my original script with the Join and Calculate Field tools to see what works best for our environment.

Thanks again

Chris and Caleb:

After further research I think I have found out why my script which performs the same action on a much larger dataset runs quickly.  I create an index on the field in the table that I am using to join to the feature class before I create the join.  I took the model from another person who built the model in modelbuilder and I exported it to a python script where I made some tweaks (That is why I was unaware of the index previously).

As such Chris, you could also add a arcpy.AddIndex_management step to your process in the CIS table which should dramatically speed up your original script (Only if you can not get Caleb's more advanced script to work for you).

[ModernElectric]

Michael & Caleb-

   I thought I had the code(s) good to go - but apparently I was wrong and starting to get a little frustrated because I do not understand what I am doing wrong. I can get the code to work just fine BUT with only (1) field at a time. When I add multiple fields to my list in both codes - it does not update anything.

Here is the MemoryTableTools.py code:

mport arcpy
from arcpy import env
from MemoryTableTools import *

#Set environment settings
env.workspace = "C:\\MEWCo GIS System\\Electric System\\MEWCo_Electric_Model-LOCAL.gdb"

# Set Local variables
inFeatures = "SERVICE_LOCATION"
joinField = "POLE_NUMBER"
joinField2 = "MAPNO"
joinTable = "CIS_Account_Data"
up_fields = ['CUSTOMER_NAME', 'ACCOUNT_NUMBER', 'TWACS_NUMBER', 'ACCOUNT_STATUS']
jn_fields = ['CUSTNAME', 'ACCOUNTNO', 'METERNO', 'ACCOUNT_STATUS_DESC']

# Run Attribute Update
MemoryTableTools.AttributeUpdate(inFeatures, 'POLE_NUMBER', up_fields, joinTable,'MAPNO',jn_fields)

Here is the other Python script that I am using to run the update:

import sys, traceback, arcpy, os
from os import path as p

def AttributeUpdate(source_table, in_field, update_fields, join_table, join_key, join_values):
    try:
        """ Updates fields from one table to user defined fields in another table"""
        # Check input parameters
        if not arcpy.Exists(source_table):
            print source_table + ' not found!\nPlease verify that full path of table exists'
            sys.exit()
        if not arcpy.Exists(join_table):
            print join_table + ' not found!\nPlease verify that full path of table exists'
            sys.exit()
        if in_field not in [f.name for f in arcpy.ListFields(source_table)]:
            print in_field + ' not found in ' + p.basename(source_table)
            sys.exit()
        if join_key not in [f.name for f in arcpy.ListFields(join_table)]:
            print join_key + ' not found in ' + p.basename(join_table)
            sys.exit()
        for fld in update_fields:
            if fld not in [f.name for f in arcpy.ListFields(source_table)]:
                print fld + ' not found in ' + p.basename(source_table)
                print 'Please verify that field names match in ' + p.basename(source_table)
                sys.exit()
        for fldb in join_values:
            if fldb not in [f.name for f in arcpy.ListFields(join_table)]:
                print fldb + ' not found in ' + p.basename(join_table)
                print 'Please verify that field names match in ' + p.basename(join_table)
                sys.exit()
        if not type(join_values) == list:
            join_values = list(join_values)
        if not type(update_fields) == list:
            update_fields = list(update_fields)

        # Make sure there is matching number of join and update fields
        update_dict = {}
        if len(update_fields) == len(join_values):
            for i in range(len(update_fields)):
                update_dict[join_values] = update_fields
                
            for k,v in update_dict.iteritems():
                # Create Dictionary
                path_dict = {}
                srows = arcpy.SearchCursor(join_table)
                for srow in srows:
                    keyrow = srow.getValue(join_key)
                    valrow = srow.getValue(k)
                    path_dict[keyrow] = valrow
                
                # Update Cursor
                urows = arcpy.UpdateCursor(source_table)
                for row in urows:
                    upkey = row.getValue(in_field)
                    try:
                        if upkey in path_dict:
                            row.setValue(v, path_dict[upkey])
                            urows.updateRow(row)
                    except:
                        pass  # skip nulls

                print '\'%s\' field in "%s" updated successfully' %(v, p.basename(source_table))
            del row, urows, srow, srows
          
        else:
            print 'ERROR:  Number of update fields and value fields does not match'
                
    except:
        print '\nERROR in AttributeUpdate Function.\nPlease Check input parameters and try again.'



if __name__ == '__main__':

    shp = r'C:\MEWCo GIS System\Electric System\MEWCo_Electric_Model-LOCAL.gdb\Electric\SERVICE_LOCATION'
    dbf = r'C:\MEWCo GIS System\Electric System\MEWCo_Electric_Model-LOCAL.gdb\CIS_Account_Data'

    # Attribute Update
    up_fields = ['CUSTOMER_NAME', 'ACCOUNT_NUMBER', 'TWACS_NUMBER', 'ACCOUNT_STATUS']
    jn_fields = ['CUSTNAME', 'ACCOUNTNO', 'METERNO', 'ACCOUNT_STATUS_DESC']
    AttributeUpdate(shp,'POLE_NUMBER',up_fields,dbf,'MAPNO',jn_fields)

Can you take a quick look and see what I am doing wrong - why I can get it to work with (1) field but when I add additional fields to it - it will not work at all.

Thank you

[MichaelVolz]

Chris:

Caleb's script is far more advanced than the script I was working on originally with you.  At this point the only help I can provide you with is on the original script as I do not have the time to digest and understand Caleb's script.

[ModernElectric]

Chris:

Caleb's script is far more advanced than the script I was working on originally with you.  At this point the only help I can provide you with is on the original script as I do not have the time to digest and understand Caleb's script.

Michael-

Not a problem....I am working on doing my homework on your last post dealing with the index(s)

[Anonymous User]

Can you take a quick look and see what I am doing wrong - why I can get it to work with (1) field but when I add additional fields to it - it will not work at all.

Hmm, it looks right to me.  I am able to get the script to work with multiple fields. 

change the exception code to this and let me know what the error messages are:

except:
        # Get the traceback object
        tb     = sys.exc_info()[2]
        tbinfo = traceback.format_tb(tb)[0]
        pymsg = "PYTHON ERRORS:\nTraceback info:\n%s\nError Info:\n%s\n" % (tbinfo, sys.exc_info()[1])
        msgs  = "ArcPy ERRORS:\n%s\n" % arcpy.GetMessages(2)
        arcpy.AddError(pymsg)
        arcpy.AddError(msgs)
        print pymsg
        print msgs

EDIT: One thing you want to make sure of before hand is that all the fields that you are trying to copy from one table to another are the same type (int to int, string to string).

[ModernElectric]

Caleb-

     It appears the script is and is not working.... When I run the script in Python IDLE - it shows that it is running and I am not getting errors back... But when I go look at the feature class - the fields are not updated. They are still showing the same data that I had before I ran the script. So it tells me - the script is not updating the field attributes. I can make it work with only (1) field - but when I add additional fields to the list - that is when it does not update.

Any ideas??

[Anonymous User]

That is strange...I use this all the time to update multiple fields and do not have this problem.  I am using a slightly different version of the script though since I'm at Arc 10.1, but my version is essentially doing the same thing.  I have changed a few parts and simplified the code.  Try this:

import sys, traceback, arcpy, os
from os import path as p

def AttributeUpdate(source_table, in_field, update_fields, join_table, join_key, join_values):
   
    # Make sure there is matching number of join and update fields
    update_dict = {}
    if len(update_fields) == len(join_values):
        for i in range(len(update_fields)):
            update_dict[join_values] = update_fields
            
        for k,v in update_dict.iteritems():
            # Create Dictionary
            path_dict = {}
            srows = arcpy.SearchCursor(join_table)
            for srow in srows:
                keyrow = srow.getValue(join_key)
                valrow = srow.getValue(k)
                path_dict[keyrow] = valrow
            del srow, srows
            
            # Update Cursor
            urows = arcpy.UpdateCursor(source_table)
            for row in urows:
                upkey = row.getValue(in_field)
                if upkey in path_dict:
                    row.setValue(v, path_dict[upkey])
                    urows.updateRow(row)
                else:
                    pass  # skip nulls
                
            del row, urows
            print '\'%s\' field in "%s" updated successfully' %(v, p.basename(source_table))
        
    else:
        print 'ERROR:  Number of update fields and value fields does not match'
            


if __name__ == '__main__':

    shp = r'C:\MEWCo GIS System\Electric System\MEWCo_Electric_Model-LOCAL.gdb\Electric\SERVICE_LOCATION'
    dbf = r'C:\MEWCo GIS System\Electric System\MEWCo_Electric_Model-LOCAL.gdb\CIS_Account_Data'

    # Attribute Update
    up_fields = ['CUSTOMER_NAME', 'ACCOUNT_NUMBER', 'TWACS_NUMBER', 'ACCOUNT_STATUS']
    jn_fields = ['CUSTNAME', 'ACCOUNTNO', 'METERNO', 'ACCOUNT_STATUS_DESC']
    AttributeUpdate(shp,'POLE_NUMBER', up_fields, dbf, 'MAPNO', jn_fields)

[ModernElectric]

Caleb-

   At this time - BINGO!!! - Looks like it worked this time. The fields updated.

I hope - (1) last question. I have a few other tables/feature classes that I want to be able to do this too.... Specifically dealing with the MemoryTableTools.py that is in the Python directory on my local drive --> For other Tables/Feature Classes - do I add to this python script and just add a inFeatures (Example: inFeatures2)???

Or do I need to make a seperate MemoryTableTools.py for each feature class??

Thanks again

That is strange...I use this all the time to update multiple fields and do not have this problem.  I am using a slightly different version of the script though since I'm at Arc 10.1, but my version is essentially doing the same thing.  I have changed a few parts and simplified the code.  Try this:

import sys, traceback, arcpy, os
from os import path as p

def AttributeUpdate(source_table, in_field, update_fields, join_table, join_key, join_values):
   
    # Make sure there is matching number of join and update fields
    update_dict = {}
    if len(update_fields) == len(join_values):
        for i in range(len(update_fields)):
            update_dict[join_values] = update_fields
            
        for k,v in update_dict.iteritems():
            # Create Dictionary
            path_dict = {}
            srows = arcpy.SearchCursor(join_table)
            for srow in srows:
                keyrow = srow.getValue(join_key)
                valrow = srow.getValue(k)
                path_dict[keyrow] = valrow
            del srow, srows
            
            # Update Cursor
            urows = arcpy.UpdateCursor(source_table)
            for row in urows:
                upkey = row.getValue(in_field)
                if upkey in path_dict:
                    row.setValue(v, path_dict[upkey])
                    urows.updateRow(row)
                else:
                    pass  # skip nulls
                
            del row, urows
            print '\'%s\' field in "%s" updated successfully' %(v, p.basename(source_table))
        
    else:
        print 'ERROR:  Number of update fields and value fields does not match'
            


if __name__ == '__main__':

    shp = r'C:\MEWCo GIS System\Electric System\MEWCo_Electric_Model-LOCAL.gdb\Electric\SERVICE_LOCATION'
    dbf = r'C:\MEWCo GIS System\Electric System\MEWCo_Electric_Model-LOCAL.gdb\CIS_Account_Data'

    # Attribute Update
    up_fields = ['CUSTOMER_NAME', 'ACCOUNT_NUMBER', 'TWACS_NUMBER', 'ACCOUNT_STATUS']
    jn_fields = ['CUSTNAME', 'ACCOUNTNO', 'METERNO', 'ACCOUNT_STATUS_DESC']
    AttributeUpdate(shp,'POLE_NUMBER', up_fields, dbf, 'MAPNO', jn_fields)

[Anonymous User]

I hope - (1) last question. I have a few other tables/feature classes that I want to be able to do this too.... Specifically dealing with the MemoryTableTools.py that is in the Python directory on my local drive --> For other Tables/Feature Classes - do I add to this python script and just add a inFeatures (Example: inFeatures2)???

Or do I need to make a seperate MemoryTableTools.py for each feature class??

Nice, yeah I noticed I had a few logical errors the first one (I had not used the 10.0 version in a while!).  The answer to you question...No.  If you have indeed saved the MemoryTableTools.py in a location inside the Python26 folder (the Lib folder for example), you should just be able to import the AttributeUpdate function into any other script.  So if you have to perform this operation for many tables, you could just import that tool and set up all your variables in the new script.  You can import many different ways:

import MemoryTableTools

# call the function using full namespace
MemoryTableTools.AttributeUpdate(.......)

OR you can do it without the namespace:

from MemoryTableTools import *

# call the function without namespace
AttributeUpdate(........)

OR you can import the specific tool and and not have to use the namespace

from MemoryTableTools import AttributeUpdate

AttributeUpdate(.........)

The latter two really do not make a difference since this is the only function you have inside the module.  In my MemoryTableTools script I have several different functions so I usually just use the second method.  Any of the the above will work.  The namespace is always the safest, but as long as you do not have the same names for functions in different imported modules the other 2 are fine as well.

This may be a little confusing at first, I know modules and imports took me a while to fully understand.  Below is an example of a few tests you can run.  For the first one, I just tell python to print the full path to the imported module.  The second demonstrates on how you can read all valid PYTHONPATH's.

>>> import MemoryTableTools
>>> print MemoryTableTools.__file__
C:\Python27\ArcGISx6410.1\lib\MemoryTableTools.py
>>> import sys
>>> for s in sys.path:
 print s

 
C:\Windows\system32
C:\Python27\ArcGISx6410.1\Lib\idlelib
C:\Windows\system32\python27.zip
C:\Python27\ArcGISx6410.1\DLLs
C:\Python27\ArcGISx6410.1\lib
C:\Python27\ArcGISx6410.1\lib\plat-win
C:\Python27\ArcGISx6410.1\lib\lib-tk
C:\Python27\ArcGISx6410.1
C:\Python27\ArcGISx6410.1\lib\site-packages
C:\Program Files (x86)\ArcGIS\Desktop10.1\bin64
C:\Program Files (x86)\ArcGIS\Desktop10.1\arcpy
C:\Program Files (x86)\ArcGIS\Desktop10.1\ArcToolbox\Scripts
C:\Python27\ArcGISx6410.1\Lib\CalebsTools
>>> 

EDIT: One more thing I wanted to mention just to make this a little clearer.  When you create your own functions (and/or classes), you can simply import the module (just the script.py) into any other script and use the tools just like an arcpy tool.  When you run any arcpy function, you simply import arcpy and use a tool from that module.  So inside the arcpy module, for example there is the AddField_management() tool.  To use this you type arcpy.AddField_management().  Any custom tools you write can be used the exact same way.  You import the module and use the namespace to run a function from that module (unless you use from module import *, then you can skip the namespace part and just use the function name).  I hope this makes sense.

[ModernElectric]

Caleb-

  You have been a huge help and I appreciate your time in helping me get this process working.... I am a little confused though since I am still very much a rookie when it comes to working with Python scripting. I have the basics down.. but this advanced stuff is a little over my head.

My next process:
   Service Location feature class (the same feature class I have been working with)
   CIS_Service_Meters Table

What do I put where in which script.

I have the Memory script in the Python directory. And I will make a new python script for the CIS Service Meters to update into the Service Location feature class

ALSO - Do you have a good website tutorial that you can point me to that I can learn about all this advanced python scripting (Dictionaries, indexes, etc)?

Thank you