Error Updating SQL Server Table using pyodbc

781
2
Jump to solution
01-18-2019 01:21 PM
LucasMurray2
Occasional Contributor II

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

0 Kudos
1 Solution

Accepted Solutions
JoshuaBixby
MVP Esteemed Contributor

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

View solution in original post

0 Kudos
2 Replies
JoshuaBixby
MVP Esteemed Contributor

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

View solution in original post

0 Kudos
LucasMurray2
Occasional Contributor II

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

0 Kudos