Processinging Joined tables with Python

[MatthewHyner]

I'm trying to get started with using Python to automate some tasks I'll be needing to do regularly...  so far it hasn't been too difficult but this one has me stumped.

I'm trying to process some OSM data, and creating a Spatial Join between the Building Footprints and Landuse.  Then I want to copy a field from Landuse over and process it a bit, finally remove the join(which may not really be necessary here but I'd like to know how)

The problem I'm running into is the Spatial Join creates a new name for itself each time its run, and I'm not sure how to access that name to copy the table attribute over let alone remove the join.  Here's what I have so far.  In this case, the joins name is now BuildingFootprintsCE_AddSpatialJoin_2, and it increases in value each time it is run. 

arcpy.management.AddSpatialJoin(
target_features="BuildingFootprintsCE",
join_features="landuse",
join_operation="JOIN_ONE_TO_ONE",
join_type="KEEP_ALL",
field_mapping=r'osm_id "osm_id" true true false 11 Double 0 11,First,#,C:\Visual\Area\US_Fort-Lauderdale\GIS\shape\landuse.shp,osm_id,-1,-1;name "name" true true false 48 Text 0 0,First,#,C:\Visual\Area\US_Fort-Lauderdale\GIS\shape\landuse.shp,name,0,48;type "type" true true false 16 Text 0 0,First,#,C:\Visual\Area\US_Fort-Lauderdale\GIS\shape\landuse.shp,type,0,16',
match_option="INTERSECT",
search_radius=None,
distance_field_name=""
)
arcpy.management.CalculateField(
in_table="BuildingFootprintsCE",
field="BuildingFootprintsCE.type",
expression="!BuildingFootprintsCE_AddSpatialJoin_1.type!.title()",
expression_type="PYTHON3",
code_block="",
field_type="TEXT",
enforce_domains="NO_ENFORCE_DOMAINS"
)

arcpy.management.RemoveJoin(
in_layer_or_view="BuildingFootprintsCE",
join_name="BuildingFootprintsCE_AddSpatialJoin_2"
)

Would appreciate any assistance...

[JohannesLindner · Accepted solution]

# add the spatial join, store the result layer in a variable
joined_layer = arcpy.management.AddSpatialJoin("BuildingFootprintsCE", "landuse","JOIN_ONE_TO_ONE", "KEEP_ALL")[0]

# get the join name from the layer's attributes
join_name = joined_layer.connectionProperties["destination"]["dataset"]
print(f"join name: {join_name}")

# do the calculation
expression = f"!{join_name}.type!.title()"
print(f"expression: {expression}")
arcpy.management.CalculateField("BuildingFootprintsCE", "BuildingFootprintsCE.type", expression, "PYTHON3")

# remove the join
arcpy.management.RemoveJoin("BuildingFootprintsCE", join_name)

[MatthewHyner]

Thankyou!  Blew my mind for a few minutes, but I think I see what's going on here now.  Messed with Python and controlling a raspberry pi a ways back...trying to apply it to ArcGIS now.