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

[chris_del101]

`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?

[HaydenWelch]

This is gonna be used as a "bringing it all together" example lol. If you want a sneak peak, I'm putting a ton of examples and musings on GitHub here:

https://github.com/hwelch-fle/cursor-magic

[chris_del101]

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?

[TonyAlmeida]

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()

[chris_del101]

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. 

[RPGIS]

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.

[HaydenWelch]

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

[chris_del101]

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.

[RPGIS]

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.

[chris_del101]

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.

[HaydenWelch]

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.