How to check if a MySQL connection is open in Python?

Question

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:

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.

...
    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).

 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.

Shadowtrooper · Accepted Answer · 2020-10-06 06:27:45Z

7

You can check the connection with the is_connected method with this library python-mysql:

cnx = connector.connect(user="user",password="pass",host="host",database="database")

if (cnx.is_connected()):
    print("Connected")
else:
    print("Not connected")

answered Oct 6, 2020 at 6:27

Shadowtrooper

1,47216 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Swastik Padhi · Accepted Answer · 2015-10-20 14:38:10Z

4

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"

answered Oct 20, 2015 at 14:38

Swastik Padhi

1,90916 silver badges28 bronze badges

1 Comment

Constantin Over a year ago

This is useful to check if the MysqlConnection object was instantiated - or if a connection was ever established. It does not verify if a connection is still alive

Community · Accepted Answer · 2017-05-23 12:22:33Z

2

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?

edited May 23, 2017 at 12:22

CommunityBot

11 silver badge

answered Jul 15, 2016 at 17:32

Caio Casimiro

631 silver badge7 bronze badges

Comments

Bartleby · Accepted Answer · 2020-03-04 04:13:19Z

1

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!")

answered Mar 4, 2020 at 4:13

Bartleby

1,4711 gold badge15 silver badges16 bronze badges

1 Comment

greendino Over a year ago

bad practice also wrong answer. "socket.gaierror: [Errno 11001] getaddrinfo failed"

Ailen Li · Accepted Answer · 2017-09-26 06:46:02Z

you should code like this: at every execute before , ping the mysql-server,for example

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()

Collectives™ on Stack Overflow

How to check if a MySQL connection is open in Python?

5 Answers 5

Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related