Error Updating SQL Server Table using pyodbc

[LucasMurray2]

Hello,

A customer of mine has set up a SQL Server table with address, latitude, and longitude fields.  They've asked me to geocode the addresses and populate the latitude and longitude fields with the results.  This is a non-spatial table.  They plan to use the lat/long values in other, non-ESRI, applications.

I've already got a process that will read the addresses from the SQL Server table, geocode them, and save the results into a feature class within a file geodatabase.  I'm trying to build a python script that will update the SQL Server table with the results from the feature class. 

import pyodbc
import arcpy
fc = r'C:\FGDB.gdb\featureclass'
cnxn = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};'
 'Server=SQLServer;'
 'Database=Database;'
 'Trusted_connection=yes')
with arcpy.da.SearchCursor(fc,['FCID','GCAcc']) as curFC:
 curSQL=cnxn.cursor()
 curSQL.execute('UPDATE tabAddress SET Addr1_GCAcc = ? WHERE TableID = ?', curFC[1], curFC[0])
 curSQL.commit()
curSQL.close()‍‍‍‍‍‍‍‍‍‍‍‍

FYI, I changed the feature class and SQL server names in the above code.  However, I was able to successfully read the SQL server table using the pyodbc connection.

After I run the script, I get the following error message:

Traceback (most recent call last):
 File "C:\Users\myprofile\Desktop\testODBC\asdf.py", line 14, in <module>
 curSQL.execute('UPDATE tabAddress SET Addr1_GCAcc = ? WHERE TableID = ?', curFC[1], curFC[0])
SystemError: error return without exception set‍‍‍‍‍‍‍‍

FYI, FCID is a long integer field and GCAcc is a string field.  Could the FCID field in the execute statement not be formatting correctly?  I checked with the SQL Server admin and I'm supposed to have update capabilities.

Thank you,

Lucas Murray

[JoshuaBixby · Accepted solution]

In your code snippet, what do you think curFC[1] and curFC[0] are exactly?  You have created the cursor but you haven't started iterating through it yet, so trying to index it will generate an error:


>>> import arcpy
>>> fc =  # path to feature class or table
>>> cur = arcpy.da.UpdateCursor(fc, "*")
>>> cur[0]
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
SystemError: error return without exception set
>>> 
‍‍‍‍‍‍‍‍

[LucasMurray2]

Thanks for catching that.  Apparently I needed another cup of coffee that day.  Once I added the iteration,everything worked.  Thank you, Joshua.