CODEWITHSUNDEEP
pythonsqlms-accesspyodbc
ScottishTapWater
ScottishTapWater

Reputation: 4776

Syntax error for parameterized query with pyodbc

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?

Upvotes: 0

Views: 571

Answers (1)

mkleehammer
mkleehammer

Reputation: 386

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)

Upvotes: 3

Related Questions