2

I have a table with fields

TABLE_PASTE(
      user_text longtext NOT NULL,
      number integer NOT NULL
 )

I am trying to insert a row in this table TABLE_PASTE from python using MySQLDb driver.

text="large_text"
value = 1
cursor.execute("Update TABLE_PASTE set user_text = ? where number = ?",(text,value))

Am getting this error

query = query % db.literal(args)
TypeError: not all arguments converted during string formatting
FastTurtle
  • 2,301
  • 19
  • 19
user1159517
  • 5,390
  • 8
  • 30
  • 47

2 Answers2

11

With MySQLdb you want to use parameterized (prepared statements technically don't exist in python) statements similar to string formatting in Python 2.x.

cursor.execute("Update TABLE_PASTE set user_text = %s where number = %s",(text,value))

Note that even though your value is an int you still must use %s to format it in your query.

You can find a good tutorial on MySQLdb here.

FastTurtle
  • 2,301
  • 19
  • 19
  • 1
    how to avoid sql injection in the above example? – isti_spl Feb 14 '14 at 11:51
  • Doesn`t work. For variable text='abc' which is a string cursor.execute appends extra ' single quotes like cursor.execute("insert into table %s values %s", ('schema_name', 'ttt')) gives error Exception 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 ''source_schema_name' values 'ttt'' at line 1 – ramu Jul 22 '15 at 21:55
-7

simply you can just use

cursor.execute("Update TABLE_PASTE set user_text = {} where number = {}" .format(text,value))

where format function will format your input accordingly whether its an int or a string!

kleopatra
  • 51,061
  • 28
  • 99
  • 211
Kita
  • 1
  • 7
    You should not do this because it bypasses SQL escaping. – Chris Aug 08 '14 at 00:48
  • 2
    Direct string formatting of database statements with user input results in SQL injection vulnerabilities. The other answer here using parameterized queries should result in proper quoting to avoid SQL injection. – AaronM May 19 '15 at 17:37