Python实现封装MySQLdb基本操作类

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

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

#封装MySQLdb类基本操作mysqldb


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