topic Database is stale after deleted table data using Pro UI and fetching using SearchCursor in Python Questions

Database is stale after deleted table data using Pro UI and fetching using SearchCursor

chris_del101 — Wed, 23 Jul 2025 18:03:16 GMT

`arcpy.da.SearchCursor` is returning unpredicable results.

I have an empty table in pro. I just deleted it and saved it, so I know it's empty.
When I then `SearchCursor` on that table it returns results! But the table is empty!

cur = arcpy.da.SearchCursor(full_table_path, field_names) for c in cur: print(c)

I wanted to use this function to check for duplicates before inserting, but it seems highly unstable. Is there any way to make it pick up recent changes?
Whatever the issue, if the table is not actually empty but just shows empty, or if it's the function, I just need it to work accurately.
It's a file table, so maybe this is why?

Re: How to use SearchCursor accurately

TonyAlmeida — Tue, 22 Jul 2025 21:31:45 GMT

Try this, always use the cursor within a WITH statement to ensure proper cleanup.

with arcpy.da.SearchCursor(full_table_path, field_names) as cur:
    for row in cur:
        print(row)

Re: How to use SearchCursor accurately

HaydenWelch — Wed, 23 Jul 2025 03:43:09 GMT

As Tony said, you always want to use the context manager protocol when it's implemented (with Foo() as bar : ...).

Cursors will maintain a lock on the table until they're deleted (with del) which happens automatically once you exit the with block.

They can also be confusing if you are using different inputs. Say a layer with a selection or a definition query instead of a raw table/feature resource.

Cursors that are passed a layer object with implicitly respect any selections and queries you have on that layer, but if you pass it the raw data source, they won't respect those selections and queries.

It's good practice to be very clear what you're passing into the Cursor. I usually do this by extracting the selection set from the layer and populating the "where_clause" argument of the initializer with a SQL statement "OBJECTID IN (<selection_set>)" as this lets me more easily debug what the Cursor is seeing.

Re: How to use SearchCursor accurately

RPGIS — Wed, 23 Jul 2025 13:21:09 GMT

I agree with both @TonyAlmeida and@HaydenWelch. Using the 'with' statement is general best practice when it comes to using any cursor. Like @HaydenWelch said, the 'with' statement ensures the object deletes after the cursor finishes executing. The other thing you can include are break conditions to stop the execution earlier or pass conditions to skip over rows.

Re: How to use SearchCursor accurately

HaydenWelch — Wed, 23 Jul 2025 13:20:53 GMT

I'm gonna make a "Comprehending Cursors in Context" Blog post soon to try and document more advanced Cursor patterns. My favorite way to use a SearchCursor is in a comprehension because it immediately de-structures the data into whatever Python object you want and doesn't store a ref so it's deleted as soon as the comprehension runs.

Re: How to use SearchCursor accurately

RPGIS — Wed, 23 Jul 2025 14:51:02 GMT

I think that is a great idea. Let me know if you are open to making it a joint blog or allowing some input from others to add to it. I often use the comprehension to create dictionaries while simultaneously having another dictionary to field map different schemas on the fly. That significantly reduces the amount of effort when it comes to migrating data or running specific analyses.

Re: How to use SearchCursor accurately

HaydenWelch — Wed, 23 Jul 2025 15:10:33 GMT

I'll set up a repo on GitHub, DM me your username and I'll add you as a collaborator!

Re: Database is stale after deleted table data using Pro UI and fetching using SearchCursor

chris_del101 — Wed, 23 Jul 2025 18:06:41 GMT

Note: The syntax I used was for brevity. I'm using `with` already, so this is not the issue. Apologies for the extra noise. I think I'll change the post title since this is not really a SearchCursor issue (I think)

This real issue, I've confirmed, is that the database state is stale. It's not a SearchCursor specific issue but still...

1.In Pro I select all in the database table and click delete, and confirm.

2. Save(?). It saves something, I am prompted, but not sure what (is saved)

Here a loading bar comes up saying "saving" for a second (not shown). The table is empty.

FYI only, as closing like this refreshes the data: but I don't want to have to restart.

When closing instead I get two prompts

And then

Pro would now close - in theory. We are trying not to close it every time.

3. Run Query - Data is returned!

records = [] full_table_path = f"{self.sde_path}\\{table_name}" with arcpy.da.SearchCursor(full_table_path, field_names) as cursor: for row in cursor: records.append(row) return records

