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
