Updating a row in a feature class from another feature class

Question

I'm trying to write a Python script that takes data from a table in a database and geocodes and updates customer data in another table based on whether it is NEW or CHANGED. I'm having two problems with the script:

The data is updated on a daily basis so I need to use the previous day's date in a select statement. Currently, I'm using a script that uses the datetime module to calculate the previous day's date, but I'm having issues using that variable in the Where statement. SNAP_DATE is the field that contains the date for the table.

The other, bigger, problem I'm having issues with is updating the table with the "CHANGED" customers. I've selected all the Changed addresses into a table and Geocoded it and altered the fields to match the Final table that needs to be updated. There is no primary field for the final table, however using two fields together-Site_ID and House_NBR- creates a unique identifier. What I want to do is have Python run through the final table and when it finds matches between the two tables, it replaces the row in the final table with the row from the Changed table.

I've looked at other people who have had similar problems and figure an Update Cursor is probably the best way to do this, but I just can't figure out how to do it for my specific case. I don't have a lot of experience with Python and cursors have been tough for me.

I've put the code that I've written here for reference.

# Import arcpy module
import arcpy
import datetime
from arcpy import env
env.overwriteOutput=True
env.workspace="C:/Data/GeocodeFiles/GeoAddr.gdb"
##arcpy.CreateFileGDB_management("C:/Data/GeocodeFiles", "GeoAddr.gdb")
today=datetime.date.today()
one_day=datetime.timedelta(days=1)
yesterday=today-one_day

### Local variables:
rawaddresses = "C:/Users/catherine.grenfell/AppData/Roaming/ESRI/Desktop10.5/ArcCatalog/Connection to pawlknims02.sde/WLKNIMS01.GIS.VW_NIMS_HOUSE_CUST_DAILY_DELTA"
Temp_Addresses = "C:/Data/GeocodeFiles/GeoAddr.gdb/Temp_Addresses"
Geocoder = "C:/Data/StreetMapPremium/nahr_2016.1_usa_locs/USA_PntAddrStrAddr"
New = "C:/Data/GeocodeFiles/GeoAddr.gdb/New"
Changed = "C:/Data/GeocodeFiles/GeoAddr.gdb/Changed"
New_Addresses = "C:/Data/GeocodeFiles/GeoAddr.gdb/New_Addresses"
Changed_Addresses = "C:/Data/GeocodeFiles/GeoAddr.gdb/Changed_Addresses"
Final= "C:/Users/catherine.grenfell/AppData/Roaming/ESRI/Desktop10.5/ArcCatalog/Connection to pawlknims02.sde/WLKNIMS01.GIS.Address"

# Process: Table Select
arcpy.TableSelect_analysis(rawaddresses, Temp_Addresses, "SNAP_DATE = 'yesterday'")
print "Table Selected"

#New Select
arcpy.TableSelect_analysis(Temp_Addresses, New, "Change_desc = 'NEW'")
print "New Created"

#Change Select
arcpy.TableSelect_analysis(Temp_Addresses, Changed, "Change_desc = 'CHANGED'")
print "Changed Created"

# Process: Geocode Addresses
arcpy.GeocodeAddresses_geocoding(New, Geocoder, "Address Address VISIBLE NONE;City ADDR_CITY VISIBLE NONE;State ADDR_STATE VISIBLE NONE;ZIP_Code ADDR_ZIP_5 VISIBLE NONE", New_Addresses, "STATIC")
print "New Geocoded"
arcpy.GeocodeAddresses_geocoding(Changed, Geocoder, "Address Address VISIBLE NONE;City ADDR_CITY VISIBLE NONE;State ADDR_STATE VISIBLE NONE;ZIP_Code ADDR_ZIP_5 VISIBLE NONE", Changed_Addresses, "STATIC")
print "Changed Geocoded"


#Delete Select
arcpy.TableSelect_analysis(rawaddresses, New, "Change_desc = 'NEW'")