This is same item at the top of the list in the first screen shot.

('Total Sample Weight', 'Kg', 'Sample Weight Total: The total mass of the sample before processing.', '32', 32.0, '1111 ', 'gggg')

These inaccurate results should not happen. Is there anyway to make the empty state proliferate?

Re: Database is stale after deleted table data using Pro UI and fetching using SearchCursor

TonyAlmeida — Wed, 23 Jul 2025 18:09:59 GMT

Sound like you might need to start an edit session to save when you delete features before you use the search cursor?

# Start edit session edit = arcpy.da.Editor(self.sde_path) edit.startOperation() # Your operations here edit.stopOperation()

Re: Database is stale after deleted table data using Pro UI and fetching using SearchCursor

RPGIS — Wed, 23 Jul 2025 20:16:02 GMT

A search cursor only accesses the data and does not apply any edits. @TonyAlmeida would be correct under the conditions of using either DeleteCursor or UpdateCursor. If you are looking to get all unique values first and then delete any that are duplicates, then you would need to set your script to look like the following.

Feature = 'filepath of table or featurelass' Searching = arcpy.da.SearchCursor( Feature, ['CommonIdField'] ) Updating = arcpy.da.UpdateCursor( Feature, ['CommonIdField'] ) Deleting = arcpy.da.DeleteCursor( Feature, ['CommonIdField'] ) # List comprehension of valid ids ValidIDs = [ row[0] for row in Searching ] # Delete rows based on matching ids in the list above edit = arcpy.da.Editor(os.path.dirname(Feature )) with edit as e: with Deleting as cursor: for row in cursor: r = row[0] if ValidIDs.count(r) >=2: cursor.deleteRow(row) ValidIDs.remove(r)

This should at least help guide you in the right direction.

@TonyAlmeida or @HaydenWelch please correct me if the above script is incorrect. I have used this structure in previous scripts but it has been almost a year since I last used python.

Re: Database is stale after deleted table data using Pro UI and fetching using SearchCursor

chris_del101 — Wed, 23 Jul 2025 20:23:25 GMT

I think I've gotten off track here. My goal of searching for duplicates comes after the stale data problem is solved. Sorry if I am misunderstanding.

Lets say I want to get all the data in table for easiness sake.

This returns data but it should not, since the table is empty, or shows as empty. I need it to return no data when there is no data.

with arcpy.da.SearchCursor(full_table_path, field_names) as cursor: for row in cursor: print(row)

I am not making any edits at this time, except in the UI - deleting the data in the table by clicking the delete button (then saving). Then running this query.

The screen shots show what happens. Sorry if I'm not getting your meaning. Thanks for the responses.

Re: Database is stale after deleted table data using Pro UI and fetching using SearchCursor

RPGIS — Wed, 23 Jul 2025 20:55:18 GMT

Yes. If you have editor tracking on or any modifiable date field then all you need to search for are the dates that are closely associated with whatever timeframe you specify. Python is incredibly flexible in terms of dates. Like I also said earlier, within the cursor you can add a SQL query expression to quickly filter records. For example,

import arcpy # Define the feature class or table feature_class = "path_to_your_feature_class" # Define the SQL query (e.g., select features where 'Population' > 1000) sql_query = "Population > 1000" # Use a SearchCursor to iterate through the filtered records with arcpy.da.SearchCursor(feature_class, ["Name", "Population"], sql_query) as cursor: for row in cursor: print(f"Name: {row[0]}, Population: {row[1]}")

This code was autogenerated by Microsoft's CoPilot but it should give some ideas. The SQL query can filter for only records that meet that criteria.

Re: Database is stale after deleted table data using Pro UI and fetching using SearchCursor

chris_del101 — Wed, 23 Jul 2025 21:32:55 GMT

I don't see the connection between the responses and my original question, but thanks for responses nonetheless. Note: I did change the post title to better reflect the problem, i.e. it is not a cursor problem.

So this solves my problem:

Delete the table data in Pro UI.
Under the Table tab > Manage Edits > Save
Run a search of all rows
- No data is returned

Just saving with the regular save icon in the corner, or with ctrl-s, did not suffice. Why is there a second save action? No idea, but this is what is required.

Re: Database is stale after deleted table data using Pro UI and fetching using SearchCursor

HaydenWelch — Thu, 24 Jul 2025 04:55:04 GMT

You have the general idea correct, but you are still assigning reference to the cursors in the initialization that persist until the death of the scope.

