arcpy.SelectLayerByAttribute_management Issues - how to use with an update cursor.

[EdX1]

Hello,

I have two questions, firstly about using SelectByAtribute_managementm and secondly why my selection won't work with my updateCursor. I apologise for the bad formatting below.

I am using:

arcpy.SelectLayerByAttribute_management(sTable_path, "NEW_SELECTION", sOldWhere)

to select 2 records from a 200k+ table of records. Those which match the SQL statement within sOldWhere

I wish to double check the correct items are selected, I am using:

selection_count = int(arcpy.GetCount_management(sTable_path).getOutput(0))

 This returns a count of 200k+, which is incorrect. The SQL statement is valid. It should have two items selected.


If I save the output of the SelectLayerByAttribute_management to a variable via:

itemsToRetire, itemsToRetireCount = arcpy.SelectLayerByAttribute_management(sTable_path, "NEW_SELECTION", sOldWhere)​

I can either print out itemsToRetireCount, or run getCount_management on itemsToRetire to get a result of 2, as expected.


What is going on? 


Secondly and some more context, I am accessing a large table via sde connection. 
I wish to then run an update cursor on the (2) selected table items, but using the itemsToRetire variable in this update cursor does not seem to work.  It hangs and eventually says the table cannot be updated.

sTable_path contains the full path to the actual table, going through a sde connection. 

SDE_Workspace_Path contains just the path to the SDE connection, not including the specific table

Here is a overview of the code, I have set up an edit session before accessing sTablePath using:

        edit = arcpy.da.Editor(SDE_Workspace_Path) #opening workspace path
        edit.startEditing(False, True) 
        edit.startOperation()
        try:

            itemsToRetire, itemsToRetireCount = arcpy.SelectLayerByAttribute_management(sTable_path, "NEW_SELECTION", sOldWhere)
            #selection_count = int(arcpy.GetCount_management(sTable_path).getOutput(0))
            print(f"!!!Selected {itemsToRetireCount} items for update using where clause: {sOldWhere}!!!")

...

        with arcpy.da.UpdateCursor(itemsToRetire, ("X", "Y")) as oRows:
            

but my update cursor won't work, and has a generic error of, cannot update table. 


Thanks. 

[JoshuaBixby]

It is best to keep a post to a single question because asking multiple questions in the same post tends to make the comments a mess when some people will reply to one question, some to the other, and some to both.  These two questions are closely related enough one could argue it is just one question, but please keep this in mind for future questions.

Regarding your first question of Get Count, it is expected behavior.  As its name states, Select Layer By Attribute works on layers, not data sets.  Somewhere back a few years ago, Esri decided to augment Select Layer By Attribute to accept an input data set and it will create a layer, select on it, and return it.  This is what is happening in your case.  When you use Get Count on the data set directly, it is returning the total number of records on the data set, which is should.

[EdX1]

Hello, would using 

arcpy.MakeTableView_management(sTable_path, "TableView", where_clause=sOldWhere) 

be more appropriate in this case given I am accessing a table? If so, how can I then run an update cursor on this temporary table which this creates and save it to my main dataset? Or should I save this to a variable instead of "tableview"? I will be running this code via the terminal of arcPro. 

Thanks.

[EdX1]

also, documentation: https://pro.arcgis.com/en/pro-app/latest/tool-reference/data-management/select-layer-by-attribute.ht...

Says it takes a table view as a input dataset. the data I am providing is a table. Is this not correct? as its a table view, rather than a table directly? 

[JoshuaBixby]

If you want to run a cursor against the selected records then pass itemsToRetire to the cursor since it is a table view or layer (depends on data source) that has the records selected.

[RhettZufelt]

Update Cursor accepts a where clause itself so there really is no need to select by attributes first. 

just put the expression in the cursor itself.

R_

[EdX1 · Accepted solution]

The issue was the result of incorrectly using 

edit.startEditing(False, True)

As this table was not versioned, this caused the script to hang for a long period of time. To get this to work I should have done False, False.