Passing variables (table name,data) into python mysqld query

Question

I am trying to pass the name of the table and information to be inserted into the table as parameters. The query I have come up with is below:

sql = """INSERT INTO `%s` (title,link,description) VALUES(%%s,%%s,%%s)""" % (feed,title_xml,link_xml,description_xml)
cursor.execute(sql)

However, I am getting the following error:

Traceback (most recent call last):
  File "slicer_1.py", line 41, in <module>
    sql = """INSERT INTO `%s` (title,link,description) VALUES(%%s,%%s,%%s)""" %(feed,title_xml,link_xml,description_xml)
TypeError: not all arguments converted during string formatting

What could be wrong here?

BrenBarn · Accepted Answer · 2013-10-13 19:55:29Z

1

You have double %% on the last three %s. This means that only the first %s will be substituted in the string substitution, which means you can only pass one value to be substituted. Perhaps you meant to pass the other three to the execute call to be handled by the SQL library's safe substitution? Either that or you need to make all four have just one % so they all get substituted in the first step. (I'm not sure if things like MySQLdb let you parameterize VALUES data.)

Edit: If you take the second suggestion, you probably want to do it like this:

"""INSERT INTO `%s` (title,link,description) VALUES("%s","%s","%s")""" % (feed,title_xml,link_xml,description_xml)

edited Oct 13, 2013 at 19:55

answered Oct 13, 2013 at 19:12

BrenBarn

253k39 gold badges421 silver badges392 bronze badges

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

4 Comments

user2876777 Over a year ago

Removed %% and made it % and the error I get now is----> Traceback (most recent call last): _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 'Phailin kills 23, mass evacuations save thousands,feedproxy.google.com/~r' at line 1")

BrenBarn Over a year ago

@user2876777: Probably you need to quote the values in VALUES.

user2876777 Over a year ago

code changed to """INSERT INTO %s (title,link,description) VALUES(%s,%s,%s)""" % (feed,title_xml,link_xml,description_xml) output is ---> _mysql_exceptions.OperationalError: (1054, "Unknown column 'Cyclone Phailin: India Meteorological Department wins battle over forecasts' in 'field list'")

user2876777 Over a year ago

Thank you that worked, however, I am able to pass only 2 variables, title and link. When I edit it to add the third variable, I get the following error --> 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 \'0" height="1" src="ndtv.com.feedsportal.com/c/33805/f/606695/s/327d6dc5/s\' at line 1')

Collectives™ on Stack Overflow

Passing variables (table name,data) into python mysqld query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related