给Python中的MySQLdb模块添加超时功能的教程
Jun 10, 2016 pm 03:13 PM使用Python操作MySQL数据库的时候常使用MySQLdb这个模块。
今天在开发的过程发现MySQLdb.connect有些参数没法设置。通过这个页面我们可以看到在connect的时候,可以设置的option和client_flags和MySQL c api相比差不少。
一个很重要的参数 MYSQL_OPT_READ_TIMEOUT没法设置,这个参数如果不设置,极致状况MySQL处于hang住,自动切换IP漂移,客户端无法重连到新MySQL。
给MySQLdb加Option很简单,只要修改_mysql.c这个把Python对象映射到MySQL操作的文件,添加参数,再加一段mysql_option即可。
下面是修改后的git diff 文件
?View Code BASH diff --git a/_mysql.c b/_mysql.c index d42cc54..61a9b34 100644 --- a/_mysql.c +++ b/_mysql.c @@ -489,9 +489,10 @@ _mysql_ConnectionObject_Initialize( "named_pipe", "init_command", "read_default_file", "read_default_group", "client_flag", "ssl", - "local_infile", + "local_infile", "read_timeout", NULL } ; int connect_timeout = 0; + int read_timeout = 0; int compress = -1, named_pipe = -1, local_infile = -1; char *init_command=NULL, *read_default_file=NULL, @@ -500,7 +501,7 @@ _mysql_ConnectionObject_Initialize( self->converter = NULL; self->open = 0; check_server_init(-1); - if (!PyArg_ParseTupleAndKeywords(args, kwargs, "|ssssisOiiisssiOi:connect", + if (!PyArg_ParseTupleAndKeywords(args, kwargs, "|ssssisOiiisssiOii:connect", kwlist, &host, &user, &passwd, &db, &port, &unix_socket, &conv, @@ -509,7 +510,8 @@ _mysql_ConnectionObject_Initialize( &init_command, &read_default_file, &read_default_group, &client_flag, &ssl, - &local_infile /* DO NOT PATCH FOR RECONNECT, IDIOTS + &local_infile, &read_timeout + /* DO NOT PATCH FOR RECONNECT, IDIOTS IF YOU DO THIS, I WILL NOT SUPPORT YOUR PACKAGES. */ )) return -1; @@ -540,6 +542,12 @@ _mysql_ConnectionObject_Initialize( mysql_options(&(self->connection), MYSQL_OPT_CONNECT_TIMEOUT, (char *)&timeout); } + + if (read_timeout) { + unsigned int timeout = read_timeout; + mysql_options(&(self->connection), MYSQL_OPT_READ_TIMEOUT, (char *)&timeout); + } + if (compress != -1) { mysql_options(&(self->connection), MYSQL_OPT_COMPRESS, 0); client_flag |= CLIENT_COMPRESS;
代码修改完毕,python setup.py install 即可,如果出现mysql_config找不到的问题。你还要修改setup_posix.py文件。
hoterran@hoterran-laptop:~/Projects/MySQL-python-1.2.3$ git diff setup_posix.py diff --git a/setup_posix.py b/setup_posix.py index 86432f5..f4f08f1 100644 --- a/setup_posix.py +++ b/setup_posix.py @@ -23,7 +23,7 @@ def mysql_config(what): if ret/256 > 1: raise EnvironmentError("%s not found" % (mysql_config.path,)) return data -mysql_config.path = "mysql_config" +mysql_config.path = "/usr/local/mysql/bin/mysql_config" def get_config(): import os, sys
编译通过,我们来试试添加的read_timeout这个参数。
conn = MySQLdb.connect(host = DB_SERVER,user = DB_USERNAME,passwd = DB_PASSWORD,db = DB_NAME, port=int(DB_PORT), client_flag = 2, read_timeout = 10)
然后执行语句前,你试着把mysql用gdb hang住10s后,python就会异常抛错
OperationalError: (2013, 'Lost connection to MySQL server during query') >/home/hoterran/Projects/dbaas/trunk/dbtest.py(18)() >mydb.execute_sql(conn, sql) (Pdb) --Return-- > /home/hoterran/Projects/dbaas/trunk/dbtest.py(18)()->None > mydb.execute_sql(conn, sql) (Pdb) OperationalError: (2013, 'Lost connection to MySQL server during query') > <string>(1)<module>()->None

Hot Article

Hot tools Tags

Hot Article

Hot Article Tags

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

What are the advantages and disadvantages of templating?

Google AI announces Gemini 1.5 Pro and Gemma 2 for developers

For only $250, Hugging Face's technical director teaches you how to fine-tune Llama 3 step by step

Share several .NET open source AI and LLM related project frameworks

A complete guide to golang function debugging and analysis
