python3数据库mysql增删改查

#导入python和mysql的程序驱动
import pymysql

#import sys
#reload(sys)
#sys.setdefaultencoding( "utf-8" )

#一、创建数据库对象connection
conn = pymysql.connect(
    host = 'localhost',
    user = 'root',
    passwd = '',
    charset='utf8'
)
#二、创建数据库数据承载对象--获取游标对象cursor
cursor = conn.cursor()

#1.创建一个数据库--教务系统
#cursor.execute('create database if not exists School ')

#2.选择数据库
conn.select_db('School')

#3.删除数据库
#cursor.execute('drop database if exists School')

#4.创建数据库中的表
cursor.execute('create table if not exists Student(Sno char(10)not null,Sname char(10),Ssex char(2),Sage int,Sdept varchar(20))')

#5.删除表
#cursor.execute('drop table if exists Student')

#6.显示创建的数据库中的表
cursor.execute('select* from Student')
#得到一元祖 存放查询数据
def display():
    a=cursor.fetchone()
    print(a)

#7.插入数据
def insert():
    cursor.execute('insert  into Student(Sno,Sname,Ssex,Sage,Sdept)values("95001","李勇","男",20,"CS")')
    cursor.execute('insert into Student(Sno,Sname,Ssex,Sage,Sdept)values("95002","刘晨","女",19,"IS")')
    cursor.execute('insert into Student(Sno,Sname,Ssex,Sage,Sdept)values("95003","王敏","女",18,"MA")')
    cursor.execute('insert into Student(Sno,Sname,Ssex,Sage,Sdept)values("95004","张立","男",19,"IS")')
    cursor.execute('insert into Student(Sno,Sname,Ssex,Sage,Sdept)values("11111","王婵","男",19,"IS")')

#提交到数据库  如果没有这一步就无法保存在数据库中


#8.查询数据库所有字段
def ALLdisplay():
    count=cursor.execute('select* from Student')
    print('共有'+str(count)+'条记录')
    rows = cursor.fetchall()
    print('-----------------------------------')

    #遍历元祖输出数据
    for row in rows:
        print(row[0],row[1],row[2],row[3],row[4])#五个属性

#9.删除数据：
def delete():
    cursor.execute('delete from Student where Sno = "11111" ')
    conn.commit()

#10.改数据：
def redata():
    cursor.execute('update Student set Ssex="女"where Sname="李勇"')
    conn.commit()

#查询数据
def find():
    cursor.execute('select* from Student where Sno="95003"')

find()
redata()
ALLdisplay()
#三、关闭游标
cursor.close

#四、关闭对象
conn.close