topic Append only ids that don't exist in another class in ArcGIS Pro Questions

Append only ids that don't exist in another class

ThomasHamming — Thu, 03 Feb 2022 15:22:31 GMT

I have 2 points classes with the same attributes, and I am looking to append data from one to the other. This is simple enough, but the first class has a lot of data that is already in the second class. I am looking to append only the rows/ids that don't exist in the other class already, and I can't seem to find an easy way to do this in pro. Any idea on how to do this?

For example, if this were a program it might look something like this:

for row in class1:

if row.id not in class2:

class2.append(row)

Re: Append only ids that don't exist in another class

JayantaPoddar — Thu, 03 Feb 2022 15:43:30 GMT

A workaround. How about Append followed by Delete Identical?

Re: Append only ids that don't exist in another class

Robert_LeClair — Thu, 03 Feb 2022 15:44:36 GMT

I wonder if a Pairwise Erase workflow would accomplish this task. The input features parameter is FC1. The erase features parameter is FC2. The output feature class is those features that are not coincident. Then use the output feature class to append the data to FC2.

Re: Append only ids that don't exist in another class

ThomasHamming — Thu, 03 Feb 2022 20:20:14 GMT

Thanks, and this is a solution but not an ideal one. The class I am appending from has over 10,000 points, and a normal append operation would take hours to complete. Theoretically only adding missing points would take much less time.

Re: Append only ids that don't exist in another class

JohannesLindner — Fri, 04 Feb 2022 09:16:42 GMT

Re: Append only ids that don't exist in another class

ThomasHamming — Fri, 04 Feb 2022 15:36:35 GMT

Thank you, this worked!

Re: Append only ids that don't exist in another class

JonathanWhite1 — Wed, 13 Apr 2022 18:48:40 GMT

I am trying to get that script to work in python notebook but no luck. Just copied above and added paths for target_class, append_class and specified the id_field. I'm super new to this so its probably something simple I am not understanding.

SyntaxError Traceback (most recent call last) File C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\Lib\ast.py, in parse: Line 35: return compile(source, filename, mode, PyCF_ONLY_AST) SyntaxError: (unicode error) 'unicodeescape' codec can't decode bytes in position 2-3: truncated \UXXXXXXXX escape (<string>, line 2)

Re: Append only ids that don't exist in another class

JohannesLindner — Mon, 25 Apr 2022 08:43:57 GMT

Sorry it took so long to answer, I was on vacation... If you haven't found a soultion yet:

It's probably because you used backslashes instead of forward slashes in your paths. Single backslashes are special characters in Python strings, they are used to encode characters that you can't simply type, e.g. "\n" for a linebreak.

Use one of these methods:

# forward slashes "path:/to/your/fc" # double backslashes "path:\\to\\your\\fc" # define it as raw string r"path:\to\your\fc"

Re: Append only ids that don't exist in another class

JonathanWhite1 — Tue, 17 May 2022 22:20:13 GMT

No matter what I do it just gives me this error below. I have tried just about everything. The only thing I can think of is perhaps this will not work with AGOL hosted tables? The target layer is in an AGOL relationship class tables while the append layer is just in AGOL feature set tables. I have also attempted to use a local file geodatabase table as the append class but I get the same error.

Traceback (most recent call last):
File "S:\GISDataActive\ParcelMap\AutomatedUpdates\Appendaddress.py", line 24, in <module>
append_layer = arcpy.management.MakeFeatureLayer(append_class, "append_layer", where_clause)
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\management.py", line 9395, in MakeFeatureLayer
raise e
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\management.py", line 9392, in MakeFeatureLayer
retval = convertArcObjectToPythonObject(gp.MakeFeatureLayer_management(*gp_fixargs((in_features, out_layer, where_clause, workspace, field_info), True)))
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\geoprocessing\_base.py", line 512, in <lambda>
return lambda *args: val(*gp_fixargs(args, True))
arcgisscripting.ExecuteError: Failed to execute. Parameters are not valid.
ERROR 000732: Input Features: Dataset https://services2.arcgis.com/wEula7SYiezXcdRv/arcgis/rest/services/Parcel_Updates/FeatureServer/1 does not exist or is not supported
Failed to execute (MakeFeatureLayer).
Failed script AddressAppend...
Failed to execute (AddressAppend).

Re: Append only ids that don't exist in another class

JohannesLindner — Wed, 18 May 2022 08:51:27 GMT

