主要通过调用函数的方式来实现学生学生信息的管理
def openDB(host, user, passwd, db, port):
import pymysql
dbObj = pymysql.connect(host=host, user=user, password=passwd, db=db, port=port, charset='utf8')
cueObj = dbObj.cursor()
return dbObj, cueObj
def readDB(curObj, sql):
curObj.execute(sql)
result = curObj.fetchall()
return result
def writeDB(dbObj, curObj, sql):
try:
curObj.execute(sql)
dbObj.commit()
return 0
except:
dbObj.rollback()
return -1
def closeDB(dbObj, curObj):
dbObj.close()
curObj.close()
def searchStu(name='',cid='', sex='', race=''):
sql = 'select * from student where '
if name == '':
sql = sql + 'sname=sname'
else:
sql = sql + f"sname='{name}'"
if cid == '':
sql = sql + ' and cid=cid'
else:
sql = sql + f" and cid={cid} "
if sex == '':
sql = sql + ' and sex=sex'
else:
sql = sql + f" and sex='{sex} '"
if race == '':
sql = sql + ' and race=race'
else:
sql = sql + f" and race='{race}'"
#print(sql)
return readDB(curObj, sql)
def addStu(Sname,birth,firstday,sex,CID,race='',height=0,hobbies='',remarks=''):
sql = f"insert into student(Sname,birth,firstday,race,sex,height,CID,hobbies,remarks) values('{Sname}','{birth}','{firstday}','{race}','{sex}','{height}',{CID},'{hobbies}','{remarks}')"
#print(sql)
return writeDB(dbObj,curObj,sql)
def updateStu(name,birth,firstday,sex,CID,race='',height=0,hobbies='',remarks=''):
sql=f"update student set birth='{birth}',firstDay='{firstday}',sex='{sex}',CID={CID} "
if race!='':
sql=sql+f", race='{race}' "
else:
sql=sql
if height != 0 or height!='':
sql=sql+f", height={height} "
else:
sql=sql
if hobbies != '':
sql=sql+f", hobbies='{hobbies}' "
else:
sql=sql
if remarks!='':
sql=sql+f", remarks='{remarks}' "
else:
sql=sql
sql=sql+f" where Sname='{name}'"
#print(sql)
return writeDB(dbObj,curObj,sql)
def deletStu(name):
sql=f"delete from student where Sname='{name}'"
#print(sql)
return writeDB(dbObj,curObj,sql)
def getStuSearchInfo():
try:
name, cid, sex, race = input('请输入您要查询的信息(每个信息请以逗号分隔)\n姓名、班级、性别、民族：').split('，')
stutuple = searchStu(name, cid, sex, race)
if len(stutuple) > 0:
for i in range(0, len(stutuple)):
print(
f"学号：{stutuple[i][0]:<7} 姓名：{stutuple[i][1]:<12} 出生日期：{stutuple[i][2]} 入学日期：{stutuple[i][3]} \n性别：{stutuple[i][5]:<7} 班级：{stutuple[i][7]:<14}")
print('民族：', stutuple[i][4], '\t', '身高：', stutuple[i][6], '\t', '爱好：', stutuple[i][8], '备注：',
stutuple[i][9], '\n')
else:
print('您输入的信息不存在，未找到符合条件的记录!')
except:
print('您输入的信息有误！\n请检查输入的信息是否为四个数据、数据之间是否用中文逗号隔开')
def getStuAddInfo():
try:
name, birth, firstday, sex, cid = input('请输入必填信息(每个信息使用中文逗号分割开)\n姓名、出生日期、入学日期、性别、班级：').split('，')
race, height, hobbies, remarks = input('请输入选填信息(每个信息使用中文逗号分隔开)\n民族、身高、爱好、备注：').split('，')
if name != '' and birth != '' and firstday != '' and sex != '' and cid != '':
cid = int(cid)
if height == '':
height = 0
if sex == '男' or sex == '女':
result = addStu(Sname=name, birth=birth, firstday=firstday, race=race, sex=sex, height=height, CID=cid,
hobbies=hobbies, remarks=remarks)
if result == 0:
print('新增信息成功！')
elif result == -1:
print('新增信息失败！')
else:
print('性别输入错误，请选填“男”或“女”')
else:
print('必填信息不可为空！')
except:
print('信息输入不符合规范！请输入符合规范的四个数据并使用中文逗号分隔数据！')
def getStuUpdateInfo():
name = input('请输入您的姓名：')
if name != '':
searchResult=searchStu(name,cid='',sex='',race='')
if len(searchResult) >0:
try:
birth, firstday, sex, cid = input('请输入您要修改必填的信息\n出生日期,入学日期,性别,班级,：').split('，')
if searchResult[0][4] == '':
race = ''
print('民族信息不可更改')
else:
race = input('请输入选填信息民族：')
if searchResult[0][6] == 0:
height = 0
print('身高信息不可更改')
else:
height = input('请输入选填信息身高：')
if searchResult[0][8] == '':
hobbies = ''
print('爱好信息不可更改')
else:
hobbies = input('请输入选填信息爱好：')
if searchResult[0][9] == '':
remarks = ''
print('备注信息不可更改')
else:
remarks = input('请输入选填信息备注：')
if name != '' and birth != '' and firstday != '' and sex != '' and cid != '':
cid = int(cid)
result = updateStu(name=name, birth=birth, firstday=firstday, sex=sex, CID=cid, race=race,
height=height, hobbies=hobbies, remarks=remarks)
if result == 0:
print('信息修改成功！')
elif result == -1:
print('信息修改失败')
except:
print('您输入的信息不符合规范！\n请检查输入的信息是否为四个数据、数据之间是否用中文逗号隔开')
else:
print('您输入的姓名不存在，未找到符合条件的记录！')
else:
print('姓名不可为空，请重新输入')
def getStuDeleteInfo():
name=input('请输入您要删除的学生姓名：')
if name!='':
searchResult = searchStu(name, cid='', sex='', race='')
if len(searchResult) > 0:
result=deletStu(name)
if result==0:
print('删除信息成功！')
elif result==-1:
print('删除信息失败')
else:
print('您输入的姓名不存在，未找到符合条件的记录！')
else:
print('姓名不能为空，请重新输入！')
if __name__ == '__main__':
while True:
dbObj, curObj = openDB(host='localhost', user='root', passwd='123456', port=3306, db='school')
option = input('请输入您要进行的选择1.search 2.add 3.update 4.delete：')
if option.isdigit() and len(option) == 1:
if option == '1': # 查询
getStuSearchInfo()
elif option == '2': # 新增
getStuAddInfo()
elif option == '3': # 修改
getStuUpdateInfo()
elif option == '4': # 删除
getStuDeleteInfo()
else:
print('非法输入！请重新输入')
closeDB(dbObj,curObj)
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）