"""
作者：20181221曾宇涛
文件名称：数据库
时间：2020.4.29
"""
import pymysql
def add1():
    sql = "insert into student (name, studentID, class, age ) VALUES ('王五', '20191102',  '201911',  '19')"
    cursor.execute(sql)

def add2():
    sql = "insert into student (name, studentID, class, age ) VALUES ('梁六', '20191103',  '201912',  '20')"
    cursor.execute(sql)

def remove():
    sql = "DELETE FROM student WHERE class = '201911'"
    cursor.execute(sql)

def update():
    sql = "UPDATE student SET class = '201910' WHERE name = '梁六'"
    cursor.execute(sql)

def check():
    try:
        sql2 = "select * from student"
        cursor.execute(sql2)
        data = cursor.fetchall()
        print("student表中的数据有：", data)
    except:
        sql_2 = '''
        CREATE TABLE `student` (
            `id` INT NOT NULL AUTO_INCREMENT,  
            `name` VARCHAR (50) NOT NULL,
            `studentID` VARCHAR (8) NOT NULL,
            `class` VARCHAR (8) NOT NULL,  
            `age` INT NOT NULL,
            PRIMARY KEY (`id`)
        ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
        '''
        cursor.execute(sql_2)
        print("创建数据表成功")

def check2():
    sqlt = "show tables"
    cursor.execute(sqlt)
    data = cursor.fetchall()
    print("当前数据表有：", data)

db = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', charset='utf8')

cursor = db.cursor()

try:
    sql_1 = "USE python"
    cursor.execute(sql_1)
    print("当前数据库为：python")
except:
    sql = "CREATE DATABASE IF NOT EXISTS python"
    cursor.execute(sql)
    print("创建数据库成功")
    sql_1 = "USE python"
    cursor.execute(sql_1)

if __name__ == "__main__":
    check()
    check2()
    add1()
    check()
    remove()
    check()
    add2()
    check()
    update()
    check()
    cursor.close()
    db.close()