Hello All,
I am switching over from the testing environment to the enterprise geodatabase, using the script template from Allison posted above and incorporated a few other items as well. When I run the script against the enterprise geodatabase I get the following error: "workspace already in transaction mode" in line 47. "for row in fcrows:"
I am wondering if the section Cursors and Locking of the following link has something to do with it: Accessing data using cursors—Help | ArcGIS for Desktop and how I would take care of such an issue. SQL 2008 R2 if that matters.
Here is my full code:
import arcpy
workspace = r'C:\GISScripts\GRSTasksConnection.sde'
fc = r'C:\GISScripts\GRSTasksConnection.sde\COMGIS2.GIS.GeoConcerns'
tbl = r'C:\GISScripts\GRSTasksConnection.sde\COMGIS2.GIS.GR_IDTable'
inc_type = 'GeoReporting'
with arcpy.da.SearchCursor(tbl, ['NEXTVAL', 'INTERVAL'], where_clause = """RPTTYPE = '{}'""".format(inc_type)) as tblrows:
for row in tblrows:
sequence_value = row[0]
interval_value = row[1]
edit = arcpy.da.Editor(workspace)
edit.startEditing(False, True)
edit.startOperation()
with arcpy.da.UpdateCursor(fc, 'ConcernID', where_clause="""ConcernID is null""") as fcrows:
for row in fcrows:
row[0] = "C16{:04d}".format(sequence_value)
fcrows.updateRow(row)
sequence_value += interval_value
with arcpy.da.UpdateCursor(tbl, 'NEXTVAL', where_clause="""RPTTYPE = '{}'""".format(inc_type)) as tblrows:
for row in tblrows:
row[0] = sequence_value
tblrows.updateRow(row)
pContact = "PrimaryContact"
contactInfo = "PrimaryInformation"
pDept = "PrimaryDepartment"
sql = """{} is NULL AND {} IS NULL AND {} IS NULL""".format(pContact, contactInfo, pDept)
options = [[1, 'Damaged or Missing Street Sign', 'Street Division', '715-486-2081', 'Street Division', 'GRS_StreetPD'], [1, 'Fire Hydrant Issue', 'Marshfield Utilities Water', '715-387-1195', 'Marshfield Utilities Water', 'GRS_Leaks'], [1, 'Icy Intersection or Road', 'Street Division', '715-486-2081', 'Street Division', 'GRS_StreetPD'], [1, 'Pothole', 'Street Division', '715-486-2081', 'Street Division', 'GRS_StreetPD'], [1, 'Public Parking Issue', 'Police Department', '715-384-3113', 'Police Department', 'GRS_Police'], [1, 'Public Vegetation Concern', 'Street Division', '715-486-2081', 'Street Division', 'GRS_Streets'], [1, 'Road Obstruction', 'Street Division', '715-486-2081', 'Street Division', 'GRS_StreetPD'], [1, 'Street Light Issue', 'Marshfield Utilities Electrical', '715-387-1195', 'Marshfield Utilities Electrical', 'GRS_MU_Electric'], [1, 'Street Parking Issue', 'Police Department', '715-384-3113', 'Police Department', 'GRS_Police'], [1, 'Traffic Issue', 'Engineering', '715-486-2034', 'Engineering', 'GRS_TrafficPD'], [1, 'Traffic Signal Issue', 'Engineering', '715-486-2034', 'Engineering', 'GRS_TrafSignPD'], [1, 'Vision Obstruction', 'Engineering', '715-486-2034', 'Engineering', 'GRS_EVegAndObstruct'], [1, 'Water Bubbling From Street', 'Marshfield Utilities Water', '715-387-1195', 'Marshfield Utilities Water', 'GRS_Leaks'], [1, 'Other', 'Street Division', '715-486-2081', 'Street Division', 'GRS_StreetPD'], [2, 'Cracked Sidewalks', 'Engineering', '715-486-2034', 'Engineering', 'GRS_Esidewalk'], [2, 'Curb Ramp Issue', 'Engineering', '715-486-2034', 'Engineering', 'GRS_ECurbRamp'], [2, 'Slope Issue', 'Engineering', '715-486-2034', 'Engineering', 'GRS_Esidewalk'], [2, 'Snow or Ice Covered Sidewalk', 'Street Division', '715-486-2081', 'Street Division', 'GRS_StreetPD'], [2, 'Tree or Brush Clearance Issue', 'Engineering', '715-486-2034', 'ngineering', 'GRS_EVegAndObstruct'], [2, 'Uneven Sidewalk', 'Engineering', '715-486-2034', 'Engineering', 'GRS_Engineering'], [2, 'Other', 'Engineering', '715-486-2034', 'Engineering', 'GRS_Engineering'], [5, 'Garbage or Recycling Pickup', 'Street Division', '715-486-2081', 'Street Division', 'GRS_Streets'], [5, 'Other', 'Street Division', '715-486-2081', 'Street Division', 'GRS_Streets'], [4, 'Commercial Signage Concern', 'Development Services', '715-486-2077', 'Development Services', 'GRS_Zoning'], [4, 'Temporary Signage Concern', 'Development Services', '715-486-2077', 'Development Services', 'GRS_Zoning'], [4, 'Other', 'Development Services', '715-486-2077', 'Development Services', 'GRS_Zoning'], [3, 'Abandoned or Junk Vehicle', 'Police Department', '715-384-3113', 'Police Department', 'GRS_Police'], [3, 'Building Maintenance Issue', 'Development Services', '715-486-2077', 'Development Services', 'GRS_BuildService'], [3, 'Fence Concern', 'Development Services', '715-486-2077', 'Development Services', 'GRS_Ordinance'], [3, 'Fire Code Violation', 'Fire Department', '715-384-3118', 'Fire Department', 'GRS_Fire'], [3, 'Outdoor Burning', 'Fire Department', '715-384-3118', 'Fire Department', 'GRS_Fire'], [3, 'Land Use Concern', 'Development Services', '715-486-2077', 'Development Services', 'GRS_Zoning'], [3, 'Property Maintenance Concern', 'Police Department', '715-384-3113', 'Police Department', 'GRS_Ordinance'], [3, 'Setback or Property Concern', 'Development Services', '715-486-2077', 'Development Services', 'GRS_Ordinance'], [3, 'Weeds or Overgrown Vegetation', 'Street Division', '715-486-2081', 'Street Division', 'GRS_Streets'], [3, 'Other', 'Development Services', '715-486-2077', 'Development Services', 'GRS_Ordinance'], [6, 'Park Concern', 'Parks and Recreation', '715-384-4642', 'Parks and Recreation', 'GRS_Parks'], [6, 'Shelter Concern', 'Parks and Recreation', '715-384-4642', 'Parks and Recreation', 'GRS_Parks'], [6, 'Trail Concern', 'Parks and Recreation', '715-384-4642', 'Parks and Recreation', 'GRS_Parks'], [6, 'Zoo Concern', 'Parks and Recreation', '715-384-4642', 'Parks and Recreation', 'GRS_Parks'], [9, 'Electrical Service Issue', 'Marshfield Utilities Electrical', '715-387-1195', 'Marshfield Utilities Electrical', 'GRS_ElectricPD'], [9, 'Water Issue', 'Marshfield Utilities Water', '715-387-1195', 'Marshfield Utilities Water', 'GRS_Leaks'], [7, 'Dumping into a Ditch', 'Street Division', '715-486-2081', 'Street Division', 'GRS_StreetPD'], [7, 'Dumping into a Sewer', 'Street Division', '715-486-2081', 'Street Division', 'GRS_StreetPD'], [7, 'Dumping into a Stream or Pond', 'Street Division', '715-486-2081', 'Street Division', 'GRS_StreetPD'], [7, 'Other', 'Street Division', '715-486-2081', 'Street Division', 'GRS_StreetPD'], [8, 'Culvert Replacement', 'Engineering', '715-486-2034', 'Engineering', 'GRS_EDrainage'], [8, 'Ditch Flow Issue', 'Engineering', '715-486-2034', 'Engineering', 'GRS_EDrainage'], [8, 'Ponding Water', 'Engineering', '715-486-2034', 'Engineering', 'GRS_EDrainage'], [8, 'Other', 'Engineering', '715-486-2034', 'Engineering', 'GRS_Engineering']]
with arcpy.da.UpdateCursor(fc, ['GeneralConcern', 'SpecificConcern', 'PrimaryContact', 'PrimaryInformation', 'PrimaryDepartment', 'PrimaryEmailGroup'] , where_clause = sql) as cursor:
for row in cursor:
for option in options:
if option[0] == row[0] and option[1] == row[1]:
row[2] = option[2]
row[3] = option[3]
row[4] = option[4]
row[5] = option[5]
if not row[2] and not row[3] and not row[4] and not row[5]:
print 'No Match'
cursor.updateRow(row)
reassignYes = "ReassignedTo"
reassignemail = "ReassignedEmailSent"
sql = """{} IS NOT NULL OR {} = 1""".format(reassignYes, reassignemail)
reassignOptions = [[51, 'Development Services', 'Development Services', '715-486-2077', 'GRS_Zoning', 1], [52, 'Engineering', 'Engineering', '715-486-2034', 'GRS_ETrafficSignal', 1], [53, 'Fire Department', 'Fire Department', '715-384-3118', 'GRS_Fire', 1], [54, 'Marshfield Utilities Electrical', 'Marshfield Utilities Electrical', '715-387-1195', 'GRS_MU_Electric', 1], [55, 'Marshfield Utilities Water', 'Marshfield Utilities Water', '715-387-1195', 'GRS_MU_Water', 1], [56, 'Parks and Recreation', 'Parks and Recreation', '715-384-4642', 'GRS_Parks', 1], [57, 'Police Department', 'Police Department', '715-384-3113', 'GRS_Police', 1], [58, 'Street Division', 'Street Division','715-486-2081', 'GRS_Streets', 1]]
with arcpy.da.UpdateCursor(fc, ['ReassignedTo', 'PrimaryDepartment', 'PrimaryContact', 'PrimaryInformation',"PrimaryEmailGroup","ReassignedEmailSent"] , where_clause = sql) as reassign:
for row in reassign:
for option in reassignOptions:
if option[0] == row[0]:
row[1] = option[1]
row[2] = option[2]
row[3] = option[3]
row[4] = option[4]
row[5] = option[5]
if not row[2] and not row[3] and not row[4] and not row[5]:
print 'No Match'
reassign.updateRow(row)
edit.stopOperation()
edit.stopEditing(True)