python操作数据库

#coding=utf-8
__author__ = 'libai'
import MySQLdb
import traceback
import random
import string
class mysqlUtil(object):
    #获取连接和游标
    def __init__(self):
        self.conn=MySQLdb.connect(
            host='localhost',
            port=3306,
            user='root',
            passwd='root',
            charset='utf8'
        )
        self.cur=self.conn.cursor()
    #创建数据库
    def create_db(self,sql):
        self.cur.execute(sql)
        self.conn.commit()
    #获取数据库
    def user_db(self,db_name):
        self.conn.select_db(db_name)
    #创建表
    def create_table(self,db_name,sql):
        self.conn.select_db(db_name)
        self.cur.execute(sql)
        # self.conn.commit()
    #插入数据
    def insert(self,db_name,sql,values):
        self.conn.select_db(db_name)
        self.cur.execute(sql,values)
        # self.conn.commit()
    #插入多条数据
    def insert_many(self,db_name,sql,values):
        self.conn.select_db(db_name)
        self.cur.executemany(sql,values)
        self.conn.commit()
    #查询一条数据
    def select_one(self,db_name,sql):
        self.conn.select_db(db_name)
        self.cur.execute(sql)
        value=self.cur.fetchone()
        return value
    #查询多条语句
    def select_many(self,db_name,sql,n):
        self.conn.select_db(db_name)
        self.cur.execute(sql)
        return self.cur.fetchmany(n)
    #查询所有数据
    def select_all(self,db_name,sql):
        self.conn.select_db(db_name)
        self.cur.execute(sql)
        return self.cur.fetchall()
    #修改数据
    def update(self,db_name,sql):
        self.conn.select_db(db_name)
        self.cur.execute(sql)
        self.conn.commit()
    #批量修改
    def update_many(self,db_name,sql,value):
        self.conn.select_db(db_name)
        self.cur.executemany(sql,value)
        self.conn.commit()
    #关闭连接
    def quit(self):
        self.cur.close()
        self.conn.commit()
        self.conn.close()
    #删除一条数据
    def delete(self,db_name,sql):
        try:
            self.conn.select_db(db_name)
            self.cur.execute(sql)
            self.conn.commit()
        except:
            traceback.print_exc()
    #删除多条数据
    def delete_many(self,db_name,sql,values):
        try:
            self.conn.select_db(db_name)
            self.cur.executemany(sql,values)
            self.conn.commit()
        except MySQLdb.Error,e:
            print e
    #回滚操作
    def rollback(self):
        self.conn.rollback()
if __name__=='__main__':
    my_sql=mysqlUtil()
    #插入数据
    # sql="insert into student(name,age,email,tel) values(%s,%s,%s,%s)"
    # values=('baoshizhen',18,'baoshizhen@qq.com','17600340039')
    # my_sql.insert('nidaye',sql,values)
    # my_sql.quit()

    #插入多条数据
    # values=[]
    # sql="insert into student(name,age,email,tel) values(%s,%s,%s,%s)"
    # for i in range(10):
    #     name=''.join((random.sample(string.letters,5)))
    #     age=random.randint(1,100)
    #     email=name+'@qq.com'
    #     tel='1'+str(random.choice([3,5,8,9]))+str(random.random())[2:11]
    #     values.append((name,age,email,tel))
    # my_sql.insert_many('nidaye',sql,values)
    # my_sql.quit()

    #查询一条数据
    # sql='select * from student;'
    # value=my_sql.select_one('nidaye',sql)
    # print value


    #查询多条数据
    # sql='select * from student'
    # values=my_sql.select_many('nidaye',sql,5)
    # for i in values:
    #     print i

    #查询所有数据
    # sql='select * from student'
    # values=my_sql.select_all('nidaye',sql)
    # for i in values:
    #     print i

    #修改数据
    # sql='update student set name="%s" where id=11' %('end')
    # my_sql.update('nidaye',sql)
    # my_sql.quit()

    #批量修改
    # sql='update student set name=%s where id=%s'
    # values=[('first',1),('second',2)]
    # my_sql.update_many('nidaye',sql,values)
    # my_sql.quit()

    #删除一条数据
    # sql='delete from student where id=11'
    # my_sql.delete('nidaye',sql)
    # my_sql.quit()

    #删除多条数据
    # sql='delete from student where id =%s'
    # values=[(8),(9)]
    # my_sql.delete_many('nidaye',sql,values)
    # my_sql.quit()

    #回滚操作
    my_sql.user_db('nidaye')
    my_sql.cur.execute('insert into student(name,age,email,tel) values(%s,%s,%s,%s)',('bao',18,'bao@qq.com','17600340040'))
    my_sql.cur.execute('select * from student where name="bao"')
    value=my_sql.cur.fetchone()
    print value
    my_sql.rollback()
    my_sql.cur.execute('select * from student where name="bao"')
    value=my_sql.cur.fetchone()
    print value