如何检查 MySQL 连接是否在 Python 中打开？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33239480/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to check if a MySQL connection is open in Python?
提问by BAE
I am using MySQLdb (http://mysql-python.sourceforge.net/). It seems that connection.open and connection.sqlstate() do not work for me. Below is the code:
我正在使用 MySQLdb ( http://mysql-python.sourceforge.net/)。似乎 connection.open 和 connection.sqlstate() 对我不起作用。下面是代码：
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
But when I ran "sudo service mysql stop; sleep 60; sudo service mysql start;" to do the testing. The output is as following. It seems that the following piece of output repeated for ever (I killed the process finally). When the server is down, connection.open is 1 and connection.sqlstate() is 00000. But when server is up, connection.executemany() still throw exceptions. Any ideas? Thanks.
但是当我运行“sudo service mysql stop; sleep 60; sudo service mysql start;”时 做测试。输出如下。以下输出似乎永远重复了（我最终终止了该过程）。服务器宕机时，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
更新
I tested again. The output is as following. each sleep is 10 seconds. The output is OK except the connection.open is 1 even when server is down. But connection.sqlstate() is right (HY000).
我又测试了。输出如下。每次睡眠时间为 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.
回答by Swastik Padhi
Try this-
尝试这个-
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"
回答by Caio Casimiro
I have been searching for this solution for some time and I couldn't find an elegant solution.
我一直在寻找这个解决方案一段时间，但我找不到一个优雅的解决方案。
It seems there isn't a direct way of doing that. You will only find out your connection is closed if you try to execute a query.
似乎没有直接的方法可以做到这一点。如果您尝试执行查询，您只会发现您的连接已关闭。
I end up doing something similar to this answer: How to check the connection alive in python?
我最终做了类似于这个答案的事情： How to check the connection alive in python?
回答by Ailen Li
you should code like this: at every execute before , ping the mysql-server,for example
你应该这样编码：在每次执行之前，ping mysql-server，例如
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()
回答by Bartleby
Just in case anyone finds this helpful:
以防万一有人觉得这有帮助：
import socket
import time
sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
while sock.connect_ex(('db', 3306)) != 0: # 'db' is the host, 3306 is the port
print('MySQL is not ready yet.')
time.sleep(2)
sock.close()
print("Now it's up and running! Bye!")
