import MySQLdb
from DBUtils.PooledDB import PooledDB
class DBHelper(object):
    
    __pool = None
    
    def __init__(self):
        self._conn = DBHelper.__getConnection()
        self._cursor = self._conn.cursor()
    @staticmethod
    def __getConnection():
        if DBHelper.__pool is None :
            __pool = PooledDB(creator = MySQLdb,
                              mincached=1,
                              maxcached=20,
                              host = '127.0.0.1',
                              port = 3306,
                              user = 'root',
                              passwd = '123456',
                              db = 'test')
        
        return __pool.connection()
    def execute(self, sql, parameter=None):
        if parameter is None:
            self._cursor.execute(sql)
        else :
            self._cursor.execute(sql, parameter)
        
    def readOne(self, sql, parameter=None):
        if parameter is None :
            count = self._cursor.execute(sql)
        else :
            count = self._cursor.execute(sql, parameter)
        if count > 0:
            return self._cursor.fetchone()
        else :
            return None
        
    def readList(self, sql, parameter=None):
        if parameter is None :
            count = self._cursor.execute(sql)
        else :
            count = self._cursor.execute(sql, parameter)
        if count > 0:
            return self._cursor.fetchall()
        else :
            return None
        
    def commint(self):
        self._conn.commit()
        
    def close(self):
        if self._cursor :
            self._cursor.close()
        if self._conn :
            self._conn.close()