Trouble with MySQL UPDATE syntax with the module mysqldb in Python

Question

I am attempting to execute the following query via the mysqldb module in python:

for i in self.p.parameter_type: cursor.execute("""UPDATE parameters SET %s = %s WHERE parameter_set_name = %s""" % (i, float(getattr(self.p, i)), self.list_box_parameter.GetStringSelection()))

I keep getting the error: "Unknown column 'M1' in 'where clause'". I want to update columns i with the value getattr(self.p, i), but only in rows that have the column parameter_set_name equal to self.list_box_parameter.GetStringSelection(). The error suggests that my query is looking for columns by the name 'M1' in the WHERE clause. Why is the above query incorrect and how can I correct it?

No, M1 was the value obtained from self.list_box_parameter.GetStringSelection() in the above example. Sorry, I could probably have written the question better. — curious
– curious, Commented Jan 31, 2010 at 8:58

rytis · Accepted Answer · 2010-01-31 09:00:50Z

1

i see now, i think you need to enclose parameter_set_name = %s in quotes such as:

parameter_set_name = "%s"

otherwise it's trying to acces column M1

so:

cursor.execute("""UPDATE parameters SET %s = %s WHERE parameter_set_name = \"%s\" """ % (i, float(getattr(self.p, i)), self.list_box_parameter.GetStringSelection()))

answered Jan 31, 2010 at 9:00

rytis

2,73123 silver badges31 bronze badges

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

2 Comments

curious Over a year ago

That worked!! Thank you :) Why do I need the quotes for the third case of %s but not the 2nd case?

rytis Over a year ago

i'm guessing mysql sees a float number which cannot be column name so just works out that it's a value rather than a name

burnall · Accepted Answer · 2010-01-31 08:57:49Z

0

It looks like query is formed with wrong syntax. Could you display string parameter of cursor.execute?

answered Jan 31, 2010 at 8:57

burnall

8405 silver badges10 bronze badges

3 Comments

curious Over a year ago

For example, putting in actual strings instead of %s, the above query would like like: UPDATE tablename SET columnname = "something" WHERE another_column_name = "something_else" The error is suggesting that my where clause is searching for the column "something else" rather than the column another_column_name with the value "something_else"

curious Over a year ago

I've tried the query with real strings in MySQL directly and that seemed to work. I believe the trouble is with the syntax around %s, or the way I am using cursor.execute ...

burnall Over a year ago

I mean that you should check EXACT (calculated) parameter of execute in debugger.

Collectives™ on Stack Overflow

Trouble with MySQL UPDATE syntax with the module mysqldb in Python

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related