Oof. Yeah, arcpy (and in extension the desktop geoprocessing tools) don't play well with web content.

You can interact with hosted layers using the ArcGIS API for Python, but I don't know anything about that.

So, you have two options:

Download the hosted layers, run my script in the desktop environment, overwrite the hosted layer with the result
Figure out how to do it in the Python API. There are probably multiple community members who can help you.

Re: Append only ids that don't exist in another class

RexRobichaux — Fri, 12 Aug 2022 12:34:23 GMT

Hello Johannes,

Awesome and elegant solution! I'm trying to use this script to achieve a similar delta load between two feature classes but I'm running into an error I can't seem to get past. Regardless of how I try to structure the WHERE clause...arcpy continues to throw the 000358 error code indicating something isn't right with the SQL syntax. I'm printing out the (large) where clause (I have about 18k unique STATION_IDs that it's parsing through)...but everything looks find from a syntax perspective. After two days of testing/tweaking...I'm about to give up. Do you see anything amiss here? For development, both feature classes are local in a file geodatabase. I've tested in Python 3.x and 2.x and receive the same error in either environment.

import arcpy target_class = "C:\\Rex\\WQM_STATIONS_FINAL\\WQM_STATIONS_DELTA_LOAD.gdb\\WQM_STATIONS_FINAL" append_class = "C:\\Rex\\WQM_STATIONS_FINAL\\WQM_STATIONS_DELTA_LOAD.gdb\\WQM_STATIONS" id_field = "STATION_ID" # read the ids that are already in the target class existing_ids = [row[0] for row in arcpy.da.SearchCursor(target_class, [id_field])] # create a SQL where clause "STATION_ID NOT IN (1, 2, 3)" if isinstance(existing_ids[0], str): id_list = ["'{}'".format(i) for i in existing_ids] #print id_list else: id_list = ["'{}'".format(i) for i in existing_ids] #print id_list where_clause = '{} NOT IN ({})'.format(id_field, ", ".join(id_list)) print (where_clause) # create a layer of all append features that are not in the target class, append append_layer = arcpy.management.MakeFeatureLayer(append_class, "append_layer", where_clause) arcpy.management.Append(append_layer, target_class)

Thanks Johannes (and/or anyone else) that might have any ideas!

Re: Append only ids that don't exist in another class

JohannesLindner — Fri, 12 Aug 2022 06:15:15 GMT

Syntax looks fine. Could maybe be something with your weird id format.

Is the field in the append class really called STATION_ID? Are you able to use the query in the Select By Attributes tool?

Can you post the gdb, either here or in a pm?

Re: Append only ids that don't exist in another class

RexRobichaux — Fri, 12 Aug 2022 12:30:54 GMT

Thanks for the prompt response Johannes! Correct- the field name in both feature classes is "STATION_ID". I've tried a subset selection in both ArcGIS Pro and ArcMap and both worked fine (with say 4-5 stations)... which further made me scratch my head. I've attached the zip scratch/dev file geodatabase to this post to see if that helps. If/when I get this working and pushed to test/prod, the WQM_STATIONS feature class will be access/stored in an enterprise Oracle geodatabase, however the target FC (WQM_STATIONS_FINAL) will still be in a FGDB.

PS- it's expected for there to be a good deal more attributes in the WQM_STATIONS_FINAL FC...I'll likely have to make a field mapping at some point as well for the append if I can just get past this SQL expression error. Thanks again for offering to take a look at this!

Re: Append only ids that don't exist in another class

JohannesLindner — Fri, 12 Aug 2022 15:01:00 GMT

Yeah, it's your id values. Some of them contain apostrophes/single quotes. These characters are used in SQL to denote string values, so the where clause got corrupted.

For example, in this part, it recognizes the commas as string values and everything you actually want to select as mumbo jumbo:

(for the record, putting the where clause in there froze my PC for over 5 minutes. Can't imagine why, it's only 404.000 characters long :D)

Well, turns out there is only one id with an apostrophe:

[i for i in existing_ids if "'" in i] ["YOUNG'S POND"]

So either fix that id or make the code safer. Online search suggests that to escape a single quote in SQL, you just put another single quote in front of it.

So the code gets a tiny bit more complicated (replace call in line 12):

