CODEWITHSUNDEEP
pythoninsertionmysql-python
user721975
user721975

Reputation: 1267

Python MySQLdb issues (TypeError: %d format: a number is required, not str)

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"

Upvotes: 76

Views: 113808

Answers (6)

Davide Andrea
Davide Andrea

Reputation: 1413

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 time to figure it out!

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

Upvotes: 5

Aminah Nuraini
Aminah Nuraini

Reputation: 19206

Based on this answer https://stackoverflow.com/a/46517683/1564659

I finally use %s even for floats and digits. It will be converted right away when inserted to the table and SQL detected the table data type

Upvotes: 3

Keith
Keith

Reputation: 43064

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

Upvotes: 183

jkmali
jkmali

Reputation: 1

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.

Upvotes: -1

Roshan
Roshan

Reputation: 77

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.

Upvotes: -1

orain
orain

Reputation: 107

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)
                    )

Upvotes: 0

Related Questions