python操作mysql数据库——增删改查操作（二）

前面一篇介绍了如何连接数据库，这一篇主要是介绍如何对数据库进行增删改查的操作。

完整的Python操作mysql数据库脚本如下

# coding = utf -8
import pymysql

class  Mysql(object):
    def __init__(self):
        try:
            self.conn = pymysql.connect(
                host = '39.108.237.57',
                port = 3306,
                user = 'root',
                passwd = '380475066lhJ',
                db = 'test',
                charset = 'utf8',
            )
        except Exception as e:
            print(e)
        else:
            print("connect successfully")

            #使用cursor()方法创建一个游标对象cursor
            self.cur = self.conn.cursor()


    #创建表
    def creat_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 ("John","jack",25,"F",8000)"""
        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 success")



    #向数据库表中查询数据
    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("fname= %s,lname = %s,age = %d,sex =%s,income = %d"%(fname,lname,age,sex,income))
        except Exception as e:
            print(e+"select data fail")
        else:
            print("select data success")



    #向数据库中更新数据
    def update(self):
        sql = """UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = 'M'"""
        try:
            self.cur.execute(sql)
            self.conn.commit()
        except Exception as e:
            print(e)
        else:
            print("updata date 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.creat_table()
    mysql.add()
    mysql.show()
    mysql.update()
    mysql.rem()
    mysql.close()