Must declare the scalar variable “@Station” with pyodbc UPDATE query

Question

Station = SN[0]
Asset = AN[0]
y = ASD[0]
z = ACD[0]

cursor = conn.cursor()
cursor.execute('SELECT * FROM station')
cursor.execute('''
    UPDATE station
    SET ActualStartDate = y, ActualCompletionDate = z
    WHERE StationName = @Station and AssetNumber = @Asset
    ''')
conn.commit()

I am getting this error:

('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the scalar variable "@Station". (137) (SQLExecDirectW)')

I would like to get the values from 4 numpy arrays above, SN[0], AN[0], SCD[0] SSD[0] and use these values in the UPDATE Query.

Is it possible to execute this?

Not sure if it will help, but what happens if you change the variable "Station" to something else in case there's a conflict with sharing the table name? — JonTout

Gord Thompson Gord Thompson · Accepted Answer · 2021-08-13T12:03:17

As noted in the pyodbc documentation, your parameter placeholders must be question marks and you must pass the parameter values along with the SQL command text in the .execute() call:

sql = """\
UPDATE station
SET ActualStartDate = ?, ActualCompletionDate = ?
WHERE StationName = ? and AssetNumber = ?
"""
params = (y, z, Station, Asset)
cursor.execute(sql, params)

Must declare the scalar variable “@Station” with pyodbc UPDATE query

1 Answers