Writing python script to select attributes not in growing table with over 4000 values?

Question

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?

Answer 1

Expanding on the comment I made:

import os, sys, arcpy

# A different way of getting a list of raster files, I find this one better
# as os.listdir returns *everything* and you need to sort out the junk.
arcpy.env.workspace = 'directory location'
jpgList = arcpy.ListRasters('*','JPG') # List only JPEG images in the *workspace*

photos =([s.lower().strip('.jpg') for s in jpgList ]) # do all comparison in lower case

arcpy.CopyFeatures_management(feature class location ,r"in_memory/headphotos")
arcpy.AddField_management("in_memory/headphotos",'Located','SHORT') # Add the 'Located' field

# using an update cursor mark every row to indicate if the photo is located
with arcpy.da.UpdateCursor("in_memory/headphotos",['ce_id','Located']) as UCur:
    for URow in UCur:
        if URow[0].lower() in photos: # if 'ce_id' in lower case is found in photos
            URow[1] = 1               # set 'Located' to 1 (Yes)
        else:
            URow[1] = 0               # otherwise set 'Located' to 0 (No)
        UCur.updateRow(URow)          # Save the change for this row

# Using a where clause to limit to only unfound rows
arcpy.MakeFeatureLayer_management("in_memory/headphotos",'tmp_layer','Located = 0')
arcpy.CopyFeatures_management('tmp_layer',new layer)

I hope this brings you to a better understanding of using cursors in arcpy. Please note the with statement, this is possibly universally agreed to be the best way to handle arcpy.da cursors; do not confuse with arcpy cursors (not da) which are incompatible with with statements. This is because cursors, of either type, must be deleted to release locks on datasets which can cause real headaches.