Why does pyodbc sometimes yield a SQL "Arithmetic overflow error" related to nvarchar when-CodePudding

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.

CodePudding user response：

The issue is that you are using ? substitution for the column name. The database adapter will quote the value as if it were a data column, so the expression becomes UPDATE Media SET (string)=(integer), and I'm guessing the parser tries to coerce the integer to a string to compare it.

Don't use ? substitution for column and table names.