I am trying to write a Python script that will automate the process of comparing files within a directory to the id of a feature class, and then create (or refresh) a layer with only the features that do not find a file within the directory with their id.
I have created a Python script that almost successfully does this (works with test data) but my problem seems to be that the directory it is looking at contains over 4000 files (which appears to be SELECT BY ATTRIBUTE's limit) and will continue to grow in size.
Here is an example of the script I am trying to use:
import arcpy
import os
jpg = os.listdir(directory location)
photos =([s.strip('.jpg') for s in jpg])
arcpy.CopyFeatures_management(feature class location ,r"in_memory/headphotos")
tmp_layer = arcpy.mapping.Layer("in_memory/headphotos")
arcpy.SelectLayerByAttribute_management(in_layer_or_view=tmp_layer,selection_type="NEW_SELECTION", where_clause=' "ce_id" NOT IN' + str(tuple(photos)))
arcpy.CopyFeatures_management(tmp_layer,new layer)
You will notice that I use str(tuple(directory reference) as my current mode of cycling through the list of 4000+ files.
Is there a way to get past the problem of SELECT BY ATTRIBUTE only managing to contain some not all of the files?
Is there a way that I could possibly be doing this more efficiently?
NOT IN
is heinously inefficient with only a handful of members; using it with thousands is just not viable. If I were using a SQL database I'd do an OUTER JOIN. Don't forget about the ability to add selection elements and flip the selection set at the end.