topic Re: arcpy.da.SearchCursor returns no rows and InsertCursor returns error in Python Questions

arcpy.da.SearchCursor returns no rows and InsertCursor returns error

MaryDuBose — Wed, 05 Apr 2023 15:04:21 GMT

I am working on a Python script for ArcPro that is being run as a geoprocessing script tool, and running into 2 separate errors when I test the script tool. Background info: the tool is querying and modifying data stored in a MSSQL enterprise geodatabase. Each user is connected to the database using operating system authentication, and everyone is using ArcPro 2.8.

Error 1: In one part of the script, there is a SearchCursor that is supposed to return all rows from a particular layer that match a query. I have added messages to the script tool to help with debugging. When I have users test the tool, they get messages saying 0 rows were found. However, when they directly select rows from the layer using the same query, they do get results.

Error 2: Other times, I will get a different error - the SearchCursor will find what it's supposed to find, but then the script will fail in a different section where it's using an InsertCursor to add new rows to a table. The error message is "workspace already in transaction mode". I read the Esri help article on how to troubleshoot this, and have already changed all the cursors in my script to be declared directly and then deleted after use, rather than using a with statement, as suggested. All users have an Advanced license so that is not the issue. The final suggestion is to set both the with_undo and the multiuser_mode parameters to False in the startEditing() function. However, I need multiuser_mode to be True since I need for multiple users to be able to use this tool.

See below the relevant sections of code. I would appreciate any suggestions on how to troubleshoot either or both of these errors.

