Select to view content in your preferred language

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

1484
9
Jump to solution
04-05-2023 08:04 AM
MaryDuBose
New Contributor III

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

 

 

0 Kudos
1 Solution

Accepted Solutions
by Anonymous User
Not applicable

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

 

View solution in original post

9 Replies
VinceAngelo
Esri Esteemed Contributor

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

0 Kudos
MaryDuBose
New Contributor III

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.

0 Kudos
by Anonymous User
Not applicable

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.

0 Kudos
MaryDuBose
New Contributor III

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. 

0 Kudos
by Anonymous User
Not applicable

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

like

 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)}'"""

 

0 Kudos
MaryDuBose
New Contributor III

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.

0 Kudos
by Anonymous User
Not applicable

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

 

MaryDuBose
New Contributor III

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.

0 Kudos
MaryDuBose
New Contributor III

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.

0 Kudos