python操作mysql数据库

import pymysql

class Mysql(object):
    def __init__(self):
        try:
            # 打开数据库连接
            #连接数据库所需的值，可以在__init__()中传入
            self.conn = pymysql.connect(
                host = 'localhost',
                port = 3306,
                user = "root",
                passwd = 'root',
                db = "test",
                charset = 'utf8'
            )
        except Exception as e:
            print(e)
        else:
            print("connect successfully")
            # 使用 cursor() 方法创建一个游标对象 cursor
            self.cur = self.conn.cursor()

    def create_table(self):
        try:
            # 使用 execute() 方法执行 SQL，如果表存在则删除
            self.cur.execute("DROP TABLE IF EXISTS EMPLOYEE")
            # 使用预处理语句创建表
            sql = """CREATE TABLE EMPLOYEE (
                   FIRST_NAME  CHAR(20) NOT NULL,
                   LAST_NAME  CHAR(20),
                   AGE INT,  
                   SEX CHAR(1),
                   INCOME FLOAT )"""
            #执行sql语句
            self.cur.execute(sql)
            print("create table success")
        except Exception as e:
            print("create table error\n" + e)

    def add(self):
        #数据库插入语句
        sql = """insert into EMPLOYEE(First_Name,
                Last_Name,Age,Sex,Income) 
                values('Mac','Mohan',20,'F',2000);"""
        try:
            self.cur.execute(sql)
            # 提交到数据库执行
            self.conn.commit()
        except Exception as e:
            print(e)
            # 发生错误时回滚
            self.conn.rollback()
            print("fail to add new data")
        else:
            print("insert data seccess！")

    # Python查询Mysql使用
    # fetchone()方法获取单条数据, 使用fetchall()方法获取多条数据。
    # fetchone(): 该方法获取下一个查询结果集。结果集是一个对象
    # fetchall(): 接收全部的返回结果行.
    # rowcount: 这是一个只读属性，并返回执行execute()
    # 方法后影响的行数。
    def show(self):
        sql = "select * from employee"
        try :
            self.cur.execute(sql)
            #fetchall()返回的结果是list，list里面再嵌套list
            res = self.cur.fetchall()
            for row in res:
                fname = row[0]
                lname = row[1]
                age = row[2]
                sex = row[3]
                income = row[4]

                # 打印结果
                print("\n fname =%s,lname =%s,age = %d, sex=%s,income=%d \n " % (fname, lname, age, sex, income))
        except  Exception as e:
            print(e + "select data fail")
        else:
            print("select data success")

    #更新数据库
    def upodate(self):
        sql = "update employee set age = age + 1 where sex ='%c'" %("m")
        try:
            self.cur.execute(sql)
            self.conn.commit()
        except Exception as e:
            print(e)
        else:
            print("update data success")

    #删除数据库中数据
    def rem(self):
        sql = 'delete from employee where sex = "M"'
        try:
            self.cur.execute(sql)
            self.conn.commit()
        except Exception as e:
            print(e)
        else:
            print("delete data success")

    #关闭数据库连接
    def close(self):
        self.cur.close()
        self.conn.close()
        print("close database success")



if __name__ == "__main__":
    mysql = Mysql()
    mysql.create_table()
    mysql.add()
    mysql.show()
    mysql.upodate()
    mysql.rem()
    mysql.close()