to strip ' or " while using string in python

Question

I have column names in string , now to update table in mysql in the following code :

cursor.execute("""update websites SET %s = %s where weblink = %s""",(key,value,x))

gives error:

_mysql_exceptions.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 ''blog' = 1 where weblink = 'http://blogspot.com/'' at line 1")

key,value = 'blog',2

in cursor.execute key is string and sql table columns are without string , how to solve this problem

Traceback (most recent call last):
  File "pgrank.py", line 28, in <module>
    cursor.execute("""update websites SET %s = %s where weblink = %s""",(key,value,x))
  File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.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 '\'blog\'' = 1 where weblink = 'http://blogspot.com/' at line 1')

The title of your question has nothing to do with your question, or the problem you are having. — Inbar Rose
– Inbar Rose, Commented Apr 4, 2013 at 8:01
I couldnt understand this problem , is it because ' or something else change as you feel like — Harshit
– Harshit, Commented Apr 4, 2013 at 8:05
Please provide your actual code, as well as the actual error message you are getting with its trace. — Inbar Rose
– Inbar Rose, Commented Apr 4, 2013 at 8:11

glglgl · Accepted Answer · 2013-04-04 08:18:59Z

2

The "inherent" replacing works fine for data, but not for table names.

In SET %s = %s, the first %s gets replaced by 'blog' while it should be blog or even `blog`.

You should do

cursor.execute("""update websites SET `%s` = %%s where weblink = %%s""" % key, (value,x))

because these are two distinct technologies.

Better readability would be provided by

cursor.execute("update websites SET `" + key + 
    "` = %s where weblink = %s", (value,x))

and safety is increased if you check if key contains the ` character.

answered Apr 4, 2013 at 8:18

glglgl

91.5k13 gold badges157 silver badges230 bronze badges

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

Collectives™ on Stack Overflow

to strip ' or " while using string in python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related