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?

mkleehammer · Accepted Answer · 2017-03-10 23:30:09Z

3

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)

answered Mar 10, 2017 at 23:30

mkleehammer

3862 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gord Thompson Over a year ago

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

Collectives™ on Stack Overflow

Syntax error for parameterized query with pyodbc

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related