**学生管理系统**
1、需求分析
实现对学生信息的管理，学生具有姓名，邮箱，年龄属性
2、系统功能
1、创建一个学生信息表
2、添加一个学生信息
3、删除一个学生信息
4、修改一个学生信息
5、查询一个学生信息
6、删除一个学生信息表
3、关系模型
Student(Name,Email,Age)
4、E-R图
完整代码如下：
import pymysql
def menu():
print(“欢迎使用本系统！”)
print("-"*30)
print(“学生成绩管理系统”)
print(“1、添加一个学生信息表”)
print(“2、添加一个学生的信息”)
print(“3、删除一个学生的信息”)
print(“4、修改一个学生的信息”)
print(“5、查询一个学生的信息”)
print(“6、删除一个学生信息表”)
print("-"*30)
def connection():
try:
db = pymysql.connect(‘localhost’, ‘root’, ‘root’, ‘mysql’)
print(‘数据库连接成功!’)
except pymysql.Error as e:
print(‘数据库连接失败’+str(e))
db.close()
def CreateTable():
db = pymysql.connect(‘localhost’, ‘root’, ‘root’, ‘mysql’)
cur=db.cursor()
cur.execute(‘DROp TABLE IF EXISTS Student’)
sqlQuery = “CREATE TABLE Student(Name CHAr(20) NOT NULL ,Email CHAr(20),Age int )”
cur.execute(sqlQuery)
db.close()
def Insert():
db = pymysql.connect(‘localhost’, ‘root’, ‘root’, ‘mysql’)
cur = db.cursor()
sqlQuery = " INSERT INTO Student (Name, Email, Age) VALUE (%s,%s,%s) "
a=input(“请输入学生姓名：”)
b=input(“请输入学生邮箱：”)
c=input(“请输入学生年龄：”)
value = (a, b, c)
try:
cur.execute(sqlQuery, value)
db.commit()
print(‘数据插入成功！’)
except pymysql.Error as e:
print(“数据插入失败：” + e)
db.rollback()
db.close()
def deletedata():
db = pymysql.connect(‘localhost’, ‘root’, ‘root’, ‘mysql’)
cur = db.cursor()
sqlQuery = “DELETE FROM Student where Name=%s”
a=input(“请输入要删除学生的姓名：”)
value = (a)
try:
cur.execute(sqlQuery, value)
db.commit()
print(‘Date Deleted Successfully’)
except pymysql.Error as e:
print(“数据删除失败：” + str(e))
# 发生错误时回滚
db.rollback()
def update():
db = pymysql.connect(‘localhost’, ‘root’, ‘root’, ‘mysql’)
cur = db.cursor()
num=input(“输入数字：（1更改名字，2更改邮箱，3更改年龄）”)
if num ==‘1’:
sqlQuery = “UPDATE Student SET Name= %s WHERe Name=%s”
a = input(“请输入要更新学生的姓名：”)
b = input(“请输入更新后的名称：”)
if num ==‘2’:
sqlQuery = “UPDATE Student SET Email= %s WHERe Name=%s”
a = input(“请输入要更新学生的姓名：”)
b = input(“请输入更新后的邮箱：”)
if num ==‘3’:
sqlQuery = “UPDATE Student SET Age= %s WHERe Name=%s”
a = input(“请输入要更新学生的姓名：”)
b = input(“请输入更新后的年龄：”)
value = (a, b)
try:
cur.execute(sqlQuery, value)
db.commit()
print(‘数据更新成功！’)
except pymysql.Error as e:
print(“数据更新失败：” + str(e))
# 发生错误时回滚
db.rollback()
db.close()
def Find():
db = pymysql.connect(‘localhost’, ‘root’, ‘root’, ‘mysql’)
cur = db.cursor()
sqlQuery = “SELECT * FROM Student”
try:
cur.execute(sqlQuery)
results = cur.fetchall()
for row in results:
name = row[0]
email = row[1]
age = row[2]
print(‘Name:%s,Email:%s,Age:%s’ % (name, email, age))
except pymysql.Error as e:
print(“数据查询失败：” + str(e))
db.close()
def Delete():
db = pymysql.connect(‘localhost’, ‘root’, ‘root’, ‘mysql’)
cur = db.cursor()
sqlQuery = ‘DROP TABLE IF EXISTS Student’
cur.execute(sqlQuery)
print(‘表删除成功!’)
db.close()
def main():
connection()
menu()
while True:
number=input(“请输入数字：”)
if number==‘1’:
CreateTable()
elif number==‘2’:
Insert()
elif number==‘3’:
deletedata()
elif number==‘4’:
update()
elif number==‘5’:
Find()
elif number==‘6’:
Delete()
elif number>=‘7’:
print(“感谢使用本系统！”)
break
main()
