SQLite3 UPDATE error in python

Question

I am getting an "Operator error near "?"" error when I run the following SQL statement;

    key = 'field_a'
    value = '01/01/2011'
    #self.testac = '010101010'

    self.qry.execute('''UPDATE data_base SET ?=? WHERE atnumber = ?''',(key, value, self.testac))
    self.qry.commit()

key and value are dynamically generated based on the field the user wants to edit which is why the SET statement has ?=?

Any ideas?

Thanks!

millimoose · Accepted Answer · 2011-11-15 02:54:37Z

4

At a guess, the ? syntax is only for parameter binding. That is, inserting the values of bound parameters converted appropriately formatted and escaped strings. It's not a general string substitution facility – just use regular string substitution for the column names.

For example, you'd use:

key = 'field_a'
value = '01/01/2011'
#self.testac = '010101010'

sql = '''UPDATE data_base SET %(key)s=? WHERE atnumber = ?''' % dict(key=key)
self.qry.execute(sql, (value, self.testac))

edited Nov 15, 2011 at 2:54

answered Nov 15, 2011 at 1:14

millimoose

40.1k11 gold badges89 silver badges138 bronze badges

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

3 Comments

Lance Collins Over a year ago

Can you mix '?' and string substitutions? Something like: ('''UPDATE data_base SET %s=? WHERE atnumber = ?''',(key,value,self.testac) or in this case would it be better practice to just use string formatting for all of the variables? Thanks!

millimoose Over a year ago

Python uses either printf-style syntax (using the % operator) or Windows-style extensible syntax (using the format() method) for string formatting – neither uses ? on its own as a special character. (They might or might not clash with SQLite query syntax, but not in your example.)

millimoose Over a year ago

@LanceCollins I updated the answer with an example of what I had in mind. I'm using printf-style formatting because I'm more used to it, but it's recommended to use new-style formatting in new code.

tacaswell · Accepted Answer · 2011-11-23 18:16:04Z

2

The ? syntax is only for binding value to protect against injection attacks (example).

If you are going to take user input for the left side of the = you have to make sure to sanitize it your self. To dynamically generate statements any python string method will work, but you really shouldn't do this.

A better way would be to have a dictionary of pairs {key:sql_str}

call_dict = {'col_name':'UPDATE data_base SET col_name=? WHERE atnumber=?'}
self.qry.execute(call_dict[key],(value,self.testac))

[edit, fixed typo]

edited Nov 23, 2011 at 18:16

answered Nov 15, 2011 at 23:45

tacaswell

88.2k23 gold badges222 silver badges203 bronze badges

Collectives™ on Stack Overflow

SQLite3 UPDATE error in python

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related