topic Re: Table join on SDE FC in Python? in Python Questions

Table join on SDE FC in Python?

JeremyRead — Wed, 04 Jan 2012 14:52:23 GMT

I am simply trying to join a table from an SDE featureclass to another table, then calculate some field values after the join. It is a very simple process, and relatively easy to do in ArcMap itself, but I'm having a really hard time getting it to work in Python. Apparently AddJoin_management will not work for SDE FCs, so what options are there?

# Import arcpy module
import arcpy

# Local variables:
AUSTIN_WWManhole = "Database Connections\\wwwgisoraold.sde\\AUSTIN.AustinWastewater\\AUSTIN.WWManhole"
IMSV7_COMPSMH = "Database Connections\\Hansen_Tables_Production.odc\\IMSV7.COMPSMH"

# Join tables based on COMPKEY field, only keep matching records
arcpy.AddJoin_management(AUSTIN_WWManhole, "COMPKEY", IMSV7_COMPSMH, "COMPKEY", "KEEP_COMMON")

# Select records where METERED = 'Y'
arcpy.SelectLayerByAttribute_management(AUSTIN_WWManhole, "NEW_SELECTION", "\"IMSV7.METERED\" = 'Y'")

# Calculate METERINDICATOR = "Y" for selected records
arcpy.CalculateField_management(AUSTIN_WWManhole, "AUSTIN.WWManhole.METERINDICATOR", "\"Y\"", "PYTHON_9.3", "")

# Select records where METERED = 'N' and METERINDICATOR = 'Y'
arcpy.SelectLayerByAttribute_management(AUSTIN_WWManhole, "NEW_SELECTION", "\"IMSV7.METERED\" = 'N' AND AUSTIN.WWManhole.METERINDICATOR = 'Y'")

# Calculate METERINDICATOR = "N" for selected records
arcpy.CalculateField_management(AUSTIN_WWManhole, "AUSTIN.WWManhole.METERINDICATOR", "\"N\"", "PYTHON_9.3", "")

print "Complete"

Re: Table join on SDE FC in Python?

MathewCoyle — Wed, 04 Jan 2012 15:43:10 GMT

I usually create a local copy and use arcpy.JoinField_management()

Re: Table join on SDE FC in Python?

JeremyLuymes — Wed, 04 Jan 2012 15:45:07 GMT

I think what you have there will work, you just need to convert your feature classes to feature layers.

Use arcpy.MakeFeatureLayer(AUSTIN_WWMHOLE,"Manhole") for example, and then just use "Manhole" instead of your feature class for inputs in the other commands.

For your table you might have to use arcpy.MakeTableView to get the join to work as well.

EDIT: The joinfield mentioned above probably won't work because it permanently adds a field...I assume you wouldn't want that for an SDE feature class.

Re: Table join on SDE FC in Python?

JeremyRead — Wed, 04 Jan 2012 16:33:23 GMT

I think what you have there will work, you just need to convert your feature classes to feature layers.

Use arcpy.MakeFeatureLayer(AUSTIN_WWMHOLE,"Manhole") for example, and then just use "Manhole" instead of your feature class for inputs in the other commands.

For your table you might have to use arcpy.MakeTableView to get the join to work as well.

EDIT: The joinfield mentioned above probably won't work because it permanently adds a field...I assume you wouldn't want that for an SDE feature class.

Thanks, I will try that. And you're right, I don't want to permanently add a field... I just want to join them to do the field calculations.

Re: Table join on SDE FC in Python?

JeremyRead — Wed, 04 Jan 2012 18:13:58 GMT

Well, I successfully used arcpy.MakeFeatureLayer_management to create the "Manhole" layer, however when I add it as a parameter in arcpy.AddJoin_management it says that "Manhole is not defined."

Also tried making a table view out of WWManhole and still got the same error: "Manhole is not defined".

Don't understand why it is saying this when I just created it right before running the AddJoin command....

Re: Table join on SDE FC in Python?

