Python 封装MySQL类

发布时间：2019-09-22 07:46:44编辑：auto阅读（2035）

   以后再也不用每次都重新写啦！

   

import MySQLdb 
OperationalError = MySQLdb.OperationalError
class MySQL: 
    def __init__(self,host,user,password,port=3306,charset="utf8"): 
        self.host=host 
        self.port=port
        self.user=user 
        self.password=password 
        self.charset=charset 
        try: 
            self.conn=MySQLdb.connect(host=self.host,port=self.port,user=self.user,passwd=self.password)
            self.conn.autocommit(False)
            self.conn.set_character_set(self.charset) 
            self.cur=self.conn.cursor() 
        except MySQLdb.Error as e: 
            print("Mysql Error %d: %s" % (e.args[0], e.args[1])) 
                    
    def __del__(self):
        self.close()
                         
    def selectDb(self,db): 
        try: 
            self.conn.select_db(db) 
        except MySQLdb.Error as e: 
            print("Mysql Error %d: %s" % (e.args[0], e.args[1])) 
                         
    def query(self,sql): 
        try: 
            n=self.cur.execute(sql) 
            return n 
        except MySQLdb.Error as e: 
            print("Mysql Error:%s\nSQL:%s" %(e,sql)) 
                         
    def fetchRow(self): 
        result = self.cur.fetchone() 
        return result 
                         
    def fetchAll(self): 
        result=self.cur.fetchall() 
        desc =self.cur.description 
        d = [] 
        for inv in result: 
            _d = {} 
            for i in range(0,len(inv)): 
                _d[desc[i][0]] = str(inv[i]) 
                d.append(_d) 
        return d 
                         
    def insert(self,table_name,data): 
        columns=data.keys()
        _prefix="".join(['INSERT INTO `',table_name,'`'])
        _fields=",".join(["".join(['`',column,'`']) for column in columns])
        _values=",".join(["%s" for i in range(len(columns))])
        _sql="".join([_prefix,"(",_fields,") VALUES (",_values,")"])
        _params=[data[key] for key in columns]
        return self.cur.execute(_sql,tuple(_params)) 
                       
    def update(self,tbname,data,condition):
        _fields=[]
        _prefix="".join(['UPDATE `',tbname,'`','SET'])
        for key in data.keys():
            _fields.append("%s = %s" % (key,data[key]))
        _sql="".join([_prefix ,_fields, "WHERE", condition ])
                           
        return self.cur.execute(_sql)
                       
    def delete(self,tbname,condition):
        _prefix="".join(['DELETE FROM  `',tbname,'`','WHERE'])
        _sql="".join([_prefix,condition])  
        return self.cur.execute(_sql)
                         
    def getLastInsertId(self): 
        return self.cur.lastrowid 
                         
    def rowcount(self): 
        return self.cur.rowcount 
                         
    def commit(self): 
        self.conn.commit() 
                       
    def rollback(self):
        self.conn.rollback()
                         
    def close(self): 
        self.cur.close() 
        self.conn.close()
                           
if __name__=='__main__':
    n=MySQL('127.0.0.1','root','123456',3306)
    n.selectDb('test')
    tbname='map'
    a=({'id':3,'x':3,'y':3},{'id':4,'x':4,'y':4},{'id':5,'x':5,'y':5})
    for d in a:
        n.insert(tbname,d)
    n.commit()




 一个重量级的MySQL-Python 封装类：facebook python mysql

关键字：