topic Re: arcpy.SelectLayerByAttribute_management Issues - how to use with an update cursor. in Python Questions

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

EdX1 — Mon, 18 Mar 2024 02:15:04 GMT

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.

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

JoshuaBixby — Mon, 18 Mar 2024 13:33:47 GMT

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.

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

EdX1 — Mon, 18 Mar 2024 22:17:28 GMT

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.

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

EdX1 — Mon, 18 Mar 2024 22:33:18 GMT

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

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?

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

JoshuaBixby — Tue, 19 Mar 2024 14:01:30 GMT

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.

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

RhettZufelt — Tue, 19 Mar 2024 17:45:18 GMT

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.

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

EdX1 — Mon, 15 Apr 2024 06:10:22 GMT

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.