import arcpy target_class = "C:\\Rex\\WQM_STATIONS_FINAL\\WQM_STATIONS_DELTA_LOAD.gdb\\WQM_STATIONS_FINAL" append_class = "C:\\Rex\\WQM_STATIONS_FINAL\\WQM_STATIONS_DELTA_LOAD.gdb\\WQM_STATIONS" id_field = "STATION_ID" # read the ids that are already in the target class existing_ids = [row[0] for row in arcpy.da.SearchCursor(target_class, [id_field])] # create a SQL where clause "STATION_ID NOT IN (1, 2, 3)" if isinstance(existing_ids[0], str): id_list = ["'{}'".format(i.replace("'", "''")) for i in existing_ids] #print id_list else: id_list = ["'{}'".format(i) for i in existing_ids] #print id_list where_clause = '{} NOT IN ({})'.format(id_field, ", ".join(id_list)) print (where_clause) # create a layer of all append features that are not in the target class, append append_layer = arcpy.management.MakeFeatureLayer(append_class, "append_layer", where_clause) arcpy.management.Append(append_layer, target_class)

And it works!

(And then the Append throws an exception, because your fields don't match, but that's besides the point...)

Re: Append only ids that don't exist in another class

RexRobichaux — Fri, 12 Aug 2022 20:55:24 GMT

Awesome- thanks for sniffing that issue out Johannes...I suspected there might be a illicit character or two in there somewhere. This gets me up and running.

It's always surprised me there wasn't an off the shelf GP tool to do a delta compare/load as it's such a common ETL scenario (for us at least). Thanks again for the help and this nifty solution!

Re: Append only ids that don't exist in another class

JustPete — Mon, 10 Jul 2023 11:46:18 GMT

Hello,

Is there a way ion which the syntax can be run but doesn't create a new feature layer, and just appends the target dataset? It may already do this, I'm a python novice

Re: Append only ids that don't exist in another class

JohannesLindner — Sat, 16 Sep 2023 06:34:48 GMT

doesn't create a new feature layer

The three easiest ways to solve the original question in Python are:

create a layer with a definition query and append that layer (this is what my original answer does)
create a layer of the whole feature class, select the wanted rows and append that layer
do it all with arcpy.da.*Cursors

Options one and two create a temporal layer. If you run the script in the Python Window of an ArcGIS Project, that layer will be added to the current map. If that is your problem, you can add these lines to the end to remove the layer.

try: m = arcpy.mp.ArcGISProject("current").activeMap lyr = m.listLayers("append_layer")[0] m.removeLayer(lyr) except: print("Could not remove the temp layer")

If you don't want to create the layer for a different reason, you can use option 3 (untested, will probably be slower for large datasets):

target_class = "path:/to/class_2" append_class = "path:/to/class_1" id_field = "RowID" # read the ids that are already in the target class existing_ids = [row[0] for row in arcpy.da.SearchCursor(target_class, [id_field])] # create an SQL where clause "IdField NOT IN (1, 2, 3)" if isinstance(existing_ids[0], str): id_list = ["'{}'".format(i) for i in existing_ids] else: id_list = [str(i) for i in existing_ids] where_clause = "{} NOT IN ({})".format(id_field, ", ".join(id_list)) # get a list of all field names fields = [f.name for f in arcpy.ListFields(target_class)] # exclude some of those fields to avoid raising errors # replace the field "Shape" with "SHAPE@" exclude_fields = ["OBJECTID", "GlobalID", "Shape_Length", "Shape_Area"] fields = [f for f in fields if f not in exclude_fields] fields = ["SHAPE@" if f == "Shape" else f for f in fields] # start inserting into the target class with arcpy.da.InsertCursor(target_class, fields) as i_cursor: # start reading from the append_class, use the query we defined earlier with arcpy.da.SearchCursor(append_class, fields, where_clause) as s_cursor: # copy the rows for row in s_cursor: i_cursor.insertRow(row)

Re: Append only ids that don't exist in another class

JustPete — Fri, 15 Sep 2023 15:16:40 GMT

Hi Johannes,

This for the code, but it is returning this error:

NameError                                 Traceback (most recent call last)
In  [3]:
Line 18:    fields = [f.name for f in arcpy.ListFields(fc)]

NameError: name 'fc' is not defined

Re: Append only ids that don't exist in another class

JohannesLindner — Sat, 16 Sep 2023 06:35:44 GMT

Oops, used a wrong variable name... fixed

Re: Append only ids that don't exist in another class

JustPete — Fri, 22 Sep 2023 14:09:09 GMT

Perfect, thanks Johannes