python MySQLdb 如何设置读超时read_timeout

红太狼 2021-11-16 14:22 735阅读 0赞

在python中，经常用到 `MySQLdb`操作MySQL数据库。  
在实现上，`MySQLdb`并不是纯python的，而是封装了MySQL C API库`_mysql`。

对于MySQLdb是否支持`read_timeout`，其[使用手册][Link 1]中对这个参数只字未提。所以，`read_timeout`是否真的可用，是存在疑惑的。stack overflow上面也有人问到同样的[问题][Link 2]。

接下来，我们从`MySQLdb`的源码库[MySQLdb-python github地址][MySQLdb-python github]开始，看下是否支持`read_timeout`。

# `MySQLdb`的源码 #

先看下代码库中的`HISTORY`[文件][Link 3]:

beta 4
    ======
    
    Added support for the MySQL read_timeout option. Contributed by
    Jean Schurger (jean@schurger.org).
    
    Added a workaround so that the MySQL character set utf8mb4 works with Python; utf8 is substituted
    on the Python side.

其中,已经明确提到，已经对参数`read_timeout`提供了支持。

再来看下，底层代码是如何实现的[\_mysql.c][mysql.c]:

/* According to https://dev.mysql.com/doc/refman/5.1/en/mysql-options.html The MYSQL_OPT_READ_TIMEOUT apear in the version 5.1.12 */
    #if MYSQL_VERSION_ID > 50112
    #define HAVE_MYSQL_OPT_TIMEOUTS 1
    #endif
    
    
    #ifdef HAVE_MYSQL_OPT_TIMEOUTS
    	if (read_timeout) { 
    		unsigned int timeout = read_timeout;
    		mysql_options(&(self->connection), MYSQL_OPT_READ_TIMEOUT,
    				(char *)&timeout);
    	}
    	if (write_timeout) { 
    		unsigned int timeout = write_timeout;
    		mysql_options(&(self->connection), MYSQL_OPT_WRITE_TIMEOUT,
    				(char *)&timeout);
    	}
    #endif

从代码中，可以看到，MySQL从5.1.12版本开始支持`read_timeout`.

`MySQLdb`在具体实现上，通过  
`mysql_options()`设置参数`MYSQL_OPT_READ_TIMEOUT`,来实现读超时。

关于`MYSQL_OPT_READ_TIMEOUT`和`MYSQL_OPT_WRITE_TIMEOUT`,可以参考MySQL官方文档说明  
[mysql\_options()][mysql_options]。

下面来看下`MySQLdb-python`中的`read_timeout`如何使用。

# `read_timeout`例子 #

下面例子中，设置`read_timeout`为5s, 并使sql语句执行超过5s。  
查看其执行结果。

import MySQLdb
    from datetime import datetime
    
    
    host = "127.0.0.1"
    port = 3306
    sql = "select sleep(10)"
    user = "root"
    passwd = "Aa123456"
    
    conn = MySQLdb.connect(host=host, port=port, user=user,passwd=passwd, connect_timeout=2, read_timeout=5, charset="utf8")
    cursor = conn.cursor()
    
    print("now:", datetime.now())
    
    try:
            cursor.execute(sql)
    except Exception as e:
            print("now:", datetime.now())
            print("except:", e)
            raise
    
    ret = cursor.fetchone()
    print("result:", ret)
    
    cursor.close()
    conn.close()
    print("end")

output:

now: 2019-07-28 15:57:40.424942
    now: 2019-07-28 15:57:45.425193
    except: (2013, 'Lost connection to MySQL server during query')
    Traceback (most recent call last):
      File "read_timeout.py", line 19, in <module>
        cursor.execute(sql)
      File "/Users/lanyang/workspace/orange-service/.venv/lib/python3.6/site-packages/MySQLdb/cursors.py", line 198, in execute
        res = self._query(query)
      File "/Users/lanyang/workspace/orange-service/.venv/lib/python3.6/site-packages/MySQLdb/cursors.py", line 304, in _query
        db.query(q)
      File "/Users/lanyang/workspace/orange-service/.venv/lib/python3.6/site-packages/MySQLdb/connections.py", line 217, in query
        _mysql.connection.query(self, query)
    MySQLdb._exceptions.OperationalError: (2013, 'Lost connection to MySQL server during query')

可以看到，当sql语句执行超过5s后，连接被断开。  
已经达到预期的效果。

# 参考 #

[Python 操作 MySQL 数据库][Python _ MySQL]

[MySQLdb-python安装][MySQLdb-python]

[MySQLdb-python使用文档][MySQLdb-python 1]

[MySQLdb-python github地址][MySQLdb-python github]

[MySQL C API][]

[Link 1]: http://mysql-python.sourceforge.net/MySQLdb.html#introduction
[Link 2]: https://stackoverflow.com/questions/17416961/mysqldb-read-timeout
[MySQLdb-python github]: https://github.com/farcepest/MySQLdb1
[Link 3]: https://github.com/farcepest/MySQLdb1/blob/master/HISTORY
[mysql.c]: https://github.com/farcepest/MySQLdb1/blob/master/_mysql.c
[mysql_options]: https://dev.mysql.com/doc/refman/5.7/en/mysql-options.html
[Python _ MySQL]: https://www.runoob.com/python/python-mysql.html
[MySQLdb-python]: https://pypi.org/project/MySQL-python/#files
[MySQLdb-python 1]: http://mysql-python.sourceforge.net/MySQLdb.html
[MySQL C API]: https://dev.mysql.com/doc/refman/5.7/en/c-api.html