1.需求分析
实现对学生信息的管理,学生具有姓名,学号,年龄三个属性
2.系统功能
1.创建一个学生信息表
2.添加一个学生信息
3.删除一个学生信息
4.修改一个学生信息
5.查找所有学生信息
6.删除一个学生信息表
3.关系模型
student(Name,Email,Age)
4.E-R图
5.代码:
import pymysql
def menu():
print('欢迎使用学生信息管理系统!')
print('*'*30)
print('1.创建一个学生信息表')
print('2.添加一个学生信息')
print('3.删除一个学生信息')
print('4.修改一个学生信息')
print('5.查找所有学生信息')
print('6.删除一个学生信息表')
print('*'*30)
def connection():
try:
db = pymysql.connect(host='localhost',
user='root',
password='521666',
database='lc',
charset='utf8')
print('数据库连接成功!')
except pymysql.Error as e:
print('数据库连接失败'+str(e))
db.close()
def CreateTable():
db = pymysql.connect(host='localhost',
user='root',
password='521666',
database='lc',
charset='utf8')
cur = db.cursor()
sql = 'create table if not exists student(Name varchar(20) not null,Email varchar(20),Age int)'
cur.execute(sql)
db.close()
def Insert():
db = pymysql.connect(host='localhost',
user='root',
password='521666',
database='lc',
charset='utf8')
cur = db.cursor()
sql = 'insert into student value(%s,%s,%s)'
Name = input('请输入学生姓名:')
Email = input('请输入学生学号:')
Age = input('请输入学生年龄:')
value=(Name,Email,Age)
try:
cur.execute(sql,value)
db.commit()
print('数据插入成功!')
except pymysql.Errot as e:
print('数据插入失败!'+e)
db.rollback()
db.close()
def deletedata():
db = pymysql.connect(host='localhost',
user='root',
password='521666',
database='lc',
charset='utf8')
cur = db.cursor()
sql = 'delete from student where Name = %s'
value = input('请输入要删除的学生的姓名:')
try:
cur.execute(sql,value)
db.commit()
print('数据删除成功!')
except pymysql.Error as e:
print('数据删除失败'+e)
db.rollback()
db.close()
def update():
db = pymysql.connect(host='localhost',
user='root',
password='521666',
database='lc',
charset='utf8')
cur = db.cursor()
num = input('请输入数字:(1.修改名字,2.修改学号,3.修改年龄)')
if num == '1':
sql = 'update student set Name=%s where Name=%s'
Before = input('请输入修改之前的学生姓名:')
After = input('请输入修改之后的学生姓名:')
if num == '2':
sql = 'update student set Email=%s where Name=%s'
Before = input('请输入要修改信息的学生的姓名:')
After = input('请输入修改之后的学生学号:')
if num == '3':
sql = 'update student set Age=%s where Name=%s'
Before = input('请输入要修改信息的学生的姓名:')
After = input('请输入修改之后的学生年龄:')
value=(After,Before)
try:
cur.execute(sql,value)
db.commit()
print('数据更新成功!')
except pymysql.Error as e:
print('数据更新失败'+e)
db.rollback()
db.close()
def Find():
db = pymysql.connect(host='localhost',
user='root',
password='521666',
database='lc',
charset='utf8')
cur = db.cursor()
sql = 'select * from student'
try:
cur.execute(sql)
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('数据查询失败'+e)
db.rollback()
db.close()
def Delete():
db = pymysql.connect(host='localhost',
user='root',
password='521666',
database='lc',
charset='utf8')
cur = db.cursor()
sql = 'drop table if exist student'
cur.execute(sql)
print('表删除成功!')
db.close()
def main():
connection()
menu()
while True:
number = input('请输入数字:')
if number == '1':
CreateTable()
if number == '2':
Insert()
if number == '3':
deletedata()
if number == '4':
update()
if number == '5':
Find()
if number == '6':
Delete()
if number >= '7':
print('感谢使用本系统!')
break
main()