在使用python 对wordpress tag 进行细化代码处理时,遇到了调用MySQLdb模块时的出错,由于错误提示和问题原因相差甚远,查看了N久代码也未发现代码有问题。后来问了下师傅,被告知MySQLdb里有一个断接的坑 ,需要进行数据库重连解决。
一、报错代码及提示
运行出错的代码如下:
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函数时,报错如下:
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相关的变量
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-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’ 。这里问题虽然已经解决,不过并未细挖到真正的根因。
三、建议
从网上要看的结果来看MySQLdb还有不支持长连接的坑,这个可以通过上面提到的修改my.cnf参数解决,也可以考虑使用其他支持设置timeout时间mysql模块。另外,也建议使用MySQLdb时,可以考虑使用一个对其二次封装的模块torndb —- A lightweight wrapper around MySQLdb 。
具体使用可以参看:http://tornado.readthedocs.org/en/branch2.4/database.html