Python实现封装MySQLdb基本操作类-Tutorial Python-php.cn

Python实现封装MySQLdb基本操作类

零到壹度

Lepaskan： 2018-04-08 09:40:13

asal

1994 orang telah melayarinya

这篇文章主要介绍了Python实现封装MySQLdb基本操作类,小编觉得挺不错的，现在分享给大家，也给大家做个参考。一起跟随小编过来看看吧

# -*- coding:utf-8 -*-
import MySQLdb
import datetime
import sys
reload(sys)
sys.setdefaultencoding(&#39;utf-8&#39;)

#封装MySQLdb类基本操作mysqldb


class mysqldb:
        conn = &#39;&#39;
        cursor = &#39;&#39;
        def __init__(self,host1=&#39;localhost&#39;,user1=&#39;root&#39;,passwd1=&#39;www&#39;,database1=&#39;test&#39;,charset1=&#39;utf8&#39;):
           try:          
             print &#39;init_ ok&#39;
                self.conn=MySQLdb.connect(host=host1,user=user1,passwd=passwd1,db=database1,port=3306,charset=charset1);
                self.cursor = self.conn.cursor(MySQLdb.cursors.DictCursor)            
            except MySQLdb.Error,e:
                error = &#39;Connect failed! ERROR (%s): %s&#39; %(e.args[0],e.args[1])               
                     print error
                     sys.exit()        
        #针对读操作返回结果集
        def _exeCute(self,sql=&#39;&#39;):
            try:
                self.cursor.execute(sql)
                records = self.cursor.fetchall()                
                return records            
            except MySQLdb.Error,e:
                error = &#39;MySQL execute failed! ERROR (%s): %s&#39; %(e.args[0],e.args[1])              
                      print error                    
                      #sys.exit()
        #针对更新,删除,事务等操作失败时回滚
        def _exeCuteCommit(self,sql=&#39;&#39;):
            try:
                self.cursor = self.conn.cursor()
            self.cursor.execute(sql)
                self.conn.commit()            
            except MySQLdb.Error,e:
                self.conn.rollback()
                error = &#39;MySQL execute failed! ERROR (%s): %s&#39; %(e.args[0],e.args[1])               
                     print error                    
                     #sys.exit()
        #创建表        
        #tablename:表名称,attr_dict:属性键值对,constraint:主外键约束
        #attr_dict:{&#39;book_name&#39;:&#39;varchar(200) NOT NULL&#39;...}
        #constraint:PRIMARY KEY(`id`)
        def _createTable(self,table,attr_dict,constraint):
            sql = &#39;&#39;
            sql_mid = &#39;`id` bigint(11) NOT NULL AUTO_INCREMENT,&#39;
            for attr,value in attr_dict.items():
                sql_mid = sql_mid + &#39;`&#39;+attr + &#39;`&#39;+&#39; &#39;+ value+&#39;,&#39;
            sql = sql + &#39;CREATE TABLE IF NOT EXISTS %s (&#39;%table
            sql = sql + sql_mid
            sql = sql + constraint
            sql = sql + &#39;) ENGINE=InnoDB DEFAULT CHARSET=utf8&#39;
            print &#39;_createTable:&#39;+sql
            self._exeCuteCommit(sql)        
            
        #查询表内容
        #cond_dict:{&#39;name&#39;:&#39;xiaoming&#39;...}
        #order:&#39;order by id desc&#39;
        def _select(self,table,cond_dict=&#39;&#39;,order=&#39;&#39;):
            consql = &#39; &#39;
        if cond_dict!=&#39;&#39;:           
                 for k,v in cond_dict.items():
                        consql = consql+k+&#39;=&#39;+v+&#39; and&#39;
                consql = consql + &#39; 1=1 &#39;
            sql = &#39;select * from %s where &#39;%table
            sql = sql + consql + order            
            print &#39;_select:&#39;+sql            
            return self._exeCute(sql)        
            
        #插入单条数据
        def _insert(self,table,attrs,value):
            #values_sql = [&#39;%s&#39; for v in attrs]
            attrs_sql = &#39;(&#39;+&#39;,&#39;.join(attrs)+&#39;)&#39;
            values_sql = &#39; values(&#39;+&#39;,&#39;.join(value)+&#39;)&#39;
            sql = &#39;insert into %s&#39;%table
            sql = sql + attrs_sql + values_sql        
            print &#39;_insert:&#39;+sql
            self._exeCuteCommit(sql)        
            #插入多条数据
        #attrs:[id,name,...]
        #values:[[1,&#39;jack&#39;],[2,&#39;rose&#39;]]
        def _insertMany(self,table,attrs,values):
            values_sql = [&#39;%s&#39; for v in attrs]
            attrs_sql = &#39;(&#39;+&#39;,&#39;.join(attrs)+&#39;)&#39;
            values_sql = &#39; values(&#39;+&#39;,&#39;.join(values_sql)+&#39;)&#39;
            sql = &#39;insert into %s&#39;%table
            sql = sql + attrs_sql + values_sql            
            print &#39;_insertMany:&#39;+sql            
            try:            
            print sql            
            #print values
            for i in range(0,len(values),20000):
                    self.cursor.executemany(sql,values[i:i+20000])
                    self.conn.commit()            
            except MySQLdb.Error,e:
                self.conn.rollback()
                error = &#39;_insertMany executemany failed! ERROR (%s): %s&#39; %(e.args[0],e.args[1])             
                    print error
                    sys.exit()       
        def _now(self):
            now0 = datetime.datetime.now()
            now = now0.strftime(&#39;%Y-%m-%d %H:%M:%S&#39;)            
            print now        
      def _close(self):
            self.cursor.close()
            self.conn.close()    
      def __del__(self):
        self._close()if __name__ == &#39;__main__&#39;:
    a = mysqldb(&#39;localhost&#39;,&#39;root&#39;,&#39;www&#39;,&#39;test&#39;,&#39;utf8&#39;)
    a._now()
        table=&#39;test_mysqldb&#39;
        attrs={&#39;name&#39;:&#39;varchar(200) DEFAULT NULL&#39;,&#39;age&#39;:&#39;int(11) DEFAULT NULL&#39;}
        constraint=&#39;PRIMARY KEY(`id`)&#39;
        a._createTable(table,attrs,constraint)
    col = [&#39;name&#39;,&#39;age&#39;]
    val = [&#39;\&#39;xiegonghai\&#39;&#39;,&#39;25&#39;]
    a._insert(&#39;test_mysqldb&#39;,col,val)    
       print a._select(table)

Salin selepas log masuk

Atas ialah kandungan terperinci Python实现封装MySQLdb基本操作类. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!