给Python中的MySQLdb模块添加超时功能的教程
使用Python操作MySQL数据库的时候常使用MySQLdb这个模块。
今天在开发的过程发现MySQLdb.connect有些参数没法设置。通过这个页面我们可以看到在connect的时候，可以设置的option和client_flags和MySQLcapi相比差不少。
一个很重要的参数MYSQL_OPT_READ_TIMEOUT没法设置，这个参数如果不设置，极致状况MySQL处于hang住，自动切换IP漂移，客户端无法重连到新MySQL。
给MySQLdb加Option很简单，只要修改_mysql.c这个把Python对象映射到MySQL操作的文件，添加参数，再加一段mysql_option即可。
下面是修改后的gitdiff文件
?ViewCodeBASH
diff--gita/_mysql.cb/_mysql.c
indexd42cc54..61a9b34100644
---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};
intconnect_timeout=0;
+intread_timeout=0;
intcompress=-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/*DONOTPATCHFORRECONNECT,IDIOTS
+&local_infile,&read_timeout
+/*DONOTPATCHFORRECONNECT,IDIOTS
IFYOUDOTHIS,IWILLNOTSUPPORTYOURPACKAGES.*/
))
return-1;
@@-540,6+542,12@@_mysql_ConnectionObject_Initialize(
mysql_options(&(self->connection),MYSQL_OPT_CONNECT_TIMEOUT,
(char*)&timeout);
}
+
+if(read_timeout){
+unsignedinttimeout=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;
代码修改完毕，pythonsetup.pyinstall即可，如果出现mysql_config找不到的问题。你还要修改setup_posix.py文件。
hoterran@hoterran-laptop:~/Projects/MySQL-python-1.2.3$gitdiffsetup_posix.py
diff--gita/setup_posix.pyb/setup_posix.py
index86432f5..f4f08f1100644
---a/setup_posix.py
+++b/setup_posix.py
@@-23,7+23,7@@defmysql_config(what):
ifret/256>1:
raiseEnvironmentError("%snotfound"%(mysql_config.path,))
returndata
-mysql_config.path="mysql_config"
+mysql_config.path="/usr/local/mysql/bin/mysql_config"
defget_config():
importos,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用gdbhang住10s后，python就会异常抛错
OperationalError:(2013,'LostconnectiontoMySQLserverduringquery') >/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,'LostconnectiontoMySQLserverduringquery') ><string>(1)<module>()->None