Custom Pything script tool for ArcGIS using SDE

199
5
Jump to solution
03-27-2018 07:35 AM
RafiqBasaria2
New Contributor III

Hello. I am building an ArcGIS Toolbox tool that needs to join data from an SDE view table to an ArcGIS File Geodatabase feature class.

Currently, I am receiving this error message: 

Traceback (most recent call last):
File "C:\Projects\MDC\MDC_ViewtoGIS_SingleFC.py", line 53, in <module>
arcpy.CalculateField_management(gdfc, gdfc+"."+gai, vwfc+"."+vai, "VB", "")
File "c:\program files (x86)\arcgis\desktop10.4\arcpy\arcpy\management.py", line 3360, in CalculateField
raise e
ExecuteError: ERROR 999999: Error executing function.
Object required: 'MDCTest_1025'
Failed to execute (CalculateField).


Failed to execute (MDCSingleFC).

The code that I have so far is below.

Eventually, I will need to convert this to a script that will automatically run against 115 different feature classes on a weekly schedule, running against fixed database, with error logging being saved to a specific location...but one problem at a time.

Also, I know that some of these feature classes will not have all of the fields listed as variable in this script. Is there a way to tell the script to skip any fields that it does not find?

import arcpy, sys, string, os, subprocess

gdfc = arcpy.GetParameterAsText(0)
vwfc = arcpy.GetParameterAsText(1)

vwd = r'C:\Users\rbasaria\AppData\Roaming\ESRI\Desktop10.4\ArcCatalog\MDCTest_1025.sde'

gca = vca = "Conservation_Area_Name"
gcn = vcn = "Conservation_Area_Number"
gn = vn = "Network"

gfi = "Field_ID"
gai = "Asset_ID"
gt1n = "Tier_1_System_Name"
gt1t = "Tier_1_System_Type"
gt2n = "Tier_2_Major_Subsystem_Name"
gt2t = "Tier_2_Major_Subsystem_Type"
gt3n = "Tier_3_Minor_Subsystem_Name"
gt3t = "Tier_3_Minor_Subsystem_Type"
gt4n = "Asset_Name"

vfi = "FieldID"
vai = "AssetID"
vt1n = "Tier_1_Name"
vt1t = "Tier_1_Type"
vt2n = "Tier_2_Name"
vt2t = "Tier_2_Type"
vt3n = "Tier_3_Name"
vt3t = "Tier_3_Type"
vt4n = "Tier_4_Name"

slct = gdfc+"."+gai+" IS NULL OR ("+gdfc+"."+gai+" <> "+vwfc+"."+vai+")"

arcpy.AddJoin_management(gdfc, gfi, vwfc, vfi)
arcpy.SelectLayerByAttribute_management(gdfc, "NEW_SELECTION", slct)
arcpy.CalculateField_management(gdfc, gdfc+"."+gai, vwfc+"."+vai, "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gca, vwfc+"."+vca, "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gcn, vwfc+"."+vcn, "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gn, vwfc+"."+vn, "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gt1n, vwfc+"."+vt1n, "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gt1t, vwfc+"."+vt1t, "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gt2n, vwfc+"."+vt2n, "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gt2t, vwfc+"."+vt2t, "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gt3n, vwfc+"."+vt3n, "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gt3t, vwfc+"."+vt3t, "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gt4n, vwfc+"."+vt4n, "VB", "")
arcpy.SelectLayerByAttribute(gdfc, "CLEAR_SELECTION", "")
arcpy.RemoveJoin_management(gdfc)
0 Kudos
1 Solution

Accepted Solutions
RafiqBasaria2
New Contributor III

Ok, that makes sense. I am still pretty new at Python, and coding in general, so I don't understand a lot of things, but these little snippets definitely help me to understand things better, so I definitely appreciate it.

I just figured out what the issue was, and was coming here to post it when I saw your new post. I forgot to include the bracket character surrounding the SDE field name in my field calculations. Once I added that, the entire script runs, so long as all of the attributes exist within the feature class. So I am moving on to figure out how to make the script skip any of those attributes that do not exist in the data rather than crashing out.

Here is my current code, in functioning format, assuming all attributes exist.

import arcpy, sys, string, os, subprocess

gdfc = arcpy.GetParameterAsText(0)
vwfc = arcpy.GetParameterAsText(1)

vwd = r'C:\Users\rbasaria\AppData\Roaming\ESRI\Desktop10.4\ArcCatalog\MDCTest_1025.sde'
arcpy.Exists(vwd)

gca = vca = "Conservation_Area_Name"
gcn = vcn = "Conservation_Area_Number"
gn = vn = "Network"