Not a major issue for small functions, but since most people (incorrectly I believe) write their primary scripts in the global scope, those references will live until the interpreter dies (ArcPro closes)

The only way to "pre-initialize" a cursor is to store the arguments as a tuple and destructure them when you create the cursor:

cursor_args = ('featureclass', ['fields', ...], *args)

...

values = [id for id, in SearchCursor(*cursor_args)]

Sorry for the formatting, I'm on a phone right now and don't have access to a computer lol

Re: Database is stale after deleted table data using Pro UI and fetching using SearchCursor

HaydenWelch — Thu, 24 Jul 2025 05:00:28 GMT

There are 2 "things" in ArcPro. The Database and the project. Both of them are independent, but reliant on each other. By default, Ctrl+s in pro saves the aprx/project state (layers, selection, symbology, etc). You need to hit the "Save Edits" button under the edit tab to commit the staged changes to the database.

This makes sense when you think about how many different data sources you can have in a project. Saving a project is saving your active view of that data, but the data itself could be literally anything that supports SQL.

Re: Database is stale after deleted table data using Pro UI and fetching using SearchCursor

chris_del101 — Thu, 24 Jul 2025 14:06:12 GMT

After I figured out the saving problem in the UI, and I moved to actually checking for duplicates while actively inserting, this was the way to do it.

Re: How to use SearchCursor accurately

ShariF — Fri, 25 Jul 2025 14:01:53 GMT

I find cursors to be very confounding although I read they can be very powerful. Here's a use case. We have data we pull from a 3rd party app, do GIS magic, and put it back up into our geodatabase daily. We do this for both code violations and mapping certificates of occupancy for a local municipality. Currently the workflow using a python script is: pull data from a 3rd party app, do GIS magic, delete all (rows) of data in geodatabase (this can be over 20K records at a time) and then append the new data, even though we KNOW some of that data hasn't changed. It would be great (and more efficient and better coding) to use an update cursor to update the values that have changed and then an insert cursor for those items that are new.

Re: Database is stale after deleted table data using Pro UI and fetching using SearchCursor

RPGIS — Fri, 25 Jul 2025 14:48:54 GMT

Just out of curiosity, is your project setup to automatically allow edits to happen when the project is opened up and feature or table attributes can be updated? This could be causing your issue if the data source is something other than a feature class or table in either an sde or gdb.

The other thing you mentioned is that you are working with a file table. If you could please explain that further then it might be easier for anyone or any of us to help you with your given situation?

It sounds like your file table might be either an excel spreadsheet or some other table. If that is the case have you tried importing the table and see if the results are the same. You can use python to read other tables like excel but trying to run any edits on those tables or pull information from them could result in less than desirable behaviors. I have run into similar situations using external tables and usually importing them and then running python code on that table usually fixes the issue.

It is often best to run python as a standalone script using the modules that are designed to read those file types instead of bringing them into pro. @HaydenWelch may be able to affirm this or have a different take than mine.

Re: How to use SearchCursor accurately

HaydenWelch — Fri, 25 Jul 2025 17:05:43 GMT

Here's a pretty lightweight implementation of a TableUpdater class that you could use. It leverages the 'EDITED@' token to compare update timestamps and then applies updates to existing rows with an update cursor and inserts rows that don't map to the target.

