python操作mysql的一个类

已测试通过,python2.7环境 ,mysql5.6

#coding=utf-8
#!/usr/bin/env python
import MySQLdb
import time

class MySQL:
    error_code = ''
    _instance = None
    _conn = None
    _cur  = None
    
    _TIMEOUT = 30
    _timecount = 0
    
    def __init__(self,dbconfig):
        try:
            self._conn = MySQLdb.connect(
                                         host=dbconfig['host'],
                                         port=dbconfig['port'],
                                         user=dbconfig['user'],
                                         passwd=dbconfig['passwd'],
                                         db=dbconfig['db'],
                                         charset=dbconfig['charset'])
        except MySQLdb.Error,e:
            self.eror_code = e.args[0]
            error_msg = 'MySQL Error !',e.args[0],e.args[1]
            print error_msg            
            
            if self._timecount < self._TIMEOUT:
                interval = 5
                self._timecount += interval
                time.sleep(interval)
                return self.__init__(dbconfig)
            else:
                raise Exception(error_msg)
            
        self._cur = self._conn.cursor()
        self._cur.execute('SET NAMES utf8')
        self._instance = MySQLdb
            
    def query(self,sql):
        try:           
            result = self._cur.execute(sql)
        except MySQLdb.Error,e:
            self.error_code = e.args[0]
            print "更新错误",e.args[0],e.args[1]
            result = False
        return result
    
    def update(self,sql):
        try:
            result = self._cur.execute(sql)
            self._conn.commit()
        except MySQLdb.Error,e:
            self._conn.rollback()
            self.error_code = e.args[0]
            print "更新错误",e.args[0],e.args[1]
            result = False
        return result
         
    def insert(self,sql):
        try:
            self._cur.execute("SET NAMES utf8")
            self._cur.execute(sql)
            self._conn.commit()
            return self._conn.insert_id()
        except MySQLdb.Error, e:
            self._conn.rollback()
            self.error_code = e.args[0]
            return False
        
    def fetchAllRows(self):
        return self._cur.fetchall()
    
    def fetchOneRow(self):
        return self._cur.fetchone()
    
    def getRowCount(self):
        return self._cur.rowcount
    
    def commit(self):
        self._conn.commit()
    
    def rollback(self):
        self._cur.rollback()
    
    def __del__(self):
        try:
            self._cur.close()
            self._conn.close()
        except:
            pass
        
    def close(self):
        self.__del__()
        
if __name__ == '__main__':
    dbconfg = {'host':'172.0.0.1','port':3306,'user':'my','passwd':'123456','db':'infodb','charset':'utf8'}
    db = MySQL(dbconfg)
    sql = 'SELECT * FROM t_user limit 20'
    db.query(sql)
    
    result = db.fetchAllRows()
    print result
    
    for row in result:
        for column in row:
            print column
    
    db.close()

相关推荐