Error Updating SQL Server Table using pyodbc

LucasMurray2 — Fri, 10 Dec 2021 21:08:27 GMT

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

Re: Error Updating SQL Server Table using pyodbc

JoshuaBixby — Fri, 10 Dec 2021 21:08:30 GMT

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

Re: Error Updating SQL Server Table using pyodbc

LucasMurray2 — Wed, 23 Jan 2019 17:46:25 GMT

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

topic Re: Error Updating SQL Server Table using pyodbc in Python Questions

Error Updating SQL Server Table using pyodbc

Re: Error Updating SQL Server Table using pyodbc

Re: Error Updating SQL Server Table using pyodbc