Syntax error for parameterized query with pyodbc

Question

I'm new to Python (used to C#) and need to use an Access database (.accdb) with it.

The syntax for building SQL Queries is also a bit odd for me.

I have the following:

def updateSQL(table,keyField,keyVal,field,newVal):
 sqlCommand = "UPDATE " + table + " SET (?)=(?) WHERE (?)=(?);"
 crsr.execute(sqlCommand, (field, newVal, keyField, keyVal))
 crsr.commit()
 print("Tables update successfully")

But for some reason I'm getting the following error:

[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement

I've tried a few different things with the statement and I can't for the life of me work out where it's going wrong, any ideas?

Answer 1

The '?' markers are for values, but the column name is not a value. You never want to put values into your SQL, but you will need put the columns. Try something like:

sql = 'update {} set {}=? where {}=?'.format(table, field, keyField)
cursor.execute(sql, newVal, keyVal)

• Gord Thompson: For protection against "unfortunate" table/column names, [{}] would be preferable to plain old {}.