SQL statement failing to execute in Python CGI

Question

I have an update statement in my database that is not executing. As far as I am aware, it is syntactically correct. I used this app to verify the syntax. The except block is all that is being executed and I do not understand why.

Here is the code:

for res_posts in list_of_response_ids:
temp_str = res_posts[0] # first element of res_posts tuple is string
temp_str += ":" + str(output[0])
try:
    sql = "UPDATE POST SET res_post_id = %s WHERE post_id = %d;" % (str(temp_str), int(res_posts[1]))
    cursor.execute(sql)
except:
    print "uh oh"

I can post more code if this is not enough information.

EDIT: Following Jacob's advice, I used raise and got the following error:

Traceback (most recent call last):
  File "post.cgi", line 93, in <module>
    cursor.execute(sql)
  File "/usr/lib64/python2.6/site-packages/MySQLdb/cursors.py", line 173, in     execute
self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.6/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':37 WHERE post_id = 8' at line 1")

Thank you so much!

Printing a generic message in an except block requires throwing away the Exception details provided by python. I recommend adding raise on the line after your print statement so you can see the full stack trace. If you need help interpreting the meaning of this trace, paste it in here. — JacobIRR
– JacobIRR, Commented Apr 22, 2017 at 21:36
I've added the output to my post. Any idea what's wrong with the syntax? — Azhraam
– Azhraam, Commented Apr 22, 2017 at 21:47

JacobIRR · Accepted Answer · 2017-04-22 21:51:06Z

1

Based on your traceback, there is something wrong with the type of entry you are using for the res_post_id or post_id. Currently you are not passing a representation of the res_post_id string, but a literal string. If res_post_id is a string in your DB Schema, I would recommend using %r like so:

sql = "UPDATE POST SET res_post_id = %r WHERE post_id = %d;" % (str(temp_str), int(res_posts[1]))

This will properly quote your res_post_id value for insertion.

So your statement should change from this:

UPDATE POST SET res_post_id = :37 WHERE post_id = 8;

...to this:

UPDATE POST SET res_post_id = ':37' WHERE post_id = 8;

answered Apr 22, 2017 at 21:51

JacobIRR

9,0468 gold badges47 silver badges73 bronze badges

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

1 Comment

Azhraam Over a year ago

Wow! Didn't realize it had to be so specific! That fixed it, thank you so much!

Collectives™ on Stack Overflow

SQL statement failing to execute in Python CGI

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related