Python sqlite3 OperationalError: near "?": syntax error

Question

Trying to let users update column values on existing records for a specific table named "Scenario." The record being updated is identified by an index column called "Scenario_Key", unique to each instance of this class. The code I already have produces a dictionary of key, value pairs where key is the name of the column being updated and value is the value being inserted into it. To update the sqlite database I'm trying the following:

cursor.execute("""UPDATE Scenario SET ?=? WHERE Scenario_Key=?;""", (key, new_val, self.scenario_key))

But when I try to execute by clicking the "Save and Close" button, I get the following:

Traceback (most recent call last):
File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/lib-tk/Tkinter.py", line 1536, in __call__
return self.func(*args)
File "/Users/xxx/Documents/Consulting/DCA/Damage Control Assistant/EditScenarioWindow.py", line 91, in <lambda>
SaveAndCloseButton = Button(ButtonFrame, text="Save and Close", command=lambda: self.SaveAndCloseWindow())
File "/Users/xxx/Documents/Consulting/DCA/Damage Control Assistant/EditScenarioWindow.py", line 119, in SaveAndCloseWindow
cursor.execute(cmd_string, (key, new_val, self.scenario_key))
OperationalError: near "?": syntax error

I've read over sqlite3.OperationalError: near "?": syntax error, but I'm trying to do a single sqlite query where all the variables have already been calculated, not get values from the database and build a query from there. I'm supplying the positional arguments as a tuple. So why doesn't sqlite3 like the query I'm submitting?

mechanical_meat · Accepted Answer · 2016-07-25 20:50:55Z

1

You cannot parametrize column names. While being cognisant of the possibility of SQL Injection attacks, you could instead do:

cursor.execute("""UPDATE Scenario 
                     SET {}=? 
                   WHERE Scenario_Key=?;""".format(key), 
               (new_val, self.scenario_key))

answered Jul 25, 2016 at 20:50

mechanical_meat

170k25 gold badges237 silver badges231 bronze badges

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

2 Comments

Jeff D. Over a year ago

Thanks for the quick response! This worked, and even better, I think I understand why. Thanks also for the warning. For a variety of reasons security and injection attacks aren't a major concern for this project, but definitely something to keep in mind for the future.

mechanical_meat Over a year ago

Cheers, mate. Happy coding to you!

Collectives™ on Stack Overflow

Python sqlite3 OperationalError: near "?": syntax error

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related