gfi = "Field_ID"
gai = "Asset_ID"
gt1n = "Tier_1_System_Name"
gt1t = "Tier_1_System_Type"
gt2n = "Tier_2_Major_Subsystem_Name"
gt2t = "Tier_2_Major_Subsystem_Type"
gt3n = "Tier_3_Minor_Subsystem_Name"
gt3t = "Tier_3_Minor_Subsystem_Type"
gt4n = "Asset_Name"

vfi = "FieldID"
vai = "AssetID"
vt1n = "Tier_1_Name"
vt1t = "Tier_1_Type"
vt2n = "Tier_2_Name"
vt2t = "Tier_2_Type"
vt3n = "Tier_3_Name"
vt3t = "Tier_3_Type"
vt4n = "Tier_4_Name"

slct = gdfc+"."+gai+" IS NULL OR ("+gdfc+"."+gai+" <> "+vwfc+"."+vai+")"

arcpy.AddJoin_management(gdfc, gfi, vwfc, vfi)
arcpy.SelectLayerByAttribute_management(gdfc, "NEW_SELECTION", slct)
arcpy.CalculateField_management(gdfc, gdfc+"."+gai, "["+vwfc+"."+vai+"]", "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gca, "["+vwfc+"."+vca+"]", "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gcn, "["+vwfc+"."+vcn+"]", "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gn, "["+vwfc+"."+vn+"]", "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gt1n, "["+vwfc+"."+vt1n+"]", "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gt1t, "["+vwfc+"."+vt1t+"]", "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gt2n, "["+vwfc+"."+vt2n+"]", "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gt2t, "["+vwfc+"."+vt2t+"]", "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gt3n, "["+vwfc+"."+vt3n+"]", "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gt3t, "["+vwfc+"."+vt3t+"]", "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gt4n, "["+vwfc+"."+vt4n+"]", "VB", "")
arcpy.RemoveJoin_management(gdfc)
arcpy.SelectLayerByAttribute(gdfc, "CLEAR_SELECTION", "")

View solution in original post

0 Kudos
5 Replies
RebeccaStrauch__GISP
MVP Esteemed Contributor

So, this isn't a complete solution, and it may be something with the select statement, but it always helps to do a "print" or arcpy.AddMessage to your script to see what the variable values are and make sure things exist.  I have commented our any commands that will do anything, and only did a partial list of the CalculateField commands.  There are many ways to do this, but maybe this will help you spot the issue.  Hope it helps.

import arcpy, sys, string, os, subprocess

gdfc = "myGdfc"  #arcpy.GetParameterAsText(0)
vwfc = "myVwfc"  #arcpy.GetParameterAsText(1)

vwd = r'C:\Users\rbasaria\AppData\Roaming\ESRI\Desktop10.4\ArcCatalog\MDCTest_1025.sde'
arcpy.Exists(vwd)

gca = vca = "Conservation_Area_Name"
gcn = vcn = "Conservation_Area_Number"
gn = vn = "Network"

gfi = "Field_ID"
gai = "Asset_ID"
gt1n = "Tier_1_System_Name"
gt1t = "Tier_1_System_Type"
gt2n = "Tier_2_Major_Subsystem_Name"
gt2t = "Tier_2_Major_Subsystem_Type"
gt3n = "Tier_3_Minor_Subsystem_Name"
gt3t = "Tier_3_Minor_Subsystem_Type"
gt4n = "Asset_Name"

vfi = "FieldID"
vai = "AssetID"
vt1n = "Tier_1_Name"
vt1t = "Tier_1_Type"
vt2n = "Tier_2_Name"
vt2t = "Tier_2_Type"
vt3n = "Tier_3_Name"
vt3t = "Tier_3_Type"
vt4n = "Tier_4_Name"


#slct = gdfc+"."+gai+" IS NULL OR ("+gdfc+"."+gai+" <> "+vwfc+"."+vai+")"
slct = "g IS NULL OR (g <> v)"
print(slct)

aj = "arcpy.AddJoin_management({0}, {1}, {2}, {3})".format(gdfc, gfi, vwfc, vfi)
print("addJoin: {0}".format(aj))
#arcpy.AddJoin_management(gdfc, gfi, vwfc, vfi)

sla = 'arcpy.SelectLayerByAttribute_management({0}, "NEW_SELECTION", "{1}")'.format(gdfc, slct)
print("SelectLayerByAttribute: {0}".format(sla))
#arcpy.SelectLayerByAttribute_management(gdfc, "NEW_SELECTION", slct)


g = os.path.join(gdfc, gai)
v = os.path.join(vwfc, vai)
print("  g: {0}\n  v: {1}".format(g, v))
cfList = [[gai, vai], [gca, vca], [gcn, vcn]]

for gExt, vExt in cfList:
    cf = ('arcpy.CalculateField_management({0}, {1}, {2}, "VB", ""'.format(gdfc, gExt, vExt))
    print("CalculateField: {0}".format(cf))
    #arcpy.CalculateField_management(gdfc, gExt, vExt, "VB", "")
   

