python2.7对MySQL增删改查的封装

MySQL表的建立

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `password` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

开始python对MySQL的增删改查封装

1. 第一次封装，每次新建连接、游标并关闭他们

# -*- coding:utf-8 -*-
import MySQLdb
 
class MysqldbHelper:
    #获取数据库连接
    def getCon(self):
        try:
            conn=MySQLdb.connect(host='localhost',user='root',passwd='123456',db='testpy',port=3306,charset='utf8')
            return conn
        except MySQLdb.Error,e:
            print "Mysqldb Error:%s" % e
    #查询方法，使用con.cursor(MySQLdb.cursors.DictCursor),返回结果为字典    
    def select(self,sql):
        try:
            con=self.getCon()
            print con
            cur=con.cursor(MySQLdb.cursors.DictCursor)
            count=cur.execute(sql)
            fc=cur.fetchall()
            return fc
        except MySQLdb.Error,e:
            print "Mysqldb Error:%s" % e
        finally:
            cur.close()
            con.close()
    #带参数的更新方法,eg:sql='insert into pythontest values(%s,%s,%s,now()',params=(6,'C#','good book')
    def updateByParam(self,sql,params):
        try:
            con=self.getCon()
            cur=con.cursor()
            count=cur.execute(sql,params