在使用python 对wordpress tag 进行细化代码处理时,遇到了调用MySQLdb模块时的出错,由于错误提示和问题原因相差甚远,查看了N久代码也未发现代码有问题。后来问了下师傅,被告知MySQLdb里有一个断接的坑 ,需要进行数据库重连解决。
一、报错代码及提示
运行出错的代码如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
import MySQLdb
def getTerm(db,tag):
cursor = db.cursor()
query = "SELECT term_id FROM wp_terms where name=%s "
count = cursor.execute(query,tag)
rows = cursor.fetchall()
db.commit()
#db.close()
if count:
term_id = [ int (rows[ id ][ 0 ]) for id in range (count)]
return term_id
else : return None
def addTerm(db,tag):
cursor = db.cursor()
query = "INSERT into wp_terms (name,slug,term_group) values (%s,%s,0)"
data = (tag,tag)
cursor.execute(query,data)
db.commit()
term_id = cursor.lastrowid
sql = "INSERT into wp_term_taxonomy (term_id,taxonomy,description) values (%s,'post_tag',%s) "
value = (term_id,tag)
cursor.execute(sql,value)
db.commit()
db.close()
return int (term_id)
dbconn = MySQLdb.connect(host = 'localhost' , user = 'root' , passwd = '123456' , db = '361way' , port = 3306 , charset = 'utf8' , init_command = 'set names utf8' )
tags = [ 'mysql' , '1111' , 'aaaa' , 'bbbb' , 'ccccc' , 'php' , 'abc' , 'python' , 'java' ]
tagids = []
for tag in tags:
termid = getTerm(dbconn,tag)
if termid:
print tag, 'tag id is ' ,termid
tagids.extend(termid)
else :
termid = addTerm(dbconn,tag)
print 'add tag' ,tag, 'id is ' ,termid
tagids.append(termid)
print 'tag id is ' ,tagids
|
直接可以执行,在第for循环里第二次调用getTerm函数时,报错如下:
1
2
3
4
5
6
7
8
|
Traceback (most recent call last):
File "a.py", line 40, in <module>
termid = getTerm(dbconn,tag)
File "a.py", line 11, in getTerm
count = cursor.execute(query,tag)
File "/usr/lib64/python2.6/site-packages/MySQLdb/cursors.py", line 154, in execute
charset = db.character_set_name()
_mysql_exceptions.InterfaceError: (0, '')
|
二、解决方法
初始时以为是编码问题了,又细核对了几遍未发现编码有问题,在python代码里也未发现异常。后来问过师傅后,师傅来了句提示:
只看代码有啥用,mysql 的超时时间调长点或捕获异常从连,原因是
cursor. connection 没有关闭
但是socket已经断了
cursor 这个行为不会再建立一次socket的
重新执行一次MysqlDB.connect()
看的有点懵懂,先从mysql 里查看了所有timeout相关的变量
1
|
mysql> show GLOBAL VARIABLES like "%timeout%" ;
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| table_lock_wait_timeout | 50 |
| wait_timeout | 28800 |
+----------------------------+-------+
10 rows in set (0.00 sec)
|
发现最小的超时时间是10s ,而我的程序执行起来显然就不了10s 。因为之前查过相关的报错,这里估计这个很可能是另外一个报错:2006,MySQL server has gone away 。即然和这个超时时间应该没关系,那就尝试通过MySQLdb ping测试,如果捕获异常,就再进行重连,修改后的代码为:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
|
#!/usr/bin/python
#coding=utf-8
import MySQLdb
def getTerm(db,tag):
cursor = db.cursor()
query = "SELECT term_id FROM wp_terms where name=%s "
count = cursor.execute(query,tag)
rows = cursor.fetchall()
db.commit()
#db.close()
if count:
term_id = [ int (rows[ id ][ 0 ]) for id in range (count)]
print term_id
return term_id
else : return None
def addTerm(db,tag):
cursor = db.cursor()
query = "INSERT into wp_terms (name,slug,term_group) values (%s,%s,0)"
data = (tag,tag)
cursor.execute(query,data)
db.commit()
term_id = cursor.lastrowid
sql = "INSERT into wp_term_taxonomy (term_id,taxonomy,description) values (%s,'post_tag',%s) "
value = (term_id,tag)
cursor.execute(sql,value)
db.commit()
db.close()
return int (term_id)
dbconn = MySQLdb.connect(host = 'localhost' , user = 'root' , passwd = '123456' , db = '361way' , port = 3306 , charset = 'utf8' , init_command = 'set names utf8' )
tags = [ 'mysql' , '1111' , 'aaaa' , 'bbbb' , 'ccccc' , 'php' , 'abc' , 'python' , 'java' ]
if __name__ = = "__main__" :
tagids = []
for tag in tags:
try :
dbconn.ping()
except :
print 'mysql connect have been close'
dbconn = MySQLdb.connect(host = 'localhost' , user = 'root' , passwd = '123456' , db = '361way' , port = 3306 , charset = 'utf8' , init_command = 'set names utf8' )
termid = getTerm(dbconn,tag)
if termid:
print tag, 'tag id is ' ,termid
tagids.extend(termid)
else :
termid = addTerm(dbconn,tag)
print 'add tag' ,tag, 'id is ' ,termid
tagids.append(termid)
print 'All tags id is ' ,tagids
|
再执行发现竟然OK了,而细看下结果,发现基本上每1-2次getTerm或addTerm函数调用就会打印一次'mysql connect have been close' 。
三、使用torndb模块解决mysql断连问题
1.MySQLdb和torndb的代码样例对比
torndb是facebook开源的一个基于MySQLdb二次封装的一个mysql模块,新封装的这个模块比较小,是一个只有2百多行代码的py文件。虽然代码短,功能确相较MySQLdb简便不少,并且该模块由于增加了reconnect方法和max_idel_time参数,解决了mysql的断连问题。比较下使用原生MySQLdb模块和使用torndb模块的代码:
使用MySQLdb模块的代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
|
import MySQLdb
def getTerm(db,tag):
cursor = db.cursor()
query = "SELECT term_id FROM wp_terms where name=%s "
count = cursor.execute(query,tag)
rows = cursor.fetchall()
db.commit()
#db.close()
if count:
term_id = [ int (rows[ id ][ 0 ]) for id in range (count)]
return term_id
else : return None
def addTerm(db,tag):
cursor = db.cursor()
query = "INSERT into wp_terms (name,slug,term_group) values (%s,%s,0)"
data = (tag,tag)
cursor.execute(query,data)
db.commit()
term_id = cursor.lastrowid
sql = "INSERT into wp_term_taxonomy (term_id,taxonomy,description) values (%s,'post_tag',%s) "
value = (term_id,tag)
cursor.execute(sql,value)
db.commit()
db.close()
return int (term_id)
def addCTag(db,data):
cursor = db.cursor()
query = '''INSERT INTO `wp_term_relationships` (
`object_id` ,
`term_taxonomy_id`
)
VALUES (
%s, %s) '''
cursor.executemany(query,data)
db.commit()
db.close()
dbconn = MySQLdb.connect(host = 'localhost' , user = 'root' , passwd = '123456' , db = '361way' , port = 3306 , charset = 'utf8' , init_command = 'set names utf8' )
tags = [ 'mysql' , '1111' , 'aaaa' , 'bbbb' , 'ccccc' , 'php' , 'abc' , 'python' , 'java' ]
tagids = []
for tag in tags:
if termid:
try :
dbconn.ping()
except :
dbconn = MySQLdb.connect(host = 'localhost' , user = 'root' , passwd = '123456' , db = '361way' , port = 3306 , charset = 'utf8' , init_command = 'set names utf8' )
print tag, 'tag id is ' ,termid
termid = getTerm(dbconn,tag)
tagids.extend(termid)
else :
try :
dbconn.ping()
except :
dbconn = MySQLdb.connect(host = 'localhost' , user = 'root' , passwd = '123456' , db = '361way' , port = 3306 , charset = 'utf8' , init_command = 'set names utf8' )
termid = addTerm(dbconn,tag)
print 'add tag' ,tag, 'id is ' ,termid
tagids.append(termid)
print 'tag id is ' ,tagids
postid = '35'
tagids = list ( set (tagids))
ctagdata = []
for tagid in tagids:
ctagdata.append((postid,tagid))
try :
dbconn.ping()
except :
dbconn = MySQLdb.connect(host = 'localhost' , user = 'root' , passwd = '123456' , db = '361way' , port = 3306 , charset = 'utf8' , init_command = 'set names utf8' )
addCTag(dbconn,ctagdata)
|
使用torndb的代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
#!/usr/bin/python
#coding=utf-8
import torndb
def getTerm(db,tag):
query = "SELECT term_id FROM wp_terms where name=%s "
rows = db.query(query,tag)
termid = []
for row in rows:
termid.extend(row.values())
return termid
def addTerm(db,tag):
query = "INSERT into wp_terms (name,slug,term_group) values (%s,%s,0)"
term_id = db.execute_lastrowid(query,tag,tag)
sql = "INSERT into wp_term_taxonomy (term_id,taxonomy,description) values (%s,'post_tag',%s) "
db.execute(sql,term_id,tag)
return term_id
def addCTag(db,data):
query = "INSERT INTO wp_term_relationships (object_id,term_taxonomy_id) VALUES (%s, %s) "
db.executemany(query,data)
dbconn = torndb.Connection( 'localhost:3306' , '361way' ,user = 'root' ,password = '123456' )
tags = [ 'mysql' , '1111' , 'aaaa' , 'bbbb' , 'ccccc' , 'php' , 'abc' , 'python' , 'java' ]
tagids = []
for tag in tags:
termid = getTerm(dbconn,tag)
if termid:
print tag, 'tag id is ' ,termid
tagids.extend(termid)
else :
termid = addTerm(dbconn,tag)
print 'add tag' ,tag, 'id is ' ,termid
tagids.append(termid)
print 'All tags id is ' ,tagids
postid = '35'
tagids = list ( set (tagids))
ctagdata = []
for tagid in tagids:
ctagdata.append((postid,tagid))
addCTag(dbconn,ctagdata)
|
从两者的代码上来看,使用torndb模块和原生相比,发现可以省略如下两部分:
torndb模块不需要db.cursor进行处理,无不需要db.comment提交,torndb是自动提交的;
torndb不需要在每次调用时,进行db.ping()判断数据库socket连接是否断开,因为torndb增加了reconnect方法,支持自动重连。
2.torndb的方法
torndb提供的参数和方法有:
execute 执行语句不需要返回值的操作。
execute_lastrowid 执行后获得表id,一般用于插入后获取返回值。
executemany 可以执行批量插入。返回值为第一次请求的表id。
executemany_rowcount 批量执行。返回值为第一次请求的表id。
get 执行后获取一行数据,返回dict。
iter 执行查询后,返回迭代的字段和数据。
query 执行后获取多行数据,返回是List。
close 关闭
max_idle_time 最大连接时间
reconnect 关闭后再连接
使用示例:
1
|
mysql> CREATE TABLE `ceshi` (` id ` int(1) NULL AUTO_INCREMENT ,`num` int(1) NULL ,PRIMARY KEY (` id `));
|
1
2
3
4
5
6
7
8
9
10
11
12
|
>>> import torndb
>>> db = torndb.Connection( "127.0.0.1" , "数据库名" , "用户名" , "密码" , 24 * 3600 ) # 24*3600为超时时间
>>> get_id1 = db.execute_lastrowid( "insert ceshi(num) values('1')" )
>>> print get_id1
1
>>> args1 = [( '2' ),( '3' ),( '4' )]
>>> get1 = db.executemany( "insert ceshi(num) values(%s)" , args1)
>>> print get1
2
>>> rows = db. iter ( "select * from ceshi" )
>>> for i in rows:
… print i
|
在使用过程中可能遇到的错误:
1
2
3
4
5
6
7
|
File "/home/361way/database.py", line 145, in execute_lastrowid
self._execute(cursor, query, parameters)
File "/home/361way/database.py", line 207, in _execute
return cursor.execute(query, parameters)
File "/usr/lib/pymodules/python2.7/MySQLdb/cursors.py", line 159, in execute
query = query % db.literal(args)
TypeError: not enough arguments for format string
|
写上面的代码时,我刚开始还是试着使用MySQLdb模块的方式引用数据,结果发现报参数的错误 ,经查看代码发现 ,torndb在使用几个sql方法时较MySQLdb精简过了。具体各个方法的传参方法如下(注意参数个数):
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
close()
reconnect()
iter(query, *parameters, **kwparameters)
query(query, *parameters, **kwparameters)
get(query, *parameters, **kwparameters)
execute(query, *parameters, **kwparameters)
execute_lastrowid(query, *parameters, **kwparameters)
execute_rowcount(query, *parameters, **kwparameters)
executemany(query, parameters)
executemany_lastrowid(query, parameters)
executemany_rowcount(query, parameters)
update(query, *parameters, **kwparameters)
updatemany(query, parameters)
insert(query, *parameters, **kwparameters)
insertmany(query, parameters)
|