'''
arcpy.CalculateField_management(gdfc, gdfc+"."+gai, vwfc+"."+vai, "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gca, vwfc+"."+vca, "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gcn, vwfc+"."+vcn, "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gn, vwfc+"."+vn, "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gt1n, vwfc+"."+vt1n, "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gt1t, vwfc+"."+vt1t, "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gt2n, vwfc+"."+vt2n, "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gt2t, vwfc+"."+vt2t, "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gt3n, vwfc+"."+vt3n, "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gt3t, vwfc+"."+vt3t, "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gt4n, vwfc+"."+vt4n, "VB", "")
arcpy.SelectLayerByAttribute(gdfc, "CLEAR_SELECTION", "")
arcpy.RemoveJoin_management(gdfc)'''
RafiqBasaria2
New Contributor III

Hi Rebecca,

Thanks for the response!

This definitely helped me understand a few things better, but introduced more errors throughout the process. I used a combination of what I had and what you provided, and came up with the code below.

Currently, the tool seems to run successfully, but no fields are calculated. I am assuming this is because the calculations are done on the join, and the tool does not appear to address the table.field format of those calculations. However, when I add that in, I get the same Object Required: 'MDCTest_1025' error. It seems that the error comes from the actual field calculations using the SDE data? I am not sure. But the rest of the tool appears to work...it joins, selects based on the query, unselects, and removes the join. Just not calculating any of the fields.

import arcpy, sys, string, os, subprocess

gdfc = arcpy.GetParameterAsText(0)
vwfc = arcpy.GetParameterAsText(1)

vwd = r'C:\Users\rbasaria\AppData\Roaming\ESRI\Desktop10.4\ArcCatalog\MDCTest_1025.sde'
arcpy.Exists(vwd)

gca = vca = "Conservation_Area_Name"
gcn = vcn = "Conservation_Area_Number"
gn = vn = "Network"

gfi = "Field_ID"
gai = "Asset_ID"
gt1n = "Tier_1_System_Name"
gt1t = "Tier_1_System_Type"
gt2n = "Tier_2_Major_Subsystem_Name"
gt2t = "Tier_2_Major_Subsystem_Type"
gt3n = "Tier_3_Minor_Subsystem_Name"
gt3t = "Tier_3_Minor_Subsystem_Type"
gt4n = "Asset_Name"

vfi = "FieldID"
vai = "AssetID"
vt1n = "Tier_1_Name"
vt1t = "Tier_1_Type"
vt2n = "Tier_2_Name"
vt2t = "Tier_2_Type"
vt3n = "Tier_3_Name"
vt3t = "Tier_3_Type"
vt4n = "Tier_4_Name"

slct = gdfc+"."+gai+" IS NULL OR ("+gdfc+"."+gai+" <> "+vwfc+"."+vai+")"
print(slct)

aj = arcpy.AddJoin_management(gdfc, gfi, vwfc, vfi)
print("addJoin: {0}".format(aj))

sla = arcpy.SelectLayerByAttribute_management(gdfc, "NEW_SELECTION", slct)
print("SelectLayerByAttribute: {0}".format(sla))

g = os.path.join(gdfc, gai)
v = os.path.join(vwfc, vai)
print("  g: {0}\n  v: {1}".format(g, v))
cfList = [[gai, vai], [gca, vca], [gcn, vcn], [gn, vn], [gt1n, vt1n], [gt1t, vt1t], [gt2n, vt2n], [gt2t, vt2t], [gt3n, vt3n], [gt3t, vt3t], [gt4n, vt4n]]

for gExt, vExt in cfList:
    cf = arcpy.CalculateField_management(gdfc, gExt, vExt, "VB", "")
    print("CalculateField: {0}".format(cf))

#arcpy.CalculateField_management(gdfc, gdfc+"."+gai, vwfc+"."+vai, "VB", "")
#arcpy.CalculateField_management(gdfc, gdfc+"."+gca, vwfc+"."+vca, "VB", "")
#arcpy.CalculateField_management(gdfc, gdfc+"."+gcn, vwfc+"."+vcn, "VB", "")
#arcpy.CalculateField_management(gdfc, gdfc+"."+gn, vwfc+"."+vn, "VB", "")
#arcpy.CalculateField_management(gdfc, gdfc+"."+gt1n, vwfc+"."+vt1n, "VB", "")
#arcpy.CalculateField_management(gdfc, gdfc+"."+gt1t, vwfc+"."+vt1t, "VB", "")
#arcpy.CalculateField_management(gdfc, gdfc+"."+gt2n, vwfc+"."+vt2n, "VB", "")
#arcpy.CalculateField_management(gdfc, gdfc+"."+gt2t, vwfc+"."+vt2t, "VB", "")
#arcpy.CalculateField_management(gdfc, gdfc+"."+gt3n, vwfc+"."+vt3n, "VB", "")
#arcpy.CalculateField_management(gdfc, gdfc+"."+gt3t, vwfc+"."+vt3t, "VB", "")
#arcpy.CalculateField_management(gdfc, gdfc+"."+gt4n, vwfc+"."+vt4n, "VB", "")
arcpy.SelectLayerByAttribute_management(gdfc, "CLEAR_SELECTION", "")
arcpy.RemoveJoin_management(gdfc)
0 Kudos
RebeccaStrauch__GISP
MVP Esteemed Contributor