from datetime import datetime from arcpy import ( Exists, Describe, Geometry, ) from typing import ( Generator, Any, ) from arcpy.da import ( Editor, SearchCursor, UpdateCursor, InsertCursor, ) import arcpy.typing.describe as typedesc from dataclasses import dataclass @dataclass class Changes: updates: list[int] inserts: list[int] deletes: list[int] class TableUpdater: Row = tuple[int, Geometry, datetime] # Type first 3 values of Row records def __init__(self, source_table: str, target_table: str) -> None: if not Exists(source_table) and Exists(target_table): raise ValueError('Invalid table paths!') self.target_table = target_table self.target_desc: typedesc.FeatureClass = Describe(target_table) self.source_table = source_table self.source_desc: typedesc.FeatureClass = Describe(source_table) # Do some filering to make sure we can use OID@ and SHAPE@ for the table cursors # By default a cursor will return SHAPE@XY self.source_shape_field = self.source_desc.shapeFieldName self.source_oid_field = self.source_desc.OIDFieldName self.source_fields = [field.name for field in self.source_desc.fields] self.target_shape_field = self.target_desc.shapeFieldName self.target_oid_field = self.target_desc.OIDFieldName self.target_fields = [field.name for field in self.target_desc.fields] self.target_editor = Editor(self.target_desc.workspace.catalogPath) self.fields = ['OID@', 'SHAPE@', 'EDITED@'] + [ field for field in set(self.source_fields) | set(self.target_fields) if field not in [self.source_oid_field, self.target_oid_field, self.source_shape_field, self.target_shape_field] ] self._table_diff = None @property def source_state(self): return self._gather_rows(self.source_table) @property def target_state(self): return self._gather_rows(self.target_table) @property def table_diff(self): if not self._table_diff: self._table_diff = self._get_table_diff() return self._table_diff def _gather_rows(self, table: str) -> dict[int, str]: """Gather all rows and their last updated times Parameters: table (str): Path to the table that needs updating Returns: ( dict[int, str] ): Mapping of OID to updated_at string """ return {oid: updated_at for oid, updated_at in SearchCursor(table, ['OID@', 'EDITED@'])} def _get_table_diff(self) -> Changes: """Filter out records from the new_table that are unchanged from the old_table Returns: a list of OIDs in the new_table that need to be """ old_rows = self.target_state new_rows = self.source_state return Changes( updates=list(oid for oid in new_rows if oid in old_rows and old_rows[oid] < new_rows[oid]), inserts=list(new_rows.keys() - old_rows.keys()), deletes=list(old_rows.keys() - new_rows.keys()), ) # Change Detection methods def _inserts(self) -> list[tuple[Any, ...]] | None: inserts = self.table_diff.inserts if inserts: if len(inserts) == 1: where_clause = f"{self.source_oid_field} = {inserts[0]}" else: where_clause = f"{self.source_oid_field} IN ({','.join(map(str, inserts))})" return [row for row in SearchCursor(self.source_table, self.fields, where_clause=where_clause)] def _updates(self) -> dict[int, tuple[Any, ...]] | None: updates = self.table_diff.updates if updates: if len(updates) == 1: where_clause=f"{self.source_oid_field} = ({updates[0]})" else: where_clause=f"{self.source_oid_field} IN ({','.join(map(str, updates))})" return { row[0]: row for row in SearchCursor(self.source_table, self.fields, where_clause=where_clause) } def _deletes(self) -> list[int] | None: return self.table_diff.deletes or None def apply_changes(self) -> dict[str, list[int]]: """Applies all changed rows to target dataset Returns: ( dict[str, list[int]] ): A dictionary with 'updates' and 'insert' keys that have a list of oids updated or inserted """ changes: dict[str, list[int]] = { 'updates': [], 'inserts': [], 'deletes': [], } if ( updates := self._updates() ): if len(updates) == 1: oid = list(updates.keys())[0] where_clause = f"{self.target_oid_field} = {oid}" else: where_clause = f"{self.target_oid_field} IN ({','.join(map(str, updates.keys()))})" with self.target_editor, UpdateCursor(self.target_table, self.fields, where_clause=where_clause) as cur: changes['updates'] = [cur.updateRow(updates[oid]) or int(oid) for oid, *_ in cur] if ( inserts := self._inserts() ): with self.target_editor, InsertCursor(self.target_table, self.fields) as cur: changes['inserts'] = [cur.insertRow(row) for row in inserts] if ( deletes := self._deletes() ): if len(deletes) == 1: oid = deletes[0] where_clause = f"{self.target_oid_field} = {oid}" else: where_clause = f"{self.target_oid_field} IN ({','.join(map(str, deletes))})" with self.target_editor, UpdateCursor(self.target_table, self.fields, where_clause=where_clause) as cur: changes['deletes'] = [cur.deleteRow() or oid for oid, *_ in cur] return changes def main(): from pprint import pprint source = r'source_table' target = r'target_table' table_updater = TableUpdater(source, target) changes = table_updater.apply_changes() print(f"Updated {len(changes['updates'])} rows") print(f"Inserted {len(changes['inserts'])} rows") print(f"Deleted {len(changes['deletes'])} rows") pprint(changes) if __name__ == '__main__': main()

You can use that as a rough base for what you're describing. I tried to use some advanced techniques in there so you can see the creative ways to use a cursor.

Re: How to use SearchCursor accurately

ShariF — Fri, 25 Jul 2025 16:51:04 GMT

😮wow. Thank you. That looks daunting but I'll see if I can figure it out. I hope to see your blog about cursors.