Python MySQLdb issues (TypeError: %d format: a number is required, not str)
Solution 1
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
Solution 2
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 %.)
user721975
Updated on July 05, 2022Comments
-
user721975 almost 2 years
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"
-
user721975 about 13 yearsSaved 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.
-
nosklo about 13 yearsUse oursql. It can do real parameterization, instead of fake client-side one MySQLdb does.
-
user721975 about 13 years@nosklo : OK , will give it a try. Thanks.
-
SexyBeast about 8 yearsDarn. I wasted a solid three hours trying to understand why
%d
is causing error.. -
tiankonghewo about 7 yearsI encountered this problem too. 我刚才也遇到了这个问题,看到回答,我就知道怎么做了
-
tiankonghewo about 7 yearsYou must always use %s for all fields. 这句话最实用
-
Strabek almost 7 yearsI 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.
-
Evan about 6 yearsDo 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.
-
Keith about 6 years@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.