Field Calculator messages from Python Script

[MikeLouwrens]

I have a python script that uses the Field Calculator arctoolbox tool to populate fields in a geodatabase from a SQL database table.  I runs on many feature classes and many fields.

Occasionally I get a pop-up message from the Field Calculator:

[ATTACH=CONFIG]16111[/ATTACH]

I don't know which feature class or field is causing this message, or why it's happening.  Is there a way to log these errors and suppress the pop-up? And is there a way to report which Feature Class and Field is causing it?

Is there a better way to do what I'm doing?  Would an update cursor be better?  I suspect an update cursor might take longer, but not sure...

Thanks,
Mike.

[JakeSkinner]

Hi Mike,

Are you running ArcGIS 10.1, yet?  ArcGIS 10.1 added a new data access module (arcpy.da).  The previously existing cursors (that are still listed under arcpy) are still functional and valid; however, the new arcpy.da cursors include significantly faster performance.  I tested the new arcpy.da.UpdateCursor vs the older updateCursor and CalculateField_management.  The new arcpy.da.UpdateCursor provided faster performance than the other two. 

For the error you are receiving, I would recommend incorporating print statements within your script and print each feature class you are updating.  That way you can pinpoint the problematic feature class when the error occurs.  The OBJECTID reported in the pop-up will tell you the specific feature within the feature class.

[ChristopherThompson]

Use a try: .. except: statement to help identify those errors, and keep your code so it runs as completely as it can (for instance if there are things downstream in your script that don't rely on those values that are spawning errors) - you can write a routine to create a log file to record the success/failure, and any error statements that are generated.  Print statements might help but if this code is being run through some other process you may not get a chance to see what those are.

Depending on how much data you're processing it also might be pretty easy to simply inspect the feature classes for a feature with an OID 1069 and see if the caculation you're doing is creating something that is incompatible for that object.  I've seen that specific error typically when i do something like trying to calculate a string into an numeric field.  You could also be trying to put a decimal value into an integer, or a 'long' integer into a 'short' integer, etc.  So, make sure the output from your calculation is appropriate for the fields you're calculating into.

[MikeLouwrens]

Thanks for your replies.

I have try/except statements, however the script is not failing - no python error is being produced (just an ArcGIS notification pop-up) - so this doesn't help in this situation unfortunately.

I'll try adding more print statements and see if I can pinpoint the problem feature classes or fields.  Might even add some tests to check for field types too.

I tried using an update cursor, but it appears these don't work with joined tables?  Will the new arcpy.da work with joined tables?

Cheers,
Mike.

[MikeLouwrens]

Hi again,

I've discovered the problem is mis-matched field types (which is another issue altogether - they should be all the same!)  My GIS field type is correct, the join DB field type is incorrect.

So I tried to put a test in for where the GIS field type is Double (which is the type it always fails on, as some of the DB field types are Text instead of Double).

if LayerFieldType == "Double":
    arcpy.CalculateField_management(LayerName, FC + "." + gisField, float("!" + tableNameOutput + "." + dbField + "!"), "PYTHON", "")

else:
    arcpy.CalculateField_management(LayerName, FC + "." + gisField, "[" + tableNameOutput + "." + dbField + "]", "VB", "")

however it fails on the Calculate Field with the conversion to float or double.  I'm not sure quite how to structure the code to do a conversion of the field value within the CalculateField tool.  The code in the else statement works.

FC is the name of my feature class, tableNameOutput is the name of my table.  These are prefixed on all field names due to the join.

Cheers,
Mike.

[MikeLouwrens]

OK, so it appears there is no conversion necessary - the values are converting fine.  What is happening is the field calculator is finding a NULL value, which is of course invalid in a numeric field.

Would be good if there was a way to suppress the popup messages so that the script wouldn't halt and wait for user input (useless really on a automated script)...

Cheers,
Mike.

[JakeSkinner]

Is the NULL value within the feature class's numeric field, or the database table's numeric field?  You can have NULL values within a feature class's numeric field as long as 'ALLOW NULL VALUES' is set to YES.

[MikeLouwrens]

Is the NULL value within the feature class's numeric field, or the database table's numeric field?  You can have NULL values within a feature class's numeric field as long as 'ALLOW NULL VALUES' is set to YES.

No, the NULL values were within the table's TEXT field (should have been numeric, but for some reason was text).  Some of these were even spaces instead of NULL.

What I did was added a bit that calculated all NULL or spaces in the table (in the text fields that should have been numeric) to be 0 before anything else ran.  Things are working alright now.  Time to talk to the DBA about why these fields are text instead of numeric 🙂

Cheers,
Mike.

[ChristopherThompson]

I've discovered the problem is mis-matched field types (which is another issue altogether - they should be all the same!)  My GIS field type is correct, the join DB field type is incorrect.
.... I'm not sure quite how to structure the code to do a conversion of the field value within the CalculateField tool.  The code in the else statement works.

A couple of things:  those expressions in your calc field statement are really cumbersome I think and make it easy to miss some sort of syntax error. Try creating the new value outside the calculate_field tool and then simply referencing that as a variable inside the tool.  Also, don't bother testing for a double value, since the GIS field is double and that is where the value is going to update, simply make the value you supply a double, regardless if it started out life as a string  - float(x) wont' have any impact on x if its already a double or a floating point value but makes your string play nice with double fields.

if you wrap this into an update cursor it I think it would be reasonably easy to accomplish without implementing the calc field tool with something like this:

rows = arcpy.UpdateCursor(your arguments go here)
for row in rows:
 new_value = float(row.DBfield)
 row.GISfield = new_value
 rows.updateRow(row)

or maybe

for row in rows:
 new_value = row.DBfield
 row.GISfield = float(new_value)
 rows.updateRow(row)

or even

for row in rows:
 row.GISfield = float(row.DBfield)
 rows.updateRow(row)

Also, without trying to point out the obvious too much, since the source (database) seems to be providing mixed data, and this is probably the incorrect condition for your business practices, it might be worth the effort to get the database 'fixed' so it supplies the right data in the right format.  I know how that can go though, hope that suggestion doesn't rub too much salt in wounds!