在使用python 对wordpress tag 进行细化代码处理时，遇到了调用mysqldb模块时的出错，由于错误提示和问题原因相差甚远，查看了n久代码也未发现代码有问题。后来问了下师傅，被告知mysqldb里有一个断接的坑 ，需要进行数据库重连解决。
一、报错代码及提示
运行出错的代码如下：
import mysqldbdef 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 nonedef 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函数时，报错如下：
traceback (most recent call last): file a.py, line 40, in 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相关的变量
mysql> show global variables like %timeout%;
+----------------------------+-------+| 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测试，如果捕获异常，就再进行重连，修改后的代码为：
#!/usr/bin/python#coding=utf-8import mysqldbdef 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 nonedef 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模块的代码
import mysqldbdef 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 nonedef 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 ',tagidspostid = '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的代码
#!/usr/bin/python#coding=utf-8import torndbdef 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 termiddef 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_iddef 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 ',tagidspostid = '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 关闭后再连接
使用示例：
mysql> create table `ceshi` (`id` int(1) null auto_increment ,`num` int(1) null ,primary key (`id`));
>>> 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_id11>>> args1 = [('2'),('3'),('4')]>>> get1 = db.executemany(insert ceshi(num) values(%s), args1)>>> print get12>>> rows = db.iter(select * from ceshi)>>> for i in rows:… print i
3.报错
在使用过程中可能遇到的错误：
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精简过了。具体各个方法的传参方法如下（注意参数个数）：
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)