python – Why does pyodbc sometimes yield a SQL “Arithmetic overflow error” related to nvarchar when supplied with parameters for an UPDATE command?
I am using pyodbc to connect to a local MS SQL Server 2019 instance on Windows. There is a table in a database on that instance called ‘Media’, which has a column called ‘mediaId’, which has a bigint datatype. For reference, it is a column that is uniquely constrained, but is neither a primary nor a foreign key. I am trying to update specific records in this column with pyodbc.
For reference, the following code executes successfully in Python using pyodbc.
db = DB() # db contains pyodbc context
cursor = db.cursor
command = "UPDATE Media SET {}={} WHERE id=476506567332605952".format('mediaId', 476506524101914624)
cursor.execute(command)
What does not execute cleanly is the following snippet.
db = DB()
cursor = db.cursor
command = "UPDATE Media SET ?=? WHERE id=476506567332605952"
params = ('mediaId', 476506524101914624)
cursor.execute(command, params)
As far as I am aware, the parameters should be inserted where the question marks are in the same way as the statement above. However, this yields the following error message.
[22003] [Microsoft][ODBC Driver 17 for SQL Server][SQL
Server]Arithmetic overflow error converting expression to data type
nvarchar. (8115) (SQLExecDirectW); [22003] [Microsoft][ODBC Driver 17
for SQL Server][SQL Server]The statement has been terminated. (3621)
No nvarchar types are used in this table, and the integer that is to be set falls under the bigint size limit and under the int limit for a 64-bit system in Python.
I am at a loss as to why I might be getting this error.
Read more here: Source link