Keep in mind what I mentioned before "I have commented our any commands that will do anything, and only did a partial list of the CalculateField commands."  I did this since I wouldn't have any of your files to run against it.

What I setup with the lines like

cf = arcpy.CalculateField_management(gdfc, gExt, vExt, "VB", "")

was ONLY so I could print out what the values are for the command.  If all that looks good, you will want to repeat the line without the cf =, for example, in your code

arcpy.CalculateField_management(gdfc, gExt, vExt, "VB", "")

The reason I used the  .format  syntax is it easier for the print statement to make sure it was a text value for printing.  If your format and print shows that you are putting the correct values in, add the lines like above right after the print command (modify as needed for each command, of course)

0 Kudos
RafiqBasaria2
New Contributor III

Ok, that makes sense. I am still pretty new at Python, and coding in general, so I don't understand a lot of things, but these little snippets definitely help me to understand things better, so I definitely appreciate it.

I just figured out what the issue was, and was coming here to post it when I saw your new post. I forgot to include the bracket character surrounding the SDE field name in my field calculations. Once I added that, the entire script runs, so long as all of the attributes exist within the feature class. So I am moving on to figure out how to make the script skip any of those attributes that do not exist in the data rather than crashing out.

Here is my current code, in functioning format, assuming all attributes exist.

import arcpy, sys, string, os, subprocess

gdfc = arcpy.GetParameterAsText(0)
vwfc = arcpy.GetParameterAsText(1)

vwd = r'C:\Users\rbasaria\AppData\Roaming\ESRI\Desktop10.4\ArcCatalog\MDCTest_1025.sde'
arcpy.Exists(vwd)

gca = vca = "Conservation_Area_Name"
gcn = vcn = "Conservation_Area_Number"
gn = vn = "Network"

gfi = "Field_ID"
gai = "Asset_ID"
gt1n = "Tier_1_System_Name"
gt1t = "Tier_1_System_Type"
gt2n = "Tier_2_Major_Subsystem_Name"
gt2t = "Tier_2_Major_Subsystem_Type"
gt3n = "Tier_3_Minor_Subsystem_Name"
gt3t = "Tier_3_Minor_Subsystem_Type"
gt4n = "Asset_Name"

vfi = "FieldID"
vai = "AssetID"
vt1n = "Tier_1_Name"
vt1t = "Tier_1_Type"
vt2n = "Tier_2_Name"
vt2t = "Tier_2_Type"
vt3n = "Tier_3_Name"
vt3t = "Tier_3_Type"
vt4n = "Tier_4_Name"

slct = gdfc+"."+gai+" IS NULL OR ("+gdfc+"."+gai+" <> "+vwfc+"."+vai+")"

arcpy.AddJoin_management(gdfc, gfi, vwfc, vfi)
arcpy.SelectLayerByAttribute_management(gdfc, "NEW_SELECTION", slct)
arcpy.CalculateField_management(gdfc, gdfc+"."+gai, "["+vwfc+"."+vai+"]", "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gca, "["+vwfc+"."+vca+"]", "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gcn, "["+vwfc+"."+vcn+"]", "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gn, "["+vwfc+"."+vn+"]", "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gt1n, "["+vwfc+"."+vt1n+"]", "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gt1t, "["+vwfc+"."+vt1t+"]", "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gt2n, "["+vwfc+"."+vt2n+"]", "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gt2t, "["+vwfc+"."+vt2t+"]", "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gt3n, "["+vwfc+"."+vt3n+"]", "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gt3t, "["+vwfc+"."+vt3t+"]", "VB", "")
arcpy.CalculateField_management(gdfc, gdfc+"."+gt4n, "["+vwfc+"."+vt4n+"]", "VB", "")
arcpy.RemoveJoin_management(gdfc)
arcpy.SelectLayerByAttribute(gdfc, "CLEAR_SELECTION", "")

View solution in original post

0 Kudos
RebeccaStrauch__GISP
MVP Esteemed Contributor

Glad you have it figured out.  Remember to mark this as answered, and to mark helpful comments.  This will help others in the future and close out this thread.

0 Kudos