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?

  • 2
    Have you tried in the other direction - use a search cursor to go through the features, check if they're in the list, and if they're not write them out with an insert cursor? Sep 7, 2017 at 21:58
  • 1
    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.
    – Vince
    Sep 7, 2017 at 22:11
  • 1
    As you're creating a temp feature class you could add a field (found?) and update cursor through the features setting found to 1 where the file is in the list and found to 0 where not in the list then all you need to do is select where found = 0. As you are comparing strings it's a good idea to either upper() or lower() both strings as python is a case sensitive language. Sep 7, 2017 at 22:13
  • Sorry I don't have years of experience when it comes to this kind of thing - hence why I'm the one always asking questions! I have just tried to read up on search and update cursors but I don't quite understand any examples they have shown. Can someone show me a basic example of what this would look like 'searching layer A for a similar value in table B, then populate a new field with that value'. Thanks
    – N. Begg
    Sep 8, 2017 at 1:28

1 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)
            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.

  • 1
    small typo - for in s in jpgList Sep 8, 2017 at 2:22
  • Perhaps skip update row , i.e. remove else
    – FelixIP
    Sep 8, 2017 at 2:31
  • Well spotted @MarcPfister. I've fixed that. Sep 8, 2017 at 2:34
  • Thanks, I think I will now be able to use this in the future as well.
    – N. Begg
    Sep 10, 2017 at 23:42
  • @MichaelStimson I have just tried to add a second update cursor to the same script as the above example that will change the 'Located' field to 1 if another field has a similar name ... using a LIKE statement in the Where clause. I seem to have issues with the like. with arcpy.da.UpdateCursor("in_memory/headphotos",['surname','Located']) as UCur1: for URow in UCur1: if (URow[0].lower() LIKE '%reserve%'): URow[1] = 1
    – N. Begg
    Sep 10, 2017 at 23:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.