在使用python 对wordpress tag 进行细化代码处理时，遇到了调用MySQLdb模块时的出错，由于错误提示和问题原因相差甚远，查看了N久代码也未发现代码有问题。后来问了下师傅，被告知MySQLdb里有一个断接的坑 ，需要进行数据库重连解决。

一、报错代码及提示

运行出错的代码如下：

 1import MySQLdb
 2def getTerm(db,tag):
 3        cursor = db.cursor()
 4        query = "SELECT term_id FROM wp_terms where name=%s "
 5        count = cursor.execute(query,tag)
 6        rows = cursor.fetchall()
 7        db.commit()
 8        #db.close()
 9        if count:
10                term_id = [int(rows[id][0]) for id in range(count)]
11                return term_id
12        else:return None
13def addTerm(db,tag):
14        cursor = db.cursor()
15        query = "INSERT into wp_terms (name,slug,term_group) values (%s,%s,0)"
16        data = (tag,tag)
17        cursor.execute(query,data)
18        db.commit()
19        term_id = cursor.lastrowid
20        sql = "INSERT into wp_term_taxonomy (term_id,taxonomy,description) values (%s,'post_tag',%s) "
21        value = (term_id,tag)
22        cursor.execute(sql,value)
23        db.commit()
24        db.close()
25        return int(term_id)
26dbconn = MySQLdb.connect(host='localhost', user='root', passwd='123456', db='361way', port=3306, charset='utf8', init_command='set names utf8')
27tags = ['mysql','1111','aaaa','bbbb','ccccc','php','abc','python','java']
28tagids = []
29for tag in tags:
30        termid = getTerm(dbconn,tag)
31        if termid:
32                print tag, 'tag id is ',termid
33                tagids.extend(termid)
34        else:
35                termid = addTerm(dbconn,tag)
36                print 'add tag',tag,'id is ' ,termid
37                tagids.append(termid)
38print 'tag id is ',tagids

直接可以执行，在第for循环里第二次调用getTerm函数时，报错如下：

1Traceback (most recent call last):
2  File "a.py", line 40, in <module>
3    termid = getTerm(dbconn,tag)
4  File "a.py", line 11, in getTerm
5    count = cursor.execute(query,tag)
6  File "/usr/lib64/python2.6/site-packages/MySQLdb/cursors.py", line 154, in execute
7    charset = db.character_set_name()
8_mysql_exceptions.InterfaceError: (0, '')

二、解决方法

初始时以为是编码问题了，又细核对了几遍未发现编码有问题，在python代码里也未发现异常。后来问过师傅后，师傅来了句提示：

只看代码有啥用，mysql 的超时时间调长点或捕获异常从连，原因是cursor. connection 没有关闭，但是socket已经断了cursor 这个行为不会再建立一次socket的重新执行一次MysqlDB.connect()

看的有点懵懂，先从mysql 里查看了所有timeout相关的变量

 1mysql> show GLOBAL VARIABLES like "%timeout%";
 2+----------------------------+-------+
 3| Variable_name              | Value |
 4+----------------------------+-------+
 5| connect_timeout            | 10    |
 6| delayed_insert_timeout     | 300   |
 7| innodb_lock_wait_timeout   | 50    |
 8| innodb_rollback_on_timeout | OFF   |
 9| interactive_timeout        | 28800 |
10| net_read_timeout           | 30    |
11| net_write_timeout          | 60    |
12| slave_net_timeout          | 3600  |
13| table_lock_wait_timeout    | 50    |
14| wait_timeout               | 28800 |
15+----------------------------+-------+
1610 rows in set (0.00 sec)

发现最小的超时时间是10s ，而我的程序执行起来显然就不了10s 。因为之前查过相关的报错，这里估计这个很可能是另外一个报错：2006，MySQL server has gone away 。即然和这个超时时间应该没关系，那就尝试通过MySQLdb ping测试，如果捕获异常，就再进行重连，修改后的代码为：

 1#!/usr/bin/python
 2#coding=utf-8
 3import MySQLdb
 4def getTerm(db,tag):
 5    cursor = db.cursor()
 6    query = "SELECT term_id FROM wp_terms where name=%s "
 7    count = cursor.execute(query,tag)
 8    rows = cursor.fetchall()
 9    db.commit()
10    #db.close()
11    if count:
12        term_id = [int(rows[id][0]) for id in range(count)]
13        print term_id
14        return term_id
15    else:return None
16def addTerm(db,tag):
17    cursor = db.cursor()
18    query = "INSERT into wp_terms (name,slug,term_group) values (%s,%s,0)"
19    data = (tag,tag)
20    cursor.execute(query,data)
21    db.commit()
22    term_id = cursor.lastrowid
23    sql = "INSERT into wp_term_taxonomy (term_id,taxonomy,description) values (%s,'post_tag',%s) "
24    value = (term_id,tag)
25    cursor.execute(sql,value)
26    db.commit()
27    db.close()
28    return int(term_id)
29dbconn = MySQLdb.connect(host='localhost', user='root', passwd='123456', db='361way', port=3306, charset='utf8', init_command='set names utf8')
30tags = ['mysql','1111','aaaa','bbbb','ccccc','php','abc','python','java']
31if __name__ == "__main__":
32    tagids = []
33    for tag in tags:
34        try:
35           dbconn.ping()
36        except:
37            print 'mysql connect have been close'
38           dbconn = MySQLdb.connect(host='localhost', user='root', passwd='123456', db='361way', port=3306, charset='utf8', init_command='set names utf8')
39        termid = getTerm(dbconn,tag)
40        if termid:
41            print tag, 'tag id is ',termid
42            tagids.extend(termid)
43        else:
44            termid = addTerm(dbconn,tag)
45            print 'add tag',tag,'id is ' ,termid
46            tagids.append(termid)
47    print 'All tags id is ',tagids

再执行发现竟然OK了，而细看下结果，发现基本上每1－2次getTerm或addTerm函数调用就会打印一次mysql connect have been close 。这里问题虽然已经解决，不过并未细挖到真正的根因。

三、建议

从网上要看的结果来看MySQLdb还有不支持长连接的坑，这个可以通过上面提到的修改my.cnf参数解决，也可以考虑使用其他支持设置timeout时间mysql模块。另外，也建议使用MySQLdb时，可以考虑使用一个对其二次封装的模块torndb —- A lightweight wrapper around MySQLdb 。

具体使用可以参看：http://tornado.readthedocs.org/en/branch2.4/database.html