mysql添加相同的数据报错_python mysql插入数据报错：TypeError: %d format: a number is required, not str...

(0)目录

1：起因

最近工作需求 ---- 实时统计一份数据，insert到mysql数据库中；

方法：很自然的就想到了python插入数据库，yum install MySQL-python.x86_64àimport MySQLdb(python2.X仅仅适用)

报错如下

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

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

解决方案：Theformat string is not really a normal Python format string. Youmust always use %s for all fields.也就是MySQLdb的字符串格式化不是标准的python的字符串格式化,应当一直使用%s用于字符串格式化

2，具体细节

(1)MySQLdb python包安装：

[@10.134.105.160 auto_tarh]#yum list | grep -i sql   ----MySQL-python.x86_64                    1.2.3-0.3.c1.1.el6   @rhel-6.stable-rhel-x86_64-server-6

-----yum install MySQL-python.x86_64 -->import MySQLdb(python2.X仅仅适用)

(2) mysql数据库安装

[@10.134.105.160 auto_tarh]#yum list installed | grep sql    ----检查安装列表

mysql.x86_64                   5.1.61-1.el6_2.1        @rhel-6.2-rhel-x86_64-server-6    ----- mysql –h192.168.0.1 –uusername -ppassword

mysql-devel.x86_64             5.1.61-1.el6_2.1        @rhel-6.2-rhel-x86_64-server-6

mysql-libs.x86_64              5.1.61-1.el6_2.1        @rhel-x86_64-server-6/6.2

php-mysql.x86_64               5.3.3-3.el6_2.8         @rhel-6.2-rhel-x86_64-server-6

----- yum installmysql.x86_64, 安装mysql命令

3，开始书写代码

(1)python insert代码

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)

)

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

(3)遇到的错误：

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

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

解决方案:Theformat string is not really a normal Python format string. Youmust always use %s for all fields. 也就是MySQLdb的字符串格式化不是标准的python的字符串格式化,

应当一直使用%s用于字符串格式化

(4)同时还遇到一个奇葩的错误：

_mysql_exceptions.ProgrammingError: (1064, "You havean error in your SQ L syntax; check the manual that

corresponds to your MySQLserver version for the right syntax to use near ')' at line 1")

1#coding=utf-8

2

3 import sys

4 import traceback

5 import MySQLdb

6

7 conn= MySQLdb.connect(

8 host='res.searchapp.rds.sogou',

9 port = 3306,

10 user='searchapp',

11 passwd='searchappres',

12 db ='searchapp',

13 )

14 cur = conn.cursor()

15 def test():

16 #插入一条数据

17 #sqli="""insert into novel_card values('test3', 100, 1, 0.01, '20170707')"""

18 #sqli="""insert into novel_card values('%s', %d, 1, 0.01, '20170707')"""

19

20 #cur.execute(sqli)

21 #sqli="""insert into novel_card values('%s', '%s', '%s', '%s', '%s')"""

22 #sqli="""insert into novel_card (runoob_author, show, click, ctr, data_time) values('%s', %s, %s, %s, '%s')"""

23 sqli="insert into novel_card values(%s, %s, %s, %s, %s)"

24 print sqli %('t4','1','2','0.1','20170707')

25 cur.execute(sqli, ('t5','1','2','0.1','20170707'))

26 #cur.execute(sqli,('test5', '100', '1', '0.01', '20170707'))

27 #sqli="insert into novel_card values('%s','%s')"

28 #cur.execute(sqli,('test3', '20170707'))

48 test()

49 cur.close()

50 conn.commit()

51 conn.close()