First, the relevant sections of main()... The script begins by checking the parameters entered by the user. Error 1 happens when running the check_parameters function, which then calls the get_tech_list function (which is where the SearchCursor that's causing the problems is located).

user = os.environ['USERNAME'] arcpy.env.workspace = fr"S:\Tech\{user}\Database_Connections\PMLGW_GSR.sde" edit = arcpy.da.Editor(fr"S:\Tech\{user}\Database_Connections\PMLGW_GSR.sde") edit.startEditing(False, True) edit.startOperation() check_parameters(tech, qc_week_dt, tier) p = arcpy.mp.ArcGISProject("CURRENT") m = p.listMaps()[0] lineLyr = m.listLayers("GSR Line")[0] qc1Lyr = m.listTables("*QC_T1")[0] qc2Lyr = m.listTables("*QC_T2")[0] route_ids = select_edits(tech, qc_week_dt, lineLyr) if len(route_ids) > 0: arcpy.AddMessage("Edits found for this week, beginning sample...") else: arcpy.AddWarning("No edits found for this week.") if tier == "1": arcpy.AddMessage("Adding Tier 1 Iteration...") tier_1_sample(route_ids, tech, qc_week_dt, qc1Lyr) else: arcpy.AddMessage("Adding Tier 2 Iteration...") tier_2_sample(route_ids, tech, qc_week_dt, qc2Lyr, qc1Lyr) edit.stopOperation() edit.stopEditing(True)

check_parameters() function:

def check_parameters(tech_name, week, tier_num): if week.weekday() != 0: arcpy.AddError("Please select a Monday as the QC Week.") raise arcpy.ExecuteError if tier_num not in ["1", "2"]: arcpy.AddError("Please enter 1 or 2.") raise arcpy.ExecuteError techs = get_tech_list(week) for tech in techs: arcpy.AddMessage("{t}".format(t=tech)) if tech_name.strip() not in techs: arcpy.AddError("Selected tech did not have work for this week") raise arcpy.ExecuteError arcpy.AddMessage("Parameters are valid, beginning execution...")

get_tech_list() function:

def get_tech_list(week): localtz = dateutil.tz.tzlocal() utc_offset = localtz.utcoffset(datetime.datetime.now(localtz)) past_week = week - datetime.timedelta(days=7) - utc_offset arcpy.AddMessage("Beginning of week to check: {w1}".format(w1=past_week.strftime(DATE_QUERY_FORMAT))) cur_week = week - utc_offset arcpy.AddMessage("End of week to check: {w2}".format(w2=cur_week.strftime(DATE_QUERY_FORMAT))) query = """last_edited_date >= '{d}' AND last_edited_date < '{d2}'""".format(d=past_week.strftime(DATE_QUERY_FORMAT), d2=cur_week.strftime(DATE_QUERY_FORMAT)) techs = defaultdict(set) p = arcpy.mp.ArcGISProject("CURRENT") arcpy.AddMessage("ArcPro Project Name: {pr}".format(pr=p)) m = p.listMaps()[0] arcpy.AddMessage("Map to check for GSR Line layer: {mp}".format(mp=m)) lyr = m.listLayers("GSR Line")[0] arcpy.AddMessage("Searching GSR Line Layer: {l}".format(l=lyr)) rowcount = 0 scur = arcpy.da.SearchCursor(lyr, ('last_edited_user', 'ROUTE_ID'), query) for row in scur: techs[row[0]].add(row[1]) rowcount += 1 del scur num_techs = str(len(techs)) arcpy.AddMessage("There were {r} lines edited this week.".format(r=rowcount)) arcpy.AddMessage("There were {n} techs with work for this week.".format(n=num_techs)) return techs

The error here is that rowcount is not incremented and remains at 0, suggesting that the SearchCursor is not iterating through any rows.

Error 2 (workspace already in transaction mode) happens in the tier_1_sample() function, on line 14 (using the InsertCursor to insert a row):

def tier_1_sample(rids, tech_name, week, qc_table): samples = sample(rids, int(ceil(len(rids) * 0.1))) num_samples = str(len(samples)) arcpy.AddMessage("Selected {n} lines to check".format(n=num_samples)) max_iter = get_highest_iteration(qc_table, tech_name, week) new_iter = str(int(max_iter)+1) arcpy.AddMessage("Adding Iteration {i}".format(i=new_iter)) icur = arcpy.da.InsertCursor(qc_table, ('ROUTE_ID', 'TECH', 'ITERATION', 'QC_STATUS', 'QC_WEEK')) for rid in samples: icur.insertRow((rid, tech_name, new_iter, 'Not Checked', "{m}/{d}/{y}".format(m=week.month, d=week.day, y=week.year))) del icur

Re: arcpy.da.SearchCursor returns no rows and InsertCursor returns error

VinceAngelo — Wed, 05 Apr 2023 15:13:22 GMT

You really need to `print` your SQL and try running it in a SQL browser, since it's probably not doing the right type casting (and therefore not returning rows).

- V

Re: arcpy.da.SearchCursor returns no rows and InsertCursor returns error

MaryDuBose — Wed, 05 Apr 2023 15:56:03 GMT

Thank you for the reply Vince!

After adding a line to the script to have it print the SQL query, and testing that query, I can confirm that it's being formatted correctly.

The weird thing is that after making that change (all I did is add one line to print the message), that part of the script now works correctly for all 3 users that tested it.

However, the "workspace already in transaction mode" error is still occurring.

Re: arcpy.da.SearchCursor returns no rows and InsertCursor returns error

Anonymous User — Wed, 05 Apr 2023 16:26:21 GMT

You might not need an Edit session since 'workspace already in transaction mode'. Try commenting out the Edit session lines and see if it works.

Re: arcpy.da.SearchCursor returns no rows and InsertCursor returns error

MaryDuBose — Wed, 05 Apr 2023 17:00:01 GMT

Hey Jeff, I tried commenting out all the Edit session lines, and now all users who test the script tool are experiencing the first error where the SearchCursor isn't iterating through any rows.

Re: arcpy.da.SearchCursor returns no rows and InsertCursor returns error

Anonymous User — Wed, 05 Apr 2023 17:46:46 GMT

Instead of referencing the layer from the current map project, could you 'hard code' the path to the search cursor?

lyr = fr'{arcpy.env.workspace}\db.sde.GSR_line'

Since you are using 3, take advantage of the f decorator to format your strings and make your code more concise.

query = f"""last_edited_date >= '{past_week.strftime(DATE_QUERY_FORMAT)}' AND last_edited_date < '{cur_week.strftime(DATE_QUERY_FORMAT)}'"""

Re: arcpy.da.SearchCursor returns no rows and InsertCursor returns error

MaryDuBose — Wed, 05 Apr 2023 18:52:40 GMT

Interesting, when I hardcode that path, I get:

RuntimeError: cannot open 'S:\Tech\medubose\Database_Connections\PMLGW_GSR.sde\GSR_digitize\GSR Line'

I tried uncommenting the edit operation lines, and still get this same RuntimeError.

Re: arcpy.da.SearchCursor returns no rows and InsertCursor returns error

Anonymous User — Wed, 05 Apr 2023 19:30:11 GMT

That path doesn't look right... Can you 'copy path' of the layer from the catalog? It should look something like this, with database.owner.dataset at the end..

Featureclasses can't have spaces in the name either-

<path to connection file>\Mast@NWPQL.sde\GSR_digitize.DBO.GSR_Line

Just an FYI, you can get the relative path to the users local connections by something like this:

con = os.path.join(os.getenv('APPDATA'), r'Esri\ArcGISPro\Favorites\connections\confile.sde')

Re: arcpy.da.SearchCursor returns no rows and InsertCursor returns error

MaryDuBose — Wed, 05 Apr 2023 20:54:50 GMT

Thank you Jeff! I think I was confusing myself with trying to access the feature classes through the ArcPro project/map interface.

Fixing the paths with the actual catalog paths seems to have fixed the issues. I'm going to have other users test as well tomorrow, but I'm hopeful that I now have a working version of the script!

And thanks for pointing out the f decorators, I updated my code and it's a lot cleaner now.

Re: arcpy.da.SearchCursor returns no rows and InsertCursor returns error

MaryDuBose — Fri, 07 Apr 2023 16:07:29 GMT

After having other users test the script tool, I confirmed that Jeff's solution of hardcoding the paths fixes the errors and everyone is able to run the tool without errors!

Thanks for all the help, this has been an ArcPro learning experience.