64

I am trying to do the following insert operation:

cursor.execute("""
                    insert into tree (id,parent_id,level,description,code,start,end)
                    values (%d,%d,%d,%s,%s,%f,%f)
                    """, (1,1,1,'abc','def',1,1)
                    )

The structure of my MYSQL table is:

id int(255),
parent_id int(255),
level int(11),
description varchar(255),
code varchar(255),
start decimal(25,4),
end decimal(25,4)

However when I run my program, I get the error

" File "/usr/lib/pymodules/python2.6/MySQLdb/cursors.py", line 151, in execute query = query % db.literal(args)

TypeError: %d format: a number is required, not str"

Air
  • 7,120
  • 2
  • 47
  • 78
user721975
  • 1,115
  • 3
  • 12
  • 14

5 Answers5

162

The format string is not really a normal Python format string. You must always use %s for all fields.

refer official document:

If args is a list or tuple, %s can be used as a placeholder in the query. If args is a dict, %(name)s can be used as a placeholder in the query.

-> that is: here %s is NOT formatter, but is a placeholder

crifan
  • 8,424
  • 1
  • 45
  • 35
Keith
  • 37,985
  • 10
  • 48
  • 67
  • 3
    Saved me from wasting several more hours !! Thank you very much ! Is there any other way to handle prepared statements? Because this really does not seem be a prepared statement so to speak. – user721975 Apr 26 '11 at 02:11
  • Use oursql. It can do real parameterization, instead of fake client-side one MySQLdb does. – nosklo Apr 26 '11 at 02:17
  • @nosklo : OK , will give it a try. Thanks. – user721975 Apr 26 '11 at 02:24
  • 7
    Darn. I wasted a solid three hours trying to understand why `%d` is causing error.. – SexyBeast May 24 '16 at 15:31
  • I encountered this problem too. 我刚才也遇到了这个问题,看到回答,我就知道怎么做了 – tiankonghewo Jun 08 '17 at 05:26
  • 2
    You must always use %s for all fields. 这句话最实用 – tiankonghewo Jun 08 '17 at 05:28
  • I had to convert decimal values e.g. 1.52456 to string str(value) and then I could use %s. %d did not work even if I converted value to decimal using decimal.Decimal(). Finally I got this working. – Strabek Jul 18 '17 at 11:04
  • 1
    Do you have a source for this? I trust you, and this solution worked, but I'd prefer to know an authoritative source and why they chose to mandate this. – Evan May 16 '18 at 04:11
  • 1
    @Evan The source code itself. It's just a custom string interpolation implementation. It resembles Python, but isn't the normal Python. This is done to escape the value to prevent SQL injection. – Keith May 18 '18 at 04:15
1

I got the same error, but it was for a different reason. The problem was that the string included a '%' and that confused Python:

TemplateStr = '[....] % discount rate  [....]  %s and %s
print TemplateStr % ('abc', 'def')

Python interpreted that "% discount" as "%d" and kept complaining because I was feeding it a string ('abc'), not a number.

It took me a long tome to figure it out!

(The solution is to type %% instead of %.)

Davide Andrea
  • 1,197
  • 1
  • 11
  • 35
0

try this :

cursor.execute("""
                    insert into tree (id,parent_id,level,description,code,start,end)
                    values (%s,%s,%s,'%s','%s',%s,%s)
                    """%(1,1,1,'abc','def',1,1)
                    )
Barranka
  • 18,876
  • 13
  • 56
  • 79
orain
  • 107
  • 5
0

Since his data is in integer or decimal format how can you use %s which is usually used for string format %d or %i should be used for integer or decimal values.

Roshan
  • 49
  • 8
0

Whenever you saw that type of errors, should use type() function for checking the types of values or variables...., and will see type is - 'str'. Means python takes all the values in strings (By default data type is string). That's why the error said %d is for numbers, Not for the strings. So consider %s in python for every types of field.

jkmali
  • 1
  • 2