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