python操作数据库的简单类

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

class ClassDB:
	def __init__(self):
		self.ip = "localhost"
		self.user = "root"
		self.password = "123456"
		self.DBName = "TEST"
		self.db = MySQLdb.connect(self.ip, self.user, self.password, self.DBName, charset='utf8' )
		self.cursor = self.db.cursor()

	def disconnect(self):
		# 关闭数据库连接
		self.db.close()

	def execute(self, sql):
		try:
			self.cursor.execute(sql)
			self.db.commit()
		except:
			self.db.rollback()

	def create(self, sql):
		self.cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
		self.cursor.execute(sql)

	def insert(self, sql):
	   self.execute(sql)

	def delete(self, sql):
		self.execute(sql)

	def update(self,  sql):
		self.execute(sql)

	def select(self, sql):
		try:
			self.cursor.execute(sql)
			return self.cursor.fetchall()
		except:
		   print "Error: unable to fecth data"

if __name__ == "__main__":
	db = ClassDB()

	# 创建表
	sql = """CREATE TABLE `EMPLOYEE` (
			  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
			  `FIRST_NAME` char(20) NOT NULL,
			  `LAST_NAME` char(20) DEFAULT NULL,
			  `AGE` int(11) DEFAULT NULL,
			  `SEX` char(1) DEFAULT NULL,
			  `INCOME` float DEFAULT NULL,
			  PRIMARY KEY (`id`)
			)"""
	db.create(sql)

	# 增加记录
	sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
			  LAST_NAME, AGE, SEX, INCOME)
			  VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
	db.insert(sql)
	sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
			  LAST_NAME, AGE, SEX, INCOME)
			  VALUES ('Tom', 'Mohan', 19, 'M', 200)"""
	db.insert( sql)

	sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
			  LAST_NAME, AGE, SEX, INCOME)
			  VALUES ('Bob', 'Mohan', 21, 'W', 20000)"""
	db.insert( sql)


	#更新记录
	sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')
	db.update(sql)

	# 查询记录
	sql = "SELECT * FROM EMPLOYEE WHERE INCOME > %s" % (1000)
	result = db.select(sql)
	print(result)

	#删除记录
	sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
	#db.delete(sql)

	db.disconnect()