給Python中的MySQLdb模塊添加超時功能的教程

 　　這篇文章主要介紹了給Python中的MySQLdb模塊添加超時功能的教程,timeout功能在服務器的運維當中非常有用,需要的朋友可以參考下

　　使用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 文件

　　?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 ?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文件。

　　?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 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這個參數。

　　?

1 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就會異常拋錯

　　?

1 2 3 4 5 6 7 8 9 10 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