1000 features limit using CopyFeatures_management?

Question

I wrote a script to select features from a sql server spatial database based on date, there are thousands and thousands of records. The script runs but when I used the CopyFeatures_management tool, the resulting feature class only has 1000 features in it when it should have more. (the correct amount is achieved when I run the process manually) I'm running ArcGIS 10.4 if that makes a difference.

I'm hoping to find out why the script doesnt fully complete and crashes before finishing and only selects 1000 records.

print "start"

import arcpy
from arcpy import env

env.overwriteOutput=True

DB_connection = "Database Connections\\CNTM_MAC_GIS(EPRD-SHS12-LTR,62246).sde"
Output_Feature_Layer = "gps_points"

arcpy.MakeQueryLayer_management(DB_connection, Output_Feature_Layer, "select * from CMTEN_MAC_GIS.dbo.tben_SCSGPSData", "Record_ID", "POINT", "4326", "") #selecting from sde


selection=arcpy.SelectLayerByAttribute_management(Output_Feature_Layer, "NEW_SELECTION", "RecordUpdate_DTM > '2018-10-01 07:48:09' AND RecordUpdate_DTM < '2018-10-30 07:50:04'") #selecting date ranges, beginning to end of month

saved_to="W:\MAC\GIS\Client_Requests\Stats\gps.gdb\gpspoints" #output filepath
arcpy.CopyFeatures_management(selection, saved_to) #saving to location

print "done"

Answer 1

As I describe in my blog, you'll need to created a function which selects 1000 values at a time. Here's what I came up with:

import arcpy

def LayerFromList (inLyr, inField, inList):

    """
    Returns an arcpy feature layer of the 
     features matching the values in 
     the inList in field inField
    inLyr can be a feature layer or 
     feature class
    inField is the field name in the 
     inLyr used for selection
    inList is a python list of values 
     to match to values in inField of inLyr
    Created to work around 1000 item 
     limit in Oracle database SQL querry
    """

    #convert list to strings
    mapList = map (str, inList)

    #Get Field Type
    fldType = [field.type for field
               in arcpy.ListFields (inLyr)
               if field.name == inField][0]

    #Make temporary feature layer to apply selections to
    tempLyr = "lyr0"
    num = 0
    #Get available layer name
    while arcpy.Exists (tempLyr):
        num += 1
        tempLyr = "lyr" + str (num)
    #Make layer
    arcpy.MakeFeatureLayer_management (inLyr, tempLyr)

    #features selected counter
    selected = 0
    #iterate
    while True:
        #Get next 1000 rows
        partial = mapList[selected:selected + 1000]
        #Exit while loop if there are no more items in the list
        if not partial:
            break

        #Create the SQL where cluase
        if fldType in ["String", "Guid"]:
            joinStr = "'{0}'".format ("', '".join (partial))
        else:
            joinStr = ", ".join (partial)
        sql = "{0} IN ({1})".format (arcpy.AddFieldDelimiters
                                     (tempLyr, inField), joinStr)

        #Add next 1000 rows to selection
        arcpy.SelectLayerByAttribute_management (tempLyr, 
                                                 "ADD_TO_SELECTION", 
                                                 sql)
        #Add 1000 to selected varaible
        selected += 1000


    #Make output feature layer
    num += 1
    outLyr = "lyr" + str (num)
    while arcpy.Exists (outLyr):
        num += 1
        outLyr = "lyr" + str (num)
    arcpy.MakeFeatureLayer_management (tempLyr, outLyr)

    #Delete temporary layer
    arcpy.Delete_management (tempLyr)

    return outLyr

This function will return a layer with the desired features. It can then be used as input for the CopyFeatures tool.

Answer 2

Since the source is a feature class, can you try just making a feature layer directly from it?

import arcpy
arcpy.env.overwriteOutput=True
arcpy.env.workspace = "Database Connections\\CNTM_MAC_GIS(EPRD-SHS12-LTR,62246).sde"

arcpy.MakeFeatureLayer_management("CMTEN_MAC_GIS.dbo.tben_SCSGPSData", "gps_points")

arcpy.SelectLayerByAttribute_management("gps_points", "NEW_SELECTION", "RecordUpdate_DTM > '2018-10-01 07:48:09' AND RecordUpdate_DTM < '2018-10-30 07:50:04'") 

print arcpy.GetCount_management('gps_points')[0], 'points selected'

saved_to="W:\MAC\GIS\Client_Requests\Stats\gps.gdb\gpspoints"
arcpy.CopyFeatures_management("gps_points", saved_to)

print "done"

Or maybe this approach:

import arcpy
arcpy.env.overwriteOutput=True
arcpy.env.workspace = "Database Connections\\CNTM_MAC_GIS(EPRD-SHS12-LTR,62246).sde"
temp = "in_memory/temp_pts"

arcpy.CopyFeatures_management("CMTEN_MAC_GIS.dbo.tben_SCSGPSData", temp)

arcpy.SelectLayerByAttribute_management(temp, "NEW_SELECTION", "RecordUpdate_DTM > '2018-10-01 07:48:09' AND RecordUpdate_DTM < '2018-10-30 07:50:04'") 

print arcpy.GetCount_management(temp)[0], 'points selected'

saved_to="W:\MAC\GIS\Client_Requests\Stats\gps.gdb\gpspoints"
arcpy.CopyFeatures_management(temp, saved_to)

print "done"

Answer 3

We experienced this issue when copying into the Memory workspace in ArcGIS Pro. I am recording what we found because none of the other solutions worked. It turns out if there is a bad geometry only 1000 records get copied. To fix it first run check geometry, copy out the bad geometry (of you need to keep it), delete the bad geometry out of the geodatabase using the repair geometry command. Then export. That is all there is. Sorry not using arcpy code here. Just had to record this for future you (and probably me).