如何检查MySQL连接是否在Python中打开?
问题描述:
我正在使用MySQLdb(http://mysql-python.sourceforge.net/)。看来connection.open和connection.sqlstate()不适合我。下面是代码:如何检查MySQL连接是否在Python中打开?
def open(self):
#TODO: check the connection's status
# self.__conn.open OR self.__conn.sqlstate()
try:
print "sqlstate:"+str(self.__conn.sqlstate())
print "open?"+str(self.__conn.open)
return "00000" == self.__conn.sqlstate()
except Exception as e:
print "Exception while checking MYSQL Connection:"+str(e)
return False
但是,当我跑了 “sudo的服务mysql的停止;睡眠60; sudo的服务mysql的开始;”做测试。输出如下。看起来,以后的一段输出重复了(我终于杀死了这个过程)。当服务器关闭时,connection.open为1,connection.sqlstate()为00000.但是,当服务器启动时,connection.executemany()仍会引发异常。有任何想法吗?谢谢。
...
2015-10-20 14:09:06 Exception while executing statement:(2006, 'MySQL server has gone away')
2015-10-20 14:09:06 sqlstate:00000
2015-10-20 14:09:06 open?1
2015-10-20 14:09:06 Reconnected to MYSQL.
2015-10-20 14:09:06 Exception while executing statement:(2006, 'MySQL server has gone away')
2015-10-20 14:09:06 sqlstate:00000
2015-10-20 14:09:06 open?1
2015-10-20 14:09:06 Reconnected to MYSQL.
2015-10-20 14:09:06 Exception while executing statement:(2006, 'MySQL server has gone away')
2015-10-20 14:09:06 sqlstate:00000
2015-10-20 14:09:06 open?1
2015-10-20 14:09:06 Reconnected to MYSQL.
2015-10-20 14:09:06 Exception while executing statement:(2006, 'MySQL server has gone away')
2015-10-20 14:09:06 sqlstate:00000
2015-10-20 14:09:06 open?1
...
UPDATE
我再次测试。输出如下。每次睡眠都是10秒。输出正常,但即使服务器关闭,connection.open也是1。但connection.sqlstate()是正确的(HY000)。
2015-10-20 14:35:56 Exception while executing statement:(2006, 'MySQL server has gone away')
2015-10-20 14:35:56 Exception while ping:(2003, "Can't connect to MySQL server on '10.1.1.25' (111)")
2015-10-20 14:35:56 sqlstate:HY000
2015-10-20 14:35:56 open?1
2015-10-20 14:35:56 sleeping...
2015-10-20 14:36:06 Exception while ping:(2003, "Can't connect to MySQL server on '10.1.1.25' (111)")
2015-10-20 14:36:06 sqlstate:HY000
2015-10-20 14:36:06 open?1
2015-10-20 14:36:06 sleeping...
2015-10-20 14:36:16 Exception while ping:(2003, "Can't connect to MySQL server on '10.1.1.25' (111)")
2015-10-20 14:36:16 sqlstate:HY000
2015-10-20 14:36:16 open?1
2015-10-20 14:36:16 sleeping...
2015-10-20 14:36:26 Exception while ping:(2003, "Can't connect to MySQL server on '10.1.1.25' (111)")
2015-10-20 14:36:26 sqlstate:HY000
2015-10-20 14:36:26 open?1
2015-10-20 14:36:26 sleeping...
2015-10-20 14:36:36 Exception while ping:(2003, "Can't connect to MySQL server on '10.1.1.25' (111)")
2015-10-20 14:36:36 sqlstate:HY000
2015-10-20 14:36:36 open?1
2015-10-20 14:36:36 sleeping...
2015-10-20 14:36:46 Exception while ping:(2003, "Can't connect to MySQL server on '10.1.1.25' (111)")
2015-10-20 14:36:46 sqlstate:HY000
2015-10-20 14:36:46 open?1
2015-10-20 14:36:46 sleeping...
2015-10-20 14:36:56 Exception while ping:(2003, "Can't connect to MySQL server on '10.1.1.25' (111)")
2015-10-20 14:36:56 sqlstate:HY000
2015-10-20 14:36:56 open?1
2015-10-20 14:36:56 sleeping...
2015-10-20 14:37:06 sqlstate:00000
2015-10-20 14:37:06 open?1
2015-10-20 14:37:06 Reconnected to MYSQL.
答
尝试这个 -
import MySQLdb
def main():
# Connect to the MySQL database
db = MySQLdb.connect(host = 'z.cs.utexas.edu', user = 'userName', passwd = 'password', db = 'dbName')
# Check if connection was successful
if (db):
# Carry out normal procedure
print "Connection successful"
else:
# Terminate
print "Connection unsuccessful"
答
我一直在寻找这种解决方案一段时间,我无法找到一个完美的解决方案。
似乎没有一个这样做的直接方式。如果您尝试执行查询,则只会发现连接已关闭。
我最终会做类似这样的回答了一句: How to check the connection alive in python?
答
你应该这样的代码: 在每前执行,平了MySQL服务器,例如
import MySQLdb as db
class DB(object):
def __init__(self):
try:
self.conn =mdb.connect(host='***',port=3306,user='',passwd='')
if (self.conn):
INFO_LOG("DB init success")
else:
INFO_LOG("DB init fail")
self.conn.autocommit(True)
self.conn.select_db(DB_NAME)
self.cursor = self.conn.cursor()
except Exception as e:
CRITICAL_LOG("DB init fail %s " % str(e))
def insert(self,player_id,cmd):
try:
if self.conn is None:
self.__init__()
else:
self.conn.ping(True)
self.cursor.execute('INSERT INTO table values("%s",%s")' %
(player_id,cmd))
except Exception as e:
import traceback
traceback.print_exc()
#error ocurs,rollback
self.conn.rollback()
你误解了我的问题... – BAE