topic Re: Error updating a database with da.UpdateCursor in Python Questions

Error updating a database with da.UpdateCursor

MatthewKramer — Thu, 29 Aug 2019 13:56:28 GMT

I have been trying to run a loop on a table in a database and then want to change one field in the table so the next time this runs, it doesn't repeat actions. I am experiencing problems updating the table. Here is the code where I think the problems are occurring:

# import xlrd - used to help read excel files
import arcpy
import os
from arcpy import env

TemplateLocation = r"\\MKRAMER\Share\Fig1AutomationProject\Required Files"
ProjectLocation = r"\\MKRAMER\Share\Fig1AutomationProject"
OutputLocation = ProjectLocation + "\\Output Files"

projTable = r'C:\Users\mkramer\AppData\Roaming\ESRI\Desktop10.7\ArcCatalog\Connection to ACCTG-SQL.sde\ProjectLocations.dbo.SiteMapList'
#projTable.definitionQuery = "\"WBS1\" = '185-202' AND \"WBS2\" = ''"

try:
#sets data pulled from the database to variables

# Changing the aspects of the environment to allow overrides
arcpy.env.workspace = projTable
arcpy.env.overwriteOutput = True

# starts an edit session to change values
edit = arcpy.da.Editor(os.path.dirname(projTable))
edit.startEditing(False,True)

edit.startOperation()
with arcpy.da.UpdateCursor(projTable, ['WBS1','WBS2','Longitude','Latitude','ProjName','ClientName','City','map_created'],"\"map_created\" = '0'") as cursor:

print "Start Loop"

for row in cursor:

and then I get the error when I try to update the row at the bottom of the for loop here:

      arcpy.RefreshTOC()
      arcpy.RefreshActiveView()
      mxd.saveACopy(ProjectLocation + "\\" + projectNumber.replace("-","") + "_SiteLocationMap.mxd")
      arcpy.mapping.ExportToPDF(mxd, ProjectLocation + "\\" + projectNumber.replace("-","") + "_SiteLocationMap.pdf", image_quality = "NORMAL")
      row[7] = 1
      cursor.updateRow(row)
      del mxd, df, insetDF
   print "End Loop"

edit.stopOperation()
edit.stopEditing(true)

Here is the error I am throwing:

Traceback (most recent call last):
File "//MKRAMER/Share/Fig1AutomationProject/AutomationScript.py", line 164, in <module>
cursor.updateRow(row)
SystemError: error return without exception set

I apologize for any formatting issues, first time poster here. If there is anything I left out that could be of help, just let me know. Thank you for any help!

Re: Error updating a database with da.UpdateCursor

RichardFairhurst — Thu, 29 Aug 2019 14:37:20 GMT

Your code is both using the mxd variable and deleting the mxd variable within the for loop. That will cause an error if the steps in the loop are executed more than one time. Repeating steps more than once is kind of the point of using a for loop, so make sure you only delete variables within a for loop that are created inside the for loop, otherwise you should dedent the del statement to remove it from the for loop and only execute it once after the for loop has been completed.

On the surface, everything seems to be OK as far as declaring the cursor, updating the 7th field of the current row and updating the row using the cursor. That assumes that the map_created field is a numeric field and not a string field or some other type of field. However, since you are performing this edit within an Editor session I assume the data is versioned. Does this data use any other advanced settings that require an Editor session, such as it's a geometric network or anything else?

Re: Error updating a database with da.UpdateCursor

MatthewKramer — Thu, 29 Aug 2019 14:50:02 GMT

I am creating a standard map from a template and saving a copy and exporting the map at the end of each loop. I want to keep the template map the same so I have a fresh map each iteration. I didn't include the declaration in the first picture but I initialize the mxd variable in the first line of the for loop. The data is a SQL-Server and the map_created field is a short integer. I am not sure if the data is versioned but the code does not run without the edit commands in there either.

Re: Error updating a database with da.UpdateCursor

MatthewKramer — Thu, 29 Aug 2019 15:09:53 GMT

I figured it out, I believe since the database I am working with isn't a geodatabase I had to use ArcSDESQLExecute to reconnect and make my own update query to make the change. Code to do that below:

SQLConnection = arcpy.ArcSDESQLExecute(r'C:\Users\mkramer\AppData\Roaming\ESRI\Desktop10.7\ArcCatalog\Connection to ACCTG-SQL.sde')
SQLQuery = SQLConnection.execute("update ProjectLocations.dbo.SiteMapList set map_created=1 where WBS1='{0}'".format(projectNumber))

Thanks!

Re: Error updating a database with da.UpdateCursor

RichardFairhurst — Thu, 29 Aug 2019 15:30:56 GMT

That is interesting that a da cursor wouldn't work with your SQL-Server set up, since I have used da cursors with my SQL-Server set up, but I assume there are a variety of ways to configure SQL-Server and that your set up and mine could be different is some significant way. Anyway, I am glad there is support for controlling SQL-Server using commands that are more native to that database that are working for you. It did seem like the SDE component was the likely culprit behind your problem.