JeremyLuymes — Sun, 12 Dec 2021 03:53:47 GMT

Are you using quotations around the feature layers/tables. Something like this:

# Import arcpy module
import arcpy

# Local variables:
AUSTIN_WWManhole = "Database Connections\\wwwgisoraold.sde\\AUSTIN.AustinWastewater\\AUSTIN.WWManhole"
IMSV7_COMPSMH = "Database Connections\\Hansen_Tables_Production.odc\\IMSV7.COMPSMH"

arcpy.MakeFeatureLayer(AUSTIN_WWManhole,"Manhole")
arcpy.MakeTableView(IMSV7_COMPSMH,"Table")

# Join tables based on COMPKEY field, only keep matching records
arcpy.AddJoin_management("Manhole", "COMPKEY", "Table", "COMPKEY", "KEEP_COMMON")

# Select records where METERED = 'Y'
arcpy.SelectLayerByAttribute_management("Manhole", "NEW_SELECTION", "\"IMSV7.METERED\" = 'Y'")

# Calculate METERINDICATOR = "Y" for selected records
arcpy.CalculateField_management("Manhole", "AUSTIN.WWManhole.METERINDICATOR", "\"Y\"", "PYTHON_9.3", "")

# Select records where METERED = 'N' and METERINDICATOR = 'Y'
arcpy.SelectLayerByAttribute_management("Manhole", "NEW_SELECTION", "\"IMSV7.METERED\" = 'N' AND AUSTIN.WWManhole.METERINDICATOR = 'Y'")

# Calculate METERINDICATOR = "N" for selected records
arcpy.CalculateField_management("Manhole", "AUSTIN.WWManhole.METERINDICATOR", "\"N\"", "PYTHON_9.3", "")

print "Complete"

Re: Table join on SDE FC in Python?

JeremyRead — Sun, 12 Dec 2021 03:53:50 GMT

Nevermind, finally got it to work after a lot of trial and error:

# Import arcpy module
import arcpy

# Local variables:
IMSV7_COMPSMH = "C:\\Users\\readj\\AppData\\Roaming\\ESRI\\Desktop10.0\\ArcCatalog\\Hansen_Tables_Production.odc\\IMSV7.COMPSMH"
AUSTIN_WWManhole = "Database Connections\\wwwgisoraold.sde\\AUSTIN.AustinWastewater\\AUSTIN.WWManhole"
WWManhole_View = "WWManhole_View"

print " "
print "Update Meter Indicator Script STARTED: " + str(datetime.datetime.now())
print " "

# Make Table View of WWManhole
arcpy.MakeTableView_management(AUSTIN_WWManhole, WWManhole_View)

# Join tables based on COMPKEY field, only keep matching records
arcpy.AddJoin_management(WWManhole_View, "COMPKEY", IMSV7_COMPSMH, "COMPKEY", "KEEP_COMMON")

print "Tables Joined: " + str(datetime.datetime.now())

# Select records where METERED = 'Y'
arcpy.SelectLayerByAttribute_management(WWManhole_View, "NEW_SELECTION", "METERED = 'Y'")

# Calculate METERINDICATOR = "Y" for selected records
arcpy.CalculateField_management(WWManhole_View, "AUSTIN.WWManhole.METERINDICATOR", "'Y'", "PYTHON")

print "Calculated METERINDICATOR = 'Y': " + str(datetime.datetime.now())

# Select records where METERED = 'N' and METERINDICATOR = 'Y'
arcpy.SelectLayerByAttribute_management(WWManhole_View, "NEW_SELECTION", "METERED = 'N' AND AUSTIN.WWManhole.METERINDICATOR = 'Y'")

# Calculate METERINDICATOR = "N" for selected records
arcpy.CalculateField_management(WWManhole_View, "AUSTIN.WWManhole.METERINDICATOR", "'N'", "PYTHON")

print "Calculated METERINDICATOR = 'N': " + str(datetime.datetime.now())

print " "
print "Update Meter Indicator Script COMPLETED: " + str(datetime.datetime.now())