#Delete_Field
arcpy.DeleteField_management("New_Addresses", ["Loc_name", "Status", "Score", "Match_type", "Match_addr",
                                       "Side", "X", "Y", "Latitude", "Longitude", "Addr_type",
                                       "Addnum", "StPreDir", "StPreType", "StName", "StType", "StDir",
                                       "City", "Subregion", "Region", "Postal", "Country", "LangCode",
                                       "Distance", "RegionAbbr", "Xmin", "Xmax", "Ymin", "Ymax", "StAddr",
                                       "AddNumFrom", "AddNumTo", "Arc_Address", "ARC_City", "ARC_State",
                                       "ARC_Zip_Code", "SNAP_DATE", "change_desc"])

arcpy.DeleteField_management("changed_Addresses", ["Loc_name", "Status", "Score", "Match_type", "Match_addr",
                                       "Side", "X", "Y", "Latitude", "Longitude", "Addr_type",
                                       "Addnum", "StPreDir", "StPreType", "StName", "StType", "StDir",
                                       "City", "Subregion", "Region", "Postal", "Country", "LangCode",
                                       "Distance", "RegionAbbr", "Xmin", "Xmax", "Ymin", "Ymax", "StAddr",
                                       "AddNumFrom", "AddNumTo", "Arc_Address", "ARC_City", "ARC_State",
                                       "ARC_Zip_Code", "change_desc"])


print "Fields Deleted"

arcpy.AlterField_management("New_Addresses", 'displayX', 'longitude')
arcpy.AlterField_management("New_Addresses", 'displayY', 'latitude')
arcpy.AlterField_management("Changed_Addresses", 'displayX', 'longitude')
arcpy.AlterField_management("Changed_Addresses", 'displayY', 'latitude')
print "Fields Renamed"

##Append
arcpy.Append_management(["New_Addresses", "changed_addresses"], Final, "NO_TEST")
print "Appended"

print "done"

Answer 1

It is difficult to debug your code without the data but some things you could try is:

Use AddFieldDelimiters and format to get correct syntax for all your sql queries, example:

feature_class = r'C:\data.gdb\features'
fieldname = 'Somefield'
sql = """{0} = 'NEW'""".format(arcpy.AddFieldDelimiters(datasource=feature_class, field=fieldname))
arcpy.TableSelect_analysis(in_table=feature_class, out_table='sometable', where_clause=sql)

I dont think you can pass a datetime object in an sql statment, you need to format it in a way arcpy will recognize. See this question: Using Python to select records by date field

Matchinig two tables/featureclasses on two fields in each table can be done by combining the fields into another field, then join on this and use Field Calculator to update. Or use a dictionary and the da.UpdateCursor:

fc1 = r'C:\data.gdb\fc1'
fc1_keyfield1 = 'City'
fc1_keyfield2 = 'Street'
fc1_field_to_update = 'Olddata'

fc2 = r'C:\data.gdb\fc2'
fc2_keyfield1 = 'City'
fc2_keyfield2 = 'Street'
fc2_field_with_newdata = 'Newdata'

#Create a dictionary using the da.SearchCursor and dictionary comprehension
newvaluesdict = {i[0]+i[1]:i[2] for i in arcpy.da.SearchCursor(fc2,[fc2_keyfield1,fc2_keyfield2,fc2_field_with_newdata])}

with arcpy.da.UpdateCursor(fc1,[fc1_keyfield1,fc1_keyfield2,fc1_field_to_update]) as cursor:
    for row in cursor:
        if row[0]+row[1] in newvaluesdict:
            row[2] = newvaluesdict[row[0]+row[1]]
            cursor.updateRow(row)

In this example if you pass CityStreet to the dictionary it will return whatever value is in the Newdata field:

some_data_returned_by_da_SearchCursor = [('Paris','Street1',123),('New York','Street2',456)]
d = {i[0]+i[1]:i[2] for i in some_data_returned_by_da_SearchCursor}

>>d
{'ParisStreet1': 123, 'New YorkStreet2': 456}

>>d['ParisStreet1']
123