CODEWITHSUNDEEP
pythonsqlcgi
Azhraam
Azhraam

Reputation: 77

SQL statement failing to execute in Python CGI

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!

Upvotes: 0

Views: 52

Answers (1)

JacobIRR
JacobIRR

Reputation: 8956

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;

Upvotes: 1

Related Questions