Python Singleton class to handle "MySQL server has gone away" error

Question

When I was trying to execute a query -

select * from students where id = 20

using Python's MySQLdb library, I was getting an exception: _mysql_exceptions.OperationalError saying MySQL server has gone away. Some of my friends suggested me to use the existing mysql connection. So I started referring this link and used the same Singleton class from the link in my code below, it still gives me the same exception.

#DBConnect.py
import MySQLdb as connector
from Singleton import Singleton
class DBConnect:
    # As the link suggests
    __metaclass__  = Singleton
    def __init__(self):
        self.dbConnection = connector.Connect(host = 'localhost', user = 'root', 
                                          passwd = 'root', db = 'school')
        self.dbCursor = self.dbConnection.cursor(cursorclass=connector.cursors.DictCursor) 

    def getRecord(self,query):
        self.dbCursor.execute(query)
        result = self.dbCursor.fetchone()
        return result

    def __del__(self):
        self.dbCursor.close()
        self.dbConnection.close()

if __name__ == '__main__':
    dc = DBConnect()
    query = "select * from students where id = 20"
    result = dc.getRecord(query)

I have tried to create more instances of DBConnect like -

dc2 = DBConnect()
dc3 = DBConnect()

When I print dc, dc2 and dc3 it gives -

<__main__.DBConnect object at 0x8a5fe8c> 
<__main__.DBConnect object at 0x8a5fe8c> 
<__main__.DBConnect object at 0x8a5fe8c>

Meaning the same instance. So what seems to be the problem? Why can't I resolve this?

Answer 1

The problem is actually the Singleton. Its sole purpose as a singleton is to ensure that only one instance of a class is created. When you use the __metaclass__ = Singleton line in your class, you are ensuring that only one instance of DBConnect can be created, and that any subsequent instantiations of that class will point back to the same instance. Therefore when you use dc2 = DBConnect() and dc3 = DBConnect(), you are just referring back to the same instance, hence the same identifier you see.

Here is the part of your Singleton code that is causing the behavior:

def __call__(self, *args, **kw):
        if self.instance is None:
            self.instance = super(Singleton, self).__call__(*args, **kw)

        return self.instance

After you create an instance of DBConnect, self.instance is no longer None, meaning that the instance you first created (which gets stored in self.instance) is the one that is returned anytime you call your class.

My (unsolicited) advice would be to start from scratch and just try to get a basic connection working with the MySQLdb library - the error you mention up top is indicative of MySQL error, so doing some troubleshooting there may be the best course of action.