Python中MySQLdb和torndb模块对MySQL的断连问题处理

在使用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' 。

三、使用torndb模块解决mysql断连问题
1.MySQLdb和torndb的代码样例对比
torndb是facebook开源的一个基于MySQLdb二次封装的一个mysql模块，新封装的这个模块比较小，是一个只有2百多行代码的py文件。虽然代码短，功能确相较MySQLdb简便不少，并且该模块由于增加了reconnect方法和max_idel_time参数，解决了mysql的断连问题。比较下使用原生MySQLdb模块和使用torndb模块的代码：
使用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)
    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的代码

    #!/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 关闭后再连接
使用示例：

    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_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

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)