python数据操作方法封装
工作中经常会用到数据的插叙、单条数据插入和批量数据插入，以下是本人封装的一个类，推荐给各位：
1 #!/usr/bin/env python 2 # -*- coding:utf-8 -*- 3 # Author:Eric.yue 4 5 import logging 6 import MySQLdb 7 class _MySQL(object): 8 def __init__(self,host, port, user, passwd, db): 9 self.conn = MySQLdb.connect( 10 host = host, 11 port = port, 12 user = user, 13 passwd = passwd, 14 db = db, 15 charset='utf8' 16 ) 17 18 def get_cursor(self): 19 return self.conn.cursor() 20 21 def query(self, sql): 22 cursor = self.get_cursor() 23 try: 24 cursor.execute(sql, None) 25 result = cursor.fetchall() 26 except Exception, e: 27 logging.error("mysql query error: %s", e) 28 return None 29 finally: 30 cursor.close() 31 return result 32 33 def execute(self, sql, param=None): 34 cursor = self.get_cursor() 35 try: 36 cursor.execute(sql, param) 37 self.conn.commit() 38 affected_row = cursor.rowcount 39 except Exception, e: 40 logging.error("mysql execute error: %s", e) 41 return 0 42 finally: 43 cursor.close() 44 return affected_row 45 46 def executemany(self, sql, params=None): 47 cursor = self.get_cursor() 48 try: 49 cursor.executemany(sql, params) 50 self.conn.commit() 51 affected_rows = cursor.rowcount 52 except Exception, e: 53 logging.error("mysql executemany error: %s", e) 54 return 0 55 finally: 56 cursor.close() 57 return affected_rows 58 59 def close(self): 60 try: 61 self.conn.close() 62 except: 63 pass 64 65 def __del__(self): 66 self.close() 67 68 mysql = _MySQL('127.0.0.1', 3306, 'root', '123456', 'test') 69 70 def create_table(): 71 table = """ 72 CREATE TABLE IF NOT EXISTS `watchdog`( 73 `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, 74 `name` varchar(100), 75 `price` int(11) NOT NULL DEFAULT 0 76 ) ENGINE=InnoDB charset=utf8; 77 """ 78 print mysql.execute(table) 79 80 def insert_data(): 81 params = [('dog_%d' % i, i) for i in xrange(12)] 82 sql = "INSERT INTO `watchdog`(`name`,`price`) VALUES(%s,%s);" 83 print mysql.executemany(sql, params) 84 85 86 if __name__ == '__main__': 87 create_table() 88 insert_data()
