Why is my SQL query not working?

Question

I'm posting this, and getting this error:

c.execute("""update resulttest set category=""" + key + ", value=""" + str(value))

OperationalError: (1054, "Unknown column 'composed' in 'field list'")

I am trying to update the columns category and field, composed is simply a value in category.

Why is this error coming up?

What have you tried? Have you looked at the actual query string you are composing? (Hint: it's not correct.) — jbindel, Jul 13 '12 at 23:19
Also, please don't do things this way. You open yourself to SQL injection problems. Use prepared statements instead. — jbindel, Jul 13 '12 at 23:29

score 3 · Answer 1 · edited May 23 '17 at 12:20

3

You can use interpolation and parameterized queries for easier to read syntax, no escaping quotes or anything.

c.execute("""update resulttest set category=?, value=?""", (key, value))

https://stackoverflow.com/a/775399/594589

edited May 23 '17 at 12:20

Community

1
1

answered Jul 13 '12 at 23:19

dm03514

54,664
18
108
145

GTSouza · Answer 2 · 2012-07-13T23:32:45.527

2

// **mysql based**
string cmd = "UPDATE resulttest SET category=\"" + key + "\", value=\"" + str(value) + "\"";

// **sql based**
string cmd = "UPDATE resulttest SET category='" + key + "', value='" + str(value) + "'";

// make sure you command output a escaped format
// UPDATE resulttest SET category='test', value='test'
// UPDATE resulttest SET category="test", value="test"

c.execute(cmd);

edited Jul 13 '12 at 23:32

answered Jul 13 '12 at 23:15

GTSouza

365
4
16

1

I think you are barking up the wrong tree. String literals must be enclosed in single quotes within SQL. – dbenham Jul 13 '12 at 23:24
@dbenham I have based on mysql, Why you just edit the answer to fix then? – GTSouza Jul 13 '12 at 23:30

score 2 · Answer 3 · answered Jul 13 '12 at 23:18

You might see something interesting if you generate the query string and then print it out before you try to execute it. Have you tried that?

I think your quotation marks are probably not done correctly. That is, perhaps you want the query to look like this:

update resulttest set category='somekey', value='composed'

I don't think that is what you'll get for the query string you are composing.

Why is my SQL query not working?

3 Answers3